MSSQL: Queries that do not return large result sets. Nonclustered Index limitation

UPDATED: 24 March 2014
What is Nonclustered Index?
A nonclustered index contains the index key values and row locators that point to the storage location of the table data.

Queries that do not return large result sets. - Nonclustered Index Design Guidelines
Source: http://technet.microsoft.com/en-us/library/ms179325(v=sql.105).aspx

We are using hibernate in our project to support multiple dialect. Its our routine process to get queries that are generated by hibernate. We do analysis on that queries and suggest indexes and other stuffs. We replaced ? in query with 0. We fired that query in Microsoft SQL Server Management Studio and Execution plan suggested index. We created index with bit modification. After executing same query again what we see is index is not used by query.

We were clueless why is database not using index. We went through lots of article but no one wrote specifically for this rule of nonclustered index. We did few more run on different tables and queries. We were not able to derive perfect rule that...

Database will not use Nonclustered Index when Its returning 'n' records.
It's yet to be discovered from my side. If you guys have perfect rule for this do share in comment. I'll put here if its really a case.

Case Scenario:
SELECT * 
FROM s_master this_ 
LEFT OUTER JOIN t_master tlist2_ 
ON this_.id = tlist2_.s_id 
WHERE this_.document_id = 0 
AND this_.library_id = 0
This query returned 2248 records and as Microsoft SQL server design guide line suggests, query won't use index. We replaced 0 with specific value and It started using Index.

Conclusion:
Nonclustered Index is only useful when you are sure that your query won't return much records.

You'd like to read more about index, scroll through this articles...

0 comments :