FIX: Nonclustered Index Not Chosen with Varchar and LIKE Clause
ID: Q245406
|
The information in this article applies to:
-
Microsoft SQL Server version 7.0
BUG #: 55007 (SQLBUG_70)
SYMPTOMS
On large tables, a nonclustered index covering the WHERE clause is not chosen if a varchar field is specified by a LIKE clause.
RESOLUTION
A supported fix that corrects this problem is now available from Microsoft, but
it has not been fully regression tested and should be applied only to systems
experiencing this specific problem. If you are not severely affected by this
specific problem, Microsoft recommends that you wait for the next SQL Server service pack
that contains this fix.
To resolve this problem immediately, contact Microsoft Product Support Services
to obtain the fix. For a complete list of Microsoft Product Support Services
phone numbers and information on support costs, please go to the following
address on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
The English version of this fix should have the following file attributes or later:
Version File name Platform
---------------------------------
7.00.706 S70706I.EXE Intel
7.00.706 S70706A.EXE Alpha
NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.
WORKAROUND
When possible, replace the nonclustered index covered by the WHERE clause with a clustered index. In the example given in the MORE INFORMATION section of this article, you would change the primary key clustered index by a nonclustered index. Re-create index [BUT000~SOT] as clustered.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 7.0.
MORE INFORMATION
The following script will reproduce the problem:
drop table BUT000
go
create table BUT000
(CLIENT varchar(3) NOT NULL, PARTNER varchar(10) NOT NULL,
BPEXT varchar(22) NOT NULL, MCNAME varchar(32) NOT NULL,
BU_SORT1 varchar(21) NOT NULL, BU_SORT2 varchar (21) NOT NULL)
Go
alter table BUT000
add constraint [BUT000~0]
primary key clustered (CLIENT, PARTNER)
go
create nonclustered index [BUT000~EXT] on BUT000 (CLIENT, BPEXT)
go
create nonclustered index [BUT000~NAM] on BUT000 (CLIENT, MCNAME)
go
create nonclustered index [BUT000~SOT] on BUT000 (CLIENT, BU_SORT1,BU_SORT2)
go
-- Primary Key violations occur during the table load
-- due to the usage of the rand function
declare @counter1 int
declare @counter2 int
declare @counter3 int
declare @counter4 int
declare @var2 varchar(32)
set nocount on
set @counter1 = 1
set @counter3 = 0
set @counter2 = 40000
set @counter4 = convert(int, rand() * 32)
set @var2 = char(convert(int,(rand()*26)) + 65)
+ char(convert(int,(rand()*26)) + 65)
+ char(convert(int,(rand()*26)) + 65) + 'JTH'
while @counter1 < 600000
begin
select @counter3 = @counter2
while @counter4 > 0
begin
insert into BUT000
values ('260', convert(varchar, @counter3),'', @var2,'','')
set @counter3 = @counter3 +1
set @counter4 = @counter4 -1
set @counter1 = @counter1 + 1
end
set @var2 = char(convert(int,(rand()*26)) + 65)
+ char(convert(int,(rand()*26)) + 65)
+ char(convert(int,(rand()*26)) + 65)
+ 'JTH'
set @counter2 = @counter2 + convert(int,rand()*10000)
set @counter4 = convert(int, rand() * 32)
end
Then run a query such as:
select * from BUT000 where CLIENT ='260' and MCNAME like ' MCK%'
The query uses the primary key index by seeking through the CLIENT column,
which has a selectivity of 1, instead of using the index BUT000~NAM, which
is covering the WHERE clause.
Additional query words:
Keywords : SSrvTran_SQL kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug