Microsoft SQL Server

Working with Clustered Index in MS SQL Server

Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order.

Creating a Table in Clustered Index

To better explain how SQL Server creates clustered indexes, lets create a new table by executing the following command.

Create Table Employee
 (
 EmpId Int,
 EmpName varchar(20)
 )

When you first create a new table, there is no index created by default, and a heap is created.

Now, Adding a few records in this table:-

Insert Into Employee Values (4, Replicate (‘a’,20))
 Insert Into Employee Values (6, Replicate (‘b’,20))

After inserting the data in the Employee table , we can view the contents of the table by executing the following command.

Select EmpID From Employee

The output should be as follows:

Empid
4
6

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. A clustered index can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.

Some points to remember when using clustered indexes:

  • The reordering of the index occurs every time the index changes (ie: on Update, Insert, Delete).
  • They affect the physical order of data so there can only one clustered index.
  • They reorder the way records in the table are physically stored.

When you create a clustered index, try to create it as a UNIQUE clustered index, not a non-unique clustered index. The reason for this is that while SQL Server will allow you to create a non-unique clustered index, SQL Server will make it unique for you by adding a 4-byte “uniqueifer” to the index key to guarantee uniqueness. This increases the size of the key and reduces performance.

The post Working with Clustered Index in MS SQL Server appeared first on OptimusBI.