SQL Index

An Index is a data structure that improves the speed/fast of data retrieval on a database table.


NOTE:

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns.

For example, when you create a table and identify a particular column to be the primary key, the Database Engine automatically creates a PRIMARY KEY constraint and index on that column.

For example, when you create a table and identify a particular column to be the unique key, the Database Engine automatically creates a UNIQUE KEY constraint and index on that column. 

 

CREATE Index

CREATE Index statement is used to create a new table. Here is the Syntax for creating a new Index.

CREATE  CLUSTERED/NONCLUSTERED INDEX  IndexName

ON TableName

(

            "Column1",

            "Column2",

            "Column3"

)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

CREATE CLUSTERED Index

Only one Clustered Index can be created on a table.

EXAMPLE 1:

CREATE CLUSTERED INDEX IX_Employee_EmployeeID

ON dbo.Employee(EmployeeID)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

EXAMPLE 2:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.Employee') AND name=N'IX_Employee_EmployeeID')

BEGIN

CREATE CLUSTERED INDEX IX_Employee_EmployeeID

ON dbo.Employee(EmployeeID)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

END

CREATE NON CLUSTERED Index

SQL server 2008 and later versions, Maximum 999 Non Clustered Indexes can be created on a table.

EXAMPLE 1:

CREATE NONCLUSTERED INDEX IX_Employee_EmployeeName

ON dbo.Employee(EmployeeName)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

EXAMPLE 2:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.Employee') AND name=N'IX_Employee_EmployeeName')

BEGIN

CREATE NONCLUSTERED INDEX IX_Employee_EmployeeName

ON dbo.Employee(EmployeeName)

WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

END

EXAMPLE 3:

From the following DDL for creating Employee table, there are two Indexes automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. Here, defined Primary Key column always creates CLUSTRED INDEX name with defined constraint name [PK_Employee_EmployeeID] and defined UNIQUE Key column always creates NON CLUSTERED INDEX name with defined constraint name.[UQ_Employee_EmployeeName]

-- Create Department table with necessary constraints

CREATE TABLE [dbo].[Department](

[DepartmentID] [int] NOT NULL IDENTITY(0,1),

       [DepartmentName] [varchar](50) NOT NULL,

       [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)

       CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

       ( [DepartmentID] ASC) ON [PRIMARY]

 ) ON [PRIMARY]

-- Create Employee table with necessary constraints

CREATE TABLE [dbo].[Employee](

   [EmployeeID] int NOT NULL IDENTITY(1,1),

   [DepartmentID] int NOT NULL CONSTRAINT FK_Employee_DepartmentID

                              REFERENCES [Department](DepartmentID),

   [EmployeeName] varchar(50) NOT NULL,

   [Gender] char(1) NOT NULL CONSTRAINT CH_Employee_Gender

                            CHECK ([Gender] in('M','F','O')),

   [BirthDate] date NOT NULL,

   [Salary] decimal(10,2) NULL,

   [Detail] varchar(max) NULL,

   [IsActive] bit NOT NULL CONSTRAINT DF_Employee_IsActive DEFAULT (1),

   CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE(EmployeeName),

   CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

   ([EmployeeID] ASC) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

DROP INDEX 

Example for deleting the existing index using the DROP INDEX command is given below.

EXAMPLE 1:

DROP INDEX IX_Employee_EmployeeID ON dbo.Employee

EXAMPLE 2:

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.Employee') AND name=N'IX_Employee_EmployeeID')

BEGIN

    DROP INDEX IX_Employee_EmployeeID ON dbo.Employee

END

RENAME Index 

Example for renaming the existing Index syntax is given below.

EXAMPLE 1:

EXEC sp_rename 'IX_Employee_EmployeeID, 'IX_Employee_EmployeeNumber'

Another way of renaming the existing Index is, CREATE another Index with new name and then DROP the existing Index.

Filtered Index

It can be created only as NON-CLUSTERED Index and can be used on VIEWS only if they are persisted views. The following example provided the employee records are filtered with Gender = ‘M’. It cannot be created on full-text Indexes.

EXAMPLE 1:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.Employee') AND name=N'IX_Employee_Male')

BEGIN

CREATE NONCLUSTERED INDEX IX_Employee_Male

ON dbo.Employee(EmployeeName)

WHERE Gender = 'M' WITH (PAD_INDEX = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)

END

Filtered Index can be used on VIEW.

CREATE VIEW dbo.VW_Employee_Male

AS

SELECT * FROM dbo.Employee

WHERE Gender = 'M'

Check the existence of Index 

EXAMPLE 1:

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.Employee') AND name=N'IX_Employee_EmployeeID')

       PRINT 'Index does not exist'     

ELSE

       PRINT 'Index exists' 

SELECT Index Columns

Example for retrieving the existing Indexes using the SELECT command is given below. The following command retrieves the Index names and Index columns of given database.

EXAMPLE 1:

select st.name as [table],

si.name as [index],

si.type_desc,

isnull(stuff((select ',' + sc.name + case when sic.is_descending_key=1 then N'?' else N'' end from sys.index_columns sic join sys.columns sc on sc.object_id=sic.object_id and sc.column_id=sic.column_id where sic.object_id=si.object_id and sic.index_id=si.index_id and sic.is_included_column=0 order by sic.is_included_column,sic.key_ordinal for xml path('')),1,1,'') ,'')

as [index_columns],

isnull(stuff((select ',' + sc.name from sys.index_columns sic join sys.columns sc on sc.object_id=sic.object_id and sc.column_id=sic.column_id where sic.object_id=si.object_id and sic.index_id=si.index_id and sic.is_included_column=1 order by sic.is_included_column,sic.key_ordinal for xml path('')),1,1,''), '') as [index_included_columns],

nullif((iif(iif(isnull(ddius.last_user_seek,-1) >= isnull(ddius.last_user_scan,-1), isnull(ddius.last_user_seek,-1), isnull(ddius.last_user_scan,-1)) >= isnull(ddius.last_user_lookup,-1), iif(isnull(ddius.last_user_seek,-1) >= isnull(ddius.last_user_scan,-1), isnull(ddius.last_user_seek,-1), isnull(ddius.last_user_scan,-1)), isnull(ddius.last_user_lookup,-1))), -1)

as [last_user_usage],

ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates,

si.is_primary_key,

si.is_unique,

si.is_unique_constraint,

si.ignore_dup_key,

si.has_filter,

si.fill_factor,

si.allow_row_locks,

si.allow_page_locks,

si.is_disabled

from

sys.indexes si

join sys.tables st

on st.object_id=si.object_id

left outer join sys.dm_db_index_usage_stats ddius

on si.object_id = ddius.object_id

and si.index_id = ddius.index_id

and ddius.database_id = db_id()

order by

st.name,si.name

Refer the different types of index from following link.

https://msdn.microsoft.com/en-us/library/ms175049.aspx