Basic about Indexes – using and example

This SQL tutorial explains how to create and drop indexes with syntax and examples.

What is an Index in SQL?

An index is a performance-tuning method of permitting faster retrieval of records. An index creates an entry for every price that seems in the listed columns. Each index identify must be unique in the database.

Create an Index

You can create an index in SQL using the CREATE INDEX statement.

Syntax

The syntax to create an index in SQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n);

UNIQUE The UNIQUE modifier shows that the mixture of values in the indexed columns should be unique. index_name The identify to assign to the index. table_name The identify of the desk in which to create the index. column1, column2, … column_n The columns to use in the index.

Example

Let’s seem at an example of how to create an index in SQL.

For example:

CREATE INDEX websites_idx
  ON websites (site_name);

In this example, we’ve got created an index on the web sites desk referred to as websites_idx. It consists of only one area – the site_name field.

We could additionally create an index with extra than one area as in the instance below:

CREATE INDEX websites_idx
  ON websites (site_name, server);

This would create an index referred to as websites_idx that is made up of two columns – site_name and server.

Unique Index

Similar to a primary key, a special key approves you to choose one column or aggregate of columns that have to be unique for each record. Although you can only have one fundamental key on a table, you can create as many unique indexes on a table as you need.

To create a special index on a table, you want to specify the UNIQUE keyword in the CREATE INDEX statement.

For example:

CREATE UNIQUE INDEX websites_idx
  ON websites (site_name);

This example would create a special index on the site_name subject so that this discipline must constantly consists of a special value with no duplicates. This is a splendid way to put into effect integrity within your database if you require unique values in columns that are no longer part of your predominant key.

Drop an Index

You can drop an index in SQL the use of the DROP INDEX statement.

Syntax

The syntax to drop an index in SQL is:

For Oracle and PostgreSQL:

DROP INDEX index_name;

For MySQL and MariaDB:

DROP INDEX index_name
  ON table_name;

For SQL Server:

DROP INDEX table_name.index_name;

index_name The title of the index to drop. table_name The identify of the desk that the index belongs to.

Example

Let’s look at an example of how to drop an index called websites_idx from the web sites table.

For Oracle:

DROP INDEX websites_idx;

Because each index title ought to be unique within the database, we do now not have to specify the web sites table in the DROP INDEX assertion in Oracle.

For MySQL and MariaDB:

DROP INDEX websites_idx
  ON websites;

For SQL Server:

DROP INDEX websites.websites_idx;

As you can see, each database has unique differences in their syntax. Be sure you use the right DROP INDEX command.