SQL Server CREATE INDEX

Learning Objective

The objective of this SQL Server tutorial is to teach you how to create an index on a table and view in a database.

What is an INDEX?

An index is a list of items ordered alphabetically or on some other ordering criteria so that the items and the information about the items can be accessed easily and quickly.

What is SQL Server INDEX?

A database index also serves the same purpose as a normal index and allows us to access and retrieve a row of data in less time with less effort using lesser computing resources. There are 3 main types of indexes in SQL Server and we will learn to create all of them. The 3 main types of indexes are.

  1. Clustered Index – A clustered index is the default index which is created automatically by SQL Server on the table primary key.  There can be only one clustered index for a table and hence to create a custom clustered index using a different table column or column group as the key we have to delete the default clustered index and then create a new one. The only way to prevent the automatic creation of the default clustered index is by not specifying a primary key on the table which never really happens in real life.
  2. Non-Clustered Index – Any additional index created on a table where the index key is not the primary key but some other column or group of columns is a non-clustered index. SQL Server allows the creation of 999 non-clustered indexes on a table for faster querying and data retrieval.
  3. Unique Index – A unique index can be either clustered or non-clustered. If a clustered index already exists then it will be a non-clustered unique index. Otherwise it can be created as a clustered unique index. A unique index is an index where the index key is a column or group of columns with the unique key constraint defined i.e. columns which do not have duplicate values.

SQL Server CREATE INDEX Examples

An index is created using CREATE INDEX. An index can be created on both tables and views.

1) Creating Clustered Index

The basic syntax of the command to create a clustered index is the following.

CREATE CLUSTERED INDEX index_name
ON table_name (column1 ASC|DESC, column2 ASC|DESC, …………);

In this syntax,

  • column1/column2 – indicates the column which is the key for the index.
  • ASC|DESC – indicates the order in which the key column values should be sorted. If nothing is specified the values are sorted in ascending order.

Before creating an index, we will create a table called students without defining any primary key on the table as below.

CREATE TABLE students 
(
   rollno TINYINT,  
   firstname CHAR (50),  
   lastname CHAR (50),  
   stream CHAR (20)
);

After table creation we will insert some data into the table. Note that I have entered the records haphazardly and not in the order of roll numbers.

INSERT INTO students (rollno, firstname, lastname, stream)
VALUES
(23, 'Rizwan', 'Ahmed', 'Commerce'),
(11, 'Amy', 'Williams', 'Arts'),
(25, 'Nick', 'Jones', 'Science'),
(13, 'Laura', 'Wells', 'Science'),
(29, 'Michael', 'Bull', 'Science'),
(33, 'Wayne', 'John', 'Commerce'),
(35, 'Julie', 'Summers', 'Arts'),
(30, 'Val', 'Kostner', 'Science');

To check for index on the table we run the following T-SQL command and see that there is no index for the table since no primary key has been defined on the table.

EXEC SP_HELPINDEX students;  

The object 'dbo.students' does not have any indexes, or you do not have permissions.   Completion time: 2020-09-26T23:38:21.6254082+05:30

We will now create a clustered index on the table using the CREATE INDEX command as below. Index names are usually prefixed with ix and the same has been done below. It is a convention and not a requirement.

CREATE CLUSTERED INDEX ix_key_rollno ON students (rollno);

If we run the EXEC SP_HELPINDEX command again we can see the new clustered index we have created. It shows the index information as below.

index_nameindex_descriptionindex_keys
ix_key_rollnoclustered located on PRIMARYrollno

2) Creating Non-Clustered Index

The basic syntax of the command to create a non-clustered index is the following.

CREATE NONCLUSTERED INDEX index_name 
ON table_name (column1 ASC|DESC, column2 ASC|DESC, …………);  

In this syntax,

  • NONCLUSTERED is optional. If nothing is specified SQL Server interprets it as a request to create a non-clustered index and creates a non-clustered index.

So, we will now create a non-clustered index on the students table. But this time we will use two columns for the index key – firstname and lastname. While creating a non-clustered index using multiple columns the column order is very important. The column which is used more frequently in searches and which helps to group records together should be placed before or precede the other columns in the column list. In the below query we have specified the lastname column first and given it more priority.

CREATE NONCLUSTERED INDEX ix_key_studentname 
ON students (lastname, firstname);

If we run the EXEC SP_HELPINDEX command again we can see the new non-clustered index along with the previously created clustered index. It shows the index information as below. The second row in the result below shows the non-clustered index.

index_nameindex_descriptionindex_keys
ix_key_rollnoclustered located on PRIMARYrollno
ix_key_studentnamenonclustered located on PRIMARYlastname, firstname

3) Creating Unique Index

The basic syntax of the command to create a unique non-clustered index is the following.

CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_name (column1 ASC|DESC, column2 ASC|DESC, …………);  

Before creating unique index, we have dropped the existing indexes as below. 

DROP INDEX students.ix_key_rollno; DROP INDEX students. ix_key_studentname;

Now we will add the unique constraint on the rollno column in the table with the following ALTER TABLE command.

ALTER TABLE students ADD CONSTRAINT uq_constraint UNIQUE (rollno);

With a unique constraint in place we can now create a unique index as below. Note that I have created the unique index as clustered and explicitly specified descending as the sorting order for the index key.

CREATE UNIQUE CLUSTERED INDEX ix_uq_rollno ON students (rollno DESC);  

We can check the new index using the EXEC SP_HELPINDEX command which will show the new index as below.

index_nameindex_descriptionindex_keys
ix_uq_rollnoclustered, unique located on PRIMARYrollno(-)

If we do a SELECT on the table, we will see that the resultset is in descending order of rollno with the higher value rollno’s showing on top. This is in line with the sorting order specified for the unique index.

SELECT * FROM students;  
rollnofirstnamelastnamestream
35Julie                                            Summers                                          Arts               
33Wayne                                            John                                             Commerce           
30Val                                              Kostner                                          Science            
29Michael                                          Bull                                             Science            
25Nick                                             Jones                                            Science            
23Rizwan                                           Ahmed                                            Commerce           
13Laura                                            Wells                                            Science            
11Amy                                              Williams                                         Arts               

4) Creating Index on View

The basic syntax of the command to create an index on a table view is the following. In the syntax everything is almost same except that you specify a view instead of a table to create the index on.

CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name 
ON view_name (column1 ASC|DESC, column2 ASC|DESC, …………);  

Before indexing a view, we will first we will create a new view on the students table with the following command. Please note that WITH SCHEMABINDING is mandatory. Also, you need to specify the columns which you want to include in the view and SELECT * will not work.

CREATE VIEW students_view WITH SCHEMABINDING AS SELECT rollno, firstname, lastname FROM dbo.students;

We can check whether the view has been created by doing a SELECT on the view as below.

SELECT * FROM students_view;  

It will show all the records as below.

rollnofirstnamelastnamestream
35Julie                                            Summers                                          Arts               
33Wayne                                            John                                             Commerce           
30Val                                              Kostner                                          Science            
29Michael                                          Bull                                             Science            
25Nick                                             Jones                                            Science            
23Rizwan                                           Ahmed                                            Commerce           
13Laura                                            Wells                                            Science            
11Amy                                              Williams                                         Arts               

Now we will create two indexes and verify their creation. The first command below creates a unique clustered index on the rollno column and the second command creates a non-clustered index on the lastname column.

CREATE UNIQUE CLUSTERED INDEX ix_students_view ON students_view (rollno);
CREATE NONCLUSTERED INDEX ix_nc_students_view ON students_view(lastname);

After creation we can check them using the EXEC SP_HELPINDEX command with the view name as below.

EXEC SP_HELPINDEX students_view;    

It will list both the indexes as below.

index_nameindex_descriptionindex_keys
ix_nc_students_viewnonclustered located on PRIMARYlastname
ix_students_viewclustered, unique located on PRIMARYrollno

Advertisement