Microsoft KB Archive/245406

= FIX: Nonclustered Index Not Chosen with Varchar and LIKE Clause =

Article ID: 245406

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q245406



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.



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.

Keywords: kbbug kbfix KB245406

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.