Summary -

In this topic, we described about the below sections -

How to create index?

To fetch data quickly and increase performance, we need to create index on table. By using CREATE INDEX Statement, we can create index on table in database.

Syntax -

Create Index with duplicates – 

CREATE INDEX index_name 
ON Table_name (column1, column2, …, columnN);

Create Index with duplicates – 

 CREATE UNIQUE INDEX index_name
ON Table_name (column1, column2, …, columnN);

Create Clustered Index with duplicates – 

CREATE UNIQUE CLUSTERED INDEX index_name 
ON Table_name (column1, column2, …, columnN);

Create non-clustered Index with duplicates – 

CREATE NONCLUSTERED INDEX index_name
ON Table_name (column1, column2, …, columnN);

Create non-clustered Index with duplicates -

CREATE UNIQUE NONCLUSTERED INDEX index_name
ON Table_name (column1, column2, …, columnN);
  • Index_name - Represents name of the index that is going to create.
  • Table_name - Represents name of the table.
  • Column1, column2, …, columnN - Represents the columns that are used to create index.
  • UNIQUE - Creates a unique index on a table or view. In the unique index, no two rows can have the same index key value. A cluster index should be unique.

Note - The default index type is NONCLUSTERED. If CLUSTERED is not specified, a nonclustered index is created.

Example –

Consider employee_details with eid, ename, designation, manager_id, date_of_hire, salary and dept_id as shown below -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-11-07 45000.00 1000
002 Employee2 Director 2019-11-07 40000.00 2000
003 Employee3 Manager Employee1 2019-11-07 25000.00 1000
004 Employee4 Manager Employee2 2019-11-07 24000.00 2000
005 Employee5 Analyst Employee3 2019-10-08 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 20000.00 1000
007 Employee7 Clerk Employee3 2019-10-08 15000.00 1000
008 Employee8 Salesman Employee4 2019--10-08 15000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 15000.00 2000

scenario – Creating an index on table.

Requirement – Create an index idx_ed on manager_id column in employee_details table . The query was as follows -

CREATE INDEX idx_ed 
    ON employee_details (manager_id);

By executing above query, we can create an index on employee_details table.


Scenario – Creating unique index on table.

Requirement – Create unique index idx_empid on emp_id column in employee_details table.
The query was as follows - 

CREATE UNIQUE INDEX idx_empid 
ON employee_details (emp_id);

By executing above query, we can create unique index on employee_details table.


Scenario – Creating clustered index on table.

Requirement – Create clustered index idx_empid on emp_id column in employee_details table. The query was as follows - 

CREATE CLUSTERED INDEX idx_empid 
ON employee_details (emp_id);

By executing above query, we can create clustered index on employee_details table.


Scenario – Creating nonclustered index on table.

Requirement – Create nonclustered index idx_empid on emp_id column in employee_details table. The query was as follows - 

CREATE NONCLUSTERED INDEX idx_empid 
       ON employee_details (emp_id);

By executing above query, we can create nonclustered index on employee_details table.


Scenario – Creating unique nonclustered index on table.

Requirement – Create unique nonclustered index idx_empid on emp_id column in employee_details table. The query was as follows - 

CREATE UNIQUE NONCLUSTERED INDEX idx_empid 
     ON employee_details (emp_id);

By executing above query, we can create unique nonclustered index on employee_details table.


scenario – Creating composite index on table.

Requirement – Create composite index idx_cemp on emp_id, emp_name in employee_details table. The query was as follows - 

CREATE INDEX idx_cemp
   ON employee_details (emp_id, emp_name);

By executing above query, we can create composite index on employee_details table.


scenario – Disable existing index.

Requirement – Disable existing index idx_ed on employee_details table. The query was as follows – 

CREATE INDEX idx_ed
 ON employee_details (manager_id);
 WITH(DROP_EXISTING = ON);

Now existing index idx_ed was disabled by executing above query.