Repair Clustered and Non-Clustered Index in SQL Server

In a general way, if we define a cluster then we will define like this: A cluster is a group of some items which acts like a single system. From the figure 1, you can easily find your answer. We have 3 clusters on the image. They all have their own systems but, for each other they are only one system.

Clustered Index: Every database has rows and columns. Clustered index is used to determine the order in which the rows of the table are stored into disk.Let’s take an example, suppose we have table named Student which has a column named Roll_No. If we create a clustered index on the column Roll_No then all the rows inside the table Student will be sorted on the disk space according to the column Roll_No.

Point to remember: Index follows the tree data structure in which leaf nodes contains the actual data of the table. Leaf nodes are those nodes which have no child and they will always present on the bottom level of the tree.

Advantage: Searching and fetching of the data become faster with the cluster index because data will physically sorted right next to the each other.

Disadvantage: Clustered index is not useful or those columns that have repeated value.

How to crate clustered index: I will discuss a T-SQL method to create clustered index. Let’s take the same example of table Student that we have discussed above. With a default database, Clustered index automatically created with the primary key.

USE AdventureWorks2012;

Go

CREATE TABLE dbo.Student

(Roll_Noint NOT NULL,

Name char(10) NULL,

Address varchar(70) NULL);

GO

This query will create a table of three columns named Roll_No, Name and Address. Now we create a clustered index:

CREATE CLUSTERED INDEX IX_Student _Roll_No

ON dbo.Student (Roll_No); 

GO

This is the way to create clustered index in a table. You can choose the clustered index name according to your choice. Rather than this approach, you try it by SQL Server Management Studio.

Non-Clustered Index: Non-clustered index contain pointers that point to actual data. Pointer is a variable which contains the address of actual data.

From the above image it is clear that, the leaf nodes contain all the pointers. The main difference between clustered and non-clustered, in clustered type when we reach on the leaf nodes then we are on the actual data. In non-clustered type, when we reach on the leaf nodes then we are on the pointers.

Advantages: This approach is useful when data has redundancy.

Disadvantages: Data fetching is less easyas compared to clustered index. 

Note: A table can contain more than one non-clustered index because they do not affect the order of the row which contains data.

How to crate Non-clustered index: Using T-SQL, you can create a non-clustered index like this:

USE AdventureWorks2012;

GO

CREATE NONCLUSTERED INDEX IX_Student _Name

ON dbo.Student (Name); 

GO

Corruption in clustered and non-clustered index: When corruption occurs in the SQL server database then you should check the corrupt objects. Run the DBCC CHECKDB command:

DBCC CHECKDB(CorruptDB) With No_InfoMsgs, All_ErrorMsgs, TableResults;

GO

It returns database ID, object ID and index ID. Here the term index ID shows the status of the index. Take a look on the index ID mapping:

ID 0 = heap

ID 1 = Clustered

ID 2 = Non-clustered 

Solution: If the index ID is greater than 1 that is non-clustered index then drop and recreate it. If the index Id is 0 or 1 then try these methods:

Restore from the backup:

If the data model is full or bulk logged then you can backup of the tail log. In simple recovery model, you will not get this option and you can only restore from the last backup.Run this command to restore from the backup:

RESTORE DATABASE YourDatabaseName FROM DISK = ‘C:\YourDatabaseName.bak’ WITH NORECOVERY

If you want to repair a specific page then you can run this command:

RESTORE DATABASE YourDatabaseName PAGE = ‘1:85663’ FROM DISK = ‘C:\YourDatabaseName.bak’ WITH NORECOVERY

In section PAGE = ‘1:85663’, 1 is Index ID and 85663 is the page no.

More options:

You can also try these automatic repairing options. Take a look on these options:

REPAIR_REBUILD: Its syntax is:

DBCC CHECKDB (‘YourDatabaseName’,REPAIR_REBUILD)

Basically this is for minor corruption on the database.

REPAIR_ALLOW_DATA_LOSS: As it is clear from its name, it allows the data loss and tried to repair all errors. When corruption is very severe then this command deallocates the page from the memory and modifies the links. Its syntax is:

DBCC CHECKDB(‘YourDatabaseName', REPAIR_ALLOW_DATA_LOSS)

A Final Option: If you are unable to repair your database by these options then the final way is to use a secure third party tool. Before buying any tool just read all instructions very carefully.

Final words: We have seen the different methods to repair the corruption of index in SQL server database and I am sure that, you can use these options without any trouble and according to level of corruption. DBCC CHECKDB is very strong tool but it can turn into a dangerous tool when someone usesthis without knowledge.