PrimaryKey and Index

All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where
a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.p
hx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:

>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)