Thursday, February 7, 2008

SQL SERVER : INDEX

How do we Create INDEX in SQL SERVER ?

Indices are created in an existing table to locate rows more efficiently and quickly. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.

Why do we Create INDEX in SQL SERVER ?

Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search. Index are going to boost the performance when applied on large databases but you may not see much performance difference when applied on small databases.

What are various types of INDICES in SQL SERVER ?

  1. Unique Index
  2. Simple Index


How do we create UNIQUE INDEX ?

SYNTAX:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)


A unique index means that two rows cannot have the same index value.


How do we create SIMPLE INDEX ?

SYNTAX:
CREATE INDEX index_name
ON table_name (column_name)


When the UNIQUE keyword is omitted, duplicate values are allowed.