{"id":1748,"date":"2019-04-19T11:13:03","date_gmt":"2019-04-19T04:13:03","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1748"},"modified":"2024-03-14T15:17:04","modified_gmt":"2024-03-14T08:17:04","slug":"sql-server-clustered-indexes","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-indexes\/sql-server-clustered-indexes\/","title":{"rendered":"SQL Server Clustered Indexes"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the SQL Server clustered index and how to define a clustered index for a table.<\/p>\n\n\n\n

Introduction to SQL Server clustered indexes #<\/a><\/h2>\n\n\n\n

The following statement creates a new table<\/a> named production.parts<\/code> that consists of two columns part_id<\/code> and part_name<\/code>:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> production.parts(\n    part_id   INT<\/span> NOT<\/span> NULL<\/span>, \n    part_name VARCHAR<\/span>(100<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> \n    production.parts(part_id, part_name)\nVALUES<\/span>\n    (1<\/span>,'Frame'<\/span>),\n    (2<\/span>,'Head Tube'<\/span>),\n    (3<\/span>,'Handlebar Grip'<\/span>),\n    (4<\/span>,'Shock Absorber'<\/span>),\n    (5<\/span>,'Fork'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
When you query data from the production.parts<\/code> table, the query optimizer needs to scan the whole table to search.<\/p>\n\n\n\n
For example, the following SELECT<\/code> statement finds the part with id 5:<\/p>\n\n\n
SELECT<\/span> \n    part_id, \n    part_name\nFROM<\/span> \n    production.parts\nWHERE<\/span> \n    part_id = 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you display the estimated execution plan in SQL Server Management Studio, you’ll see how SQL Server came up with the following query plan:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Note that to display the estimated execution plan in SQL Server Management Studio, you click the Display Estimated Execution Plan<\/strong> button or select the query and press the keyboard shortcut Ctrl+L<\/code>:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Because the production.parts<\/code> table has only five rows, the query executes very fast. However, if the table contains a large number of rows, it’ll take a lot of time and resources to search for data.<\/p>\n\n\n\n
To resolve this issue, SQL Server provides a dedicated structure to speed up the retrieval of rows from a table called index.<\/p>\n\n\n\n
SQL Server has two types of indexes: clustered index and non-clustered index. We will focus on the clustered index in this tutorial.<\/p>\n\n\n\n
A clustered index stores data rows in a sorted structure based on its key values. Each table has only one clustered index because data rows can be only sorted in one order. A table that has a clustered index is called a clustered table.<\/p>\n\n\n\n
The following picture illustrates the structure of a clustered index:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
A clustered index organizes data using a special structured so-called B-tree<\/a> (or balanced tree) which enables searches, inserts, updates, and deletes in logarithmic amortized time.<\/p>\n\n\n\n
In this structure, the top node of the B-tree is called the root node<\/strong>. The nodes at the bottom level are called the leaf nodes<\/strong>. Any index levels between the root and the leaf nodes are known as intermediate levels.<\/p>\n\n\n\n
In the B-Tree, the root node and intermediate-level nodes contain index pages that hold index rows. The leaf nodes contain the data pages of the underlying table. The pages in each level of the index are linked using another structure called a doubly-linked list.<\/p>\n\n\n\n
SQL Server Clustered Index and Primary Key Constraint #<\/a><\/h2>\n\n\n\n
When you create a table with a primary key<\/a>, SQL Server automatically creates a corresponding clustered index that includes primary key columns.<\/p>\n\n\n\n
This statement creates a new table<\/a> named production.part_prices<\/code> with a primary key that includes two columns: part_id<\/code> and valid_from<\/code>.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> production.part_prices(\n    part_id int<\/span>,\n    valid_from date<\/span>,\n    price decimal<\/span>(18<\/span>,4<\/span>) not<\/span> null<\/span>,\n    PRIMARY KEY<\/span>(part_id, valid_from) \n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
If you add a primary key constraint to an existing table that already has a clustered index, SQL Server will enforce the primary key using a non-clustered index:<\/p>\n\n\n\n
This statement defines a primary key for the production.parts<\/code> table:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> production.parts\nADD<\/span> PRIMARY KEY<\/span>(part_id);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server created a non-clustered index for the primary key.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Using SQL Server CREATE CLUSTERED INDEX statement to create a clustered index. #<\/a><\/h2>\n\n\n\n
When a table does not have a primary key, which is very rare, you can use the CREATE CLUSTERED INDEX<\/code> statement to add a clustered index to it.<\/p>\n\n\n\n
The following statement creates a clustered index for the production.parts<\/code> table:<\/p>\n\n\n
CREATE<\/span> CLUSTERED INDEX<\/span> ix_parts_id\nON<\/span> production.parts (part_id);  <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you open the Indexes<\/strong> node under the table name, you will see the new index name ix_parts_id<\/code> with type Clustered<\/code>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
When executing the following statement, the SQL Server traverses the index (Clustered Index Seek) to locate the rows, which is faster than scanning the whole table.<\/p>\n\n\n
SELECT<\/span> \n    part_id, \n    part_name\nFROM<\/span> \n    production.parts\nWHERE<\/span> \n    part_id = 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
SQL Server CREATE CLUSTERED INDEX syntax #<\/a><\/h2>\n\n\n\n
The syntax for creating a clustered index is as follows:<\/p>\n\n\n
CREATE<\/span> CLUSTERED INDEX<\/span> index_name\nON<\/span> schema_name.table_name (column_list);  <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
The production.parts<\/code> table does not have a primary key<\/a>. Therefore SQL Server stores its rows in an unordered structure called a heap<\/strong>.<\/p>\n\n\n\n
And this statement inserts some rows<\/a> into the production.parts<\/code> table:<\/p>\n\n\n