MS SQL Server - creating index on calculated field in table -sql 2000

Author

creating index on calculated field in table -sql 2000

jaynika

2006-03-21, 3:24 am

Hi all,

we have a table with these columns,

col1 (int)
col2(bigint)
col3 ((convert(varchar(10
),[col1]) + '_' + convert(varchar(20),
[col2]))
....
...
colN

Where col3 is a unique key for our transaction system..

Question :

(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)

I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.

(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ?? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?

any help is appreciated.

thanks.

Tom Moreau

2006-03-21, 3:24 am

You can create an index on a computed column in SQL 2000. If you're
querying on it often, then it's worth the hit to build it.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jaynika" <jaynika@gmail.com> wrote in message
news:1142892662.549052.163100@i39g2000cwa.googlegroups.com...
Hi all,

we have a table with these columns,

col1 (int)
col2(bigint)
col3 ((convert(varchar(10
),[col1]) + '_' + convert(varchar(20),
[col2]))
....
...
colN

Where col3 is a unique key for our transaction system..

Question :

(1) what will be a performance implications if we query on this 'col3'
field ( where underlying fields are already indexed )
select * from table1 where col3 in (select id from table2 where f1 =
something and f2 = somethingelse)

I tried to see the execution plan but didn't make much sense..I also
tried above query where table1 has 100,000 records.. but didn't see any
impact..
Buy in production, we have seen this type of query taking 3 times more
to return results.

(2) To eliminate above problem, i thought of having index on col3, but
couldn't find any information on 'cost of creating index on caluculated
field'
does any one know how expensive it is to create an index on col3 ?? How
does sql server manage the indexes on calculated field ? What happens
when a new row is inserted ?

any help is appreciated.

thanks.

jaynika

2006-03-21, 8:23 pm

Thanks TOM,

but what happens when a new row is inserted in the table. does SQL
server rebuilds the index for computed cloumn ?

thx again!!!

Doug

2006-03-25, 1:23 pm

no, it wouldnt' rebuild the entire index.

The only difference between calc'd fields and regular fields is the cpu
cycles to calculate the expression.
If it were me, I would be VERY tempted to have a trigger the calculated
and evaluated col3 on insert and update based upon col1 adn col2.
then, just store the data in col3 as your finalized form.
Then, just have an index on col3, without the calculations. I base
that upon the precept that you are going to be using col3 for a bunch
of other things, and you might as well just have the data the way you
are going to need it.
regards,
doug

Sponsored Links