SQL Indexes

SQL Indexes – The index is a method of performance tuning that allows for much faster retrieval of records. The index creates a record for each value that appears in the indexed columns. Each index name must be unique in the database.

Create an index

You can create indices in SQL using the CREATE INDEX operator.

CREATE INDEX syntax in SQL

CREATE [UNIQUE] INDEX index_name_id
ON table_name_id column1_id, column2_id, ... column_n_id);

• UNIQUE – The UNIQUE modifier indicates that the combination of values in the indexed columns should be unique
• index_name_id – The name to be given to the index
• table_name_id – Name of the table where the index should be created
• column1_id, column2_id,… column_n_id – Columns for index use

Example:
Let us consider an example of how to create an index in SQL.
For example:

CREATE INDEX webs_idx
ON webs (site_name_id);

In this example, we created an index for a website table called webs_idx. It consists of only one field, the site_name_id field.

We could also create indexes with more than one field, as shown below.

CREATE INDEX webs_idx
ON webs (site_name_id, server_id);

This will create the webs_idx index, which consists of two columns – site_name_id and server_id.

UNIQUE INDEX

Like the primary key, a unique key allows you to select one column or a combination of columns that should be unique for each record.

To create a unique index for a table, you must specify the UNIQUE keyword in the CREATE INDEX statement.
For example.

CREATE UNIQUE INDEX webs_idx
ON webs (site_name_id);

This will create a unique index in the field site_name_id, so that this field must always contain a unique value without duplicates. This is a great way to ensure the integrity of your database if you require unique values in columns that are not part of your primary key.

Delete index

You can delete indexes in SQL using the DROP INDEX operator.

Syntax for removing an index in SQL

For Oracle and PostgreSQL.

DROP INDEX index_name_id;

For MySQL and MariaDB.

DROP INDEX index_name_id; for MySQL and MariaDB.
ON table_name_id;

For SQL Server.

DROP INDEX table_name_id.index_name_id;

• index_name_id – Index name to delete
• table_name_id – Name of the table to which the index belongs

Example:
Let’s look at an example of how to remove an index with the name sites_idx from a site table.

For Oracle.

DROP INDEX webs_idx;

Since each index name must be unique in the database, we do not need to specify the websites table in the Oracle DROP INDEX operator.

For MySQL and MariaDB.

DROP INDEX webs_idx
ON webs;

For SQL Server.

DROP INDEX webs.webs_idx;

As you can see, each database has unique differences in its syntax. Make sure that you use the correct DROP INDEX command.

SQL Index. Indexes in SQ. Database Index