Tags: Database Index , MSSQL

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.

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...

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...

• MSSQL: How to find all database indexes on column?
• Execution Plan in MSSQL Server
• MSSQL: Queries to 'Show All Database Indexes' and 'Drop All Database Indexes'
• MSSQL list of useful queries - Part 1 (6th point)

Tags: Database Index , MSSQL