Using force index hint - SQL Server by serverku

I wrote about my previous just learned tips about covering index and will publish my future post for the detail understanding it. We should generate index statistics and index usage report periodically, so we can have more idea of the index utilization. Sometime we require different indexes other than the query optimizer used for the execution for the best performance.

How can we use different indexes other that query optimizer used for execution? You can force the indexes to be used with Index Hint. Here is the small demonstration by example. Please look on below script and created objects for demo.

-- Creating objects
USE DEMO
GO

-- Creating table which will be used for demo

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

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

GO

-- Inserting some sample records in tables 

INSERT INTO TblForceIndexHint
SELECT 
object_id,
name,
create_date,
type_Desc  
FROM SYS.OBJECTS

GO

Now creating one clustered index, Normal non-clustered index and an additional non-clustered index with include means covering index.

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

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

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

To review how force Indexes hint works, We need to get the data with CreateDate filtered. So let us start with there.

-- Keep query optimizer to decides and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE CreateDate >= GETDATE() - 50

Here you see the first query (by Query optimizer) and last query (forcing index hint) have used same index. Reviewing same index hints, but the query will get the data on ObjectId filter from where condition.

-- Keep query optimizer to decide and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE ObjectId >= 1000000

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE ObjectId >= 1000000

Here you see the first query (by Query optimizer) and third query (forcing index hint) have used same index. Please share your experience if you ever used force indexes hint.