SQL Server - Creating Index

Index is a database object created on a table to arrange records in such a way that the data can be searched quickly and efficiently. The table with index is best suitable when there is frequent search against the table. If the table is updated or new records are inserted frequently, the existence of Index will slow down this process. So it is not advisable to use Index on those kind of tables. There are different types Indices that can be created in SQL Server. Some of the options and types of Indices are explained below.

UNIQUE

Creates a unique index on a table or view. In case of UNIQUE index no two records are allowed to have same value on the fields the index is created upon. A clustered index on a view must be unique. UNIQUE Index cannot be created, if the table contains duplicate value even if the IGNORE_DUP_KEY property is set to ON. Only columns with NOT NULL constraint is allowed to be part of Unique Index columns. Multiple NULL values are considered as duplicate.

CLUSTERED

Creates an index which orders the key values physically as it is ordered logically. Only one clustered index is allowed for a table or view. Creating a unique clustered index on a view physically materializes the view. When we create unique Clustered Index on a view, it should be created before any other index is created on the view. Clustered Index should be created before creating any Non-Clustered indices. The word CLUSTERED is used to create the Clustered index, absence of the word will create a Non-Clustered Index. A view with a unique clustered index is called an indexed view.

NONCLUSTERED

Creates an index that specifies the logical ordering of a table. With a non-clustered index,  the records are ordered logically, not physically using index tables. The index keys are ordered as in the logical order specified and physical order is independent of logical order. A table can have maximum 999 Non-Clustered Indexes. Indexes can be created implicitly with PRIMARY KEY or UNIQUE or explicitly with CREATE INDEX.

In simple words, Clustered Indexes orders the records physically. Non-Clustered Indexes orders records logically. Below is the syntax for creating the indexes.

The difference between above 3 are: Unique index decides whether the key values are unique are not. Unique index allows only one NULL value in the key column whereas the Clustered allows multiple NULL values in key columns. Clustered and Non-clustered decides whether to organize records physically or logically.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX Index_Name 

    ON Table_Name ( column_1 [ ASC | DESC ] [ ,...n ] ) 

    [ WHERE <Filter_Condition> ]

Example 1:

CREATE UNIQUE INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 1 creates a Unique Index on the table Emp_Tab.

Example 2:

CREATE CLUSTERED INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 2 creates a Clustered Index on the table Emp_Tab and physically order the records on the basis of emp_id.

Example 3:

CREATE INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

or

CREATE NONCLUSTERED INDEX IX_Emp_Tab 

    ON Emp_Tab ( emp_id ASC ) ;

Example 3 creates a Non-Clustered Index on the table Emp_Tab and order the records on the basis of emp_id logically.

Thanks for reading this post !