Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Why Primary Key constraint creates Clustered Index on the PK column by default?
Can we create a table which has a primary key, but NO clustered index. I need PK constraint to maintain the unique values + not null on the column, but i do not want a clustered index as there are too inserts in this table.
I have read that having clustered index has performance impacts on inserts.
Why Primary Key constraint creates Clustered Index on the PK column by default?
That is what MS-SQL-Server programmers decided the default to be. A good clustered index is one that has unique values (as the Primary Key), is narrow (as most primary keys are or at least should be) and is ever-increasing. So, most of the times, the primary key is a good (or the best) choice for the clustered key (there can be at most one clustered key per table).
Can we create a table which has a primary key, but NO clustered index?
Yes, you can. By explicitely defining all indices and especially the primary key as non-clustered. If you think that you don't need a clustered key on a table, you can do that and have the primary key as non-clustered. The unique and not null constraints will still be enforced.
You should be ok to define the PK explicitly as NonClustered, and it won't be.
If you don't say anything, and there isn't already a clustered index, then the PK will created clustered by default.
If your clustered primary key is sequential, then inserts to the end are not a problem. It's only when you insert a record where the primary key value is in the middle of existing values. This will re-sort the data if the primary key is clustered.
If you choose not to use a clustered primary key, there can still be performance issues if the data of the primary key splits across database pages. You should look at the fill factor and make sure it is large enough to handle inserts in the middle of the index without page fragmentation.
Here are a couple of good references:
You could use a contraint on the table to maintain the constraint you want.
You could create a unique index on the column.
However, I am not familiar with performance issues with primary keys. If you have an auto-incrementing primary key (identity), and are only inserting rows at the end, then performance should not be an issue.
