Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Creating Indexes with SQL Server Management Studio


By:   |   Last Updated: 2018-08-20   |   Comments   |   Related Tips: More > Indexing

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.

sql server indexes

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.

sql server indexes

Once we click the Add button, we can see four columns from the Kids1 table, lets choose RoomNo as the clustered index column.

sql server indexes

Once you click OK, we can see column "RoomNo" is added to the index as shown below.

sql server indexes

If we refresh the table Kids1 in SSMS, we can see the index as CI_ROOM_NUM (Clustered).

sql server indexes

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.

sql server indexes

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.

sql server indexes

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.

sql server indexes

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 indexes

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.

sql server indexes

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.

sql server indexes

Uniqueness is not checked as we could have many Males and Females.

sql server indexes

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.

sql server indexes

The new index can be seen in the below screen.

sql server indexes
Code Details

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.

sql server indexes

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 indexes

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.

sql server indexes

Click on the Add button, and select the "Sex" column and click OK.

sql server indexes

After clicking OK, the below screen will appear. If you notice we have the "Sex" column is added in the list.

sql server indexes

Now click OK to save.

sql server indexes

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.

sql server indexes

Let’s change the properties of the Index and see how it’s getting reflected. I will make three modification as below:

  1. Auto compute Statistics as False
  2. Allow Row locks as True
  3. Pad Index as False

Right click on the NCI_Kids index and select Properties.

sql server indexes

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 indexes

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


Last Updated: 2018-08-20


next webcast button


next tip button



About the author
MSSQLTips author Jayendra Viswanathan Jayendra is a Project Leader with many years of IT experience. He has strong knowledge in software development and project management.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools