Creating indexes in SQL Server is an essential aspect of optimizing database performance. While there are multiple ways to create indexes, using SQL Server Management Studio (SSMS) provides a graphical user interface (GUI) that offers various options for index configuration. In this article, we will explore the different index properties available in SSMS and understand their significance.

Creating a Clustered Index

Let’s start by creating a table for demonstration purposes and then create a clustered index using the CREATE INDEX command:

CREATE TABLE Test (
  id INT,
  name VARCHAR(50)
);

CREATE CLUSTERED INDEX ix_1 ON Test (id);

To verify the index, navigate to the source database, go to Tables, then dbo.Test, and finally Indexes. Right-click on the index and select “Script Index as” followed by “Create To” and choose “New Query Editor Window.” This will provide you with the script that includes all the default configurations for the index.

SSMS Index GUI

Alternatively, you can create the same clustered index using the SSMS GUI. Right-click on Indexes and select the desired index type, such as clustered index, non-clustered index, XML index, clustered columnstore index, or non-clustered columnstore index.

General Tab

The General tab in the create new index window provides the following options:

• Table Name: Displays the name of the table for which the index is being created.
• SQL Server Index Name: By default, SSMS generates a unique name for the index in the format of [Index type]_YYYYMMDD-hhmmss. It is recommended to use a proper naming convention that includes the index type, name, table, and key column for easy identification.
• Index Type: Displays the selected index type, such as clustered or non-clustered. You can also choose to create a unique clustered index.
• Index Key Columns: Allows you to specify the key column(s) for the index. You can select the desired column(s) from the list of available columns.

SQL Server Index Options Page

Clicking on the Options page provides various index properties that can be configured:

• Auto Recompute Statistics: Determines whether SQL Server should automatically update index statistics. It is recommended to leave this option at its default value unless you regularly update statistics.
• Ignore Duplicate Values: Specifies whether duplicate keys are allowed in the index column. By default, duplicate keys are not allowed for non-unique indexes.
• Allow Row Locks and Allow Page Locks: These properties allow you to specify whether row-level locking or page-level locking is allowed. It is generally recommended to let SQL Server choose the locking mechanism efficiently.
• Allow Online DML Processing: In a 24*7 OLTP environment, enabling this option allows for online index maintenance, preventing blocking of other user queries during index maintenance operations.
• Maximum Degree of Parallelism for SQL Server Index: Specifies the maximum number of processors that can be used for parallel query execution. By default, it is set to use all available processors.
• Optimize For Sequential Key: This option, available in SQL Server 2019, improves performance for concurrent inserts by controlling the rate at which new heads can request a latch.
• Sort in Tempdb: Determines whether the sort results for index creation should be stored in TempDB. By default, SQL Server uses the database in which the index resides.
• SQL Server Index Fill Factor: Specifies how much empty space should be left on each leaf level page of the index. It is recommended to carefully consider the fill factor value, as it can impact database performance.
• Pad Index: Similar to fill factor, pad index applies to the non-leaf levels of an index. It is only available when the fill factor is set.

Storage

The Storage tab allows you to define the filegroup for the index and select a partition scheme if partitions are defined in the SQL Server table. It is considered a best practice to create a separate filegroup for storing indexes.

Conclusion

In this article, we have explored the various index properties available in SSMS for SQL Server. It is important to be aware of these properties and configure them according to the specific workload requirements. However, it is crucial to exercise caution when changing default values directly in a production database. It is recommended to implement these configurations in a non-production instance and perform proper load testing before applying them to a production environment.