Is a primary key automatically an index?

Question

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]

Answer 1

It is better because it is clustered.

Answer 2

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

Answer 3

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.

Answer 4

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.