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: -
