Basics of Indexes in SQL Server

Indexes in SQL Server:

If you see a book, in last of book an index is provided that has some titles and page numbers. You don’t need to search all pages for a particular topic .See in index, select topic and go to direct page number. The same concept applies in SQL server.

An index is data structure that allows fast data retrieval. Indexes can be created using single column or more than one column. CREATE INDEX statement is used to create index.

Basic Syntax:

CREATE INDEX_TYPE INDEX

SAMPLE_INDEX_NAME

ON

SAMPLE_TABLE_NAME(SAMPE_COLUMN_NAME)

-- or

SAMPLE_TABLE_NAME(SAMPE_COLUMN_NAME1,SAMPE_COLUMN_NAME2)

There are three types of indexing available in SQL Server.

1. UNIQUE,
2. CLUSTERED or
3. NONCLUSTERED

The above SQL Code create Non-Unique, non clustered index on SAMPLE_TABLE_NAME.

When you create a table with Primary key a clustered index PK_SAMPLE_TABLE_NAME is created by default.

·         UNIQUE INDEX: In Unique index no two rows are permitted with same index key value.

CREATE UNIQUE INDEX

idx_name

on

DailyIncome(VendorId)

You cannot create UNIQUE index if column have duplicate values.

Error occurred

Example:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.DailyIncome' and the index name 'idx_name'.

·         CLUSTERED INDEX: A CLUSTERED index is organized as B-Tree data structure. It determines physical order of data in a table. There is only one clustered index allows per table. When you create Primary key a clustered indexes automatically created if there is no clustered index on table.

Example:

CREATE CLUSTERED  INDEX

idx_name

on

DailyIncome(VendorId)

·         NONCLUSTERED INDEX :It is useful when data is repeat in columns.A table can have more than one non clustered index.With this,Physical order of rows is independent from indexed order. Each table can have up to 999 non clustered indexes. For indexed views, non clustered indexes can be created only on a view that has a unique clustered index already defined.

·         CREATE NONCLUSTERED  INDEX

idx_non_clus

on

DailyIncome(VendorId,IncomeDay)

·         CREATE NONCLUSTERED  INDEX

idx_non_clus1

on

DailyIncome(VendorId,IncomeDay,IncomeAmount)

Drop Index:

DROP INDEX dbo.CustomerDetailsers.IDX_CustomerName

Indexes can be created and updated by SSMS easily.To add new index expand Table in Object Explorer.

• Go to Index.
• Right Click on Index
• Select New Index
• A dialog Box Opens

It will be good practice if you start index name with IDX like IDX_TABLE_NAME.

Select Index type:

Types are :

 Clustered   :Select this when  your queries use relational operators (=, <, >, <=, => and BETWEEN ),Group By ,Order by,Join.

  Non Clustered : This is preferred when clustered index is already exists.It does not changes the physical of data in order table.It can be useful when your table is not generally updated.When you use this ,do not use large number of columns.

Primary XML : MSDN

Spatial :  A spatial index is a type of extended index that allows you to index a spatial column. A spatial column is a table column that contains data of a spatial data type, such asgeometry or geography. MSDN

• Add columns : all columns of table shows in a dialog box select as you want.and click ok.

Further Readings:

MSDN

Effective Clustered Index

Prev--->Basics of Triggers in SQL Server                                           Next--->For XML clause in T-SQL