FIX: Nonclustered Index Not Chosen with Varchar and LIKE Clause

Article translations Article translations
Article ID: 245406 - View products that this article applies to.
This article was previously published under Q245406
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55007 (SQLBUG_70)
Expand all | Collapse all

SYMPTOMS

On large tables, a nonclustered index covering the WHERE clause is not chosen if a varchar field is specified by a LIKE clause.

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 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.

Properties

Article ID: 245406 - Last Review: October 22, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB245406

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com