One small Sql tuning issue

[spaliwal]

Hi
I am using sql server 2000.In a cursor stmt I am using

Select * from tab where Isnull(IsMigrated,0) = 0

in the same cursor I am Updating IsMigrated = 1

update tab set IsMigrated = 1

IsMigrate field will have 1 /0 for Yes/No flagging. I have made a Clustered index on it , vol of recs is in lacs . Pl. advise me wether it is a wise descision to put index on IsMigrated column or not.

 

[balachandar]

if there are going to be null values , indexing is not adviced

 

[aalmeida]

No it is not wise to put an index on it because it is not very selective. Remenber that all indexes do is to create a new set of data ordered by the index order, and a bit colum that has only 1 or 0 values would put half of the data in one group and the other half in another group, to finde something you would setill have to run a table scan. Read the BOL article "Designing an Index" for more info. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[fluteplr]

I agree with the above with the following exception. If the difference between the number of 0 and 1 values is very high, say 10% 1's and 90% 0's and you will be looking for the 1's it may be useful to have an index. Testing is really the only way to find out.

 

[aalmeida]

I could agree with fluteplr my only issue is that if the 1 or 0 change often to the same record the index would reduce over all performance to the server because it has to be recreated to often what causes too many I/O hits to the server. AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"