Explore SQL Server Index Properties in SSMS

Introduction

We can create indexes in SQL Server using both GUI and t-SQL method. Once we create an index using t-SQL, we specify the index name, key columns, included columns, filter to create it. We do not consider other index options. While on the other hand, if you use the SSMS GUI method, it gives many options to you. You might get overwhelmed with all SSMS index options. In this article, we will take a look at all SSMS index properties.

Let’s first create a table for demo and use the CREATE INDEX command for the clustered index on it. In the index script, we specified the clustered index key, and it creates the index with all default options:

CREATE TABLE Test
(id   INT, 
 name VARCHAR(50)
);
CREATE CLUSTERED INDEX ix_1 ON Test(id);

To verify the index, navigate to source database -> Tables -> dbo.Test-> Indexes. Right-click on the index -> Script Index as -> Create To -> New Query Editor Window:

Script Index

It gives you the following script with all default configurations:

View script

We can see these index properties while creating the index using SSMS Index GUI. For this purpose, right-click on the earlier index and drop it.

Now, we will create the same clustered index using SSMS. First right-click on the Indexes and choose the required index type such as clustered index, Non-Clustered index, XML index, Clustered Columnstore index, Non-Clustered columnstore index:

Create Clustered SQL Server Index

Note:_ If you are not sure about Index type, you can refer Performance tuning – Indexes section at SQLShack to learn all about SQL Server Indexes_

General tab

It gives you the following create new index window.

Table Name

It gives you the table name for which we want to create an index.

SQL Server Index Name

By default, it generates a unique name for the index in the format of [Index type]_YYYYMMDD-hhmmss. You should use a proper format to easily identify the index, its type, table, key from the index name. for example, you can use format such as [IX_Index name_tablename_keycolumn].

Index type

As we have chosen to create a clustered index, it shows that in the index type. You can put a check on the UNIQUE to create a Unique Clustered Index:

Index Key Columns

It is the key column in a table for which we want to create the index. Click on Add, and you can see all columns of the selected table:

Once you select the key column, it shows you on the general page. By default, it shows an ascending order for the key column. You can change the value as descending if required.

Note: You should analyze whether ascending or descending order is useful for your workloads:

#development #indexes #sql server management studio (ssms) #sql