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

Status : 

  Postponed<br /><br />
		Due to current priorities, the product team decided to postpone the resolution of this item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
1
Sign in
to vote
ID 355368 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 7/8/2008 6:50:23 AM
Access Restriction Public
Primary Feedback Item 206543

Description

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.


Sign in to post a comment.
Posted by Umachandar [MSFT] 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 Umachandar [MSFT] 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.