In Microsoft SQL Server, there are different types of indexes designed to optimize the performance of data retrieval operations. Each index type has its specific use case, and understanding these types helps in improving query performance while maintaining efficient data storage and retrieval. Indexes are especially helpful for speeding up SELECT queries, but they can also impact the performance of INSERT, UPDATE, and DELETE operations.

Key Index Types:

1. Clustered Index

   • A Clustered Index sorts and stores the data rows of a table based on the indexed column(s). It defines the physical order of the rows in the table, meaning that the table data is stored in the order of the clustered index.
   • Each table can have only one clustered index because it physically rearranges the data.

   Example SQL (creating a clustered index on ProductID):

   CREATE CLUSTERED INDEX idx_ProductID
   ON Products (ProductID);

2. Non-Clustered Index

   • A Non-Clustered Index creates a separate structure from the actual table data, holding pointers to the data rows. Unlike a clustered index, a table can have multiple non-clustered indexes, which makes it flexible for optimizing various types of queries.
   • Non-clustered indexes are typically used for columns frequently used in WHERE, JOIN, or ORDER BY clauses.

   Example SQL (creating a non-clustered index on CustomerName):

   CREATE NONCLUSTERED INDEX idx_CustomerName
   ON Customers (CustomerName);

3. Unique Index

   • A Unique Index ensures that all values in the indexed column(s) are unique. This is often applied to columns where you need to prevent duplicate values, such as a ProductCode or EmailAddress. If a PRIMARY KEY is defined, it automatically creates a unique clustered index.

   Example SQL (creating a unique index on ProductCode):

   CREATE UNIQUE INDEX idx_ProductCode
   ON Products (ProductCode);

4. Filtered Index

   • A Filtered Index is a non-clustered index that is created with a filter condition. It is useful when you want to index only a subset of rows in a table, such as indexing only active customers or products above a certain price.
   • This type of index reduces index size and improves performance by focusing only on relevant data.

   Example SQL (creating a filtered index for active customers):

   CREATE NONCLUSTERED INDEX idx_ActiveCustomers
   ON Customers (CustomerID)
   WHERE IsActive = 1;

5. Full-Text Index

   • A Full-Text Index allows for searching large text fields, such as product descriptions or customer comments, using special SQL queries. This index supports advanced search operations like finding words, phrases, or complex patterns within text.
   • Full-text indexing is especially useful for large amounts of unstructured data.

   Example SQL (creating a full-text index on product descriptions):

   CREATE FULLTEXT INDEX ON Products (ProductDescription)
   KEY INDEX idx_ProductID;

6. XML Index

   • An XML Index is created specifically for querying and working with XML data types stored in SQL Server. It improves the performance of queries that retrieve data from XML columns by indexing the XML structure.

   Example SQL (creating an XML index on an XML column):

   CREATE PRIMARY XML INDEX idx_ProductDetailsXML
   ON Products (ProductDetails);

7. Columnstore Index

   • A Columnstore Index is optimized for large-scale data analytics and reporting. Instead of storing data row-by-row, it stores data column-by-column, which leads to significant performance improvements for queries that scan large amounts of data, such as SUM or AVG operations.

   Example SQL (creating a columnstore index on the Sales table):

   CREATE CLUSTERED COLUMNSTORE INDEX idx_Sales
   ON Sales;

Benefits of Indexes:

• Faster Queries: Queries that use indexed columns are processed more quickly because the database engine can find rows efficiently.
• Efficient Sorting: Indexes can be used to speed up sorting (ORDER BY), grouping (GROUP BY), and searching operations.

Drawbacks of Indexes:

• Increased Storage: Indexes require additional disk space.
• Slower Write Operations: Operations like INSERT, UPDATE, and DELETE may slow down since indexes also need to be updated.

Understanding these index types allows you to choose the most appropriate index for different use cases, ensuring optimal database performance.

CREATE UNIQUE INDEX idx_primary ON Users (UserID);

CREATE INDEX idx_secondary ON Orders (OrderDate);

CREATE UNIQUE INDEX idx_unique_email ON Users (Email);

CREATE INDEX idx_composite ON Orders (CustomerId, OrderDate);

CREATE FULLTEXT INDEX idx_fulltext ON Articles (Content);

-- Note: Bitmap indexes are specific to certain DBMS, example not universally applicable

CREATE CLUSTERED INDEX idx_clustered ON Customers (CustomerID);

CREATE NONCLUSTERED INDEX idx_nonclustered ON Orders (OrderDate);

CREATE SPATIAL INDEX idx_spatial ON Locations (GeographyColumn);

-- Note: Hash indexes are specific to certain DBMS, example not universally applicable

CREATE INDEX idx_covering ON Orders (OrderDate, CustomerID, OrderTotal);

CREATE INDEX idx_partial ON Orders (OrderDate) WHERE OrderStatus = 'Shipped';

CREATE INDEX idx_expression ON Users (LOWER(Username));

-- B-Tree is the default for many CREATE INDEX commands

CREATE INDEX idx_gin ON Documents USING GIN (DocumentTokens);

CREATE INDEX idx_gist ON Locations USING GiST (GeographyColumn);