mithun.gite (4/27/2009)
SELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?
Because there's no filter of any form. That query reads the entire table, hence the only way to do it is to scan the clustered index
SELECT CODE FROM TEST101
WHERE ID=5
ORDER BY CODE
--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?
Probably because the optimiser thinks that it's faster to scan the index on code and discard rows that don't match the ID than to seek on the cluster and then have to sort the resulting row set.
All the nonclustered indexes have the clustering key included.
SELECT CODE FROM TEST101
WHERE ID=5 AND CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID"
Because SQL generally only uses one index per table when producing a query plan and since the only index that has ID, Code and AcNo is the clustered index, it will seek on that one
SELECT CODE FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
Probably because it thinks that the two nonclustered indexes aren't selective enough and that it's cheaper to scan the cluster than to seek on either index and have to do bookmark/key lookups
SELECT CODE FROM TEST101
WHERE ID=5
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
It shouldn't be. That one should seek on the cluster. Possibly because the table is so small the scan of the noncluster is slightly faster
SELECT CODE FROM TEST101
WHERE CODE=105
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
Because you're filtering on Code
Try adding a lot more data (at least 10000 rows) and try again. I'm guessing that you'll see quite a different behaviour on some of them. 10 rows is far to little to do meaningful analysis of indexing.
As for links, try these:
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability