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]

Answers


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


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.


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.


It is better because it is clustered.


Need Your Help

Custom Resizable Handles in JQuery UI

jquery html jquery-ui user-interface resizable

I've been attempting to create a resizable textbox (ASP.NET multiline TextBox / HTML textarea) and use JQuery UI to make it resizable, but seem to be running into a few issues involving custom drag