Indexes in SQL Server

Index in sql is created on existing tables to retrieve the rows quickly.

When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

---

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name1, column_name2, column_name3….)

* index_name is the name of the INDEX.

* table_name is the name of the table to which the indexed column belongs.

* column_name1, column_name2.. is the list of columns which make up the INDEX.

Example:

CREATE INDEX PIndex ON Persons (LastName)     ** For Single Column

CREATE INDEX PIndex ON Persons (LastName, FirstName)     ** For Single Column

---

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.

---

Drop Index in SQL

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

Syntex:

DROP INDEX table_name.index_name

---

Clustered Indexes

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

Nonclustered Indexes

A nonclustered index is analogous to an index in a textbook. The data is stored in one place, the index in another, with pointers to the storage location of the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order (which can be dictated by a clustered index). If no clustered index is created on the table, the rows are not guaranteed to be in any particular order.

Note: Main difference between Clustered and Non-Clustered is;

Clustered Index: There can be only one Clustered index for a table. This is usually made on the primary key.

Non-Clustered Index: There can be many Clustered indexes on the table.