Christopher Stobbs (5/13/2009)
Just a side not for anyone that is interested.I've created a table on my test system with two columsn
VARCHAR(900) --Max size for a clustered index
VARCHAR(32)
even when I create two index one clustered on the first col and one nonclustered on the second column, my query always uses parallelism and always uses the first index(the bigger one) and always runs slow.
I'm going to trying to find out how the optimizer decides which index/column etc to use for a count 🙂
wish me luck
I'm interested to know what will be the results of those queries, and how the optimiser will behave!
I'm guessing a
select count(*) from tableX
should take the most updated statistics or the index with the smaller size. Well, that would make sense, unless all indexes were updated at the same time.
You might as well want to try
select count(smallerIndexedField) from tableX
to see if it changes from the Select count(*), and if it forces the optimiser to choose the index, or at least, help it take the decision?
Anywayz, update this thread if you get a chance, I'm interested in having a conclusion on this, 😉
Good luck,
Cheers,
J-F