What is meant by clustered index?
Matthew Wilson
Updated on May 04, 2026
A clustered index is an index which defines the physical order in which table records are stored in a database. Since there can be only one way in which records are physically stored in a database table, there can be only one clustered index per table. By default a clustered index is created on a primary key column.
What is the use of clustered index?
Differences between Clustered Index and NonClustered Index
| Parameters | Clustered |
|---|---|
| Use for | You can sort the records and store clustered index physically in memory as per the order. |
| Storing method | Allows you to stores data pages in the leaf nodes of the index. |
| Size | The size of the clustered index is quite large. |
Why is it called a clustered index?
2 Answers. A clustered index represents the physical order of the records on disk. Nonclustered indices are merely “pointers” to the physical records in the table; they are in order of their key(s) and contain the data of their keys and any included columns.
How does Clustered index work in SQL server?
SQL Index Types A clustered index alters the way that the rows are physically stored. When you create a clustered index on a column (or a number of columns), the SQL server sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in an alphabetical order.
What is the difference between clustered index and non-clustered index in SQL Server?
A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one. A Non-Clustered Index defines a logical order that does not match the physical order on disk.
What is ColumnStore index in SQL Server?
Columnstore indexes were first introduced in SQL Server 2012. They are a new way to store the data from a table that improves the performance of certain query types by at least ten times. They are especially helpful with fact tables in data warehouses. Now, I admit that when columnstore indexes were first introduced, I found them very intimidating.
How do I create Index in SQL?
In Object Explorer, expand the database that contains the table on which you want to create a nonclustered index. Expand the Tables folder. Right-click the table on which you want to create a nonclustered index and select Design. On the Table Designer menu, click Indexes/Keys. In the Indexes/Keys dialog box, click Add.
What is unique index in SQL Server?
Unique Index or Primary Key Index are physical structure that maintain uniqueness over some combination of columns across all rows of a table. It is a convenient way to enforce a Unique Constraint for SQL Server.
What are derived tables in SQL Server?
Derived Tables in SQL Server. A derived table is an example of a subquery that is used in the FROM clause of a SELECT statement to retrieve a set of records. You can use derived tables to break a complex query into separate logical steps and they are often a neat alternative to using temporary tables.
