Contents
Is PK a clustered index?
A table cannot have more than one clustered index, although it can have a single composite clustered index (ie the table is physically ordered by eg Surname, Firstname, DOB). The PK is often (but not always) a clustered index.
Should PK be clustered?
Your PK does not need to be your clustered index key.
Is PK indexed?
Yes a primary key is always an index. If you don’t have any other clustered index on the table, then it’s easy: a clustered index makes a table faster, for every operation. YES!
Are primary key indexed?
Yes a primary key is always an index. If you don’t have any other clustered index on the table, then it’s easy: a clustered index makes a table faster, for every operation. If you don’t have one, it’s quite obvious the DTA would recommend one, and put it on the Primary Key column(s) by default.
Is the primary key and clustered index always the same?
Therefore it doesn’t mean that a Primary Key and a Clustered Index is always the same in SQL Server. It is the same by default, but you can change this behavior if you want. The Primary Key constraint is always on the logical level, and the index structure is on the physical level to enforce the constraint itself.
Is the clustered index always the same in SQL Server?
When you look now again at sys.indexes, you can see now that you have a heap table in front of you (a table without a Clustered Index), and that SQL Server has generated an additional Unique Non-Clustered Index to enforce the Primary Key constraint. Therefore it doesn’t mean that a Primary Key and a Clustered Index is always the same in SQL Server.
How are changes to primary key constraints checked?
Changes to primary key constraints are checked with foreign key constraints in related tables. Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table.