6 Different Types of Indexes in SQL Server

Spread the love

Indexes are important for optimizing database performance in SQL Server. They help SQL Server find the information it needs quickly, significantly speeding up query execution and data retrieval. Without indexes, SQL Server would have to scan entire tables row by row, which can be a time-consuming process, especially for large datasets.

This blog will explain various index types in SQL Server, practical examples, and a step-by-step guide to implement them. By the end, you’ll understand how indexes work, when to use them, and how they can transform query performance.

Detailed Explanation of Index Types

1. Clustered Index

A clustered index sorts and stores the data rows in the table based on the key values. Each table can have only one clustered index because the data rows themselves are sorted in this order.

Example:

-- Create a sample Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(18, 2)
);

-- Insert sample records
INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES
(1, 'John Doe', 'IT', 70000),
(2, 'Jane Smith', 'HR', 60000),
(3, 'Sam Wilson', 'Finance', 80000),
(4, 'Lisa Brown', 'IT', 72000),
(5, 'Tom Hanks', 'Sales', 50000);

-- Create a clustered index (automatically created with the primary key)
CREATE CLUSTERED INDEX IX_Employees_EmployeeID ON Employees(EmployeeID);

Note: Clustered index is automatically created with the Primary key

2. Non-Clustered Index

A non-clustered index contains pointers to the actual data rows in the clustered index or heap. You can create multiple non-clustered indexes on a table.

Example:

-- Create a non-clustered index on the Salary column
CREATE NONCLUSTERED INDEX IX_Employees_Salary ON Employees(Salary);

3. Unique Index

A unique index ensures that the indexed column(s) contain unique values. It’s automatically created when defining a unique constraint.

Example:

-- Create a unique index on the Name column
CREATE UNIQUE INDEX IX_Employees_Name ON Employees(Name);

4. Filtered Index

A filtered index is a non-clustered index with a WHERE clause, making it ideal for scenarios where only a subset of rows needs indexing.

Example:

-- Create a filtered index for employees in the IT department
CREATE NONCLUSTERED INDEX IX_Employees_IT ON Employees(Department)
WHERE Department = 'IT';

5. Full Text Index

A full-text index is used for complex text-based searches, such as searching for words or phrases in large text columns.

Example:

-- Enable full-text indexing and create a catalog
CREATE FULLTEXT CATALOG FTC_Employees;

-- Create a full-text index on the Name column
CREATE FULLTEXT INDEX ON Employees(Name)
KEY INDEX IX_Employees_EmployeeID
ON FTC_Employees;

When you execute the Full Text Index, you might get the below error

“’ix_employees_employeeid’ is not a valid index to enforce a full-text search key. a full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. choose another index for the full-text key.”

To solve this error you need to execute the below query

SELECT name AS primary_key_index_name
FROM sys.indexes 
WHERE is_primary_key = 1 
AND object_id = OBJECT_ID('Employees');

From this query, you will get the primary key index name. This index name needs to be added to the full text index

-- Create a full-text index on the Name column
CREATE FULLTEXT INDEX ON Employees([Name])
KEY INDEX PK__Employee__7AD04FF14EFEF4DA
ON FTC_Employees;

Verify the Full Text Index

6. Columnstore Index

A columnstore index is optimized for data warehousing and analytics by storing data in a columnar format, reducing storage and improving query performance.

Example:

-- Create a columnstore index
CREATE COLUMNSTORE INDEX IX_Employees_Columnstore ON Employees(Salary);

Query Execution Plan Analysis

Here, we’ll demonstrate how indexes improve query performance using an execution plan.

Before Index:

-- Query without index
SELECT * FROM Employees WHERE Salary > 50000;

Run the query in SQL Server Management Studio (SSMS) and observe the execution plan. You’ll likely see a table scan, indicating SQL Server had to check every row.

After Index:

-- Query with a non-clustered index
CREATE NONCLUSTERED INDEX IX_Employees_Salary ON Employees(Salary);

-- Re-run the query
SELECT * FROM Employees WHERE Salary > 50000;

After applying the index, you’ll notice an index seek in the execution plan, significantly reducing the query cost.

Real-World Examples

Clustered Index:

For primary keys or unique identifiers like EmployeeID.

SELECT * FROM Employees WHERE EmployeeID = 101;

Non-Clustered Index:

For queries filtering or sorting on non-primary key columns.

SELECT * FROM Employees ORDER BY Salary DESC;

Filtered Index:

For specific subsets of data.

SELECT * FROM Employees WHERE Department = 'IT';

You can view all indexes in the indexes folder.

Best Practices for Using Indexes

Clustered Index: Use for columns with unique and sequential values, like primary keys.
Non-Clustered Index: Ideal for frequently queried columns used in WHERE, JOIN, or ORDER BY clauses.
Unique Index: Use to enforce uniqueness in data.
Filtered Index: Optimize for queries targeting specific subsets of rows.
Avoid Over-Indexing: Too many indexes can slow down INSERT, UPDATE, and DELETE operations.
Monitor Storage Costs: Indexes consume storage space; balance performance with resource usage.

Conclusion

Indexes are essential tools for optimizing SQL Server performance. By understanding and implementing the right types of indexes, you can dramatically improve query efficiency and application responsiveness. Remember to balance indexing with maintenance and storage considerations.

Quick-Reference Chart:

Index Type	Use Case	Advantages
Clustered Index	Primary key, unique identifiers	Faster retrieval by key
Non-Clustered	Frequent filtering/sorting	Flexible, multiple indexes
Unique Index	Enforce data uniqueness	Data integrity
Filtered Index	Subset of data	Reduced storage, targeted
Full-Text Index	Text search	Advanced search capability
Columnstore	Analytics and reporting	Analytics and Reporting

Spread the love