Problem
Indexes allow queries to run faster for data selection and are a key part of high performance for SQL Server. In this tip, will see the types of indexes used in SQL Server such as clustered indexes, non-clustered indexes, filtered indexes and how to create, modify and delete indexes.
Solution
We will look at:
- Clustered Indexes
- Non-clustered Indexes
- Filtered indexes
- Altering and Dropping indexes
SQL Server Clustered Index Example
Clustered indexes physically sort the data pages by a column or columns that are part of the clustered index. A clustered index can be created on one or multiple columns and you can have only one clustered index per table. The reason for this is that a table can only have a single physical sort order at the data page level which is based on the clustered index.
Another thing to note is that if we have a primary key on a table, in most cases it is the clustered index, but this is not true in all cases.
We will see how to define a clustered index for a table without using a primary key. The below code can be used to create the table Kids1.
CREATE TABLE [dbo].[Kids1]( [RoomNo] [numeric](8, 0) NOT NULL, [RoomName] [varchar](20) NOT NULL, [RoomLink] [numeric](8, 0) NULL, [Sex] [nchar](6) NULL ) ON [PRIMARY] GO
Below we can see the “Kids1” table in SSMS. I will expand the “Kids1” table, we can see Indexes as an option. Right click on Indexes and we can see an option to create a New Index. Select Clustered Index… as shown below.
A new index creation window will appear as shown below. In the Index name column, we can give a unique name to the Cluster index. In the example, I created the index name as CI_ROOM_NUM. The naming convention I used is CI_ for Clustered Index and ROOM_NUM for the column name. This will be very useful when we are handling many indexes to be able to just look at the name to get an idea of how the index was created.
We are not creating a primary key, but ROOM_NUM is unique, so I would like to enforce uniqueness for the column RoomNo, by checking the “Unique” box.
Next, we need to add columns to the index, so click the Add button to the right of Index key columns.
Once we click the Add button, we can see four columns from the Kids1 table, lets choose RoomNo as the clustered index column.
Once you click OK, we can see column “RoomNo” is added to the index as shown below.
If we refresh the table Kids1 in SSMS, we can see the index as CI_ROOM_NUM (Clustered).
SQL Server Clustered Index Script
Below is the code that is generated by the script generator for your reference. Whatever we did with the GUI, the below code will create the clustered index via the script. The code will create a unique clustered index named CI_ROOM_NUM on the Kids1 table.
CREATE UNIQUE CLUSTERED INDEX [CI_ROOM_NUM] ON [dbo].[Kids1] ( [RoomNo] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Arguments for create index are explained below:
- STATISTICS_NORECOMPUTE is used to specify if statistics are to be recomputed.
- SORT_IN_TEMPDB is used to specify if sort results are stored in TempDB.
- IGNORE_DUP_KEY is used to specify error response when insert statement fails during inserting a record. For our example its set to OFF.
- ONLINE is used to enforce the usage of the index during queries. Unique non-clustered indexes cannot use the ONLINE property.
- ALLOW_ROW_LOCKS is used to lock rows during query. We have set it to ON.
- ALLOW_PAGE_LOCKS is used to allow page locks. Page locks are used when indexing. We have set it to ON.
SQL Server Non-Clustered Index Example
The non-clustered index pages are sorted by the index, but that doesn't affect the actual data. This is the main difference when compared to a clustered index. A non-clustered index does not touch the actual data pages except for pointers that point to the data pages. Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.
In the below screen prints, I have created a non–clustered index for the tip. It’s the same steps as we have for the clustered index.
Expand the table and right click on Indexes, click on “New Index”, then click on “Non-Clustered Index” as shown below.
The below screen will be displayed after clicking “Non-Clustered Index “. Under the section “Index key columns” I added “RoomName” as the column and selected the sort order as Ascending.
Now click on the “Included columns” tab and we can add additional columns that are included with the index for faster data retrieval, but are not actually part of the index key. Click the Add button to add “RoomLink” and then click OK.
Navigate back to the table and right click on table name and click refresh, we can see the newly created Non-Clustered index (NCI_Kids).
SQL Server Non-Clustered Index Script
The below code can be used to create a non-clustered index NCI_Kids.
SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NCI_Kids] ON [dbo].[Kids1] ( [RoomName] ASC ) INCLUDE ( [RoomLink]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Arguments for create index are explained below:
- STATISTICS_NORECOMPUTE is used to specify if statistics are to be recomputed.
- SORT_IN_TEMPDB is used to specify if sort results are stored in TempDB.
- IGNORE_DUP_KEY is used to specify error response when insert statement fails during inserting a record. For our example its set to OFF.
- ONLINE is used to enforce to usage of index during queries. Unique non-clustered indexes cannot use ONLINE property.
- ALLOW_ROW_LOCKS is used to lock rows during query. We have set it to ON.
- ALLOW_PAGE_LOCKS is used to allow page locks. Page locks is used when indexing. We have set it to ON,
SQL Server Filtered Index Example
Filtered indexes are a very useful feature of SQL Server which can have a positive impact on query performance. By using filter criteria, we can make sure the query uses a subset of the full table based on the filter settings. This results in efficient index performance and faster extraction of results. This feature gives us an upper hand when it comes to optimizing queries. Filtered indexes are useful when we have to filter NULL data, filtered data by a particular zip code, filter data with particular job code, sort based on current financial quarters in the year and so on. Filter indexes work well when compared with a full table index.
Right click on Indexes on table “Kids1” and select New Index and select Non-Clustered Index as shown below.
The index name is “NonClusteredIndex-Filter” in our example. Now click on the Add button and the select columns “Sex” and “RoomName” as the columns and click OK.
Uniqueness is not checked as we could have many Males and Females.
Now click on “Filter” page from the left side. Add the Filter Expression as Sex = 'Male' and RoomName in ('Blue','Red') and click the OK button.
The new index can be seen in the below screen.
SQL Server Non-Clustered Filtered Index Script
Here we are setting filter as Sex = ‘Male’ and RoonName in ‘Blue’, ‘Red’ as the filter criteria. By adding filter criteria, the record-set will contain only the records where the two conditions match. This will ensure only minimal data is retrieved in the dataset.
SET ANSI_PADDING ON
GO
REATE NONCLUSTERED INDEX [NCI_Kids] ON [dbo].[Kids1]
(
[RoomName] ASC,
[Sex] ASC
)
INCLUDE ( [RoomLink])
WHERE Sex = 'Male' and RoomName in ('Blue','Red')
WITH (STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Arguments for create index are explained below:
- STATISTICS_NORECOMPUTE is used to specify if statistics are to be recomputed.
- SORT_IN_TEMPDB is used to specify if sort results are to be stored in TempDB.
- DROP_EXISTING is used to rebuild the index, this clause is can be used to add, drop, modify columns. We need to set this to ON for our example.
- ALLOW_ROW_LOCKS is used to lock rows during the query. We have set it to ON.
- ALLOW_PAGE_LOCKS is used to specify whether page locks are allowed. We have set it to ON.
Deleting a SQL Server Index
Indexes can be deleted as needed and we are going to see how to delete an index that we created in the above example. In general indexes use space in the database, so it’s advisable to delete unneeded indexes. It is a best practice to remove unused indexes and we can always create a new index if needed.
The below screen print explains how to delete the [NonClusteredIndex-Filter] index using the delete option in SSMS. Once deleted if you click refresh on the Indexes folder you will see that index has been removed.
Right click on Indexes in the table “Kids1”, select the Delete option as shown below.
The Delete option opens a window as shown below. Verify the details and click OK and SQL Server will delete the index selected.
SQL Server DROP Index Script
The below code will drop the Index named NonClusteredIndex on dbo.kids1. If you notice, the drop index command does not need any arguments to be passed.
DROP INDEX [NonClusteredIndex-Filter] ON [dbo].[Kids1] GO
Modifying a SQL Server Index
Indexes can be modified based on the business need. Modifying means we can change the structure of the index. In general, we would change the columns, change uniqueness and change the options or storage details and so on. We will see how to add a column to an Index and we will try to change the properties of the Index. Modifying indexes will either modify the index or re-create the index based on the changes we do.
First let’s add a column to the existing index – NCI_Kids
Expand dbo.Kids1, expand Indexes and double click on the NCI_Kids index and the below screen will open.
Click on the Add button, and select the “Sex” column and click OK.
After clicking OK, the below screen will appear. If you notice we have the “Sex” column is added in the list.
Now click OK to save.
SQL Server will ask for confirmation to add the column name, when we click OK the index will be re-created.
SQL Server ALTER Index Script
Below is script for the above steps.
The code can be used to modify the index. A new column “Sex” is added as a modified index column. Now the index has two columns after the change is made.
SET ANSI_PADDING ON GO CREATE NONCLUSTERED INDEX [NCI_Kids] ON [dbo].[Kids1] ( [RoomName] ASC, [Sex] ASC ) INCLUDE ( [RoomLink]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Arguments for create index are explained below:
- STATISTICS_NORECOMPUTE is used to specify if statistics are to be recomputed.
- SORT_IN_TEMPDB is used to specify if sort results are to be stored in TempDB.
- DROP_EXISTING is used to rebuild the index, this clause is can be used to add, drop, modify columns. We need to set this to ON for our example.
- ALLOW_ROW_LOCKS is used to lock rows during query. We have set it to ON.
- ALLOW_PAGE_LOCKS is used to specify whether page locks are allowed. We have set it to ON.
Below is the screen print after applying the changes.
Let’s change the properties of the Index and see how it’s getting reflected. I will make three modification as below:
- Auto compute Statistics as False
- Allow Row locks as True
- Pad Index as False
Right click on the NCI_Kids index and select Properties.
These are the index properties for the NCI_Kids Index on the options page. Make the changes and click OK to save the changes.
SQL Server ALTER Index Script
Below is the code generated when the properties are modified. The below code uses the ALTER INDEX command to make the changes.
ALTER INDEX [NCI_Kids] ON [dbo].[Kids1] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
If you notice in the code, PAD_INDEX is set to OFF from ON, ALLOW_ROW_LOCKS is set to ON from OFF and STATISTICS_NORECOMPUTE is set to OFF from ON.
Next Steps
- Here are some additional tips about indexes:
- Check out the SQL Server Indexing Tutorial as well.
