C C++ Java Data Structure Python JavaScript CSS Ajax PL/SQL PHP Puzzles C programs C++ programs Java programs

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

sql index tutorial

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








COMMENTS