SQL Index – Example | Create, Unique, Composite Index

FREE Online Courses: Your Passport to Excellence - Start Now

Data stored in the tables within the database is quite large in quantity. Practically, searching and retrieving such vast data records creates speed constraints.

To simplify the searching and retrieving of data, we use various techniques, and Indexes provide one such method. In SQL, the searching algorithm is set by the system, and thus, indexing proves to be very beneficial.

Indexes are put internally and are not visible to the user.

Indexes are just like unique labels provided to the data so that the identification of data becomes easy when a search query is made. Indexing is a very popular method used with large datasets in the industry.

What are Indexes in SQL and their Usage?

Indexes are set to fasten the query and are internal to the system, i.e., Indexes are not visible to the user. Indexes can be put in a single column or by using multiple columns.

While creating indexes, we should be very cautious as once we create indexes updating the table becomes very difficult. This happens because, along with the data, we need to update the indexes as well.

Imagine in a bank server we have lakhs of users and even some have the same names. If we depend only on the names for data retrieval, the system would lag a lot.

To resolve the speed and efficiency issues, Indexes are put instead to make the process of retrieving data efficient. And indexes are kept hidden and not available to the user to ensure the security is not at stake.

The statement CREATE INDEX operates to create an index on a table. The syntax is as follows:
Syntax:

CREATE INDEX index_name
ON tableName (columnName1, columnName2, ...);

Demo Database

Let us view our database, which we will use in our demo.
Query: 

SELECT * FROM DataFlair_Employee ;

SQL database

Indexes in SQL

We use the INDEX clause to create an index in our table. The index clause helps us to query the data fast. We can decide for ourselves if we need unique Indexes or whatsoever the condition is put up.

  • Implicit Indexes: Sometimes, a database creates indexes on its own to store the data and retrieve the data efficiently and fast. These are not visible to the users and are created by using the Primary key and the stated constraints while we create the table.
  • Composite Index: Composite indexes are indexes created by using multiple columns as the constraint. We use the composite index to maintain the unique identification of the data points.

Creation of SQL Index

Syntax:

CREATE INDEX index_name
ON tableName (columnName1, columnName2, ...);

Example 1: Let us create an index over the name column of our DataFlair employee database.
Query:

CREATE INDEX name_in
ON dataflair_employee (name_emp);

create index in SQL

Creation of Unique Index in SQL

When we have some repeating common values in our table, we need unique indexes to identify them easily. For this, we use a unique index.

Syntax:

CREATE UNIQUE INDEX index_name
ON tableName (columnName1, columnName2, ...) ;

Example 1: Let us create unique indexing over our names stored in the DataFlair employee table.
Query:

CREATE UNIQUE INDEX name_unique
ON dataflair_employee (name_emp);

create unique index in SQL

Creating Index over Multiple Columns in SQL

Sometimes our columns contain duplicate or repetitive data, due to which the indexing is also duplicate and repetitive. To maintain the uniqueness of our indexes, we pass multiple columns so that the indexes are unique.
Syntax:

CREATE INDEX index_name
ON tableName (columnName1, columnName2, ...) ;

Example 1: Let us create an index on our table with multiple columns (using name and salary column).

Query:

CREATE INDEX name_multiple
ON dataflair_employee (name_emp, salary);

create multiple index in SQL

Conforming the Indexes: We can the existing indexes applied on the table by using the commands. We need to confirm the indexes already applied on the table so that we can keep away from the clashes and data can be put up in unique identities.

Drop Index in SQL

We need to remove the index if we use it on columns while updating data, as updating the data with the indexes would take a large amount of time. The syntax is as follows:
Syntax:

ALTER TABLE tableName
DROP INDEX index_name;

Query:

ALTER TABLE dataflair_employee
DROP INDEX name_multiple ;

drop index in SQL

Summary

When we have a large amount of data and the need for processing arises, we need to be very efficient and fast in searching and retrieving the data. We use the index clause to speed up our ability to search and query data.

The indexes created are unique and are used to get the data. We have understood how we can create and drop data. Indexing helps us with fast and efficient queries.

Your opinion matters
Please write your valuable feedback about DataFlair on Google

follow dataflair on YouTube

1 Response

  1. Amisha damor says:

    Very excellent… Example.

Leave a Reply

Your email address will not be published. Required fields are marked *