开发者

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 [开发者_Python百科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]


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.


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜