Speed Up SQL Server Tables Creating a Clustered Index on an Identity column will improve performance.
by Kimberly Tripp-Simonnet
VSLive! SF, Day 2, February 13, 2002 — In SQL Server 7.0 and later, the best way to improve performance is to create the right data structures for how you use data. How you use the data always dictates the data structures, indexes, procedures, and so on, for your database. Here's a simple tip: You can improve the performance of your tables by creating your Clustered Index (you have only one per table) on an Identity column that's called a monotonically increasing surrogate key. This is a general statement for most databases both OLTP and OLAP, or a combo (OLTP/OLAP). The most significant gains are found by creating a hot spot for inserts for OLTP and combo OLTP/OLAP tables. You do this by more effectively using cache (more isolated access to the tables means better cache utilization). In OLAP databases it's sometimes assumed that you should cluster for range scans, but range scans can often be better served by nonclustered covering indexes.
About the Author
Kimberly Tripp-Simonnet has worked in the database field since 1989 beginning with FoxBase and then moving to SQL Server in 1991. For the past six years, Kimberly has worked as a training and consulting specialist for her own company, SYSolutions Inc. Prior to that, Kimberly held positions at Microsoft, including subject matter expert/trainer for Microsoft University and technical writer for the SQL Server development team.
