articles

Home / DeveloperSection / Articles / Index in SQL Server

Index in SQL Server

Anonymous User8477 06-Jul-2011
Index:

Index is most important concept of SQL Server database to increase performance of retrieving data from database. Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a 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: Creating Index on Table
-----SYNTAX DEMONSTRATION OF CREATING INDEX
CREATE INDEX <INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Index on Table
---- DEMONSTRATION OF CREATING INDEX ON TABLE-----
CREATE INDEX TEST_INDEX
ON
USERLOGIN(ID)
Types of Index use in SQL Server:

There are many types of Indexes in SQL Server in which some important indexes are given as follows:

  •          Unique Index
  •          Clustered Index
  •          Non-Clustered Index
Unique Index:

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself.

UNIQUE constraints allow SQL Server administrators to specify that a column may not contain duplicate values. When you create a new UNIQUE constraint, SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains preexisting duplicates, the constraint creation command fails.

Syntax: Creating Unique Index on Table
----SYNTAX DEMONSTRATION OF CREATING UNIQUE INDEX ON TABLE
CREATE UNIQUE INDEX <INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Unique Index on Table
---- DEMONSTRATION OF CREATING UNIQUE INDEX ON TABLE-----
CREATE UNIQUE INDEX TEST_UNIQUE
ON USERLOGIN(ID,EmailId)
Syntax: Drop Index from table
----SYNTAX DEMONSTRATION OF DROP INDEX FROM TABLE
DROP INDEX <INDEX_NAME> ON <TABLE_NAME>
Example: Drop Index from table
---- DEMONSTRATION OF DROP INDEX FROM TABLE
DROPINDEX TEST_UNIQUE ON USERLOGIN
The benefits of unique indexes include the following:
  • Data integrity of the defined columns is ensured.
  • Additional information helpful to the query optimizer is provided.
Clustered Index:

Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. If there is no primary key in a table, you can add one clustered index to that table with CREATE CLUSTERED INDEX statement.

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.

With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  •        Can be used it for frequently used queries.
  •        Provide a high degree of uniqueness.
Syntax: Creating Cluster index on Table
--- SYNTAX DEMONSTRATION OF CREATING CLUSTERED INDEX-------------
CREATE CLUSTERED INDEX <CLUSTERED_INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Cluster index on Table
--- DEMONSTRATION OF CREATING CLUSTERED INDEX------
CREATE CLUSTERED INDEX TEST_CLUSTERED
ON
USERLOGIN(ID,EMAILID)
Syntax: Drop Clustered Index from table
----SYNTAX DEMONSTRATION OF DROP INDEX FROM TABLE
DROP INDEX <INDEX_NAME> ON <TABLE_NAME>
Example: Drop Clustered Index from table
---- DEMONSTRATION OF DROP INDEX FROM TABLE
DROPINDEX TEST_UNIQUE ON USERLOGIN
Non-Clustered Index:

A Non-Clustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple Non-Clustered indexes on a table or indexed view. Generally, Non-Clustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index. Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the Non-Clustered index to find the location of the data value in the table and then retrieves the data directly from that location.

This makes Non-Clustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries.

Non-Clustered Index implemented in following ways:
  •      PRIMARY KEY and UNIQUE constraints.
  •      Index independent of a constraint.
  •      Non-Clustered index on an indexed view.
Syntax: Creating Non-Clustered Index on Table
---SYNTAX DEMONSTRATION OF CREATING NON-CLUSTERED INDEX--
CREATE NONCLUSTERED INDEX <NONCLUSTERED_INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Non-Clustered Index on Table
 ---DEMONSTRATION OF CREATING NON-CLUSTERED INDEX--
CREATE NONCLUSTERED INDEX TEST_NONCLUSTERED
ON
USERLOGIN (ID,NAME,EMAILID)
Syntax: Drop Non-Clustered Index from table
----SYNTAX DEMONSTRATION OF DROP INDEX FROM TABLE
DROP INDEX <INDEX_NAME> ON <TABLE_NAME>
Example: Drop Non-Clustered Index from table
---- DEMONSTRATION OF DROP INDEX FROM TABLE
DROPINDEX TEST_UNIQUE ON USERLOGIN

 

Limit of indexes on a table:

 For SQL Server 2005:
1 Clustered Index + 249 Non-Clustered Index = 250 Index on a table
For SQL Server 2008:
1 Clustered Index + 999 Non-Clustered Index = 1000 Index on a table

 


Updated 04-Mar-2020
I am a content writter !

Leave Comment

Comments

Liked By