Clustered and Non Clustered Index: Non Clustered Index

Non-Clustered Index


Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.


The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.


You can add non key columns to the leaf level of the non-clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

Non-clustered indexes are implemented in the following ways:

UNIQUE constraints

When you create a UNIQUE constraint, a unique non-clustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

Index independent of a constraint


By default, a non-clustered index is created if clustered is not specified. The maximum number of non-clustered indexes that can be created per table is 999. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but does not include XML indexes.


Non-clustered index on an indexed view


After a unique clustered index has been created on a view, non-clustered indexes can be created.


Permissions : Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin anddb_owner fixed database roles.

> How to create a non-clustered index by using the Table Designer

In Object Explorer, expand the database that contains the table on which you want to create a non-clustered index.


Expand the Tables folder.


Right-click the table on which you want to create a non-clustered index and select Design.


On the Table Designer menu, click Indexes/Keys.


In the Indexes/Keys dialog box, click Add.


Select the new index in the Selected Primary/Unique Key or Index text box.


In the grid, select Create as Clustered, and choose No from the drop-down list to the right of the property.


Click Close.


On the File menu, click Save table_name.


> How to create a non-clustered index by using Object Explorer


In Object Explorer, expand the database that contains the table on which you want to create a non-clustered index.


Expand the Tables folder.


Expand the table on which you want to create a non-clustered index.


Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….


In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.


Under Index key columns, click Add….


In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the non-clustered index.


Click OK.


In the New Index dialog box, click OK.


> How To create a non-clustered index on a table


In Object Explorer, connect to an instance of Database Engine.


On the Standard bar, click New Query.


Use the following example into the query window and click Execute.

-----------------------------------------------------------------

USE LearnDb;

GO

-- Find and delete it if found an index already existing named IX_Employee_EmployeeId.

IF EXISTS (SELECT name FROM sys.indexes

WHERE name = N'IX_Employee_EmployeeId')

DROP INDEX IX_Employee_EmployeeId ON Company.Employee;

GO

-- Create a nonclustered index called IX_Employee_EmployeeId

-- on the Company.Employee table using the DeptId column.

CREATE NONCLUSTERED INDEX IX_Employee_EmployeeId

ON Company.Employee (DepartmentID);

GO