Like does not work correctly in some cases on SQL 2005

Like does not work correctly in some cases on SQL 2005 by slink

Closed

as Postponed Help for as Postponed

Type:	Bug
ID:	355368
Opened:	7/8/2008 6:50:23 AM
Access Restriction:	Public
Primary Feedback Item:	206543

Workaround(s)

view

User(s) can reproduce this bug

On indexed tables there is a problem with the results of the like clause.
The problem described here only occurs if an index is created on the column queried with "Like"

The ProjectNumber column is of the type varchar .
The result even seems to depend on the data to be queried.
( without an index on the column both queries are executed correctly).
On SQL 2000 the like with or without index always works correctly.

Details (expand)

German

SQL Server 2005 SP2 - Standard Edition

SQL Engine

Windows XP SP2 Professional

German

empty database, default sorting latin1_general_ci_as
create the following table

USE [TestLikeBug]
GO
/****** Object: Table [dbo].[Projects]    Script Date: 07/08/2008 15:35:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Projects](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [ProjectNumber] [varchar](12) NULL,
CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED
(
    [id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

/****** Object: Index [IX_Projects]    Script Date: 07/08/2008 15:35:57 ******/
CREATE NONCLUSTERED INDEX [IX_Projects] ON [dbo].[Projects]
(
    [ProjectNumber] ASC
) ON [PRIMARY]

enter the values as projectnumbers

P08.799
P08.796
P08.798
P08.795
P08.794
P08.781
P08.871
P08.872

select * from projects where (projectNumber like N'P08.87%')
delivers correctly
P08.871
P08.872
select * from projects where (projectNumber like N'P08.79%')
delivers nothing!!!!!!!

Attention using SQL Server 2000 anything is fine !!!

expect that also
select * from projects where (projectNumber like N'P08.79%')
delivers
P08.794
P08.795
P08.796
P08.798
P08.799

File Name	Submitted By	Submitted On	File Size
TestLikeBug.bak (restricted)	slink	7/8/2008	-
insertstatements.sql (restricted)	slink	7/14/2008	-

Posted by Microsoft on 7/14/2008 at 12:26 PM

Hi,
Thank you very much for the repro. This is the same bug as:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=206543

I have resolved your bug as duplicate. We will fix this in a future service pack as indicated.

--
Umachandar, SQL Programmability Team

Posted by slink on 7/14/2008 at 3:48 AM

Server collation and database collation both latin1_general_ci_as

Posted by slink on 7/14/2008 at 1:41 AM

The exact Version of my SQL Server is

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

But this is also happening with SQL Express and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Posted by Microsoft on 7/11/2008 at 10:51 AM

Hi,
Thanks for reporting this issue. In order to investigate further, please provide us with the following:

1. Script with INSERT statements - we are mainly interested in the data part so we can repro the results of the SELECT statements
2. Database / server collation
3. @@VERSION output of your server.

--
Umachandar, SQL Programmability Team

Posted by Hans-Gerd Sandhagen on 7/8/2008 at 10:45 AM

Der Fehler ist bei mir reproduzierbar (SQL-Server Express 2005).

Zwei Dinge sind mir dabei aufgefallen:

1. Der Fehler scheint nur aufzutreten, wenn das Feld als varchar definiert ist, in dem Query aber N'P08... angegeben wird.

2. Ein Query mit like N'P08.7%' funktioniert korrekt.