SQL Server Indexes

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

From stackoverflow

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

• 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.

  Arvo : No need for two indexes in this case - enough to have index on Col_1 and Col_2. Same index will be effectively used for lookup Col_1 values either.

• 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.