CODEWITHSUNDEEP
sql-server
Kasper Brandenburg
Kasper Brandenburg

Reputation: 212

Whats the difference between Primary key in table definitions vs. unique clustered index

What is the difference between defining the PK as part of the table definition vs. adding it as a unique clustered index. Using the example below, both tables show up as index_id 1 in sys.indexes, but only table1 has is_primary_key=1

I thought this was the same, but SSMS only shows the key-symbol on table1

Thanks.

CREATE DATABASE IndexVsHeap
GO
USE [IndexVsHeap]
GO

-- Clustered index table
CREATE TABLE [dbo].[Table1](
    [LogDate] [datetime2](7) NOT NULL,
    [Database_Name] [nvarchar](128) NOT NULL,
    [Cached_Size_MB] [decimal](10, 2) NULL,
    [Buffer_Pool_Percent] [decimal](5, 2) NULL
    CONSTRAINT [PK_LogDate_DatabaseName] PRIMARY KEY(LogDate, Database_Name) 
) 

-- Table as heap, PK-CI added later, or did i?
CREATE TABLE [dbo].[Table2](
    [LogDate] [datetime2](7) NOT NULL,
    [Database_Name] [nvarchar](128) NOT NULL,
    [Cached_Size_MB] [decimal](10, 2) NULL,
    [Buffer_Pool_Percent] [decimal](5, 2) NULL
) 

-- Adding PK-CI to table2
CREATE UNIQUE CLUSTERED INDEX [PK_LogDate_Database_Name] ON [dbo].[Table2]
(
    [LogDate] ASC,
    [Database_Name] ASC
) 
GO

SELECT object_name(object_id), * FROM sys.index_columns
    WHERE object_id IN ( object_id('table1'),  object_id('table2') )
SELECT * FROM sys.indexes
    WHERE name LIKE '%PK_LogDate%'

Upvotes: 1

Views: 70

Answers (3)

HLGEM
HLGEM

Reputation: 96552

Also remember that while a table can have only one PK, it could have multiple unique indexes (although only one can be clustered).

I can see where you might want to cluster on information that is unique in some meaningful way but might want to have a separate autogenerated nonclustered PK to make joins faster than joining on the automobile VIN number, for instance. That is why both are available.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452988

To all intents and purposes there is no difference here.

A unique index would allow null but the columns are not null anyway.

Also a unique index (though not constraint) could be declared with included columns or as a filtered index but neither of those apply here as the index is clustered.

The primary key creates a named constraint object that is schema scoped so the name must be unique. An index must only be named uniquely within the table it is part of.

I would still opt for the PK though to get the visual indicator in the tooling. It allows other developers (and possibly code) to more easily detect what is the unique row identifier.

Upvotes: 1

Francisco Goldenstein
Francisco Goldenstein

Reputation: 13767

Primary key is a key that identifies each row in a unique way (it's a unique index too). It could be clustered or not but it's highly recommended to be clustered. If it is clustered, data is stored based on that key.

A unique clustered index is a unique value (or combination of values) and the data is stored based on that index.

What's the advantage of a clustered index? if you have to an index scan (scan the whole index), data is stored together so it's faster.

Upvotes: 0

Related Questions