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, 2010 at 05:33 PM in Default

avatar image

535 41 42 47

(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.


more ▼

answered Jun 16, 2010 at 06:11 PM

avatar image

20.5k 3 7 29

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jun 16, 2010 at 05:33 PM

Seen: 1110 times

Last Updated: Jun 16, 2010 at 05:33 PM

Copyright 2018 Redgate Software. Privacy Policy