primary key's

Is it absolutely necessary to have primary keyâ's on every table. I have
several examples in a db where the logical choice for a pk is never used in
joins, where clauses, or order by clauses. I would like to create a
clustered index on these tables but not necessarily a primary key. Anyone
have advice for me?"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:65ADC838-7965-44D2-B994-FFDD3D8EBD41@.microsoft.com...
> Is it absolutely necessary to have primary key's on every table. I have
> several examples in a db where the logical choice for a pk is never used
> in
> joins, where clauses, or order by clauses. I would like to create a
> clustered index on these tables but not necessarily a primary key. Anyone
> have advice for me?
Why would you want duplicate rows in these tables?
David|||Hi,
Looks like your database is not fully a normalized one. Look into the below
considerations before creating a clusterd index:- (FROM Books online)
Before creating clustered indexes, understand how your data will be
accessed. Consider using a clustered index for:
a.. Columns that contain a large number of distinct values.
b.. Queries that return a range of values using operators such as BETWEEN,
>, >=, <, and <=.
c.. Columns that are accessed sequentially.
d.. Queries that return large result sets.
e.. Columns that are frequently accessed by queries involving join or
GROUP BY clauses; typically these are foreign key columns. An index on the
column(s) specified in the ORDER BY or GROUP BY clause eliminates the need
for SQL Server to sort the data because the rows are already sorted. This
improves query performance.
f.. OLTP-type applications where very fast single row lookup is required,
typically by means of the primary key. Create a clustered index on the
primary key.
Clustered indexes are not a good choice for:
a.. Columns that undergo frequent changes
This results in the entire row moving (because SQL Server must keep the
data values of a row in physical order). This is an important consideration
in high-volume transaction processing systems where data tends to be
volatile.
b.. Wide keys
The key values from the clustered index are used by all nonclustered
indexes as lookup keys and therefore are stored in each nonclustered index
leaf entry.
Thanks
Hari
SQL Server MVP
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:65ADC838-7965-44D2-B994-FFDD3D8EBD41@.microsoft.com...
> Is it absolutely necessary to have primary key's on every table. I have
> several examples in a db where the logical choice for a pk is never used
> in
> joins, where clauses, or order by clauses. I would like to create a
> clustered index on these tables but not necessarily a primary key. Anyone
> have advice for me?|||Yes...you really should have a primary key for your tables.
Just because the PK is not used in joins, where clauses or
order by clauses doesn't mean you should not have one. Use
in joins, order by or where clauses is not what you would
use as the criteria in selecting a primary key.
The primary key does not have to be the clustered index. You
can still create those on other columns.
-Sue
On Wed, 31 Aug 2005 14:45:10 -0700, Wes
<Wes@.discussions.microsoft.com> wrote:
>Is it absolutely necessary to have primary key?s on every table. I have
>several examples in a db where the logical choice for a pk is never used in
>joins, where clauses, or order by clauses. I would like to create a
>clustered index on these tables but not necessarily a primary key. Anyone
>have advice for me?sql