Microsoft KB Archive/245406: Difference between revisions
m (Text replacement - "<" to "<") |
m (Text replacement - ">" to ">") |
||
Line 118: | Line 118: | ||
begin | begin | ||
select @counter3 = @counter2 | select @counter3 = @counter2 | ||
while @counter4 | while @counter4 > 0 | ||
begin | begin | ||
insert into BUT000 | insert into BUT000 |
Latest revision as of 09:43, 21 July 2020
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