|
|
Posted by pb648174 on 08/15/06 15:11
Well that makes things simpler then.. I'll try adding the clustered
columns to one area of the app and see if it makes a positive or
negative performance impact. Thanks for the info guys.
Erland Sommarskog wrote:
> 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]
|