You should read Gail's articles first. They'll answer a lot of your questions. You might want to pick up Kalen Delaney's book Inside SQL Server 2005: The Storage Engine to get some idea of how things work within SQL Server.
mithun.gite (4/28/2009)
After adding 200000 rows to same table the out put has changed in my most questions , the observation is asper belowSELECT * FROM TEST101
--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?
--same in second test
For the same reason as before. The clustered index defines not only search criteria for an index, but the storage of the data itself. So when you have a table with a clustered index, the cluster is always used for data retrieval (except in the case of covering indexes, etc.)
SELECT * FROM TEST101
WHERE ID=5
ORDER BY CODE
--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?
--Now it has changed to Clustred index seek, this is normal.
There's enough data in the index for it to be selective enough to find what it needs by pulling out a particular value. By the way, since I assume this only will ever return a single row, the ORDER BY clause is pretty redundant.
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" when it is having 3 three diffrent index on three diff columns.
--same in second test
Because you're using the ID value of the clustered index and the ID is unique, the rest of the values won't really matter. The optimizer is pretty smart.
SELECT * FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
--Now it is going for non clustered index, this is normal
This is an example of a covering index, so it never even goes to the cluster to return since all the columns are included in the nonclustered index (including the cluster key column, ID).
SELECT * FROM TEST101
WHERE ID=5
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
--Now this is going for clustered index, this is notrmal
SELECT CODE FROM TEST101
WHERE CODE=105
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
-- same it is going for non clustered index
Yep, all these are correct.
Definitely read through Gail's articles. I'd also strongly suggest you pick up the book recommend above. There are other books out there that will help as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning