CODEWITHSUNDEEP
sql-serverindexingprimary-keyconstraints
Lieven Cardoen
Lieven Cardoen

Reputation: 25969

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]

Upvotes: 26

Views: 48860

Answers (4)

marc_s
marc_s

Reputation: 755187

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! It does. See Kim Tripp's excellent The Clustered Index Debate continues for background info.

So really, every useful table (except for maybe staging tables for bulkload or another few rare cases) ought to have a clustered index. 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.

Upvotes: 42

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59523

It is essentially suggesting that you should make the primary key a clustered index rather than an unclustered one. In most cases, this would be a good thing to do.

Upvotes: 2

gbn
gbn

Reputation: 432541

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...

Upvotes: 7

wRAR
wRAR

Reputation: 25559

It is better because it is clustered.

Upvotes: 1

Related Questions