What are indexes in database?

What is an Index in SQL Server?

An index is a performance-tuning method of allowing faster retrieval of records and an index creates an entry for each value that appears in the indexed columns.

Unique:

Optional. Indicates that the combination of values in the indexed columns must be unique.

Clustered:

Optional. Indicates that the logical order determines the physical order of the rows in the table.

Non-Clustered:

Optional. Indicates that the logical order does not determine the physical order of the rows in the table.

Index_name:

The name of the index to create.

Table_name:

The name of the table or view on which the index is to be created.

Column1, ... column_n:

The columns to base the index.

ASC | DESC:

The sort order for each of the columns.

Include ( column1, ... column_n ):

Optional. The columns that are not key columns to add to the leaf level of the nonclustered index.

WHERE condition:

Optional. The condition to determine which rows to include in the index.

ON partition_scheme ( column ):

Optional. Indicates that the partition schema determines the filegroups in which the partitions will be mapped.

ON filegroup:

Optional. Indicates that the index will be created on the specified filegroup.

ON default_filegroup:

Optional. Indicates the default filegroup.

FILESTREAM_ON { filegroup | partition_scheme }:

Optional. Indicates where to place the FILESTREAM data for a clustered index.

Index Example:

Let's look at an example of how to create an index in SQL Server (Transact-SQL).

For example:

CREATE INDEX contacts_idx

  ON contacts (last_name);

In this example, we've created an index on the contacts table called contacts_idx. It consists of only one field - the last_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX contacts_idx

  ON contacts (last_name, first_name);

In this example, we've created an index on the contacts table called contacts_idx but this time, it consists of the last_name and first_name fields.

Since we have not specified ASC | DESC to each of the columns, the index is created with each of the fields in ascending order. We could modify our example and change the sort orders to descending as follows:

CREATE INDEX contacts_idx

  ON contacts (last_name DESC, first_name DESC);

This CREATE INDEX example will create the contacts_idx index with the last_name sorted in descending order and the first_name sorted in descending order.

UNIQUE Index Example:

Next, let's look at an example of how to create a unique index in SQL Server (Transact-SQL).

For example:

CREATE UNIQUE INDEX contacts_uidx

  ON contacts (last_name, first_name);

This example would create an index called contacts_uidx on that contacts table that consists of the last_name and first_name fields, but also ensures that the there are only unique combinations of the two fields.

You could modify this example further to make the unique index also clustered so that the physical order of the rows in the table is determined by the logical order of the index.

For example:

CREATE UNIQUE CLUSTERED INDEX contacts_uidx

  ON contacts (last_name, first_name);

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

Rename an Index:

Syntax

The syntax for renaming an index in SQL Server (Transact-SQL) is:

sp_rename 'table_name.old_index_name', 'new_index_name', 'INDEX';

table_name

The name of the table where the index has been created.

old_index_name

The name of the index that you wish to rename.

new_index_name

The new name for the index.

Example:

sp_rename 'contacts.contacts_idx', 'contacts_index_cname', 'INDEX';

In this example, we're renaming the index on the contacts table called contacts_idx to contacts_index_cname.

Drop an Index:

Syntax

The syntax for dropping an index in SQL Server is:

DROP INDEX table_name.index_name;

table_name

The name of the table where the index has been created.

index_name

The name of the index to drop.

Example:

Let's look at example of how to drop an index in SQL Server (Transact-SQL).

For example:

DROP INDEX contacts.contacts_idx;

In this example, we're dropping an index called supplier_idx.