In this article describe the concept of sql index. It is important feature sql that can fast the searching. Here we describe the simple examples of sql index.

An index can be created in a table to find data more quickly and efficiently. Sql index is like as  book’s index helps you find information quickly within that book. 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.

Syntax for creating index

CREATE INDEX [INDEX_NAME]
ON [TABLE_NAME](COULUMN_NAME) 

Example

CREATE INDEX CourseIndex
ON Course(course_name) 

Syntax for drop index

DROP INDEX [INDEX_NAME]
ON [TABLE_NAME] 

Example

DROP INDEX CourseIndex ON Course; 

Types of index

·     Unique Index

·     Clustered Index

·     Non-clustered Index

Unique Index

Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. The SQL server administrator does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created.

The benefits of unique index is data integrity of the defined columns is ensured.

Syntax

CREATE UNIQUE INDEX [INDEX_NAME]
ON [TABLE_NAME](COULUMN_NAME) 

Example

CREATE UNIQUE INDEX UniqueCourseIndex
ON Course(course_name) 

Clustered index

Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indexes can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique non-clustered index. The primary key column cannot allow NULL values.

Syntax

CREATE CLUSTERED INDEX [INDEX_NAME]
ON [TABLE_NAME](COULUMN_NAME) 

Example

create table book
(
       book_id int,
       book_name varchar(100),
       book_price float
)
 
CREATE CLUSTERED INDEX ClusteredBookIndex
ON book(book_name) 

Non-clustered Index

When you create a unique constraint, a unique non-clustered index is created to enforce a unique constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

A non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value. The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

Syntax

CREATE NONCLUSTERED INDEX [INDEX_NAME]
ON [TABLE_NAME](COULUMN_NAME) 

Example

CREATE NONCLUSTERED INDEX NonclusteredBookIndex
ON book(book_id,book_name, book_price) 

Advantage of indexes

There are some advantages of indexes:

·      It is used for searching for records

·      It is used for sorting records

·      It is also be used for grouping records

·      It maintains unique column

Drawbacks of indexes

There are some disadvantages of indexes:

·     insert/update performance when indexed columns are modified will be worse

·     more indexes will use more disk space

·     Each index potentially adds an alternative access path for a query for the

optimizer to consider, which increases the compilation time.