Share via


How to make clustered index scan to index seek?

Question

Monday, February 15, 2010 8:56 PM

Hello,

I'm running a simple query against a Table where I select two columns and have the where clause on two other (indexed).

When view the execution plan for my query it results in a clustered index scan when I think it should be able to do an index seek.

See script below for the table and index I'm using.

The query I run is this:

"SELECT [ID],[Data] FROM [dbo].[TableA] WHERE Key1 = N'1000' and Key2 IS NULL ORDER BY ID DESC"

The table must work on Sql Server 2000 and later :( so unfortunately I cannot include the data column in the IX_TableA index (which fixed the issue in SQL Server 2005 & 2008).

Anyone have an idea of how I can make the database perform an Index Seek instead?

Best regards 

Thomas

Table and index script:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[TableA](

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

[Data] [char](44) NOT NULL,

[Key1] [nvarchar](50) NOT NULL,

[Key2] [int] NULL,

 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

CREATE NONCLUSTERED INDEX [IX_TableA] ON [dbo].[TableA] 

(

[Key1] ASC,

[Key2] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

All replies (2)

Monday, February 15, 2010 9:16 PM ✅Answered

Hmm....

Ok it seems if i put lots of data in the table (about 1000 rows) sql server decides to use the index and performs an index seek together with a Key Lookup.

http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

provided some nice insights :)


Monday, February 15, 2010 9:14 PM

How many rows are returned? Our of how many rows in the table? Similarily, what is the selectivit for only the Key1 = N'1000' condition?

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi