Index for primary keys

Somebody tells me that index will automatically be created for a primary key
by the system. There is no need to implicity create an index for a primary
key to boost performance. Is this statement correct?
KMGood ol' Books Online states:
"SQL Server automatically creates a unique index to enforce the uniqueness
requirement of the PRIMARY KEY constraint. If a clustered index does not
already exist on the table, or a nonclustered index is not explicitly
specified, a unique, clustered index is created to enforce the PRIMARY KEY
constraint."
Look up more under "Creating and Modifying PRIMARY KEY Constraints" in the
good book.
ML
http://milambda.blogspot.com/|||When you specify a PRIMARY KEY constraint for a table, SQL Server enforces
data uniqueness by creating a unique index for the primary key columns.
But you still need to create the Primary Key, SQL won't automatically do
this for you
HTH. Ryan
"krygim" <krygim@.hotmail.com> wrote in message
news:u9coDxaIGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Somebody tells me that index will automatically be created for a primary
> key
> by the system. There is no need to implicity create an index for a primary
> key to boost performance. Is this statement correct?
> KM
>|||Hi
Yes , unique clustered index is created automatically . I have seen some
issues that if you create PK by EM it does not create a clustered insdex
but it was probably fixed in SP3. Thus I always create tables by using QA
However , there are some situations that a clustered index will more useful
on the column that is not a primary key
Start with
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"krygim" <krygim@.hotmail.com> wrote in message
news:u9coDxaIGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Somebody tells me that index will automatically be created for a primary
> key
> by the system. There is no need to implicity create an index for a primary
> key to boost performance. Is this statement correct?
> KM
>|||Thanks for your response (and those from ML and Uri). However, I still have
a question that if there is any performance different (in queries related to
the PK columns) between explicitly creating an index and no explicit index.
KM
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eIw%23I7aIGHA.3816@.TK2MSFTNGP12.phx.gbl...
> When you specify a PRIMARY KEY constraint for a table, SQL Server enforces
> data uniqueness by creating a unique index for the primary key columns.
> But you still need to create the Primary Key, SQL won't automatically do
> this for you
> --
> HTH. Ryan
> "krygim" <krygim@.hotmail.com> wrote in message
> news:u9coDxaIGHA.2320@.TK2MSFTNGP11.phx.gbl...
primary
>|||When an index is used it is irrelevant how that index was initially created
-
either by the system or by the user. An index is an index.
But whether an index is used at all depends on the actual query - look up
"covering index" in Books Online. The query optimizer is free to choose any
method of data retrieval he sees fit for a given query. That's why planning
indexes is based on knowing your data and your data model and knowing typica
l
use.
ML
http://milambda.blogspot.com/

Index Analyzing Tools

Would somebody please recommend some index analyzing tools for me, I used Microsoft index tuning
wizard, which is not really useful.
Thanks in advance,
Harry
I'm not quite sure what tool you are looking for, but SQLExecMS from
www.laplas-soft.com can tell you a lot things about existing indexes, like
density, depth, selectivity and so on.
JS
"Harry" <anonymous@.discussions.microsoft.com> wrote in message
news:C4DDBA89-BBE3-44BE-85B0-977D6F0CA137@.microsoft.com...
> Would somebody please recommend some index analyzing tools for me, I used
Microsoft index tuning
> wizard, which is not really useful.
> Thanks in advance,
> Harry
|||Thank you, John. I am looking for a tool can load the workload file (for example: trace) like Index tuning wizard, but give me right anwser, not like Index tuning wizard tell me 'You got 0% or Minus Improvement