FIX: Nonclustered Index Not Chosen with Varchar and LIKE Clause (245406)

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

The following script reproduces 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.

FIX: Nonclustered Index Not Chosen with Varchar and LIKE Clause (245406)

SYMPTOMS

WORKAROUND

STATUS

MORE INFORMATION