A nonclustered index is an index that is physically separated from a table. Each is considered a separate database object. Because these objects are separate, the physical order of the table rows is not the same as their indexed order. Nonclustered indexes resemble Microsoft® Access indexes.
A clustered index is an index that has been physically merged with a table. The table and index share the same storage area. The clustered index physically rearranges the rows of data in indexed order, forming the intermediate decision nodes. The leaf pages of the index contain the actual table data. This type of architecture permits only one clustered index per table.
As a rule, you should always have a clustered index defined on a table. Doing so improves Microsoft SQL Server™ performance and space management. If you do not know the query or update patterns for a given table, you can default to using the clustered index on the primary key. Later analysis of application bottlenecks may lead you to change the clustered index on specific tables to support frequently used range queries better.
The Access utility for compacting databases does not exist for SQL Server. Instead, SQL Server rebuilds indexes. As records are deleted from a table in SQL Server, gaps in the table and its indexes remain untouched. When an entire datapage is emptied, it is reassigned. However, as long as one record remains, the datapage remains untouched, even if it is 90 percent empty. When you rebuild an index, these data pages are rewritten and filled as specified by the fill factor. (If no clustered index exists on the table, create one, and then drop it to achieve the same effect.) To compact a database fully, rebuild every index on every table. Of course, tables that experience little activity will benefit only slightly. Although it requires some work to set up such a maintenance plan, this system is easy to customize so that you can rebuild only selected tables.
| Indexes | Using Clustered Indexes |
| Using Nonclustered Indexes |