Types of Index in SQL Server

 In SQL Server, indexes are used to improve the performance of queries by providing faster access to data. There are several types of indexes in SQL Server, each suited to different use cases. Here’s an overview of the main types:

[Click on the hyperlink for the detailed description]

1. Clustered Index

• Description: A clustered index sorts and stores the data rows of the table based on the index key. The table data is physically organized in the order of the clustered index. Each table can have only one clustered index because the data rows themselves can be stored in only one order.
• Use Case: Best used on columns that are frequently searched or used for sorting, such as primary keys or other unique identifiers.
• Example:

  CREATE CLUSTERED INDEX IX_Employee_ID ON Employees(EmployeeID);

2. Non-Clustered Index

• Description: A non-clustered index creates a separate structure from the data rows. It contains pointers to the data rows that are stored in the clustered index or the table itself if there’s no clustered index. A table can have multiple non-clustered indexes.
• Use Case: Suitable for columns frequently used in search conditions (WHERE clause), joins, or for covering indexes.
• Example:

  CREATE NONCLUSTERED INDEX IX_Employee_LastName ON Employees(LastName);

3. Unique Index

• Description: A unique index ensures that the index key contains unique values. It prevents duplicate values in the index key columns. Both clustered and non-clustered indexes can be unique.
• Use Case: Useful when you need to enforce uniqueness on columns other than the primary key.
• Example:

  CREATE UNIQUE INDEX IX_Employee_Email ON Employees(Email);

4. Filtered Index

• Description: A filtered index is a non-clustered index with a WHERE clause that defines a subset of rows to be indexed. This reduces the index size and improves performance for queries that only access the filtered subset.
• Use Case: Best used when queries often filter on a specific value or range of values in a column.
• Example:

  CREATE NONCLUSTERED INDEX IX_Employee_Active ON Employees(DepartmentID) 
  WHERE IsActive = 1;

5. Columnstore Index

• Description: A columnstore index stores data in a columnar format rather than row-based, which is optimized for large-scale data warehouse queries. SQL Server offers both clustered and non-clustered columnstore indexes.
• Use Case: Ideal for analytical queries on large datasets where you need to perform aggregations, filters, and grouping.
• Example:

  CREATE CLUSTERED COLUMNSTORE INDEX IX_Employee_Columnstore ON Employees;

6. Full-Text Index

• Description: A full-text index is used to support full-text search queries on large text-based columns (e.g., VARCHAR, NVARCHAR, TEXT). It indexes words and phrases within the text, allowing for efficient searching.
• Use Case: Ideal for applications where you need to perform complex searches on text data, such as searching for words, phrases, or synonyms.
• Example:

  CREATE FULLTEXT INDEX ON Employees(LastName, FirstName)
  KEY INDEX PK_EmployeeID;

7. XML Index

• Description: XML indexes are used to optimize queries on XML data stored in XML columns. SQL Server supports primary XML indexes and secondary XML indexes, which include path, value, and property indexes.
• Use Case: Suitable for optimizing queries that retrieve specific parts of XML documents or perform complex XQuery operations.
• Example:

  CREATE PRIMARY XML INDEX IX_EmployeeXML ON Employees(EmployeeData);

8. Spatial Index

• Description: A spatial index is used to optimize queries on spatial data types like GEOMETRY and GEOGRAPHY. It enables efficient querying of spatial relationships such as intersections, containment, and proximity.
• Use Case: Ideal for applications dealing with geographical data, like mapping or location-based services.
• Example:

  CREATE SPATIAL INDEX IX_Location_Geography ON Locations(GeoLocation);

9. Hash Index (For In-Memory Tables)

• Description: A hash index is used with in-memory OLTP tables. It organizes the index data into a hash table for quick lookups. Hash indexes are non-clustered and designed for equality searches.
• Use Case: Suitable for in-memory tables where queries often involve equality comparisons.
• Example:

  CREATE TABLE Employees_InMemory (
      EmployeeID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
      LastName NVARCHAR(50),
      FirstName NVARCHAR(50)
  ) WITH (MEMORY_OPTIMIZED = ON);

10. Composite Index

• Description: A composite index is an index on multiple columns. It can be either clustered or non-clustered and is useful when queries often filter on more than one column.
• Use Case: Ideal for queries that filter or sort on multiple columns together.
• Example:

  CREATE NONCLUSTERED INDEX IX_Employee_Name ON Employees(LastName, FirstName);

Each index type serves a specific purpose, and understanding when and how to use them is crucial for optimizing query performance in SQL Server.