Microsoft KB Archive/245406

From BetaArchive Wiki
Knowledge Base


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