creating index on calculated field in table -sql 2000

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.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.|||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!!!|||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