SQL Server index Operations

In the previous articles of this series, we described the structure of the SQL Server tables and indexes, the main concepts that are used to describe the index and the basics and guidelines that are used to design the proper index. In this article, we will go through the operations that can be performed on the SQL Server indexes.

Creating Indexes

Before creating an index, it is better to follow the index design guidelines and best practices that are described in the previous article, to determine the columns that will participate in the index, the type of the created index, the suitable index options, such as the FillFactor or Sort in TempDB, and the storage location of that index.

A SQL Server index can be created using the CREATE INDEX T-SQL statement or from the New Index dialog box using the SQL Server Management Studio tool, by providing

the index name,
the index type,
the uniqueness of the index key values,
the name of the table on which the index will be created,
list of columns that will participate in that index
and different index options such as the FillFactor, Sort in TempDB, drop the existing similar indexes
and the index filegroup and location

The CREATE INDEX T-SQL syntax below is used as a template to create a new SQL Server index. The absence of the UNIQUE option will not force the uniqueness of the index key values. In the unique index, no two rows are permitted to have the same index key value. If the type of the index is not specified in the CREATE INDEX T-SQL statement, a Non-Clustered index will be created.

CREATE [UNIQUE] [ CLUSTERED | NONCLUSTERED ] INDEX [index_name] ON [TableName]

(

[Column1] ASC,

[Column2] ASC

) ON PRIMARY

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The same operation can be performed using SQL Server Management Studio, by browsing the table on which you need to create an index, right click on the Indexes node and choose New Index option, from which you can specify the type of the index to be created, as shown below:

From the General tab of the displayed New Index dialog box, the name of the table will be filled automatically, with an editable suggested name of the index. The type of the index will be also filled automatically from the provided types list, with the ability to specify the uniqueness of that index and list of columns that will be added to that index and the order of each column, as shown clearly below:

In the Options tab of the New Index dialog box, you can change the default values of the different index creation options, same as the ones specified in the WITH clause of the CREATE INDEX T-SQL statement, as shown below:

The storage location setting of the index, such as the filegroup and the partition schema in which the index will be stored, can be specified in the Storage tab of the New Index dialog box, as shown below:

From the Filter tab of the New Index dialog box, you can specify the WHERE condition of the filtered index, to select the data to be stored in the created filtered index, as shown below:

SQL Server indexes can be also created indirectly by defining the PRIMARY KEY and the UNIQUE constraint within the CREATE TABLE or ALTER TABLE statements. SQL Server will create a unique clustered index to enforce the PRIMARY KEY constraint unless you already define a clustered index on that table. Recall that we cannot create more one clustered index on each table. A unique non-clustered index will be created automatically to enforce the UNIQUE constraint. You should be granted CONTROL or ALTER permission on the table in order to be able to can create an index.

Assume that we plan to create the below table that contains two columns, the ID column as PRIMARY KEY and the UNIQUE column STD_Name, using the CREATE Table T-SQL statement below:

USE SQLShackDemo

CREATE TABLE IndexDemo

( ID INT IDENTITY (1,1) PRIMARY KEY,

STD_Name NVARCHAR(50) NOT NULL UNIQUE

)

Checking the created table from the object explorer of the SQL Server Management Studio, you will see that, two new indexes will be created on that table automatically, without providing any CREATE INDEX statement. A Clustered index will be created to enforce the PRIMARY KEY constraint and a Unique Non-Clustered index will be created to enforce the UNIQUE constraint, as shown below:

Creating new indexes on large tables should be planned carefully, due to the performance impact of this process in a production environment. You need plan carefully starting with the clustered index creation, after that you can create the Non-clustered indexes over it.

Also, setting the ONLINE option to ON when you create the index will enable other data retrieving or modification processes on the underlying table to continue, preventing the index creation process from locking the table. On the other hand, the ONLINE index creation process will take longer time than the offline default index creation process.

Using the SORT_IN_TEMPDB option can also help in reducing the time required to create the index, taking into consideration that the tempdb is hosted on a separate disk drive than the user database.

SQL Server allows us to create up to one clustered index, 999 non-clustered indexes, 249 XML indexes, 249 Spatial indexes on each table. You can add up to 16 key columns per index, with maximum allowed size for index key record equal to 900 bytes. It is not allowed to add columns with image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml datatypes to the index key. The number, record size and datatypes limitation of index key columns can be avoided by including Non-Key columns in the index, as we will see in details in the Designing Effective Non-clustered Index article.

Disabling Indexes

When you disable a SQL Server index, the definition, statistics and the data of that index will not be removed from the system catalog, but you will not be able to access that index. Disabling the Non-clustered index will prevent you from accessing that index only. On the other hand, disabling the clustered index will prevent you from accessing the underlying table’s data, until you drop or rebuild the index.

You may decide to disable an index temporarily for many purposes, such as troubleshooting a specific scenario, correcting a disk I/O error before rebuilding the index, or eliminating the temporary disk space required to store the old and new version of the index during the index rebuild process, where only 20 percent if the index size will be required for sorting the index data.

The SQL Server Engine disables database indexes that may contain an expression, database object or collation that could be changed or may conflict with a change in the upgrade process, automatically when an upgrade process to a new release or Service Pack for SQL Server is performed, and rebuilds it automatically once the upgrade process is completed successfully.

An index can be disabled manually using the ALTER INDEX DISABLE T-SQL statement. In the previously created IndexDemo table, if we plan to disable the Non-clustered index, that is used to enforce the uniqueness of the STD_Name column, using the ALTER INDEX DISABLE T-SQL statement, and check the execution plan of the below simple SELECT statement before and after disabling the index: