SQL Server Indexes

Question

What's the Need for going for Non-clustered index even though table has clustered index?

Answer 1

The "need" is to do faster lookups of columns not included in the clustered index.

Answer 2

For optimal performance you have to create an index for every combination used in your queries. For instance if you have a select like this.

SELECT *
FROM   MyTable
WHERE  Col_1 = @SomeValue AND
       Col_2 = @SomeOtherValue

Then you should do a clustered index with Col_1 and Col_2. On the other hand if you have an additional query which only looks up one of the Column like:

SELECT *
FROM   MyTable
WHERE  Col_1 = @SomeValue

Then you should have an index with just the Col_1. So you end up with two indexes. One with Col_1 and Col_2 and another with just Col_1.

Answer 3

Don't get clustered indexes confused with indexes across multiple columns. That isn't the same thing.

Here's an article that does a good job of explaining clustered vs. non-clustered indexes.

In mssql server you can only have one clustered index per table, and it's almost always the primary key. A clustered index is "attached" to the table so it doesn't need to go back to the table to get any other data elements that might be in the "select" clause. A non-clustered index is not attached, but contains a reference back to the table row with all the rest of the data.