Create Clustered Index in SQL Server

Create Clustered Index in SQL Server

Indexes play a critical role in databases. They act as indexes in a book, allowing you to search and locate various items and topics within a book. Indexes in a database perform similarly and help speed up the search speed for records stored in a database. Create Clustered Index in SQL Server is explained in this article.

Indexes play a critical role in databases. They act as indexes in a book, allowing you to search and locate various items and topics within a book. Indexes in a database perform similarly and help speed up the search speed for records stored in a database.

Clustered indices are one of the index types in SQL Server. It is used to define the order under which data is stored in a table. It works by sorting the records on a table and then storing them.

In this tutorial, you will learn about clustered indices in a table and how to define a clustered index in SQL Server.

SQL Server Clustered Indexes

Before we understand how to create a clustered index in SQL Server, let us learn how indexes work.

Consider the example query below to create a table using a basic structure.

CREATE DATABASE product_inventory;
USE product_inventory;
CREATE TABLE inventory (
    id INT NOT NULL,
    product_name VARCHAR(255),
    price INT,
    quantity INT
);

Next, insert some sample data into the table, as shown in the query below:

INSERT INTO inventory(id, product_name, price, quantity) VALUES
(1, ‘Smart Watch’, 110.99, 5),
(2, ‘MacBook Pro’, 2500.00, 10),
(3, ‘Winter Coats’, 657.95, 2 ),
(4, ‘Office Desk’, 800.20, 7),
(5, ‘Soldering Iron’, 56.10, 3),
(6, ‘Phone Tripod’, 8.95, 8 );

The example table above does not have a primary key constraint defined in its columns. Hence, SQL Server stores the records in an unordered structure. This structure is known as a heap.

Suppose you need to perform a query to locate a specific row in the table? In such a case, it will force SQL Server to scan the entire table to locate the matching record.

For example, consider the query.

SELECT * FROM inventory WHERE quantity = 8;

If you use the estimated execution plan in SSMS, you will notice the query scans the entire table to locate a single record.

Although the performance is hardly noticeable in a small database as the one above, in a database with a humongous number of records, the query may take a longer time to complete.

A way to resolve such a case is to use an index. There are various types of indexes in SQL Server. However, we will mainly focus on clustered indexes.

As mentioned, a clustered index stores the data in a sorted format. A table can have one clustered index, as we can only sort the data in one logical order.

A clustered index uses B-tree structures to organize and sort the data. This enables you to perform inserts, updates, deletes, and more operations.

Notice in the previous example; the table did not have a primary key. Hence, SQL Server does not create any index.

However, if you create a table with a primary key constraint, SQL Server automatically creates a clustered index from the primary key column.

Watch what happens when we create the table with a primary key constraint.

CREATE TABLE inventory (
    id INT NOT NULL PRIMARY KEY,
    product_name VARCHAR(255),
    price INT,
    quantity INT
);

If you re-run the select query and use the estimated execution plan, you see that the query uses a clustered index as:

SELECT * FROM inventory WHERE quantity = 8;

On SQL Server Management Studio, you can view the available indexes for a table by expanding the indexes group as shown:

What happens when you add a primary key constraint to a table that contains a clustered index? SQL Server will apply the constraint in a non-clustered index in such a scenario.

SQL Server Create Clustered Index

You can create a clustered index using the CREATE CLUSTERED INDEX statement in SQL Server. This is mainly used when the target table does not have a primary key constraint.

For example, consider the following table.

DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
    id INT NOT NULL,
    product_name VARCHAR(255),
    price INT,
    quantity INT
);

Since the table does not have a primary key, we can create a clustered index manually, as shown in the query below:

CREATE clustered INDEX id_index ON inventory(id);

The query above creates a clustered index with the name id_index on the inventory’s table using the id column.

If we browse for indexes in SSMS, we should see the id_index as:

Wrap Up!

In this guide, we explored the concept of indexes and clustered indexes in SQL Server. We also covered how to create a clustered key on a database table.

Thanks for reading, and stay tuned for more SQL Server tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *