CODEWITHSUNDEEP
sqlsql-server-2005
Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

What is advantages of non clustered index over primary key (clustered index)

i have got a table (stores data of forum, means normally no edit and update just insert) on which i have a primary key column which is as we know a clustered index. please tell me, will i get any advantage if i creates a non-clustered index on that column (primary key column)?

EDIT: my table has got currently around 60000 records, what will be better to place non-clustered index on it or create a same new table and create index and then copy records from old to new table.

Thanks

Upvotes: 2

Views: 14415

Answers (4)

Rahul mishra
Rahul mishra

Reputation: 82

The only possible advantage that I can see could be from the fact that the entries on leaf pages of nonclustered index are not as wide. They only contain index columns while the clustered index' leaf pages are the actual rows of data. Therefore, if you need something like select count(your_column_name) from your_table then scanning the nonclustered index will involve considerably smaller number of data pages. Or if the number of index columns is greater than one and you run any query which does not need data from non-indexed columns then again, nonclustered index scan will be faster.

Upvotes: 2

gbn
gbn

Reputation: 432667

  • Every table should have a clustered index
  • Non-clustered indexes allow INCLUDEs which is very useful
  • Non-clustered indexes allow filtering in SQL Server 2008+

Notes:

  • Primary key is a constraint which happens to be a clustered index by default
  • One clustered index only, many non-clustered indexes

Upvotes: 8

littlegreen
littlegreen

Reputation: 7420

A clustered index specifies the physical storage order of the table data (this is why there can only be one clustered index per table).

If there is no clustered index, inserts will typically be faster since the data doesn't have to be stored in a specific order but can just be appended at the end of the table.

On the other hand, index searches on the key column will typically be slower, since the searches cannot use the advantages of the clustered index.

Upvotes: 3

Tony
Tony

Reputation: 10357

One advantage: you can INCLUDE other columns in the index.

Upvotes: 4

Related Questions