x

which index is best

Hi can anyone please tell me,which is best option. To create a clustered index or non clustered index on a column which is often used by 'where' condition,and there is no primary key in this table.

more ▼

asked Jun 16 '10 at 05:33 PM in Default

srivivek gravatar image

srivivek
519 39 42 43

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If the table does not have a primary key, it does not necessarily mean that it does not have a clustered index (primary key can be enforced by a unique non-clustered index). While it is legal to have heap tables (the ones which don't have a clustered index), it is definitely not a good practice (though there are rare scenarios when the heap tables are justified). However, just the fact that there is a column in the table which is "often used by 'where' condition", it does not yet mean that such column is a good candicate for a clustered index. There are 2 bare minimum requirements which should be met to satisfy the clustered index candidate criteria:

  • The values should be ever increasing (every new record inserted into a table must have the clustered column(s) value(s) greater than those in existing records)

  • The column(s) should be as narrow as possible. This is because clustered index columns's values become a part of of the leaf pages of every other existing nonclustered index.

And finally, a table can have only one clustered index.

The best introduction to indexes I ever read were the 3 part series by Gail Shaw, beginning from this one: http://www.sqlservercentral.com/articles/Indexing/68439/ Please read them as they are enormously helpful.

Oleg

more ▼

answered Jun 16 '10 at 06:11 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

That reminds me... I have a blog post to write on the subject of what not to do...
Jun 16 '10 at 07:02 PM ThomasRushton ♦
+1 Leaving nothing for the rest of us with answers like that.
Jun 16 '10 at 07:12 PM Grant Fritchey ♦♦
+1 from me .
Jun 16 '10 at 07:36 PM Fatherjack ♦♦
Thank you. I could as well reduce it with just the link to Gail's series, they are truly the best on the subject. And this is a serious staff too. I know, for example, that there will always be an open argument about 1) int PK + non-clustered guid or 2) just a clustered guid, but just the fact that 10 mln records table with 5 non-clustered indexes will create half a gigabyte worth of pure waste if number two is chosen, will make one consider his indexes design wisely, and it is even before even thinking about page splits should the clustered values fail to comply with ever-increasing criteria.
Jun 16 '10 at 07:41 PM Oleg
+1 - this is what I was talking about in my other comment somewhere - high quality again here...
Jun 16 '10 at 08:25 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x79

asked: Jun 16 '10 at 05:33 PM

Seen: 823 times

Last Updated: Jun 16 '10 at 05:33 PM