Skip to main content

Clustered Index in SQL Server

What is a clustered index?
The clustered index is a special index which sorts and stores the data in the table or view based on their key values. There can be only one clustered index per table because the data rows themselves can be stored in only one order. If we talk in term of B-Tree design, the leaf nodes of the index store the data for the rest of the columns in the table so when a lookup is performed on this type of index there are no other structures that need to be referenced.

Why create clustered indexes?
Having clustered index in the table is beneficial; first, when querying the table, there is only required IO operations occurred except extra lookup to get any/all of the non-key column data. Second, it gives us a way to recognize the table data. If there is no clustered index on the table then data stored disorganized in a heap structure. When our data becomes defragmented due to DML operations then the only one way to fix this fragmentation is to reload all the data into a new table. With a clustered index in the table, we can run index reorganized or rebuild to address the fragmentation.

When clustered indexes should be created?
Generally, we use an index on those columns which heavily used in WHERE clause portion in the query. As we know query performs huge range scans on those columns in the WHERE clause can be beneficial from having a clustered index on these columns. In both cases, we will use fewer disk IO and in some cases (depending on the ORDER BY clause) we can save ourselves memory and CPU by not having to perform a sort since data is already ordered.

How to create a clustered index
A clustered index can be created on the table either through a primary key constraint or simply using create index statement. In both cases, we simply have to add the CLUSTERED keyword to either the ALTER TABLE or CREATE INDEX statement as shown in the below TSQL examples.


-- When we create a primary key
ALTER TABLE Sales.BookInventory ADD CONSTRAINT PK_BookInventory_BookID
PRIMARY KEY CLUSTERED (BookID ASC);

-- Without constraints
CREATE CLUSTERED INDEX IX_BookInventory_BookID ON Sales.BookInventory (BookID ASC);


Here I will highlight of some important thing on the clustered index and will analysis of myth about the clustered index.

1-    It is important to know; only one clustered index can be defined on each table in your database.
2-    Changing the clustered index form one column to another column may be a bit complicated and does require some downtime. Since all non-clustered indexes on table reference the row data in the clustered index if we simply drop the current clustered index and create a new one then all of the non-clustered indexes will end up being updated twice.
3-    When clustered index will be dropped, all data of the columns will move into heap it will be updating seconds time when new the new clustered index is created and all the column data move into the structure.
4-    Dropped and create new index may probably increase the fragmentation which is poison for index. So we have to rebuild all the indexes.
5-    Many people or new DBA think, we can create clustered index only on primary key column(s), this is not true, you can create clustered index on another column too which probably used more in WHERE clause. Suppose you have created a table and create an index on primary key which used rarely in WHERE clause while DateTime columns use mostly in the query, so it would be better if you create clustered index on DATETIME column.
6-    Remember, it would be better if the clustered index column has unique data and sort in length.
Query to change the clustered index
I above points I mentioned, changing the clustered index in tables is may be dangerous but following query may help you.

Suggestion: If you are not DBA then don’t take the risk, contact to your DBA he will help you.



DROP INDEX AK_BookOrder_rowguid ON Sales.BookOrder;
DROP INDEX IX_BookOrder_ProductID ON Sales.BookOrder;
ALTER TABLE Sales.BookOrder
   DROP CONSTRAINT PK_BookOrder_BookOrderID_BookOrderID;
CREATE CLUSTERED INDEX IX_BookOrder_ProductID
   ON Sales.BookOrder (ProductID ASC);
ALTER TABLE Sales.BookOrder
   ADD CONSTRAINT PK_BookOrder_BookOrderID_BookOrderID
   PRIMARY KEY NONCLUSTERED (BookOrderID ASC, BookOrderID ASC);
CREATE UNIQUE NONCLUSTERED INDEX AK_BookOrder_rowguid
   ON Sales.BookOrder (rowguid ASC);


Popular posts from this blog

Check for changes to an SQL Server table?

Problem: Suppose your team is working on the under-development project so it might be possible continuous work on the database and perform changes in Table, Stored procedure as per requirement, and daily you have to update the testing server database as per changes are done in developing server database then how it is possible to trace those changes. There are a lot of solutions for this problem which is listed below Solution 1: For SQL Server 2000, 2005 and above use the CHECKSUM command SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMYour_Table_NameWITH (NOLOCK); That will return the same number each time its run as long as the table contents haven't changed. Unfortunately CHECKSUM does not work always properly to detect changes. It is only a primitive checksum and no CRC calculation. Therefore you can't use it to detect all changes, e. g. symmetrical changes result in the same CHECKSUM! Solution 2: 1.Run the following query. Before executing query replace DB_Name with your database name…

Merge and Merge join transformation in SSIS

How to drop multiple tables with common prefix in one query?

Problem: Suppose we have a situation where we need to drop those tables that have some prefixes string, so it is possible to drop those tables with a common prefix in a single query.
Solution: yes it is possible to drop all those tables that have the common prefix in a single query. Using following query you can delete all those tables that begin with a certain prefix string. In where condition just put the common prefix string in where condition (Like ‘prefix%’)
DECLARE@queryNVARCHAR(MAX)=N'';
SELECT@query+=' DROP TABLE ' +QUOTENAME(s.name) +'.'+QUOTENAME(t.name)+';' FROMsys.tablesASt INNERJOINsys.schemasASs ONt.[schema_id]=s.[schema_id] WHEREt.nameLIKE'MX_100%';
EXECsp_executesql@query;
This query may create an issue, if a table has a foreign key relationship, you'll either need to drop them first or arrange the output to drop the tables in a certain order. If you want to monitor exactly what goes on when the query is running then use the following que…