Scalar variables collation bug. Two different but related issues in one simple script - by VM-Pire

Status : 


Sign in
to vote
ID 652116 Comments
Status Active Workarounds
Type Bug Repros 0
Opened 3/17/2011 4:03:36 PM
Access Restriction Public


The two issues:
1. SQL Server effectively moves all DECLARE statements to the place of the first DECLARE in script that leads to unexpected script result. This implicit movement affects the moved variable's collation in case of DB is switched with USE statement and the new DB have different collation.

2. SQL Server analyses variable collation in IS NULL clause where it is not necessary. We should not use a collation to check variable for NULL.
Sign in to post a comment.
Posted by Microsoft on 8/3/2011 at 3:26 PM
We have now fixed the issue in the next major version of SQL Server. Thanks again for reporting the problem.

Umachandar, SQL Programmability Team
Posted by Microsoft on 7/7/2011 at 7:25 PM

We have looked at the issues and you are right in both of your observations.

For issue number 1, we agree that the current behavior is not the best, but at the same time, it is dangerous for us to change it as there might be other customers who depend on this behavior (possibly even without knowing it), so we are hesitant to change this. As a workaround, you can explicitly specify the collation when using the variables to avoid depending on the collation from the database, like this:

IF @var collate <collation> = 'something' SELECT 1 ELSE SELECT 2

For issue 2, we agree that IS [NOT] NULL comparison should not require collation match, so we have fixed this in SQL Server codename "Denali". It will be part of the next public release after CTP3.

If you have any concerns with the answer, feel free to let us know.

- Hans Olav Norheim
Developer, SQL Engine
Posted by Microsoft on 3/28/2011 at 3:03 PM
Thanks for reporting the issue. We are investigating the problem and will let you know what we find.

Umachandar, SQL Programmability Team