| Hari Prasad 2005-08-31, 8:23 pm |
| 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@microso
ft.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?
|