What types of indexes do you know in MS SQL Server? Tell about clustered and non-clustered indexes.

In MS SQL Server, there are several types of indexes, the main ones are:

• Clustered Index
• Non-Clustered Index
• Full-text search indexes
• XML indexes
• Spatial indexes

Clustered Index

A clustered index determines the physical order of data in a table. A table can have only one clustered index because data can be sorted by only one key. When creating a clustered index, the table's rows are physically rearranged in the order of the index key.

Advantages:

• Fast data access for range queries on the key.
• Efficient for sorting and grouping operations.

Disadvantages:

• Data modifications may lead to page rebuilds, affecting performance.

Non-Clustered Index

A non-clustered index stores a separate structure containing index keys and pointers to the corresponding data rows. The physical order of data in the table does not change. A table can have multiple non-clustered indexes.

Advantages:

• Speeds up searches on non-key columns.
• Multiple indexes can be created for different query scenarios.

Disadvantages:

• Additional disk space.
• Updating data requires updating indexes, which impacts write performance.

Example of creating clustered and non-clustered indexes:

-- Creating a clustered index
CREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders(OrderDate);

-- Creating a non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID);