Non-clustered index in sql server

Question

can i specify more then one column as Non-cluster index, what will it affect?

• Pranay Rana: Check this link : databases.about.com/od/sqlserver/a/indextuning.htm

Answer 1

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.

Answer 2

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.

Answer 3

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.