primary key and indexes

Using SS2000 and EM. I saw a table designed by someone else. They had put a
primary key on a column and a unique constraint and made it clustered but not
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
Dan D.
Dan
http://www.sql-server-performance.co...ed_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.
|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
Dan D.
"Uri Dimant" wrote:

> Dan
> http://www.sql-server-performance.co...ed_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>
>
|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)

> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct

> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...[vbcol=seagreen]
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
|||I understand it now. Thanks Uri.
Dan D.
"Uri Dimant" wrote:

> Dan
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> Correct
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
>
>