Sql Server Types Of Indexes Complete Guide

Introduction to SQL Server Indexes

Indexes are data structures used in SQL Server to speed up the retrieval of data from tables. They are especially crucial when dealing with large amounts of data. By creating an index, users can reduce the amount of data the SQL Server needs to read to execute a query.

Types of Indexes

1. Clustered Index
2. Non-Clustered Index
3. Unique Index
4. Composite Index
5. Filtered Index

1. Clustered Index

A clustered index determines the physical order of data in a table.

Example: Create A Clustered Index

Consider we have a table named Employees:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    JobTitle NVARCHAR(50)
);

Now, let’s add a clustered index on the LastName column:

CREATE CLUSTERED INDEX IDX_Employees_LastName
ON Employees (LastName);

In this example, the rows in the Employees table are stored in the order of LastName.

Note: A table can only have one clustered index because the data can only be sorted in one way.

2. Non-Clustered Index

A non-clustered index does not define the physical storage of the data. Instead, it points to the location of the data within the database.

Example: Create A Non-Clustered Index

Let's create a non-clustered index on the JobTitle column in the same Employees table:

CREATE NONCLUSTERED INDEX IDX_Employees_JobTitle
ON Employees (JobTitle);

In this case, the data in the Employees table remain physically ordered by EmployeeID (the primary key), but the JobTitle index contains pointers to the EmployeeID for fast retrieval based on JobTitle.

3. Unique Index

A unique index ensures that all values in the indexed column(s) are unique, except NULL values (which can still duplicate).

Example: Create A Unique Index

Let's add a unique index to the LastName and FirstName columns to ensure no two employees have the same full name:

CREATE UNIQUE INDEX IDX_Employees_FullName
ON Employees (LastName, FirstName);

This will prevent multiple employees from having the same first and last name combination.

4. Composite Index (or Multi-column Index)

A composite index consists of more than one column. The order of columns in the index definition is significant as it affects how SQL Server will use the index.

Example: Create A Composite Index

We'll now create a composite index on the LastName and JobTitle columns:

CREATE INDEX IDX_Employees_LastName_JobTitle
ON Employees (LastName, JobTitle);

This index will help optimize queries filtering by LastName and JobTitle together or by just LastName alone.

5. Filtered Index

A filtered index includes only a subset of rows in a table, which means it's only created on rows that meet a specific filter condition. Filtered indexes can improve query performance and reduce storage costs.

Example: Create A Filtered Index

Suppose we only want to index employees who have a JobTitle of 'Manager'. We create a filtered index like this:

CREATE INDEX IDX_Employees_Manager
ON Employees (LastName)
WHERE JobTitle = 'Manager';

Now, the index IDX_Employees_Manager will only contain entries for employees with the job title 'Manager'.

Step-by-Step Guide

Here are the steps to create each type of index using SQL Server Management Studio (SSMS):

Step 1: Prepare Your Environment

1. Open SSMS.

2. Connect to your SQL Server instance.

3. Create a new database if needed:

   CREATE DATABASE IndexExamples;
   USE IndexExamples;
   

4. Create the Employees table:

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName NVARCHAR(50),
       LastName NVARCHAR(50),
       JobTitle NVARCHAR(50)
   );
   

Step 2: Create Indexes

Creating a Clustered Index

1. Right-click on the Employees table -> Scripts Table as -> Create To -> New Query Window.

2. In the new query window, modify the script or add the following SQL command:

   CREATE CLUSTERED INDEX IDX_Employees_LastName ON Employees (LastName);
   

3. Execute the query.

Creating a Non-Clustered Index

1. Again, right-click on the Employees table -> Scripts Table as -> Create To -> New Query Window.

2. Add or modify the script with the following SQL:

   CREATE NONCLUSTERED INDEX IDX_Employees_JobTitle ON Employees (JobTitle);
   

3. Run the query.

Creating a Unique Index

1. Right-click on the Employees table -> Modify.
2. In the Table Designer, click the indexes/keys button in the toolbar.
3. Click Add.
4. In the Indexes/Keys dialog box, enter the IDX_Employees_FullName as the index name.
5. Under 'Columns', select both LastName and FirstName.
6. Check the "Unique" box.
7. Click OK and save changes.

Alternatively, you can execute this SQL:

CREATE UNIQUE INDEX IDX_Employees_FullName ON Employees (LastName, FirstName);

Creating a Composite Index

1. Similar to the previous steps. Right-click on the Employees table -> Modify.
2. In the Table Designer, click the indexes/keys button in the toolbar.
3. Click Add.
4. Enter IDX_Employees_LastName_JobTitle.
5. Select both LastName and JobTitle.
6. Click OK and save changes.

Or use this SQL:

CREATE INDEX IDX_Employees_LastName_JobTitle ON Employees (LastName, JobTitle);

Creating a Filtered Index

1. Ensure you are in a query window.

2. Use the following SQL command to create a filtered index:

   CREATE INDEX IDX_Employees_Manager ON Employees (LastName)
   WHERE JobTitle = 'Manager';
   

3. Execute the query.

Step 3: Test Your Indexes

To see the effect of these indexes, run some sample queries on the Employees table and measure their performance:

-- Test Clustered Index
SELECT * FROM Employees WHERE LastName = 'Doe';

-- Test Non-Clustered Index
SELECT * FROM Employees WHERE JobTitle = 'Engineer';

-- Test Unique Index
INSERT INTO Employees (EmployeeID, FirstName, LastName, JobTitle) 
VALUES (20, 'Jane', 'Doe', 'Designer'); -- This should fail if 'Jane Doe' already exists

-- Test Composite Index
SELECT * FROM Employees WHERE LastName = 'Smith' AND JobTitle = 'Engineer';

-- Test Filtered Index
SELECT * FROM Employees WHERE JobTitle = 'Manager' AND LastName LIKE 'S%';

Notes:

• Performance gains are often noticeable in large datasets.
• Always consider the cost vs. benefit of adding indexes as they can slow down insert/update/delete operations due to the overhead of maintaining the index structure.

Conclusion

In this guide, we discussed and demonstrated how to create five different types of indexes in SQL Server: clustered, non-clustered, unique, composite, and filtered. Each type serves specific purposes and optimizing your tables with appropriate indexes can greatly improve the performance of your SQL applications.