After adding 200000 rows to same table the out put has changed in my most questions , the observation is asper below

SELECT * FROM TEST101

--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?

--same in second test

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.

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

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

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

but still in some cases i m confuse abt optimizer selecting which index...

but now i will go thru this links 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 and then again will come with new doubts...

thanks gaillllll, thank u very much, have a greta day

Mithun