Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Folks

I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed

  1. many non clustered index with single columns and non clustered index with many columns combined.

  2. Or ideally under what scenario I can combine columns to make a single non clustered index and if so, how many columns should I combine ?

  3. What is the criteria to choose the column for non clustered index ? (I am aware that its applied only to not frequent updation table)....

  4. Whats the difference between 2005/2008 and 2012 SQL Server in terms of non clustered index with regard to my question and also is it 249 is the max we can create ?

Thank you in advance.

share|improve this question

migrated from stackoverflow.com Jul 15 '12 at 9:48

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

up vote 3 down vote accepted

Having many indices requires SQL Server to maintain and update many indices for each insert, delete or update statement. So having less indices is generally better.

However, when you have a compound index (made up of multiple columns), that only helps if you use/specify the n left-most columns in your query.

So if you have an index on (City, LastName, FirstName) for an address table, that index might be used if

  • you have all three columns in your WHERE clause
  • you specify WHERE City = 'London' in your query (using the 1 left-most column)
  • you specify WHERE City = 'London' and LastName = 'Smith' in your query (using the 2 left-most columns)

However, such an index cannot ever be used for:

  • finding all rows with a WHERE FirstName = 'Joe' clause - you're not using the n left-most columns in that index
  • finding all rows with a WHERE LastName = 'Brown' AND FirstName = 'Charlie' clause - again: you're not using the n left-most columns in that index

A compound index cannot (in many cases) replace all one-column indices. Be very careful when designing and creating compound indices to make sure you design them in a way they'll really be useful!

So while compound indices do have their reason to be, and they're generally preferable - you also need to make sure they'll really be able to be used. An index that is just being maintained (and causes effort on SQL Server's side), but can never be used in any queries, is the worst index ever - no gain, only maintenance overhead.

You should also read Kimberly The Queen of Indexing Tripp's excellent blog posts on SQL Server indexing - most notably Indexes: just because you can, doesn't mean you should!

share|improve this answer
    
Hi Marc that really a great amount of valuable information and it helps me a lot, thank you very much for sharing. I will take a look at this link as well. Cheers –  Divine Jul 14 '12 at 9:29
add comment

It depends. If most of queries are using just one column in the where clause or on join clause. Then single column nc index should be fine.But when you have a combination of columns in where clause then using the combined indexes should be preferred.

You can create 999 NC indexes. Also, you could use the include clause in indexes to make more indexes covering.But note that creating unncessary indexes might slow down you DML opertaions and will cause an increase in the size of the table and indexes.

share|improve this answer
    
Thank you for sharing your thoughts, it helps me. Well 999 NC in which version ? Well, whats that include clause thing you are talking about ? I dint understood that statement much. –  Divine Jul 14 '12 at 6:23
1  
I work on 2008 R2 and I know there are 999 indexes there.Not sure about 2005. –  Gulli Meel Jul 14 '12 at 7:17
    
Include clause is quite useful feature specially to remove key lookups and thus improving the perf. It is used to have more covering indexes.Also you can have columns in include which are not allowed as the key of the column e.g. varchar(max).Also,you have limitation of 900 bytes on index key.Thus biug size columns can be specified in include clause. More details are here. msdn.microsoft.com/en-us/library/ms190806.aspx –  Gulli Meel Jul 14 '12 at 7:22
    
Hi Gulli, that really helps me a lot, thank you for your valuable reply :) Cheers –  Divine Jul 14 '12 at 9:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.