shreekanth.kavali (1/20/2013)
can any body answer this question ? In what cases developer use cluster and non cluster index .
That's a little too vague to be answerable. Most likely a clustered index. Maybe one or more nonclustered indexes too.
suppose 40,000 rows are there in table .In that case which index will it use ?
No way to answer that. Depends on the query
Maybe take a read through these:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/
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