Creating and Dropping SQL Indexes

Indexing in SQL Explained

Before we start to explain the syntax required to drop and create SQL indexes, you need to first understand what is an index in SQL. To put it short, it is a distinct database structure that simplifies the search and makes the query perform faster.

What Is an Index in SQL

An index in SQL acts as a specific pointer that helps you locate the indexed data quicker. While it has no effect on the actual data in the table, it does require some disk space to keep a copy of the indexed data.

In this manner, an SQL index works very similarly as the index section at the end of the book which takes up pages but contains no original information.

When to Use Indexes in SQL

Indexing in SQL helps to speed up using WHERE and SELECT clauses. However, it slows down the performance of UPDATE and INSERT statements, used for data input.

Using an index makes the most sense when a particular column:

• Holds little to none NULL values
• Holds values of a rather wide range
• Is often used with WHERE
• Is often used in joins
• Is not constantly updated

Syntax for Indexing in SQL

To assign an index to a particular column, you need to use the CREATE INDEX statement and define three parameters:

• The name of the index
• The name of the table
• The name of the column

CREATE INDEX indexname
ON tablename (column1, column2, ...);

In the code example below, you can see an index named idx_lastname assigned to the LastName column in the Persons table:

CREATE INDEX idx_lastname
ON Persons (LastName);

Matching Indexes

It is possible to assign the same SQL index to multiple columns. List them in the parentheses and divide them using commas:

CREATE INDEX idx_lastname
ON Persons (LastName, FirstName);

Unique Indexes

You can also assign a unique index in SQL. The syntax is almost the same:

CREATE UNIQUE INDEX indexname
ON tablename (column);

Unique SQL indexes are often used to guarantee the integrity of the column data. If you assign the unique index to a single column, it has to hold unique values. By creating a multicolumn unique index, you can ensure that each value combination is unique.

Dropping an Index

To drop an index in SQL, follow the syntax example below:

DROP INDEX [IF EXISTS] indexname
ON tablename;

The DROP INDEX statement will remove the specified index from the database.

If you try to remove an index that does not exist, the system will fire an error. To prevent one, use the IF EXISTS statement.

Note: each database management system can have its own syntax for managing indexes – make sure you read up about the one you choose.