Replacing Index Scan with Index Seek

Question

Replacing Index Scan with Index Seek

Little Nick

SSCrazy

Points: 2202
More actions
January 17, 2012 at 11:36 am

#253618

I've table as following,
CREATE TABLE [dbo].[myTbl1](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[myID] [varchar](50) NULL,
[nme] [varchar](50) NULL,
[addr1] [varchar](200) NULL,
CONSTRAINT [PK_myTbl1] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
My data as following,
insert into myTbl1 values('500','shaiful','addr1')
insert into myTbl1 values('500','shaiful','addr1')
insert into myTbl1 values('500','shaiful','addr1')
insert into myTbl1 values('500','shaiful','addr1')
insert into myTbl1 values('600','rudie','addr1')
insert into myTbl1 values('600','rudie','addr1')
insert into myTbl1 values('600','rudie','addr1')
insert into myTbl1 values('600','rudie','addr1')
insert into myTbl1 values('700','karim','street1')
insert into myTbl1 values('700','karim','street1')
insert into myTbl1 values('700','karim','street1')
insert into myTbl1 values('700','karim','street1')
/*and so on*/
My indexes as following,
CREATE NONCLUSTERED INDEX [IX_01] ON [dbo].[myTbl1]
(
[myID] 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
My execution plan have
1. Stream Aggregate
2. Index Scan (NonClustered)
My question is 'How to replace this Index Scan with Index Seek?'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I've probably missed something, but I see table, indexes, data, but I don't see a query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

A query would be nice. An exec plan would be excellent.;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Nope, no query that I saw either.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Little Nick SSCrazy Points: 2202 More actions · Answer 4

Oh sorry. My SQL as following,

select distinct nme from myTbl1 where addr1 like '%a%'

Correction,

My execution plan have

1. Hash Match

2. Clustered Index Scan (Clustured)

My question is 'How to replace this Index Scan with Index Seek?'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Short answer, you can't.

That LIKE has a leading wildcard, it's a non-SARGable predicate and the only way that can execute is with a scan, table or index. (imagine a telephone directory, it's 'indexed' by surname. find me everyone who has 'reke' anywhere in their surname.)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Little Nick SSCrazy Points: 2202 More actions · Answer 6

Little Nick

SSCrazy

Points: 2202

January 18, 2012 at 6:32 am

#1435192

Oh. tq gail