Cyber Monday Deal : Flat 30% OFF! + free self-paced courses  - SCHEDULE CALL

- SQL Server Blogs -

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: How to Use Like Operator in SQL Server?

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!

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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.

Different Types Of Indexes In SQL Server

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

Dense 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: What Does It Take To Become a Successful SQL DBA Expert?

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

How to define a Clustered Index?

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

How to define a non-clustered index?

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 The Difference Between Having And Where Clause In SQL?

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.

Unique Index

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

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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: SSIS Package - SSIS DB, Security and Upgrades

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: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

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 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: Coalesce Function SQL Server Example

The output is

Clustered Column Store Index Query

Non-Clustered Column store index diagram

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

Non-Clustured Column store Index

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!

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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: Top 151+ SQL Interview Questions and Answers You Can't Miss

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

-0 day 14 Dec 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

2 days 16 Dec 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

-0 day 14 Dec 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

-0 day 14 Dec 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

-0 day 14 Dec 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

-0 day 14 Dec 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

3 days 17 Dec 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

6 days 20 Dec 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

7 days 21 Dec 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

-0 day 14 Dec 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

13 days 27 Dec 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

6 days 20 Dec 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews