Index

DEFINITION

A database object which helps database engine to retrieve the data in a faster way without scanning an entire table

DATA STORAGE STRUCTURE

Structure	Heap	Clustered Index	Non Clustered Index
Basic Info	When there is no clustered index, data will be stored in Heap structure	Data will be stored in B-Tree structure and leaf node will be data pages.	Data will be stored in B-tree structure and leaf node contains Index Pages which points either Heap/ClusteredIndex
Sysindexes	Index_id : 0 First_iam_page : contains starting memory location	Index_id : 1 Root_page : contains starting memory location	Index_id : 2 Root_page : contains starting memory location

B-TREE STRUCTURE

• B means Balanced
• B-tree is always symmetrical, with the same number of pages on both the left and right halves at each level
• Level
• Root – Starting page. Can be only 1
• Intermediate – In between pages which holds only reference
• Leaf – They can refer to clustered Index / Heap depends on availability of Clustered Index
• Page
• Each page will have 8192 bytes, out of this, 8060 bytes can store data
• If clustered index has been created on INT column then one page can store max 2015 records
• Initially only a root page will be there. It can hold upto 2015 records. The moment 2016^th record gets inserted, page split will occur. Root page will be moved to leaf page, one more leaf page will be added. Half of the records will be moved to new leaf page and one root page will be created which will have reference of 1^st record from each leaf page.

TYPE OF INDEX

1. Clustered
1. Maximum only 1 can be created
2. By default it will be created with Primary Key. However non-clustered index can be created on primary key with following syntax

create table Temp(a int primary key nonclustered, b varchar(10)

3. No. of Key columns per Index : 16
4. Index Key Record Size: 900 bytes

2. Non-clustered
1. Maximum 999 non clustered index can be created in SQL 2008, previously it was 249
2. By default it will be created with Unique Key.
3. No. of Key columns per Index : 16
4. Index Key Record Size: 900 bytes

3. XML
1. Maximum 249 can be created
2. No. of Key columns per Index : 16
3. Index Key Record Size: 900 bytes

4. Spatial
1. Can be defined on GEOMATRY / GEOGRAPHY datatype
2. Maximum 249 can be created
3. No. of Key columns per Index : 16
4. Index Key Record Size: 900 bytes

DATATYPE RELATED

a. VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), NTEXT, IMAGE, TEXT cannot be part of Index Key columns

b. Computed columns can be indexed unless they don’t violate datatype rules

SPECIAL INDEX

Covered Index

- An index that is constructed such that SQL Server can completely satisfy queries by reading only the index is called a covering index.

- Can be created using INCLUDE clause

- Those columns won’t be counted in maximum 16 column count

- Maximum 1023 columns can be part of covering index

- Those columns will be counted only at leaf level

- Performance can be improved

- Example

CREATE NONCLUSTERED INDEX IX_TABLE ON TABLE (COLUMN1) INCLUDE (COLUMN2)

Filtered Index

- An index with WHERE clause

- Must be non-clustered

- Cannot be created on computed

- WHERE condition will support only >,>=,=,<,<=, IS NOT NULL, NOT NULL and multiple where conditions separated by AND/OR

INDEX OPTIONS

Online Index Creation

When index has to be created on live production database, then it is preferable to create with ONLINE option with value OFF. By default it will be ON. When you make it off, it will keep the lock on table and prevent any kind of access.

FillFactor

Index Hint

A way to force SQL Engine to use a particular index.

SELECT * FROM TableName WITH (INDEX = IndexName)

INDEX MAINTENANCE

Reorganizing Index / Rebuilding Index

By the amount of time, more DML operations will affect the index pages and database will have unorganized data; which will impact on performance.

DBCC SHOWCONTIG ('Domain.Domainvalue') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

If LogicalFragmentation column shows percentage (between 5 to 30), reorganize is required, >30, rebuild is required.

ALTER INDEX IndexName ON TableName REORGANIZE

(Same as DBCC INDEXDEFRAG (dbid, ObjectID, IndexID)

ALTER INDEX IndexName ON TableName REBUILD

(Same as DBCC DBREINDEX (‘TableName’, ‘IndexName’)