can i specify more then one column as Non-cluster index, what will it affect?
A:
Assuming you are asking about how to define multiple indexes, you can define as many non-clustered indexes as you want. It's the clustered index that you can only have one of, since it determines how rows are clumped together as records on disk.
The more indexes you create, the longer it will take to perform insert, update and delete operations.
Marcelo Cantos
2010-05-28 09:23:43
A:
Yes, you can have multiple columns in an index, be it clustered or non-clustered. It is very common. It's effect depends entirely on the data in your table.
Tommi
2010-05-28 09:25:38
+1
A:
Like other people have said, this will have an overhead in terms of maintaining the table, it's just a case of whether the benefits outweigh the cost.
The only reasons I can think of for doing adding a multiple-column non-clustered index are to speed up queries where you regularly search based on a combination of fields, or to make a combination of fields unique in the table. If those are your aims then generally I'd say go for it.
Martin
2010-05-28 10:19:51