Article ID: 185919
Article Last Modified on 11/14/2003
APPLIES TO
- Microsoft SQL Server 4.21a Standard Edition
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q185919
BUG #: NT 18025 (6.50)
SYMPTOMS
When a query performs a nonclustered index scan to retrieve data, all the data pages in the range, including those which do not match the criteria, are read. This behavior is not specific to the LIKE operator. The following scenario demonstrates the problem:
use pubs go set nocount on Create Table PrbLike (lname char(25),szconstant char(50)) go declare @x int select @x=1 while @x<10000 begin insert PrbLike values (ltrim(str(@x))+"_namepadding", replicate("x", 30)) select @x=@x+1 end go create index idx_name on PrbLike(lname) go set nocount off set statistics io on set showplan on go
First Query
select * from PrbLike where lname like "11_0%" go
Result
STEP 1 The type of query is SELECT FROM TABLE PrbLike Nested iteration Index: idx_name lname szconstant ------------------------- --------------------------------- 1100_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1110_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1120_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1130_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1140_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1150_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1160_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1170_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1180_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1190_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (10 row(s) affected) Table: PrbLike scan count 1, logical reads: 116, physical reads: 0, read ahead reads: 0
Second Query
select * from PrbLike where lname like "11%"
Result
STEP 1 The type of query is SELECT FROM TABLE PrbLike Nested iteration Index: idx_name lname szconstant ------------------------- ---------------------------------- 11_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ... 1199_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (111 row(s) affected) Table: PrbLike scan count 1, logical reads: 116, physical reads: 0, read ahead reads: 0
WORKAROUND
To work around the problem replace the following section of code:
select * from PrbLike where lname like "11_0%"
In its place, use this:
select P2.* from PrbLike P1, PrbLike P2 where P2.lname = P1.lname and P1.lname like "11_0%"
Result
STEP 1 The type of query is SELECT FROM TABLE PrbLike P1 Nested iteration Index: idx_name FROM TABLE PrbLike P2 Nested iteration Index: idx_name lname szconstant ------------------------- --------------------------------- 1100_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1110_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1120_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1130_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1140_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1150_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1160_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1170_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1180_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1190_namepadding xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (10 row(s) affected) Table: PrbLike scan count 1, logical reads: 5, physical reads: 0, read ahead reads: 0 Table: PrbLike scan count 10, logical reads: 40, physical reads: 0, read ahead reads: 0
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 4.x, 6.0, and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
MORE INFORMATION
This behavior is reproducible on build 6.50.201 and in all service packs.
Additional query words: prodsql
Keywords: kbbug kbpending KB185919