Today we conclude with some final thoughts regarding Ravi’s question about Clustered Indexes.
As most of our readers already know, each table in SQL Server may have up to one clustered index. This restriction is due to the fact that a clustered index is a b-tree structure, and that the data of a table is physically stored within the structure of this b-tree. Since the data may only be physically in one location, each table may only have one clustered index.
A non-clustered index, on the other hand, is also maintained inside a b-tree. The difference is that the non-clustered index only maintains a pointer to the physical location of the data (the clustered index).
So, anytime a clustered index may be used efficiently for more than one purpose you can gain additional performance by simply maintaining that clustered index. That being said, for most databases, it is a good idea to try and define a clustered index that performs weil for inserting data, but often may be the most efficient for data retrieval. It is for this reason that Microsoft has a standard of using a sequential key, and making that th eclustered index.
SQL server is tuned to work in this fashion. So, the time to conjsider using something different for your primary key and clustered index, is when a surrogate key does not accurately map to the normal data retrieval patterns. As a database developer you will have to experament to determine when to use a different methodology, and when to simply follow the documented performance characteristics of the database engine itself.
Thanks Ravi, for the interesting question. If you have your own questions you’d like to see discussed here on SSWUG feel free to leave them with us here, or drop a note to btaylor@sswug.org.
Cheers,
Ben