Impact of Nonclustered Index without Clustered Index - SQL Server by serverku

We have read and learn so many times from online sources as it is not best practice to create a non-clustered index without any clustered index created on the table. We should have clustered index on the table.

Have you practically seen that best practice? What will be the impact on query when table have a non-clustered index but not clustered index?. Without a clustered index on the table the query execution plan sometime used non-clustered index and sometimes not as depends on where condition used in the query.

I will show you here, how it behaves with and without clustered index. We have a two shot of the show and will see this behavior with normal non-clustered index and additional covering non-clustered index. Let we created a sample table and get insert some records in it.

 
--- Creating tables
IF (OBJECT_ID('ItemDetails','U') > 0 )
 DROP TABLE ItemDetails
GO

CREATE TABLE ItemDetails
(
 ItemAutiId int identity(1,1),
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO

-- Inserting sample records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

As discussed, we will go through the following, Will review execution plan for each and compare.

1. Normal non-clustered index behavior without and with clustered index on table.

-- Creating nonclustered index
CREATE NONCLUSTERED INDEX [IX_ItemType]
ON [dbo].[ItemDetails] ([ItemType])
GO

-- Running this query with Include Actual execution plan.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

 
--Now creating a clustered index on table
CREATE CLUSTERED INDEX [IX_ItemAutiId]
ON [dbo].[ItemDetails] ([ItemAutiId])
GO

-- Again running this query with Include Actual execution plan after creating clustered index.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

You can see here first case did table scan and second case did clustered index scan. Now moving other shots,

2. Covering nonclustered index behavior without and with clustered index on table.

-- Dropping existing indexes on table
DROP INDEX [IX_ItemType] ON  [ItemDetails]
DROP INDEX [IX_ItemAutiId] ON  [ItemDetails]

GO

-- Creating covering index on table
CREATE NONCLUSTERED INDEX [IX_ItemType]
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO

-- Running this query with Include Actual execution plan.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

-- Creating clustered index on table
CREATE CLUSTERED INDEX [IX_ItemAutiId]
ON [dbo].[ItemDetails] ([ItemAutiId])
GO

-- Again running this query with Include Actual execution plan after creating clustred index.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

In this scenario first case did table scan and second case did non-clustered index seek. Do you have any more idea about it if you experienced with it?

Using Covering Index and Performance Review - a new Index type of SQL Server 2005 by serverku

We all know about the indexes and the concept of it. As well as the types of the indexes. During working one query optimization, I got suggested by the SQL Server Optimizer with index creation and that index was Covering index.

What is covering index?
It is an additional index with includes columns in definition which are exists in select list. Per msdn and book online, The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It will better if we go through the demo and example. Let us start with objects creation,

USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblCoveringIndex','U') > 0)
DROP TABLE TblCoveringIndex

CREATE TABLE TblCoveringIndex
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in table 

INSERT INTO TblCoveringIndex
SELECT TOP 80000
convert(bigint,CONVERT(VARCHAR(100),a.object_id) + CONVERT(VARCHAR(100),b.object_id) ),
CONVERT(VARCHAR(100),a.name) + CONVERT(VARCHAR(100),b.name) ,
B.create_date,
b.type_Desc
FROM SYS.OBJECTS A
CROSS JOIN SYS.OBJECTS B

GO

Now we will review the execution plan without any indexes created on the table.

--  Execution plan with any indexes
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex
WHERE CreateDate >= GETDATE() - 50

GO

Now We will create a one clustered index, Normal non-clustered index and an additional non cluster index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblCoveringIndex (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblCoveringIndex (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblCoveringIndex (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

We have created an additional non-clustered index with an included column, which are going to be used in the select list. After index's creation, It is finally time to review for each index created and execution plans of the queries using each of them. Here we force the query to use the normal non-clustered index and addition non-clustered index created.

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate)) 
-- Forcing index hint of normal nonclustered index
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblCoveringIndex with (index (IX_CreateDate_Covering)) 
-- Forcing index hint of additional nonclustered index 
WHERE CreateDate >= GETDATE() - 50 
-- Applied column filter on which the indexes created

GO

(Clink on image to enlarge)

I hope you like this post. Before apply this type of index please verify execution plans and performance review and decide which indexes are better for the query plan.