CODEWITHSUNDEEP
sql-serverindexing
venkat
venkat

Reputation:

SQL Server Indexes

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

Upvotes: 0

Views: 213

Answers (3)

Al W
Al W

Reputation: 7713

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.

Upvotes: 0

Jesper Fyhr Knudsen
Jesper Fyhr Knudsen

Reputation: 7937

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.

Upvotes: 2

tpdi
tpdi

Reputation: 35181

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

Upvotes: 0

Related Questions