SQL - Non Clustered Index

The Non-Clustered indexes contain a copy of the indexed columns along with a pointer that refers to the location of the actual data in the table. It is similar to an index in a textbook that psts the topics and their corresponding page numbers, allowing readers to quickly find the information they need without having to read through the entire book.

Following are some of the key points of the Non-clustered index in SQL −

The non-clustered indexes are a type of index used in databases to speed up the execution time of database queries.

These indexes require less storage space than clustered indexes because they do not store the actual data rows.

We can create multiple non-clustered indexes on a single table.

To get a better understanding, look a the following figure illustrating the working of non-clustered indexes −

Assume we have a table with two columns named “ID” and “NAME”.

If we create a non-clustered index on a column named “ID” in the above table, it will store a copy of the “ID” column with a pointer that refers to the specific location of the actual data in the table.

Syntax

Following is the syntax to create a non-clustered index in SQL −

CREATE NONCLUSTERED INDEX index_name
ON table_name (column_name)

Here,

index_name is the name that you give to the nonclustered index.

table_name is the name of the table where you want to create the nonclustered index.

column_name is the name of the column that you want to use for the nonclustered index.

Example

Let us create a table named “CUSTOMERS” using the following query −

SQL> CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE  INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2),
);

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

SQL>INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(7,  Muffy ,  24 ,  Indore , 5500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,  Ramesh ,  32 ,  Ahmedabad , 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(6,  Komal ,  22 ,  MP , 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(2,  Khilan ,  25 ,  Delhi , 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(4,  Chaitap ,  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(3,  kaushik ,  23 ,  Kota , 2500);

Let’s retrieve the CUSTOMERS table using the following query −

SQL> SELECT * FROM CUSTOMERS

The table is successfully created in the SQL database.

+-----+-----------+-------+--------------+------------+
| ID  | NAME      | AGE   | ADDRESS      | SALARY     |
+-----+-----------+-------+--------------+------------+
|7    | Muffy     | 24    | Indore       | 5500.00    |
|1    | Ramesh    | 32    | Ahmedabad    | 2000.00    |
|6    | Komal     | 22    | MP           | 9000.00    |
|2    | Khilan    | 25    | Delhi        | 1500.00    |
|4    | Chaitap  | 25    | Mumbai       | 6500.00    |
|5    | Hardik    | 27    | Bhopal       | 8500.00    |
|3    | Kaushik   | 23    | Kota         | 2500.00    |
+-----+-----------+-------+--------------+------------+

Now, let us try to create a non-clustered index on a single column named “ID” using the following query −

SQL> CREATE NONCLUSTERED INDEX NON_CLU_ID ON CUSTOMERS(ID);

Output

On executing the above query, the output is displayed as follows −

Commands completed successfully.

Verification

Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query −

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

As we observe the output of the above query, we can find the column named “ID” in the pst of indexes.

+----------------------------------+--------------------------+--------------+
| index_name                       | index_description        | index_keys   |
+----------------------------------+--------------------------+--------------+
| CLU_ID                           | nonclustered             | ID           |
|                                  | located on PRIMARY       |              |
+----------------------------------+--------------------------+--------------+

Let’s retrieve the CUSTOMERS table again using the following query −

SQL> SELECT * FROM CUSTOMERS

As we observe the output of the above query, the non-clustered index does not sort the rows physically instead, it creates a separate key-value structure from the table data.

+-----+-----------+-------+--------------+------------+
| ID  | NAME      | AGE   | ADDRESS      | SALARY     |
+-----+-----------+-------+--------------+------------+
|7    | Muffy     | 24    | Indore       | 5500.00    |
|1    | Ramesh    | 32    | Ahmedabad    | 2000.00    |
|6    | Komal     | 22    | MP           | 9000.00    |
|2    | Khilan    | 25    | Delhi        | 1500.00    |
|4    | Chaitap  | 25    | Mumbai       | 6500.00    |
|5    | Hardik    | 27    | Bhopal       | 8500.00    |
|3    | Kaushik   | 23    | Kota         | 2500.00    |
+-----+-----------+-------+--------------+------------+

Creating Non-Clustered Index on Multiple Columns

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Now, try to create a non-clustered index on multiple columns of the table such as “ID”, “AGE” and “SALARY” using the following query −

SQL> CREATE NONCLUSTERED INDEX NON_CLUSTERED_ID 
ON CUSTOMERS(ID, AGE, SALARY);

Output

The below query will create three separate non-clustered indexes for ID, AGE, and SALARY.

Commands completed successfully.

Verification

Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query −

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

As we observe the output of the above query, we can find the column names “ID”, “AGE” and “SALARY” in the pst of indexes.

+----------------------------------+--------------------------+---------------+
| index_name                       | index_description        | index_keys    |
+----------------------------------+--------------------------+---------------+
| NON_CLUSTERED_ID                 | nonclustered             |ID, AGE, SALARY|
|                                  | located on PRIMARY       |               |
+----------------------------------+--------------------------+---------------+