Indexes in SQL

Shristi
Shristi
Nov 2 · 2 min read

Indexes are used in SQL to speed up the process of retrieval of the data from the database by accelerating the speed of execution of Select query and where clause.

It can be created and deleted without causing any effect on the source of index i.e table.

Type of Indexes

SQL Server supports two types of indexes:

1.Simple index

2.Unique Index

3.Composite Index

4.Function-Based Index

5.Clustered Index

6.Non-Clustered Index.

1.Simple Index

It is created based on only one table column.

2.Unique Index

The index created on the unique columns of the table which only contains the distinct data is called the unique index.

If the unique index is created on the column which as redundant data then the unique will not be formed on such column

3.Composite Index

An index is called a Composite index when the it is created on the two or more columns which are related i.e if the columns in the composite index are included in the where clause of the query.

4.Function-Based Index

This index allows us to create an index on the functional columns of the table.

The function-based indexes provide immediate value.

It is uncomplicated to implement and it also gives the ability to index the computed columns

5.Clustered Index

Clustered Index is used to physically store the index memory in the sorted form which is in ascending and descending order.

This index is created for each table individually.

A clustered index is suitable for the table which has heavy data modifications.

6.Non-Clustered Index.

The Non-clustered index does not store the index in a sorted form in the memory.

The benefit of this index is searching for value in a specific range.

These indexes can be create 999 for a single table.

How to create an INDEX?

An index is created using the CREATE INDEX command which gives the ability to name the INDEX to the specific table.

Syntax:

CREATE INDEX index_name

ON table_name(column_names);

Example:

CREATE INDEX Emp_sal

ON Employee(Salary);

How to drop an INDEX?

An index can be deleted using DROP command

Syntax:

DROP INDEX index_name on table_name;

Example:

DROP INDEX Emp_sal on Employee;

If you are new to SQL Server start with the following must-watch video: -

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade