How To Create Index Using Query In SQL

In this article, we will learn how to create and delete Indexes in SQL.

To create indexes in tables, the CREATE INDEX statement is used.

The users cannot see the indexes, they are just used to retrieve data from the database more quickly to speed up searches/queries.

Note: Updating a table with indexes takes more time because the indexes also need an update. So, only create indexes on columns that will be searched frequently against.

CREATE INDEX: 

The CREATE INDEX statement is used to create an index on a table. (Duplicate values are allowed)

Syntax

CREATE INDEX INDEX_NAME 
ON TABLE_NAME (column1, column2, ...columnN);

Example

The subsequent statement would create an index named “IDXTitle ” on the “Title” column in the “Article” table (It allows duplicate values):

CREATE INDEX IDXTitle 
ON Article (Title);

CREATE UNIQUE INDEX: 

The CREATE UNIQUE INDEX statement is used to create a unique index on a table. (Duplicate values are not allowed)

Syntax

CREATE UNIQUE INDEX INDEX_NAME 
ON TABLE_NAME (column1, column2, ...columnN);

Example

The subsequent statement would create an index named “IDXTitle” on the “Title” column in the “Article” table (It doesn’t allow duplicate values):

CREATE UNIQUE INDEX IDXTitle 
ON Article (Title);

DROP INDEX: 

The DROP INDEX statement is used to delete an index in a table.

Syntax

DROP INDEX TABLE_NAME.INDEX_NAME;

Example

The subsequent statement would delete an index named “IDXTitle” of “Article” table:

DROP INDEX Article.IDXTitle;

Also, check How To Add Column With Default Value In SQL