What is SQL CREATE INDEX Statement?

In this article, we will learn SQL CREATE INDEX Statement,This free SQL tutorial for complete beginners will help you learn SQL from scratch.

SQL CREATE INDEX Statement

Q: How to use SQL CREATE INDEX Statement in SQL?

SQL CREATE INDEX statement creates indexes in a table for fast and efficient data retrieval

SQL Indexes

SQL DROP INDEX Statement

SQL CREATE INDEX statement creates indexes in a table for fast and efficient data retrieval.

CREATE INDEX Statement

The CREATE INDEX Statement in SQL is used to create indexes in tables and retrieve data from the database faster than usual.

Indexes are invisible structures that work behind the scenes to speed up data retrieval operations in databases. They are essential for optimizing query performance and improving overall system efficiency.

Indexes can not be seen by users, and are only used to speed up the process of searches/queries.

Important Points:

Only use INDEX constraint on a column, that is frequently searched or used in WHERE clauses of SELECT queries.
Adding Indexes to all columns makes the process of updating the database slower, as on each update Index updates as well.

Syntax

There are two syntaxes to create index in table:

CREATE INDEX Syntax

Simple CREATE INDEX Syntax is:

CREATE INDEX index_name

ON table (column1, column2…..);

This will create an index on the table and duplicate values are allowed.

CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX syntax is:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, …);

This will create a unique index on the table and will not allow duplicate values.

SQL CREATE INDEX Statement Example

Let’s look at some examples of the CREATE INDEX Statement in SQL and understand it’s working.

First, we will create a demo database and table, on which we will use the CREATE INDEX command.

Demo SQL Database

SQL

CREATE DATABASE w3wiki;
USE w3wiki;
CREATE TABLE STUDENTS(
    STUDENT_ID INT PRIMARY KEY,
    NAME VARCHAR(20),
    ADDRESS VARCHAR(20),
    AGE INT,
    DOB DATE);
INSERT INTO STUDENTS VALUES(1,'DEV SHARMA','91 ABC STREET',25,'1991-08-19');
INSERT INTO STUDENTS VALUES(2,'ARYA RAJPUT','77 XYZ STREET',21,'1999-09-29');
INSERT INTO STUDENTS VALUES(3,'GAURAV VERMA','101 YEMEN ROAD',29,'2000-01-01');

Inserting data

Create Index in SQL Table Example

In this example, we will use the CREATE INDEX command to create an index.

Query:

 CREATE INDEX idx
 ON STUDENTS(NAME);

Output

Creating an index

Retrieving Data From the Table Using Indexes

In this example, we will use the USE INDEX command to retrieve data from the table.

 SELECT *
 FROM STUDENTS USE INDEX(idx);

Output

Retrieving data

DROP INDEX Statement

To delete an index in a table, we use the DROP INDEX Statement.

Syntax

The Syntax for DROP INDEX Statement may differ based on the type of database you use, for example:

MS Access

DROP INDEX index_name ON table_name;

SQL Server

DROP INDEX table_name.index_name;

DB2/Oracle

DROP INDEX index_name;

MySQL

ALTER TABLE table_name DROP INDEX index_name;

Important Points About SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables to retrieve data more quickly.
Indexes are used to improve the efficiency of searches for data, presenting data in a specific order, and when joining tables.
Increasing the number of indexes in a database can impact overall system performance, so indexes should be created on columns that will be frequently searched against.
The CREATE UNIQUE INDEX statement creates a unique index on a table where duplicate values are not allowed.
The DROP INDEX statement is used to delete an index from a table in SQL.