Index is most important concept of SQL Server database to increase performance of retrieving data from database. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book. Index in SQL is created on existing tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When an index is created, it first sorts the data and then it assigns a ROWID for each row. In this blog, I provide an overview of SQL Server indexes and explain how they’re defined within a database and how they can make the querying process faster.

Creating Index in SQL Server Database:

 Syntax:  Creating Index

                                create index [Index Name] on
                                [Table Name] ([Column_Name1], [Column_Name2], [Column_Name3]…………. [Column_NameN])

Example to creating index on any table:

create index indeuserlogininfo_uid on
userlogininfo (UID)

    //     Output: Command(s) completed successfully.

Types Of Indexes use in SQL Server:

 There are many types of Indexes in SQL Server in which some important indexes are given as follows:

·   Unique Index: A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself.

   Syntax: Creating Unique index

   Create Index [IndeName] on
    [Table Name] (([Column_Name1], [Column_Name2], [Column_Name3]…………. [Column_NameN])

   Example to Creating Unique Index on any table:

//            Output: Command(s) completed successfully.

   The benefits of unique indexes include the following:

create unique index uidclientinfo on

clientinfo(id,name,age)

·         Data integrity of the defined columns is ensured.

·         Additional information helpful to the query optimizer is provided.

·        Clustered Inex:ex:dex:

Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. If there is no primary key in a table, you can add one clustered index to that table with CREATE CLUSTERED INDEX statement.

With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

·         Can be used for frequently used queries.

·         Provide a high degree of uniqueness.

Syntax: Creating Clustered Index

  Create Clustered Index [clustered_IndeName] on
    [Table Name](Column_Name,….)          
   Example: Creating Clustered Index

//    Output: Command(s) completed successfully.

·        NonClustered Index:

NonClustered Index implemented in following ways:

   Syntax: Creating NonClustered Index:

Create NonClustered Index [NonClustered_IndeName] on
[Table Name] (Column Name,….)

  Example: Creating NonClustered Index

//            Output: Command(s) completed successfully.

Limit of indexes on a table:

For SQL Server 2005:

1 Clustered Index + 249 Nonclustered Index = 250 Index on a table

For SQL Server 2008:

1 Clustered Index + 999 Nonclustered Index = 1000 Index on a table

SQL Query to check index on table:

create clustered index ClsidSubcategory_Name on
subcategory(subcategoryname)

A NonClustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple NonClustered indexes on a table or indexed view. Generally, NonClustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index. Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the NonClustered index to find the location of the data value in the table and then retrieves the data directly from that location. This makes NonClustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries.

·         PRIMARY KEY and UNIQUE constraints.

·         Index independent of a constraint.

·         NonClustered index on an indexed view.

create nonclustered index Nclsidsubcategory_id on

subcategory (MacateId)

1.       select * from sys.indexes.This query contains one row for each indexes and table in the current database.

2.       select * from sys.indecolumns

 This query contains one row per column that is part of a sys.indexes index or unordered table (heap).