Programming in MS SQL Server 2012

Nonclustered Index

SQL Server implicitly creates a unique Nonclustered Index, when creates table with Unique constraint. The index column(s) are equal to constraint column(s) and the index name is equal to constraint name.

Nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index or to locate rows in a table without a clustered index (called a heap). You can create multiple nonclustered indexes on a table or indexed view.

The following example describes to create a Nonclustered Index on Employee table. This index helps to optimize join query while map child (Employee) table with parent (Department) table in the WHERE clause expression like Employee.Dept_ID = Department.Dept_ID table objects.

-- Example 149 --

USE EBusiness

-- Create a new Department Table --

CREATE TABLE dbo.Department

(

Dept_ID int Primary Key,

Dept_Name varchar(50) Not Null

)

-- Create a new Employee Table --

CREATE TABLE dbo.Employee

(

Emp_ID int Primary Key,

Emp_Name varchar(50) Not Null,

DOJ datetime Default GetDate(),

Salary numeric(7,2) Check (Salary > 1000 and Salary < 5000),

Phone varchar(50),

Dept_ID int References Department(Dept_ID)

On Delete Cascade

)

-- Find an existing index on IX_Employee and delete it if found --

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_Employee')

DROP INDEX IX_Employee ON dbo.Employee

-- Create Nonclustered Index on Employee Table --

CREATE NONCLUSTERED INDEX IX_Employee_Nonclustered

ON dbo.Employee(Dept_ID)

Query Output Screen

Structure of Nonclustered Index

Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

01. The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

02. The leaf layer of a nonclustered index is made up of index pages instead of data pages.

Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

01. If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

02. If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

The functionality of nonclustered indexes can be extended by adding included columns, called nonkey columns, to the leaf level of the index. While the key columns are stored at all levels of the nonclustered index, nonkey columns are stored only at the leaf level.

* * * * *

Your Comment To AUTHOR