CODEWITHSUNDEEP
sql-servert-sqlclustered-index
MattB
MattB

Reputation: 2241

What is the proper use of a clustered index?

I might not be framing this question correctly. By use, I don't mean where and when should I create one on a table - that would be an overly broad question.

What I mean is once I create a clustered index, does it improve performance in general or do I need to use the associated column in queries to get the performance boost?

Here's an example: Suppose I create a table with the following columns; RowNum, FileId, Name, and Date. RowNum I create as an identity column and I apply a clustered index. However, in practice the table is generally queried using FileId. For example:

SELECT
    FileId, 
    Name
FROM MyTable
WHERE FileId IN ('11101101', '11101201', '11101301')

Since the RowNum is not used in the query, do I still get any performance benefit from the index?

I've been reading up on constraints and indexes and I want to be certain I understand them. This seems to be a point that is glossed over in everything I read.

Edit: I think I've got my answer. Or at least as close to a clear-cut answer as I'm going to get.

Let me restate the question a little: What I was trying to sort out is suppose I have a table that has three columns, rowNum, Id, and Name. This table will generally be queried on Id or Name, and let's go one step further and say that we will have nonclustered indexes on each of those columns. My question was, under this scenario, does a clustered index on rowNum improve the performance of the queries that use the other columns.

As best I can gather, the answer is yes, but you might want to consider putting the clustered index on another column.

Upvotes: 0

Views: 568

Answers (3)

iDevlop
iDevlop

Reputation: 25252

Short answer is that you need to have 1! clustered index for each table, and it is often the PK. THe PK is the right candidate if it is counter (meaning new rows will go at the end of the table). There are plenty of discussions on SO (like this one)and on the net about this.

Upvotes: 0

jean
jean

Reputation: 4350

As many questions concerning permformance and indexes the right answer is: it depends.

A clustered index means your table ill be "physical" ordered by that column(s) (and that's why you only can have a single clustered index in a table). Also it's the reason why using not sequential valued column for that index is a bad idea.

Also in MSSQL Server, if you got a unique clustered index in a table any other index you create ill "implicit" include the clustered index.

General speaking...

A clustered index is good for selects when you do lots of filter/ordering over its column(s).

Also its very usual to use it on natural or surrogate keys like @Frisbee commented.

A clustered index in not good for inserts/updates and is really bad when you alter the value/insert not sequential values on the clustered index column(s) because the engine ill try to keep the index B-tree balanced and ordered.

The only way to be sure you are using a index the correct way is by acid testing it (with a bloated DB) and studing its actual query plan.

I suggest you look for sites like MSDN and SQL Server Central and learn more about indexes since it's a topic too broad for this answer.

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

If the table has a natural primary key then that is a good candidate for the clustered index.

In your case RowNum is an identity PK on a clustered index. This would be good for finding rows by RowNum and would be good for joins.

Some times you see a PK or other index used in a query that does not even seems like it uses the column.

The query you posted would benefit from a non-clustered index on FileId.

If FileId is unique then consider it for PK and skip the RowNum.

Upvotes: 1

Related Questions