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/
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment