SoFunction
Updated on 2025-03-04

Share the policy of setting indexes in SQL Server database

introduction

In SQL Server, indexes play a key role in optimizing query performance by speeding up data retrieval. The policy for setting indexes in a database is affected by the database structure, the size of the table, and the type of query to be run. Indexing strategies often involve considering the differences between dimension tables, fact data tables, large tables, and small tables. Here is a detailed classification of how to apply indexes to these different types of tables:

1. Dimension table

Dimension tables are usually small and medium-sized tables that store descriptive classified data for querying, filtering, and grouping in data warehouses and OLAP (online analysis processing) schemes. Common examples include tables such as "Customers", "Products", "Time", "Geography", etc.

Indexing strategy for dimension tables:

  • Primary key (clustered) index: The most common index on a dimension table is the primary key (usually on a proxy key or a unique identifier), which is usually a clustered index. This index physically organizes the table data in the store by key value.
CREATE TABLE Customers (

CustomerID INT PRIMARY KEY, -- Surrogate key

CustomerName NVARCHAR(100),

City NVARCHAR(50),

Country NVARCHAR(50)

);
  • Nonclustered index: If the query frequently filters certain columns (e.g., ‘City’, ‘Country’), you can create nonclustered indexes on these columns to speed up the search operation.
CREATE NONCLUSTERED INDEX idx_city_country 

ON Customers (City, Country);

  • consider：

  • Small watch: Since the dimension table is usually small in size, the overhead of creating and maintaining indexes is minimal.

  • Read-intensive query: Query the dimension table frequently for searching or joining, so indexes on frequently queried columns can significantly improve performance.

Example Use Cases: If you are running a query to find all customers of a specific Country, the index of "Country" will help:

SELECT * FROM Customers WHERE Country = 'USA';

2. Fact list

Fact data tables are usually large tables that store transaction data, indicators, and facts such as sales, orders, or financial transactions. These tables tend to grow rapidly and contain millions or even billions of rows.

Indexing strategy for fact tables:

  • Clustered index on primary key: If the fact table has a composite primary key (e.g., ‘TransactionID’, ‘ProductID’, ‘Date’), the clustered index is usually built on this composite key.
CREATE TABLE SalesFact (

TransactionID INT,

ProductID INT,

Date DATE,

Amount DECIMAL(10, 2),

PRIMARY KEY (TransactionID, ProductID, Date) -- Composite key

);
  • Nonclustered indexes on foreign keys and common filters: Since fact tables are often joined with dimension tables (e.g., ‘ProductID’, ‘CustomerID’, ‘Date’), you should create nonclustered indexes on foreign key columns and frequently filtered columns.
CREATE NONCLUSTERED INDEX idx_product_date 

ON SalesFact (ProductID, Date);
  • Filter index: Create a filter index on the "Date" column of a given date range can improve performance if the fact data table contains years of data and the query is usually for a specific date range.
CREATE NONCLUSTERED INDEX idx_sales_2023 

ON SalesFact (Date)

WHERE Date >= '2023-01-01' AND Date

  • consider：

  • Large table: Fact tables can be very large, so index maintenance needs to be carefully considered (e.g. rebuilding and reorganizing indexes). Over-indexing of fact tables can degrade write performance (for example, when data is inserted).

  • Write intensive data loading operations: Fact tables usually handle a large number of insert operations. You should try to minimize the number of indexes, or choose an index that helps a particular query without having too much impact on insertion performance.

Example Use Cases: Query for aggregate sales by product and date:

SELECT ProductID, SUM(Amount) AS TotalSales

FROM SalesFact

WHERE Date BETWEEN '2023-01-01' AND '2023-12-31'

GROUP BY ProductID;

In this case, the indexes on "ProductID" and "Date" will help speed up filtering and grouping operations.

3. Large table

Large tables are tables that contain large amounts of data (usually tens of millions to billions of rows). These tables can be fact data tables or other tables that grow larger over time.

Indexing strategies for large tables:

  • Clustered Index: For large tables, it is recommended to use clustered indexes for the most commonly used query keys (usually primary keys or date fields). The goal is to sort data on disk for efficient range scanning and search.
CREATE CLUSTERED INDEX idx_large_table_id 

ON LargeTable (LargeTableID);
  • Nonclustered index: In addition to clustered indexes, nonclustered indexes can also be used to accelerate specific query patterns. For example, if your query frequently searches for a specific column (e.g., ‘LastName’), a nonclustered index on that column will be very useful.
CREATE NONCLUSTERED INDEX idx_lastname 

ON LargeTable (LastName);
  • Partition: Partitioning large tables into smaller, more manageable parts can improve performance, especially for queries that access subsets of data based on partition keys such as dates or regions. SQL Server supports table partitioning, which can be used in conjunction with partition indexes.
CREATE PARTITION FUNCTION pf_date_range (DATE)

AS RANGE RIGHT FOR VALUES ('2020-01-01', '2021-01-01', '2022-01-01');

  • consider：

  • Index maintenance: Large tables require periodic index maintenance (e.g., rebuild or reorganize indexes) to ensure indexes remain efficient.

  • Storage cost: Indexes consume storage space, so performance improvements and storage overhead must be balanced.

4. Small watch

A small table is a table with relatively few rows (for example, less than 1,000 rows). These can be reference tables or lookup tables.

Indexing strategy for small tables:

  • Primary key (clustered) index: Even if small tables don't require too many indexes, primary keys on proxy keys or natural keys are still useful for ensuring data integrity.
CREATE TABLE Country (

CountryID INT PRIMARY KEY,

CountryName NVARCHAR(50)

);
  • Nonclustered index: Nonclustered indexes on small tables should be used with caution, as performance improvements may not be significant. However, if a specific column is frequently queried, the index can still provide some benefits.
CREATE NONCLUSTERED INDEX idx_country_name 

ON Country (CountryName);

  • consider：

  • Minimum overhead: Indexing small tables won't have much impact on performance, but it can still bring benefits to lookup operations.

  • Query mode: If a small table is queried by multiple fields, you can consider creating an index based on the columns that are often used in the query.

General best practices for indexing:

  • Avoid over-index: Too many indexes can hurt performance, especially on write-intensive tables, as every insert/update/delete operation requires maintenance of the index.

  • Monitor and optimize indexing: Use built-in tools for SQL Server (such asDatabase Tuning AdvisororSQL Server Profiler) to analyze query performance and determine which indexes contribute or compromise performance.

  • Use indexes that match the query pattern: Create indexes based on specific columns used in the ‘WHERE’, ‘JOIN’, and ‘ORDER BY’ clauses in the most common queries.

  • Consider querying execution plan: Check the query execution plan regularly to ensure that the index is used effectively.

By following these policies and taking into account the size and usage patterns of tables in the database, you can create effective indexing policies to optimize read and write performance in SQL Server.

The above is the detailed content of the strategy for setting indexes in the SQL Server database. For more information about setting indexes in SQL Server, please pay attention to my other related articles!