---
title: "SQL index"  
description: "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 sq"  
author: "Anchal Kesharwani"  
published: 2014-06-25  
updated: 2017-11-29  
canonical: https://www.mindstick.com/articles/1435/sql-index  
category: "asp.net"  
tags: ["asp.net"]  
reading_time: 4 minutes  

---

# SQL index

In this article [describe the concept](https://www.mindstick.com/forum/158622/describe-the-concept-of-a-divide-by-zero-exception-and-how-to-handle-it) 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](https://www.mindstick.com/forum/159327/how-to-set-all-values-in-a-single-column-mysql-query) or a group of columns.

##### Syntax for creating index

\

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

##### Example

\

```
CREATE INDEX CourseIndexON 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](https://www.mindstick.com/forum/159374/transaction-with-multiple-updates-and-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](https://www.mindstick.com/forum/23111/swap-keys-and-duplicate-values-in-hashmap), whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an [error message](https://www.mindstick.com/forum/23174/error-message-the-page-you-are-requesting-cannot-be-served-because-of-the-extension-configuration). 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](https://www.mindstick.com/forum/160197/what-is-the-normalization-in-sql-server-explain-its-impact-on-data-integrity-and-performance) of the defined columns is ensured.

##### Syntax

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

##### Example

\

```
CREATE UNIQUE INDEX UniqueCourseIndexON 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](https://www.mindstick.com/blog/337/clustered-non-clustered-indexing-in-sql-server) can be created on a given [database table](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-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](https://www.mindstick.com/forum/156836/what-is-a-c-sharp-program-to-print-individual-from-a-string-in-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 ClusteredBookIndexON book(book_name) 
```

##### Non-clustered Index

When you create a [unique constraint](https://www.mindstick.com/forum/159036/how-can-i-create-a-unique-constraint-that-also-allows-nulls), 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 NonclusteredBookIndexON 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.

---

Original Source: https://www.mindstick.com/articles/1435/sql-index

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.