Types Of SQL Indexes

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.

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.

Ordered Indexing Is of Two Types ?

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

CREATE UNIQUE INDEX test_index2

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

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

Columnstore indexes are of two types

Clustered Column Store Index Diagram

Clustered Column Store Index Query

CREATE CLUSTERED COLUMNSTORE INDEX cci_T1 ON [dbo].[tbl_students1]

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

Comparison between Columnstore and Rowstore index

Summary

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.