A primary key is simply a candidate key labelled as the ‘primary’ (see more in A Key to Keys), and they are enforced by the same mechanism in Sql Server – the unique index. We can query the system tables to get a quick list of these.
select t.name as TableName, i.name as IndexName, i.is_primary_key, c.name as ColumnName, c.is_nullable, i.type_desc from sys.tables t inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id inner join sys.columns c on c.object_id = t.object_id and c.column_id = ic.column_id where i.is_unique = 1 order by t.name asc, i.is_primary_key desc, i.name asc, c.name asc;
Here’s an example table:
…and the results of the query:
You might notice that the PK is not clustered. In Sql Server any index can be clustered, it does not have to be the PK and it does not have to be unique.