SQL Server Indexes-All You Need to Know

Introduction

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.

Read: Data Anamolies-Understanding Them And Learning Ways To Resolve Them

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.

Check out the updated SQL self learning module now!

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.

Read More: Clustered vs Non-Clustered Index

Clustered Index	Non-Clustered Index
Physically rearranges the record each time somebody deletes, inserts or updates a record	The 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 slow	Performance is fast
A particular table can have one clustered index	A particular table can have more than one non clustered index
Defined in a ordered data file	No ordered data file required

Ordered Indexing Is of Two Types ?

Dense Index	Sparse Index
In the dense index, there is an index record for every search key value in the database	In 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.

Read: All You Need to Know About SQL: SQL Tutorial for Beginners & Experienced

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-clustured 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.

Read: What Is Average Salary Of Database Admin In Philippine?

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.

Free demo classes live now for SQL server training, check the date!

Comparison between Clustered, Non-Clustered and Unique Index

Clustured	Non-Clustured	Unique
These are stored physically on the table	Stored in a separate place	Type 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

Read: SQL Cheat Sheet With Powerful Tips & Tricks

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 Index	Non Clustered Column Store Index
It is the physical storage for the entire table.	Secondary index that is created on a row store table

Read: Difference Between Stored Procedure and Function in SQL Server

Columnstore indexes are of two types

Clustered Column Store Index	Non 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

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

This creates a clustered column store index named cci_T1 on tbl_students1

Read: All you Need to Know About SQL Database Administrator Salary

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

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

Signup for the SQL server corporate now, new batches available now!

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 tutorial, JanBask Training offers SQL server certification training, covering all the basic to advanced SQL concepts with quality instructor-led classes.

Read: SQL Developer Resume: Examples, Samples, & Tips (2024)

FaceBook Twitter Google+ LinkedIn Pinterest Email