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
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
Theory is great, but we recommend digging deeper!
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
CREATE INDEX idx_lastname ON Persons (LastName);
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);
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;
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.