Ms SQL Server

Creating Index

Lecturer: Heng Bora Page 1

 Ms SQL Server

I- Introduction
An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes; they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes
also need an update). So you should only create indexes on columns (and tables) that will be frequently
searched against.

1. Overview

Human needs have increased tremendously. Now people are doing much more composite tasks than ever
before. The society has become very complex, a person has to work with huge amount of information every
day. In order to work with the enormous information, we must have a system where we can store,
manipulate and share the information all over the world. It is one of the core reasons for introducing
Database Management Systems (DBMS) as well as Relational Database Management Systems (RDBMS)
now-a-days.

So, one thing is clear to us that we store and manipulate data / information into a database, where the
database contains various types of tables for storing various types of data / information.

Developers can create tables of a database; it would be possible to stop at this point and just start working
with our data from here. However this would not be one of our best choices, but why? Because it is not
really a good idea for better performances, let’s take an example:

“A library has a huge collection of books, files, etc... A student requests the librarian for a book of Microsoft
SQL Server 2008, if we think without an index the librarian had to find this without any help s/he has to
search one by one! Which must be time consuming; so with a proper arrangement, that is with the help of
an index, it very much easier and faster to find out the desired one.”

There are many ways to create an index, but first of all we need to know what is an index is and how it is
held within SQL Server.

2. What is an Index?

A SQL table explanation is not good enough for getting the desired data very quickly or sorting the data in a
specific order. What we actually need for doing this is some sort of cross reference facilities where for
certain columns of information within a table, it should be possible to get whole records of information
quickly. But if we consider a huge amount of data in a table, we need some sort of cross reference to get to
the data very quickly. This is where an index within SQL Server comes in.

So an index can be defined as:

• “An index is an on-disk structure associated with a table or views that speed retrieval of rows from
the table or view. An index contains keys built from one or more columns in the table or view”.
These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows
associated with the key values quickly and efficiently.”

Lecturer: Heng Bora Page 2

 Ms SQL Server

• “An index is a database object created and maintained by DBMS. It is essentially a list of the
contents of a column or group of columns. Indexes are ordered so that extremely first search can be
computed through them to find data.”

3. Why Use an Index?

Use of SQL server indexes provide many facilities such as:

• Rapid access of information

• Efficient access of information
• Enforcement of uniqueness constraints

Correct use of indexes can make the difference between a top performing database with high customer
satisfaction and a non-performing database with low customer satisfaction.

4. Types of Indexes

SQL Server has two major types of indexes:

1. Clustered
2. Non-Clustered

The index type refers to the way the index is stored internally by SQL Server. So a table or view can contain
the two types of indexes.

4.1 Clustered

An index defined as being clustered, defines the physical order that the data in a table is stored. Only one
cluster can be defined per table. So it can be defined as:

• Clustered indexes sort and store the data rows in the table or view based on their key values. These
are the columns included in the index definition. There can be only one clustered index per table,
because the data rows themselves can be sorted in only one order.
• The only time the data rows in a table are stored in sorted order is when the table contains a clustered
index. When a table has a clustered index, the table is called a clustered table. If a table has no
clustered index, its data rows are stored in an unordered structure called a heap.

4.2 Non-Clustered

As a non-clustered index is stored in a separate structure to the base table, it is possible to create the non-
clustered index on a different file group to the base table. So it can be defined as:

• 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.
• The pointer from an index row in a non-clustered index to a data row is called a row locator. The
structure of the row locator depends on whether the data pages are stored in a heap or a clustered
table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the
clustered index key.
• You can add nonkey columns to the leaf level of the Non-Clustered index to by-pass existing index
key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

Lecturer: Heng Bora Page 3

 Ms SQL Server

More information about indexes can be found at this link.

Both clustered and non-clustered indexes can be unique. This means no two rows can have the same value
for the index key. Otherwise, the index is not unique and multiple rows can share the same key value.

Note: Indexes are automatically maintained for a table or view whenever the table data is modified.

5. Uniqueness

An index can be defined either as unique or non-unique. A unique index ensures that the data contained
within the unique index columns appear only once within the table, including “NULL”. A unique index is
commonly implemented to support the constraints.

SQL Server automatically enforces the uniqueness of the columns contained within a unique index. If an
attempt is made to INSERT a value/data that already exists in the table, then an error will be generated by the
SQL Server and finally the attempt to INSERT the data will fail.

A non-unique index is also applicable as there can be duplicate data; a non-unique index has more overhead
than a unique index when retrieving data.

II. Creating Index

1. Using Transact-SQL

CREATE [UNIQUE ] [clustered] INDEX index_name ON table_name

(
column_name1 [ASC|DESC],
column_name2 [ASC|DESC],
……………………………….

) [On FileGroup]

- Creates an index on a table. Duplicate values are allowed if not unique

- Creates a unique index on a table. Duplicate values are not allowed

Example
The SQL statement below creates an index named "PIndex" on the "LastName" column in the
"Persons" table:

Lecturer: Heng Bora Page 4

 Ms SQL Server

CREATE INDEX PIndex ON Persons

(
LastName ASC
) On GroupPerson

If you want to create an index on a combination of columns, you can list the column names within the
parentheses, separated by commas:

CREATE INDEX PIndex ON Persons

(
LastName ASC,
FirstName DESC
) On GroupPerson

2- Using SQL Server Management Studio

1. In Object Explorer, right-click the table for which you want to create an index and click Modify.

Lecturer: Heng Bora Page 5

 Ms SQL Server

2. The table opens in Table Designer.

3. From the Table Designer menu, click Indexes/Keys.

4. In the Indexes/Keys dialog box, click Add.

Lecturer: Heng Bora Page 6

 Ms SQL Server

5. Select the new index in the Selected Primary/Unique Key or Index list and set properties for the index
in the grid to the right.

6. Specify any other settings for the index and click Close.

7. The index is created in the database when you save the table.

SQL Server allows users create unique indexes on unique columns such as the identity number of the
employee or student or whatever is the unique key by which the component data are identified. A set of
columns also can be used to create a unique index. The DBA can set the option of ignoring duplicate keys in
a unique index if required. The default is No.
Lecturer: Heng Bora Page 7
 Ms SQL Server

To create a unique index

1. In Object Explorer, right-click the table and click Modify.
2. The table opens in Table Designer.
3. From the Table Designer menu, click Indexes/Keys.
4. Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of
the new index.

5. In the grid, click Type.

Lecturer: Heng Bora Page 8

 Ms SQL Server

6. Choose Index from the drop-down list to the right of the property.

7. Under Column name, select the columns you want to index. You can select up to 16 columns. For
optimal performance, select only one or two columns per index. For each column you select, indicate
whether the index arranges values of this column in ascending or descending order.

Lecturer: Heng Bora Page 9

 Ms SQL Server

8. In the grid, click Is Unique.

9. Choose Yes from the drop-down list to the right of the property.

Lecturer: Heng Bora Page 10

 Ms SQL Server

10. Select the Ignore duplicate keys option if you want to ignore new or updated data that would create a
duplicate key in the index (with the INSERT or UPDATE statement).

11. The index is created in the database when you save the table or diagram.

Lecturer: Heng Bora Page 11

 Ms SQL Server

Please note that unique indexes cannot be created on a single column if the column contains NULL in more
than one row. Similarly indexes cannot be created on multiple columns if the combination of the columns
contains NULL in some rows. The NULL values are treated as duplicate values.

Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key
values will be the same as the physical order of rows in the table. A table can have only one clustered index.

III. Maintaining Indexes

1- View Existing Indexes

sp_helpindex TableName

2- Rename an Index
We can also rename any user created object with the sp_rename stored procedure, including indexes.
The sp_rename procedure takes, at a minimum, the current name of the object and the new name for
the object. For indexes, the current name must include the name of the table, a dot separator, and the
name of the index, as shown below:

sp_rename 'TableName.Old_Index_Name', 'New_Index_Name'

3- The DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

DROP INDEX Syntax for MS SQL Server:

DROP INDEX table_name.index_name

Lecturer: Heng Bora Page 12