0

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?

Sorry in advance if this is something of a rudimentary question. 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.

This was a really broad question and I appreciate the insight everyone offered. I'm as close to a good answer as I'm going to get given the relevant facts, and I'm much more knowledgeable about indexes now. Thanks again!

MattB
  • 2,203
  • 5
  • 26
  • 48
  • Possible duplicate of [Reasons not to have a clustered index in SQL Server 2005](http://stackoverflow.com/questions/4034076/reasons-not-to-have-a-clustered-index-in-sql-server-2005) – iDevlop Jan 12 '16 at 16:18
  • 4
    Read [The Clustered Index Debate Continues....](http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/) by Kimberly Tripp - she shows that a **well-chosen** clustered index can speed up all your operations - **yes**, even `INSERT` or `DELETE` operations! Read all of Kimberly's great articles about clustered indexes - she's the *Queen of Indexing* and **really knows her stuff**! You'll learn a great deal from her blog posts – marc_s Jan 12 '16 at 16:19
  • You also might be interested in [Unreasonable Defaults: Primary Key as Clustering Key](http://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key) –  Jan 12 '16 at 22:21

3 Answers3

1

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.

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

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.

Community
  • 1
  • 1
iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • No, you don't need to **have** one clustered index for each table. There are also reasons to _not_ have one. –  Jan 12 '16 at 22:17
  • Not sure why you were downvoted. I don't know that anybody really got to what I was asking directly, but this answer took me down the right path so I'll mark it. – MattB Jan 19 '16 at 23:54
  • and the PK is often NOT the best candidate for a clustered index – Paul Maxwell Jan 20 '16 at 00:06
0

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.

Community
  • 1
  • 1
jean
  • 4,159
  • 4
  • 31
  • 52