All About Index In SQL Server 2020

Sql Server By Sagar Jaybhay

In this article you will understand what is Index In SQL Server. How To Create Index and Different Types of Indexes In SQL By Sagar Jaybhay. Also You will leran Unique and Non Unique Index what is the difference between them.

Index In SQL Server

Index is used to find data from the table quickly. An index is created on views and tables. It is similar to the book index.

If you apply index it will improve the query performance drastically. If there is no index then the query engine checks every row from beginning to end and it will require time which is bad and this is called a Table scan.

You can create an index in the SQL server in 2 ways. 1) by Query 2) Graphical Interface.

The index is a special kind of data structures which is associated with table and views by the help of this you can increase the performance of the query.

General Syntax:

Create index index_name on table_name (column_name asc)

Example

create index in_employee on Employee (empid asc)

The above query creates an in_employee index on the Employee table on the empid column.

Create Index In SQl Server
Create Index In SQl Server

To find out all the indexes created on the table using below command

sp_helpindex Employee

In this sp_heplindex is a system stored procedure.

How to drop the index?

Drop index tablename.index_name;
-- Example
Drop index employee.index_name

There are different types of indexes present in the SQL Server.

  1. Clustered
  2. Non-Clustered
  3. Unique
  4. Filtered
  5. XML
  6. Full text
  7. Spatial
  8. ColumnStore
  9. Index with included column
  10. Index on a computed column

Clustered Index

A clustered index determines the physical order of data in a table. So as it determines the physical order that’s why table can have only one clustered index.

If your column contains primary key constraint and on that table, if you create index then it automatically marks as a Clustered index.

Suppose you create a table in that on a one-column you create a primary key then it will automatically create clustered index on that column for you see below image.

Clustered Index
Clustered Index

create table tblEmp
(
id int primary key,
name varchar(100),
salary int,
gender varchar(10),
city varchar(10)
)

sp_helpindex
sp_helpindex

When your table contains a clustered index it is called a clustered table. If the table has no clustered index then the data rows are stored in an unordered structure which is called a heap.

Clustered index organizes data in a special structure so it is called B-tree.

See below image we insert data in a random manner but when we select this data you able to see it is in an ordered manner.

insert into tblEmp values(10,'sagar',1000,'Male','Pune'),(11,'XYZ',1000,'Female','USA'),(12,'ABC',1000,'Male','JAPAN'),(1,'JAYBHAY',1000,'Male','INDIA');

select * from tblEmp

Clustered Index Example
Clustered Index Example

So it means that clustered index automatically organizes the data in sorted order.

Index or Clustered can contain multiple columns while creating the index. So this index we called as Composite clustered index.

If your column allows null then you not able to create clustered index on that column.

Ex.

Telephone Directory: It is arranged as alphabetical order.

Non-Clustered Index:

It is similar to the index in the book. Non clustered index stored at one place and actual data are stored in another place. This non-clustered index has pointers to the storage location of the data.

As it stores index and data separately you can add multiple indexes on the table.

In the index itself, data is stored in ascending or descending order which doesn’t influence the data store functionality in that table.

You can add as many indexes you can have on the table.

Ex.

A textbook

create nonclustered index indx_Employee on Employee(full_name,gender);

Non Clustered Index
Non Clustered Index

Difference between Clustered and Non-Clustered Index?

Clustered Index Non-Clustered Index
Only One Index per table. No restriction on how many indexes you create.
It is faster bcoz it is the referred underlying table. Extra lookup is no needed It is not faster as compared to the clustered index. Extra lookup is needed/
No Extra disk storage space needed. Extra disk space is needed.
Size of a clustered index is quite larger Size compared to the clustered index is small.
Can improve the performance of data retrieval is the main feature. It is generally created on that column on which we took joins.

Unique Index and Non-Unique Index:

Unique Index: It is used to enforce uniqueness of values in the respective column.

By default, the primary key constraints create a unique key or unique clustered index.

If you see the uniqueness is the property of clustered and non clustered index.

create unique nonclustered index indx_Employee on Employee(full_name,gender)

What is the difference between the Unique index and Unique Constraint?

In this, there is no major difference between these 2. When you use a unique constraint the unique index is automatically gets created.

In our employee table, we add unique constraints on the email column. To add a unique constraint we need to alter the table.

alter table Employee
add constraint uq_employee
unique(email);

Unique And Non Unique in SQL
Unique And Non Unique in SQL

See above image the while running the query index gets created.

When to Use a unique constraint?

When we required data integrity we need to use unique constraints that automatically created the unique index. Data validation is the same manner whatever you create a unique index or add a unique constraint.

What are the advantages and disadvantages of indexes in SQL?

Advantages:

  1. Indexes are used to find data quickly.
  2. A select, update, delete statement with where clause can benefit from the indexes.
  3. Also, order by clause and group by clause benefit from these indexes.

Disadvantages:

  1. Additional disk space is required: Clustered index does not require additional storage. Every non clustered index required additional space as it stored from a separate table. And the amount of space required for it depends on the size of the table and how many columns you choose to create an index.
  2. Insert, Update, Delete statement become a slow: what it means for delete, update to locate the record time required is much less but when a table is huge and there are multiple indexes then these indexes need to be updated and in this case, too many indexes updates actually hurt the performance.
  3. Covering Query:- suppose you select all the columns from a table in select query and all of these columns are present in the index then there is no lookup but if these columns are not present in the index column then a lookup is required.

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts

Leave a Comment