|
|
Posted by Erland Sommarskog on 10/02/97 11:55
pb648174 (google@webpaul.net) writes:
> I've been doing a bit of reading and have read in quite a few places
> that an identity column is a good clustered index and that all or at
> least most tables should have a clustered index. The tool I used to
> generate tables made them all with non clustered indexes so I would
> like to drop all of them and generate clustered indexes.
Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.
But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.
Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @id" is a very likely
query and a clustred index may be great here.
Stu's suggestion of keeping the PK non-clustered, and adding a clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.
As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|