Types Of SQL Indexes

Types Of SQL Indexes

Last updated on 05th Oct 2020, Artciles, Blog

About author

Aravind (Sr Technical Director )

Highly Expertise in Respective Industry Domain with 7+ Years of Experience Also, He is a Technical Blog Writer for Past 4 Years to Renders A Kind Of Informative Knowledge for JOB Seeker

(5.0) | 11547 Ratings 1533

Try to imagine this. Suddenly some very special friends turn up to your house for lunch or dinner. You want to show them your cooking skills. You do not need to worry as you have your mother’s big fat recipe book with you. All you need to do is,find out your favorite recipes from the notebook and cook them. But there is a little problem. The notebook is big and you do not know on which page your favorite recipes are. You have to browse through every page of the notebook to find those recipes, which would take a lot of time.

Subscribe For Free Demo

Error: Contact form not found.

Now reframe the same situation. But this time, your recipe book has a table of content that contains the exact page number of different recipes. Having the table of contents will make the searching for the recipes much easier. Moreover, if you manage to have an appendix at the end of the notebook, where locations of different topics in the notebook are arranged based on certain keywords inside the subject, that would make the search for that recipe topic much easier.

Just like an index in the book, SQL also has an index that speeds up the data retrieval process from the database. Want to learn about types of indexes in SQL server? In the following, we will learn about all the SQL server index types and their application. If you have SQL server index interview questions to prepare, this article will be a big help.  

Before planning to learn about how to create sql index, make sure to learn types of indexes in SQL server first. In the next few paragraphs, we will learn about different SQL server index types and their usage. Let’s begin with the SQL server index tutorial.Learn SQL Server in the Easiest Way

Different Types Of Indexes In SQL Server

Imagine a table called Employee in a Payroll database. You have names starting with different characters. Some of the names start with A, some with B and so on. There are hundreds of employees on the table. There is always a possibility that the names are stored in an unsorted manner, i.e. a name starting with D comes before a name starting with A and then comes a name starting with C and so on. If somebody needs to search a particular employee name that starts with A from the list of employee names, wouldn’t it be better if the data is physically arranged in such a manner? The clustered SQL index does just that to a set of data. It arranges the data physically every time somebody enters updates or deletes a particular record from the table.


There is another way of achieving this. Instead of physically arranging the data, we can have an option to record the exact location of each record in a separate place. We can refer to that record, every time we need to search for a name in the original table. This is how non clustered SQL index works.

Clustered IndexNon-Clustered Index
Physically rearranges the record each time somebody deletes, inserts or updates a recordThe data is not rearranged. Instead, location of each record is maintained in a separate location which is referred each time a record is searched.
Performance is slowPerformance is fast
A particular table can have one clustered indexA particular table can have more than one non clustered index
Defined in a ordered data fileNo ordered data file required

Ordered Indexing Is of Two Types ?

Dense IndexSparse Index
In the dense index, there is an index record for every search key value in the databaseIn the sparse index, index records are not created for every search key
Index records contain search key value and a pointer to the actual record on the disk.index record here contains a search key and an actual pointer to the data on the disk

Dense Index Diagram


Sparse Index Diagram


How to define a Clustered Index?

CREATE CLUSTERED INDEX test_index1 ON [dbo].[tbl_students] (Firstname)

The above query creates a clustered index name test_index1 on Firstname in tbl_students.

If you are using SQL Server Management Studio you can see the index created like below:


How to define a non-clustered index?

CREATE INDEX test_index ON [dbo].[tbl_students1] (Firstname);

The above query creates a non-clustered index named test_index in tbl_students on column firstname.

The output looks like below


Unique Index

In the case of a unique index, the column based on which the index is created should be unique. This means no two rows in the indexed column can have the same value.

The Primary key is by default a uniquely indexed column.

Now let us see how we can create a unique index.

The query looks somewhat like below


ON [dbo].[tbl_students1] (Firstname)

Here we are creating a unique index called test_index2 on column Firstname of table tbl_students1.

The output of the SQL Statement looks like below.


Comparison between Clustered, Non-Clustered and Unique Index

These are stored physically on the tableStored in a separate placeType of clustered index where there is no duplicate value eg Primary key

When to index

  • Column has varieties of value
  • Column frequently used in queries
  • The table frequently used in joins.

When not to index

  • Columns which are not used in queries
  • When a table is updated very frequently
  • Columns contain few varieties of values
  • Tables with small data

Advantages of Indexing

  • Speed up SELECT query
  • Helps to make a row without duplicates(primary, unique) 
  • If the index is set to fill-text index, then we can search against large string values.

Disadvantages of Indexing

  • Indexes take additional memory space.
  • Indexes slow down INSERT, UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field. INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated.

Column Store Index and traditional Index

Clustered Column Store IndexNon Clustered Column Store Index
It is the physical storage for the entire table.Secondary index that is created on a row store table

Columnstore indexes are of two types

Clustered Column Store IndexNon Clustered Column Store Index
It is the physical storage for the entire table.Secondary index that is created on a row store table

Clustered Column Store Index Diagram


Clustered Column Store Index Query


This creates a clustered column store index named cci_T1 on tbl_students1

Read: SSRS Sub Reports and deployment process-How to do it

The output is


Non-Clustered Column store index diagram


Non-Clustured Column store Index

CREATE nonCLUSTERED COLUMNSTORE INDEX cci_T2 ON [dbo].[tbl_students1] (firstname)

This creates a non-clustered columnstore index cci_T2 on column firstname in tbl_students1.

The output is

SQL Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

Comparison between Columnstore and Rowstore index

Columnstore IndexRowstore Index
Stored in column-wise data formatStored in row-oriented data pages
Applicable mostly for read-only queries with large data sets, like data warehousing workloadsApplicable  for transactional workloads since these workloads tend to require table seeks instead of large-range table scans
Memory usage is lowerMemory usage is higher
Transaction process is slowTransaction process is fast
Fast is analytics queriesSlow in analytics queries


The above few paragraphs give readers a compressed idea on what is an SQL index, types of indexes in SQL server and their application. It is not a detailed discussion, but at least gives the reader a way forward on what is there and how to use SQL index when they are learning about SQL Server indexes, how to create SQL index or are preparing for SQL server index interview questions.

If you want to learn about how to create sql index, SQL server index types and other SQL concepts in detail and are constantly looking for the best SQL server index article ACTE Training offers SQL server certification training, covering all the basic to advanced SQL concepts with quality instructor-led classes.

Are you looking training with Right Jobs?

Contact Us

Popular Courses