Home »
SQL
SQL INDEX - To speedup data retrieval operations
In this article, we are going to learn what is SQL INDEX, how to create and use INDEX in SQL? How it speeds up the data retrieval?
Submitted by Shubham Singh Rajawat, on December 02, 2017
Indexes are special structures, which are made to speed up data retrieval operations on a database table. Indexes speed up the querying process by giving swift access to the rows of a table, it is similar to a book’s index which gives us direct access to any content inside the book.
Syntax
CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<columns>);
Here,
- UNIQUE - defines index as unique constraint. It doesn’t allow any duplicate values
- Index_name - Name of the index table
- Table_name - Name of base table
- Columns - Name of columns
There are two types of architectures in SQL
1. Clustered
In clustered architecture index values are stored in sorted manner i.e. either in ascending or descending order. Only one clustered index can be made per table.
Clustered indexes are stored physically on the table,that’s why access to them is faster i.e. Data retrieval is faster compare to non-clustered indexes.
2. Non-clustered
Data is present in arbitrary order, do not affect the physical order, create a logical order and use pointers to physical data files i.e. Data rows are spread throughout the table regardless of the value of the indexed column.We can make more than one non-clustered index per table.
Non-clustered indexes are stored separately from the table. Access to them is slower but update and insertion operations are faster on them as compare to clustered index.
Sample table
Types of Indexes
1. Composite Index
An index that contains more than one columns. It can be clustered or non-clustered.
Example
CREATE INDEX UniqueId ON Student(Enroll_No, DOB);
Output
Index created
2. Unique Index
Unique index ensures the uniqueness in each value in the indexed column.
Example
CREATE UNIQUE INDEX EnrollmentNo ON Student(Enroll_No );
Output
Index created