Relationship of Primary Key and Clustered Index

sql sql-server database database-design
F11 picture F11 · Feb 24, 2013 · Viewed 64k times · Source

Can a TABLE have a primary key without a clustered index?

And can a TABLE have a clustered index without having a primary key?

Can anybody briefly tell me the relationship between primary key and clustered index?

Answer

Neville Kuyt picture Neville Kuyt · Feb 24, 2013

A primary key is a logical concept - it's the unique identifier for a row in a table. As such, it has a bunch of attributes - it may not be null, and it must be unique. Of course, as you're likely to be searching for records by their unique identifier a lot, it would be good to have an index on the primary key.

A clustered index is a physical concept - it's an index that affects the order in which records are stored on disk. This makes it a very fast index when accessing data, though it may slow down writes if your primary key is not a sequential number.

Yes, you can have a primary key without a clustered index - and sometimes, you may want to (for instance when your primary key is a combination of foreign keys on a joining table, and you don't want to incur the disk shuffle overhead when writing).

Yes, you can create a clustered index on columns that aren't a primary key.

Related questions

What does ON [PRIMARY] mean?

I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[be_Categories]( [CategoryID] [uniqueidentifier] ROWGUIDCOL NOT …

Read More
Is it good database design to have admin users in the same table as front-end users?

I have users who can login on a front-end page, and admins who can login on an admin page. Should both users and admins be "Users" with different roles, or should they be split in different tables?

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More