Primary Key fields require index?

Whenever you define a Primary Key on a table in SQL Server 2000/2005 my
understanding was that this implicitly created a index for all the fields in
the Primary Key. Therefore, as far as I'm aware, it would be redundant to
explicitly define an index comprised of these same fields.
For example, given the following Primary key declaration...
ALTER TABLE dbo.Table1 WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
(
Field1,
Field2
) ON [PRIMARY]
GO
...the following would be unnecessary...
CREATE INDEX Index_Table1 ON Table1(Field1, Field2)
Is this correct?
Thanks in advance.yes, the index is created by the constraint clause, so there's no need to
create separate index.
peter|||Cipher a crit :
> Whenever you define a Primary Key on a table in SQL Server 2000/2005 my
> understanding was that this implicitly created a index for all the fields
in
> the Primary Key. Therefore, as far as I'm aware, it would be redundant to
> explicitly define an index comprised of these same fields.
> For example, given the following Primary key declaration...
> ALTER TABLE dbo.Table1 WITH NOCHECK ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED
> (
> Field1,
> Field2
> ) ON [PRIMARY]
> GO
> ...the following would be unnecessary...
> CREATE INDEX Index_Table1 ON Table1(Field1, Field2)
Every time you create a constraint that must be unique (PRIMARY KEY or
UNIQUE constraint) SQL Server put an INDEX.
You do not need to add your own index.
For Primary Key the index type is CLUSTERED wich is not perfect for some
primary key types. In fact it is perfect when :
1) the key is a unique column
2) the data to be stored in the time is always in the index order
wich means perfect for autoinc or timestamped DATETIME columns.
IF you want to avoid this CLUSTERED index in the PK, just add the word
NONCLUSTERED after the PRIMARY KEY spec.
Only one CLUTERED index can be set in a table, because the clustred
index in fact is the table.
A +

>
> Is this correct?
>
> Thanks in advance.
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Two indexes that are exactly the same are not needed, but since the
statistics for a composite index are only kept for the first column, maybe a
n
extra index might be of help - i.e. in a situation with a composite primary
key on columns Name, LastName an additional index on column LastName may
improve queries where the first index cannot be used.
But this, of course is not what you were asking.
ML
http://milambda.blogspot.com/