Prev: Creating a view with concatenated columns
Next: Replication Script-Out 2008 Full?
From: Stefan Hoffmann on 25 Jan 2010 05:17
hi Erland,

On 23.01.2010 00:24, Erland Sommarskog wrote:
> KS (KurtSune(a)KurtSune.com) writes:
>> I am having problem with a like search in SQL server 2008.
>> It works in x86 but not in x64.
>> The server has collation Sami_Sweden_Finland_100_CI_AS
>> --this select returns no data
>> SELECT namn,* FROM dbo.forsakringsgivare2 WHERE namn like 'L�ns%'
>
> I believe this is the bug that SQL Server MVP Dan Guzman spotted.
> See
> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackI
> D=484426
The plan does not show an index seek as mentioned by Dan Guzman.

You may take a look at repro at the end of the post. Using the unicode
literal marker changes the used constant scan to a table scan which will
return data.

but as I said, I can't explain the difference..


mfG
--> stefan <--

--
USE tempdb ;
GO

SET ANSI_NULLS ON ;
SET QUOTED_IDENTIFIER ON ;
SET ANSI_PADDING ON ;
GO

CREATE TABLE dbo.forsakringsgivare2
(
namn VARCHAR(40) COLLATE Sami_Sweden_Finland_100_CI_AS
NOT NULL
) ;
CREATE TABLE dbo.forsakringsgivare3
(
namn VARCHAR(40) COLLATE Finnish_Swedish_CI_AI
NOT NULL
) ;
GO

INSERT INTO forsakringsgivare2
VALUES ( 'L�nsf�rs�kringar' ) ;
INSERT INTO forsakringsgivare3
VALUES ( 'L�nsf�rs�kringar' ) ;

--this select returns no data
--the plan uses a constant scan
SELECT namn,
*
FROM dbo.forsakringsgivare2
WHERE namn LIKE 'L�ns%' ;

--this select returns data
--the plan uses a table scan
SELECT namn,
*
FROM dbo.forsakringsgivare2
WHERE namn LIKE N'L�ns%' ;
GO

DROP TABLE dbo.forsakringsgivare2 ;
DROP TABLE dbo.forsakringsgivare3 ;
GO

From: Erland Sommarskog on 26 Jan 2010 18:18
Stefan Hoffmann (ste5an(a)ste5an.de) writes:
> The plan does not show an index seek as mentioned by Dan Guzman.

Correct. For this query:

--this select returns no data
--the plan uses a constant scan
SELECT namn,
*
FROM dbo.forsakringsgivare2
WHERE namn LIKE 'L�ns%' ;

As you say, there is a constant scan. That is, already during compilation,
SQL Server determines that the query will not return any rows!

> You may take a look at repro at the end of the post. Using the unicode
> literal marker changes the used constant scan to a table scan which will
> return data.

I guess the implicit conversion hides literal, and prevents the
optimizer to make a quick check.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: Creating a view with concatenated columns
Next: Replication Script-Out 2008 Full?