Should primary keys be always assigned as clustered index

sql sql-server clustered-index
AbrahamJP picture AbrahamJP · Jan 4, 2011 · Viewed 8.7k times · Source

I have a SQLServer table that stores employee details, the column ID is of GUID type while the column EmployeeNumber of INT type. Most of the time I will be dealing with EmployeeNumber while doing joins and select criteria's.

My question is, whether is it sensible to assign PrimaryKey to ID column while ClusteredIndex to EmployeeNumber?

Answer

Remus Rusanu picture Remus Rusanu · Jan 4, 2011

Yes, it is possible to have a non-clustered primary key, and it is possible to have a clustered key that is completely unrelated to the primary key. By default a primary keys gets to be the clustered index key too, but this is not a requirement.

The primary key is a logical concept: is the key used in your data model to reference entities.
The clustered index key is a physical concept: is the order in which you want the rows to be stored on disk.

Choosing a different clustered key is driven by a variety of factors, like key width when you desire a narrower clustered key than the primary key (because the clustered key gets replicated in every non-clustered index. Or support for frequent range scans (common in time series) when the data is frequently accessed with queries like date between '20100101' and '20100201' (a clustered index key on date would be appropriate).

This subject has been discussed here ad nauseam before, see also What column should the clustered index be put on?.

Related questions

Why is there a scan on my clustered index?

SQL 2000 The NED table has a foreign key to the SIGN table NED.RowID to SIGN.RowID The SIGN table has a foreign key to the NED table SIGN.SignID to NED.SignID The RowID and SignID are clustered primary …

Read More
Does SQL Server creates Non clustered index by default

Ya, it is a duplicate of this. But I just needs a clarification on this article by Pinal Dave, which says the following: Scenario 4: Primary Key defaults to Clustered Index with other index defaults to Non-clustered index In this case …

Read More
Clustered index on temp table

I'm trying to optimize a procedure that has code like the following: CREATE TABLE #t1 (c1 int, c2 varchar(20), c3(varchar(50)...) CREATE CLUSTERED INDEX ix_t1 ON #t1(c3) ON [PRIMARY] I wanted to improve that by moving the CLUSTERED …

Read More