Re: Unique

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In addition to Erland's comment (completely agree with him), there are some interesting posts of Kimberly Tripp about it: http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx, and also this one: http://www.sqlskills.com/blogs/kimberly/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx

--
-----------------------------
"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático

http://blogs.solidq.com/es/elrincondeldba


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9C6387883CCE1Yazorman@xxxxxxxxxxxx
RickNZ (kiessig@xxxxxxxxxxxxxxxxx) writes:
The default definition of "primary key" is a unique, clustered, non null
index. You can override that definition, but you shouldn't normally do
so.

Here I will have to disagree strongly. Which is the clustered index of a
table should be a careful decision. True, in the case the table only has
one index, the primary key, this should also be a clustered index (unless
you really think a heap is the best choice and fully understand the
implications of using heap.)

But if you have more than one index on the table, there is all reason
to consider. And this is far from always an apparent choice. If you have
some sort of transaction table, you may want to have the clustered index
per customer/account to make such searches more efficient. On the other
hand, if there is a high insertion frequency, you may want to put the
clustered index on an IDENTITY column to reduce the number of page splits.

Also, if you have a PK which is a uniqueidentifier with a default of
newid(), you should definitely make this a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Transform/transfer 50Gb - how to do it fast?
    ... I'm also plan to make newtable partioned. ... If new the table will have the same clustered index as the old table, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Need ammunition against clustered index hampers performance
    ... I can't find ANY resource on the internet that ... Following their own guidelines, there is no clustered index in sight, ... SQL Server MVP Greg Linwood has argued fiercely for heaps, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: deadlock problem
    ... how do i do this?do u mean that i should reconsider the columns that i ... use in clustered index? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Compound Primary Key - order not as expected
    ... the physical order on disk may be yet another one. ... data in the order of the clustered index ... This may have been true by chance for SQL Server up version 6.5. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Unique
    ... one index, the primary key, this should also be a clustered index (unless ... And this is far from always an apparent choice. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)