2

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

Dr. Rajesh Rolen
  • 14,029
  • 41
  • 106
  • 178
  • 1
    possible duplicate of [What are the differences between a clustered and a non-clustered index?](http://stackoverflow.com/questions/91688/what-are-the-differences-between-a-clustered-and-a-non-clustered-index) – Neil Knight Nov 25 '10 at 10:02
  • @Ardman:It not duplicate of that, i know difference between clustered and non-clustered but wants suggestions for my specific situation. – Dr. Rajesh Rolen Nov 25 '10 at 10:13
  • Commenting on your edit to the question: You should just add the new index to the existing table, have a look at the ALTER TABLE command. 60,000 rows isn't much and it shouldn't take long to create the index. – Tony Nov 25 '10 at 10:16
  • Perhaps you should edit the question title to be more specific, such as "What are the advantages to adding a non clustered index to a table using the same column as a primary key clustered index?" – Tony Nov 25 '10 at 10:25

4 Answers4

7
  • 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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I believe, for what is worth, that the answer from "Rahul mishra" is a much better answer to the question in the OP. For example, the clustered indexes do not allow the INCLUDEs, but they do already contain all the columns, as if you've used one INCLUDE with all the columns, so that's not a good point for comparison – userfuser Feb 08 '23 at 11:12
4

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

Tony
  • 9,672
  • 3
  • 47
  • 75
3

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.

littlegreen
  • 7,290
  • 9
  • 45
  • 51
2

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.

Rahul mishra
  • 82
  • 3
  • *possible* indeed (and not *certain*), because less physical reads does not necessarily translate to a query being faster. Random IO can be 100x slower than sequential IO (taken from [here](https://stackoverflow.com/a/27193964/3002584)) – OfirD Mar 02 '22 at 09:00