Index in SQL Server

An index is a method that allows faster retrieval of records without reading the whole table. It can be created in a table to find data efficiently.

1. Create an Index in SQL server

Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ......., column_n)


Example: Create an Index for table 'Students'.

Example

CREATE INDEX std_id
ON Students (first_name);


User can create an index on a combination of columns within the parentheses as:

Example

CREATE INDEX std_id
ON Students (first_name, last_name)


Example: Create UNIQUE Index in SQL server for table 'Students'.

Example

CREATE UNIQUE INDEX std_uid
ON Students (first_name, last_name)


Example:  Create UNIQUE CLUSTERED Index in SQL server for table 'Students'.

Example

CREATE UNIQUE CLUSTERED INDEX std_uid
ON Students (first_name, last_name)


This example creates an index called 'std_uid' that is unique index based on first_name and last_name fields and the index is also clustered which changes the physical order of the rows in the table.

2. Rename an Index

Syntax

sp_rename 'table_name.old_index _name', 'new_index_name, 'INDEX'


Example: Rename the existing index 'Stud_id' to 'S_id' for table titled 'Students' in SQL server.

The query should be written as:

Example

sp_rename 'Students.Stud_id', 'Students.S_id', 'INDEX'

3. DROP an Index

Syntax

DROP INDEX table_name.index_name;


Example: To DROP an Index for table titled 'Students' in SQL Server.

Example

DROP INDEX Students.Stud_id

▲