primary key vs clustered index
suzanna
faster selects and updates. currently, there are no indices on the table
nor is there a primary key. should i create a primary key or a clustered
unique index?
do i need to worry about manually updating index statistics or running any
other utility to maintain the index???
thanks in advance
suzanna
BPMargolin
Database theory (and I believe good practice) dictate a primary key. A
Primary Key can be a clustered index, so the question is not necessarily a
primary key vs. a clustered unique index.
You have not really supplied sufficient information for us to recommend
whether your primary key should be clustered, or whether you should have
another index that is clustered. Since you do indicate that you have no
indexes (BTW, indices is correct English, but indexes is correct database
English) at current, and since you imply that you are not considering any
other indexes at present, I'd go ahead and make the primary key clustered.
If you later change your mind, it's not a huge deal to unclustered this
index, and cluster another index. Of course if your database grows to a
gigabyte, it might be time-consuming, but that's a separate consideration.
SQL Server 7.0 introduced the database option "auto update statistics" which
will automatically update the index statistics, so, in general, you do not
have to be concerned about running manual UPDATE STATISTICS.
suzanna <shy...@rcn.com> wrote in message
news:7noheu$gc2$1...@autumn.news.rcn.net...
bmc...@my-deja.com
"BPMargolin" <bpm...@ibm.net> wrote:
> You have not really supplied sufficient information for us to
recommend
> whether your primary key should be clustered, or whether you should
have
> another index that is clustered.
> other indexes at present, I'd go ahead and make the primary key
clustered.
> If you later change your mind, it's not a huge deal to unclustered
this
> index, and cluster another index.
It's usually a bad idea to cluster on an identity key, or, for that
matter, any monotonically increasing key value. Problems with
clustering on an identity key:
- You get a hotspot at the end of your table. Any new rows that get
inserted will have to go at the end, ie, they'll all be hitting the
same spot. Worse, you'll have to update the same data page and all the
same branches and leaves in the clustered index for each insert. If
you're planning to have lots of clients inserting to the table, cluster
on something else to distribute the inserts.
- You won't reuse deleted space. If your table sees any delete
operations, the rows you delete will be somewhere in the middle of the
table. Since no rows can be subsequently rewritten to that location
(remember, new rows go at the end) the only way for the space to be
reclaimed is for all rows on that data page to be deleted. Over time,
you'll see the table taking up lots more space. You can remedy this by
rebuilding the clustered index from time to time.
- Finally, a nonclustered index on the identity column will provide
similar performance for single row lookups- on average, you're looking
at between zero and one additional I/O per lookup. Since it's an
identity column, you're likely to be performing single row lookups on
the table, but unlikely to be using the ID column in range searches or
grouping.
-
Ben McEwan
Geist, LLC
bmc...@global2000.net
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.