Non clustered indexes

Question

How to know which index is required on the table. What is the main logic to create a non clustered index.?? Please explain. I am very much confuse in indexing.

Aarti · Answer

Hi Ravi The fewer indexes you have - the better. Each index might speed up some queries - but it also incurs overhead and needs to be maintained. Not so bad if you don't write much to the table. If you can combine multiple columns into a single index - perfect! But if you have a compound index on multiple columns that index can only be used if you use/need the n left-most columns. compound indexes are beneficial and desirable - but only if you can really use them! Having just one index with your all where clause columns does not help you at all if you need to select the columns individually. You should generally add non-clustered indexes to columns that are used as foreign keys.

Vilas Gite · Answer

Dear Ravi Shekhar It is like a book with Index Page on which page which chapter is located and if there are are human error for binding of pages then we can alter easily in index page. :) This is real life example of Indexing. --- A cluster index alters the way which rows are stored in table. A non-cluster index does not alter the way the rows which are stored in table.

Ravi Shekhar · Answer

Hi Vilas I know the basics meaning of Index. I just want to know that suppose I have put a sql query for search anything on the basis of some columns . In this scenario should i put non clustered index including my all columns in where clause??? is it the right way???

Ravi Shekhar · Answer

I want to know that how my non clustered index works ?? can anyone explain in simple text???

Jignesh Trivedi · Answer

hi nonclustered index is required to speed up your SQL query. Please refer http://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx http://www.mssqltips.com/sqlservertutorial/2906/choosing-nonclustered-indexes/ hope this will help you.