Index Hint Using WITH Statement

Posted: August 11, 2010 in SQL Server 2005

You can use index hint to increase performance if needed. Here is an example:

-- Set plan details off
SET SHOWPLAN_TEXT OFF
GO

-- Create temp table
CREATE TABLE dbo.Test (col1 INT IDENTITY NOT NULL, col2 INT NOT NULL);

-- Create new index
CREATE INDEX idx_test ON dbo.Test (col2);

SET NOCOUNT ON;

-- Declare variables
DECLARE @li_count INT;

-- Initialise variables
SET @li_count = 1;

-- Loop to insert 1000 records
WHILE (@li_count <= 1001)
BEGIN
   IF @li_count <= 1000
      INSERT INTO dbo.Test (col2) VALUES (1);
   -- Increment the counter
   SET @li_count = @li_count + 1;
END;

-- Select the records that were inserted
SELECT col2, COUNT(*) col2_count FROM dbo.Test GROUP BY col2
GO

-- Set plan details on
SET SHOWPLAN_TEXT ON
GO

-- This statement will NOT use an index
SELECT * FROM dbo.Test;

-- This statement will use an index
SELECT * FROM dbo.Test WITH (INDEX(idx_test))
GO

-- Set plan details off
SET SHOWPLAN_TEXT OFF
GO

-- Drop temp table
DROP TABLE dbo.Test;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s