Primary Key Vs. Not NULL Unique Key

Hi,
Can anybody tell me the difference between a
primary key constraint
and
Not NULL Unique Key constraint with clustered index
Theoritically both looks the same, but am interested to know their differences in terms of their storage and performance.
One more question that's running in my mind is, if a clustered index is created on Unique key column, what will be the index key of a NULL value
Thanks very much in advanc
GYKOne difference is that the primary key constraint is not necessarily a
clustered index. As a matter of fact, I would suggest that in most cases
the clustered index should not be a unique constraint as it will typically
cause for slow inserts. It might be better to cluster by run time criteria,
such as what are the rows that need to be referenced at the same time (more
often than not), that way when the data page is brought into RAM it is all
time well spent. Other than that, I am unaware of any difference. Anybody
else?
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||Ata
First of all I have already answered this question in programming group but
going back to your suggestions
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
I always like to say it depends so
When you create a clustered index, try to create it as a UNIQUE clustered
index, not a non-unique clustered index. The reason for this is that while
SQL Server will allow you to create a non-unique clustered index, under the
surface, SQL Server will make it unique for you by adding a 4-byte
"uniqueifer" to the index key to guarantee uniqueness. This only serves to
increase the size of the key, which increases disk I/O, which reduces
performance. If you specify that your clustered index is UNIQUE when it is
created, you will prevent this unnecessary overhead.
"Ata" <NO_SPAMar_alias001@.NO_SPAMparvan.net> wrote in message
news:pOPKb.10951$JQ1.8335@.pd7tw1no...
> One difference is that the primary key constraint is not necessarily a
> clustered index. As a matter of fact, I would suggest that in most cases
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
>
> --
> Ata R
> Parvan Consulting Inc
> NO_SPAMar_alias001@.NO_SPAMparvan.net
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> > Hi,
> >
> > Can anybody tell me the difference between a
> >
> > primary key constraint
> > and
> > Not NULL Unique Key constraint with clustered index.
> >
> > Theoritically both looks the same, but am interested to know their
> differences in terms of their storage and performance.
> >
> > One more question that's running in my mind is, if a clustered index is
> created on Unique key column, what will be the index key of a NULL value?
> >
> > Thanks very much in advance
> > GYK
>|||Ata,
>It might be better to cluster by run time criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
As a side note, this behavior is often undesirable in high-volume OLTP
environments, creating very contentious pages at the 'bottom' of the table.
This phenomenon is known as 'hot-spotting'
In that case, a more intelligent choice of clustering key is needed.
James Hokes|||The primary key is a constraint held against the table
whereas the unique index is a separate object linked to
the table (not very important just changes the way they
are handled).
Conceptually the PK identifies a record whereas a unique
index just prevents duplicate values. So theoretically the
PK should never be updated (delete + insert if required)
but this is not enforced.
Some things that need to identify records will use the PK
and will not work unless one is defined.
>--Original Message--
>Hi,
>Can anybody tell me the difference between a
>primary key constraint
>and
>Not NULL Unique Key constraint with clustered index.
>Theoritically both looks the same, but am interested to
know their differences in terms of their storage and
performance.
>One more question that's running in my mind is, if a
clustered index is created on Unique key column, what will
be the index key of a NULL value?
>Thanks very much in advance
>GYK
>.
>|||Another difference is that you may ONLY have ONE PK constraint on a table,
but you may have MANY unique constraints.;
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||James,
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
Which behaviour is "this behavior". Is hot-spotting having a unique
clustered index or is it the approach I was suggesting. Pls explain?
Ata.
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:OQ86tvS1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Ata,
> >It might be better to cluster by run time criteria,
> > such as what are the rows that need to be referenced at the same time
> (more
> > often than not), that way when the data page is brought into RAM it is
all
> > time well spent. Other than that, I am unaware of any difference.
> Anybody
> > else?
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
> In that case, a more intelligent choice of clustering key is needed.
> James Hokes
>|||Another problem of non-unique clustered indexes is when
non-clustered indexes are also on the table. Another
respondent pointed out the additional overhead storing the
keys. Another issue is where you want to reindex either by
BDReindex or by Creating Index with the Drop existing
option. Doing either will require the non-clustered
indexes also be recreated affecting downtime/usability of
the table during the operation.sql