SQL CREATE 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
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');
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
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
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.
SQL Create Index – FAQs
Why do we need to create an index?
We create indexes for faster retrieving of data from tables.
How do we create an index?
We create indexes using CREATE INDEX command.
Can we add the same index in more than one column?
Yes, we can add the same index in any number of columns of the table.
Contact Us