1

I've a table which is defined like this

CREATE TABLE [dbo].[MyTable](
    [MyTableId] [bigint] IDENTITY(1,1) NOT NULL,
    [SomeTable2Id] [bigint] NOT NULL,
    [SomeTable3Id] [bigint] NOT NULL,
    [SomeData] [smallint] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED 
(
    [MyTableId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Apart from above PK_MyTable NONCLUSTERED index, I've few other NONCLUSTERED indexes over SomeTable2Id and SomeTable3Id

I think creating CLUSTERED index above makes more sense but I'm wondering is there any good justification for NOT creating a CLUSTERED index and instead creating NONCLUSTERED?

PS There are lot of questions asked over these topics but couldn't find relevant one (in top 20 list). If this has been asked, please redirect me to the related question post.

EDIT: Consider a case where MyTable is mapping two other tables SomeTable2 and SomeTable3 and instead of having composite key, we have this MyTableId So most of the time my queries have either SomeTable2Id or SomeTable3Id and request to get other id. So in this light of usage of this table, do we really need to bother with creation of a clustered index over MyTableId or two nonclustered indices over SomeTable2Id and SomeTable3Id are sufficient?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Ankush
  • 2,454
  • 2
  • 21
  • 27

2 Answers2

4

No, you definitely should have a clustered index there. That Identity field is an ideal cluster key as well.

Here are some good articles from Kim Tripp regarding clustered indexes:

Clustered Index Debate

Clustered Index Debate Continues

Here is a whitepaper from MS about this topic as well.

JNK
  • 63,321
  • 15
  • 122
  • 138
  • 1
    +1 Kim Tripp's the ultimate resource on indexing in SQL Server, for sure! :-) – marc_s Jul 19 '11 at 19:52
  • 1
    @marc - she knows her stuff, and I like that she normally includes easy-to-reproduce examples. Gail shaw is great for that too. – JNK Jul 19 '11 at 19:53
  • does it matter if table has a foreign key referencing itself? e.g. employee table with managerid column. – Ankush Jul 19 '11 at 20:57
1

I think you definitely should have MyTableId as your the key in a clustered index without further information about how the table is actually used.

Remember, you are choosing between the table being a clustered index or a heap. A clustered index is not an index on the table, but identifies that the table is stored in a B-tree based on the clustered index key.

On both kinds of tables, non-clustered indexes can also be used.

For read performance (particularly on wider tables), the non-clustered indexes (potentially with included columns) are going to be where you are looking for gains.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Thanks. I have one concern. In my case this table acts as mapping table between two different table. I'm going to be using this table for lookup. Most of the time, I won't be using it's primary key. Does that make difference? – Ankush Oct 15 '11 at 09:11
  • @Ankush A primary key always has an index (whether or not it is the choice of clustered index for the table). If you are going to have access through other methods, consider also indexing on those keys (either clustered or non-clustered). The choice of clustering index should be unique, narrow, static, and increasing. If you have several candidates for this, I would pick the one which is most likely to be used in range queries, if not, probably an identity (as in your case). – Cade Roux Oct 16 '11 at 15:24
  • I understand that, but this doesn't address my concern in above comment. I guess you mean it **DOESN'T** make difference and go with clustered index on primary key. Right? – Ankush Nov 17 '11 at 13:29
  • @Ankush Because you are using an identity (unique, static, narrow, increasing) and it is the primary key, I would go ahead and make that the clustered index. This means your table is going to have some maintenance advantages (as the row bookmark in all the nonclustered indexes for example) over any other choice of clustered index and also over a heap. Yes, the non-clustered indexes should be used, but they should be narrower and more efficient than if they were used on the heap and you should see quite different execution plans with more index seeks. – Cade Roux Nov 17 '11 at 15:21