BorlandTalk.com

Tomislav Kardaš · Guest

Hi!

Today I discovered strange thing. I have a database that is upraded
from MSSQL 6.5. There is a table Clients that has a field Name
varchar(30). There is a record in that table with value 'BANK'. If I
issue a select:

select Name
from Clients
where Name like 'BANK%'

it does not return any rows. But if I change to like 'BANK' or even
like 'BAN%K%' than it returns the expected row.

In SQL server 6.5 this was not the behaviour. This select should
return this row. Now I have to rewrite all procedures that use this
kind of matching to somethink wich work as is:

select Name
from Clients
where Name+' ' like 'BANK%'

I have tried to reproduce this behaviour by creating a new table but
surprisingly it worked OK. So, there must be some problem with the
upgrade of the old MSSQL 6.5 tables that causes this behaviour.

Can someone comment on this? Is there any easy way to correct the
problem without rewriting the procedures?

tomi.

Martyn Ayers · Guest

Tomi,

Mike Shkolnik · Guest

Try to analyze (collect statistic) for this table and try again to execute
your query

--
With best regards, Mike Shkolnik
EMail: [email]mshkolnik (AT) scalabium (DOT) com[/email]
http://www.scalabium.com

"Tomislav Karda " <nomail@sorry> wrote

Tomislav Kardaš · Guest

Hi Mike and Martyn!

I found out that this behaviour is because of index that is existing
on that column. When I remove the index then it works as expected.

Index is created as:

CREATE INDEX [idx_Clients_Name] ON [dbo].[Clients]([Name]) ON
[PRIMARY]
GO

This is very strange because definitely on MSSQL 6.5 it don't behave
that way!

Now I changed my code in stored procedures to:

select ...
from Clients
where name+' ' like @match+'%'

and that works OK when @match equals name, but it's a bug in
MSSQL2000.

If you want to try it yourself do this:

create table tmp (
x varchar(30) not null
)

CREATE INDEX idx_tmp ON tmp(x)

insert into tmp values ('BANK')

select * from tmp
where x like 'BANK%'

drop table tmp
go

If index is created select returns no rows, and if index is not
created (put it in comment) then select returns one row. This
behaviour must definitely be a bug. How can select result depend if
index is created over the column or not?

My SQL server version is:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)

tomi.

Viatcheslav V. Vassiliev · Guest

Try install SP3 on MS SQL Server. Just tested your proc and it works OK -
one row returned (at least in Query Analyzer).

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)

"Tomislav Karda " <nomail@sorry> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:4123a184.307597902 (AT) newsgroups (DOT) borland.com...

Tomislav Kardaš · Guest

Hi Viatcheslav!

On Thu, 19 Aug 2004 10:34:29 +0400, "Viatcheslav V. Vassiliev"
<support (AT) oledbdirect (DOT) com> wrote: