Understanding Clustered Indexes in SQL Server

The idea behind adding indexes to your database structure is to improve the performance of your database. The indexes are a disk-based structure associated with a specific table or view that intended to allow users to quickly retrieve of rows of data. Your index will contain keys built from one or more columns already available in the target table or view.

One type of index is called a clustered index. Clustered indexes sort and store the data rows in the target table or view based on the key values. This means the clustered index is the table or view, so there can be only one clustered index per table or view.

How to fix issues

To fix an error related to a clustered index, you have a few options available. You need to understand these options and be prepared to apply the solution relevant to your issue.

  1. Attempt a repair. This will not always work, and it could also take a long time to complete if it is a large table. the command:  DBCC CHECKDB (‘MyDatabase’, REPAIR_REBUILD)
  2. Restore a backup. This will replace the entire database with the last good backup of the database. If there have been several changes to the database since the last backup, this might not be a good option. You can restore the database to to a development or test server and copy the contents of relevant tables to the production server.
  3. You could also use a third-party tool to recovery table-level data from the backup file without restoring the entire database. There are many tools available, and I have no recommendation as to which tool is best.
  4. Another option might be to drop and recreate the clustered index, but this is rarely a reasonable option under normal production circumstances.

Do I need More Indexes?

To understand what indexes your tables require you need to monitor the performance of your tables and views by running the Database Engine Tuning Advisor.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s