SQL - Create Index

---

What is SQL index?

An SQL index is an effective way to quickly retrieve data from a database. Indexing a table or view can significantly improve query and application performance. Though indexes help accelerate search queries, users are not able to directly see these indexes in action.

An SQL index is a special table that provides an efficient way to search larger tables enabling faster retrieval of required data. When we try to retrieve data from multiple tables using joins, indexes help to improve the performance.

There is usually no need to create an index when dealing with small tables; however, when the table contains a large number of records, having an index will significantly improve search query performance. SQL indexes are essential for optimizing the performance of any Relational Database Management System (RDBMS) as data volumes grow. SQL Server supports multiple types of indexes that can be used for this purpose.

Following are the various types of indexes in SQL −

• Clustered Indexes
• Non-Clustered Indexes
• Unique Indexes
• Filtered Indexes
• Full-text Indexes

How to create SQL Index?

The CREATE INDEX statement in SQL is used to create an index on one or more columns of a table in a database.

Syntax

Following is the syntax of CREATE INDEX statement in SQL −

CREATE INDEX index_name 
ON table_name (column_name1, column_name2,…column_nameN);

Here,

• index_name This specifies the name of the index that you want to create.
• table_name This specifies the name of the table on which you want to create the index.
• (column_name1, column_name2,…column_nameN) are the names of one or more columns on which the index is being created.

Example

First of all, let us try to create a table named CUSTOMERS using the following query −

SQL> CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID));
);

Let us insert some values into the above created table using the following query −

SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (2, 'Khilan', '25', 'Delhi', 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, 'kaushik', '23', 'Kota', 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4, 'Chaitali', '25', 'Mumbai', 6500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (5, 'Hardik','27', 'Bhopal', 8500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (6, 'Komal', '22', 'MP', 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (7, 'Muffy', '24', 'Indore', 5500);

Once the table is created, let us create an index for the column named “NAME” in the CUSTOMERS table using the following query −

SQL> CREATE INDEX index_name on CUSTOMERS(NAME);

Output

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

The stored procedure named sp_helpindex contains information about all the indexes created on a table or view. You can get the list of the indexes created on the CUSTOMERS table using the following query.

Here, we are calling the sp_helpindex procedure by passing the name of the desired table as the @objname.

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

As you observe the output of the above query, you can find the column name “NAME”, along with the ID in the list of indexes.

+----------------------------------+----------------------+------------------+
| index_name                       | index_description    | index_keys       |
+----------------------------------+----------------------+------------------+
| index_name                       | nonclustered located | NAME             |
|                                  | on PRIMARY           |                  |
| PK__CUSTOMER__3214EC27FBADB0A7   | clustered, unique,   | ID               |
|                                  | primary key located  |                  |
|                                  | on PRIMARY           |                  |
+----------------------------------+----------------------+------------------+

Creating an index on Multiple Fields

We can also create an index on multiple fields or columns of a table using the CREATE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query.

Example

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Here, we are trying to create an index on the columns “NAME” and “AGE” using the following query −

SQL> CREATE INDEX mult_index_data on CUSTOMERS(NAME, AGE);

Output

When we execute the above query, the output is obtained as follows −

Commands completed successfully.

Verification

Now, let us list all the indexes that are created on the CUSTOMERS table using the following query −

SQL> EXEC sys.sp_helpindex @objname = N'CUSTOMERS';

As you observe the output of the above query, you can find the column names “NAME”, and “AGE” along with the ID (PRIMARY KEY) in the list of indexes.

+----------------------------------+----------------------+--------------+
| index_name                       | index_description    | index_keys   |
+----------------------------------+----------------------+--------------+
| mult_index_data                  | nonclustered located | NAME, AGE    |
|                                  | on PRIMARY           |              |
| PK__CUSTOMER__3214EC27FBADB0A7   | clustered, unique,   | ID           |
|                                  | primary key located  |              |
|                                  | on PRIMARY           |              |
+----------------------------------+----------------------+--------------+