Search code examples
sql-serverindexingprimary-keyconstraints

Is a primary key automatically an index?

If I run Profiler, then it suggests a lot of indexes like this one

CREATE CLUSTERED INDEX [_dta_index_Users_c_9_292912115__K1] ON [dbo].[Users] 
(
 [UserId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF) ON [PRIMARY]

UserId is the primary key of the table Users. Is this index better than the one already in the table:

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED 
(
 [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
Solution

  • Every table needs a clustered index and a primary key. By default, the PK is clustered but it can be non-clustered if you want like you're done.

    You have specified a non-clustered PK so the profiler suggests a clustered index...

    Note: a table without a clustered index is called a "heap" because it's a pile of unstructured data...