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 :
Show quoteHide quoteEvery 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 +

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 an
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/

Splitting field contents into several columns (newbie)
Need help with a Stored Proc. - using BEGIN TRANSACTION and END
Best way to create next sequence number
NEED HELP W. QUERY USING DISTINCT AND SUM AND CASE
Where are Default Values stored