concept clustered index in category sql server

This is an excerpt from Manning's book SQL Server 2008 Administration in Action. Login to get full access to this book.

There are a few things to note here. Near the end of the output is the line repair_allow_data_loss is the minimum repair level... This essentially means that corruption was found on a clustered index (or a heap). As we’ll see in the next chapter, these items are the data pages themselves, so removing them will result in data loss, hence the warning.

Looking further up in the output, we can see error messages coming from Object ID 87671360, index ID 0, and index ID 2. Tables without a clustered index are referred to as a heap. Index ID 0 refers to a base data page from a heap table. Index ID 1 refers to a clustered index page, and index ID 2 and above refer to pages from nonclustered indexes. When interpreting the DBCC output, seeing corruptions only from index IDs 2 and above is somewhat good news; it means that the only corruption is on nonclustered index pages. In such cases, recovery is quite straightforward; we can simply rebuild the index(es), or proceed with the REPAIR_REBUILD option of DBCC, which will reinstate the missing/corrupted rows in the nonclustered index (or rebuild it). Neither of these options will result in any data loss.

After you create the clustered index on the Social Security number (SSN) column, the data within the client table is physically ordered by SSN. The table can now be considered a clustered table. In addition to reordering the existing rows in the table, new rows will be inserted in order, based on the SSN value being inserted. Figure 13.1 illustrates the difference between inserting a record into the client table as a heap compared to a version with a clustered index on SSN.

Figure 13.1. Inserting a row into a table with a clustered index will physically position the row based on the value of the column in the clustered index key, in this case SSN.

Despite the name, a clustered index is more than just an index. It also contains the table data itself, which is stored at the leaf level of the index. By default, a primary key constraint will be created as a clustered index, thereby physically ordering the table rows based on the value of the primary key column(s).

-- Create a clustered index on a nonprimary key column
CREATE TABLE dbo.client (
    clientCode int PRIMARY KEY NONCLUSTERED
    , surname nvarchar(100)
    , firstName nvarchar(100)
    , SSN char(11)
    , DOB datetime
)
GO

CREATE UNIQUE CLUSTERED INDEX cixClientSSN ON dbo.client(SSN)
GO

copy

So, generally speaking, which types of columns make the best candidates for a clustered index? In answering this, let’s recap some points from earlier in the chapter:

The clustered index key is contained in the leaf node of each nonclustered index as the row locator. If the clustered index changes from one column to another, each nonclustered index needs to be updated in order to maintain the linkage from the nonclustered index to the base table. Further, if the column value of the clustered index changes, similar updates are required in each of the nonclustered indexes.

The width of the clustered index directly affects the size of each nonclustered index. Again, this is a consequence of including the clustered index key in the leaf node of each nonclustered index.

If a clustered index is not unique, SQL Server will make it so by adding a hidden uniqueifier column to the table for inclusion in the index.

It follows that the best candidates for a clustered index are columns that