6

I found that queries below return different results on two Azure Hyperscale databases that are running the same compatibility level, same set options etc.

IF CAST(ServerProperty('Edition') AS nvarchar(128)) = 'SQL Azure' BEGIN
    SELECT 1
END
IF ServerProperty('Edition') = 'SQL Azure' BEGIN
    SELECT 2
END
IF 'SQL Azure' = ServerProperty('Edition') BEGIN
    SELECT 3
END

On one database, it returns only 1, on other database it returns 1,2 and 3.

I investigated the root cause and it seems to be caused by different collations of the databases.

For following queries:

SELECT SQL_VARIANT_PROPERTY(ServerProperty('Edition'), 'Collation')
SELECT name, collation_name, compatibility_level FROM sys.databases

The database which returns only one row, the result is:

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP850_CI_AS   150

And the result from database that returns 1,2,3 is:

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP1_CI_AS     150

So the simple comparison without the cast is comparing sql_variant with varchar (there is no difference when I use N'SQL Azure') where the underlying nvarchar from the sql_variant has in one case different collation than the DB I query and in other case it's matching.

First of all, I would assume the comparison of two strings with different collation would fail like it fails when you try to join on two columns with different collation, but it's apparently not the case here.

Anyway, what's the best way to safely compare output of a function that might be sql_variant with a varchar?

0

2 Answers 2

7

The sql_variant type is at the top of the data precedence hierarchy, so the string literal is implicitly converted to sql_variant before the comparison takes place.

The rules for sql_variant comparison include (emphasis added):

  • When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values.
  • When sql_variant values of different base data types are compared and the base data types are in the same data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.
  • When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are compared as integer values, and in the order listed. If all of these criteria are equal, then the actual string values are compared according to the collation.

That explains the results you saw.

In addition:

When handling the sql_variant data type, SQL Server supports implicit conversions of objects with other data types to the sql_variant type. However, SQL Server does not support implicit conversions from sql_variant data to an object with another data type.

The rules for collation precedence are separate. They apply when comparing strings with different collation labels. See the linked documentation for the gritty details of the Coercible-default, Implicit X, Explicit X, and No-collation labels.

Convert the sql_variant return value to the base type with the collation of the current database. This will match the collation assigned to the literal value, which should have the N prefix to be interpreted as Unicode.

For my instance, that means:

IF CONVERT(nvarchar(128), SERVERPROPERTY('Edition')) COLLATE DATABASE_DEFAULT = 
    N'Developer Edition (64-bit)'

The COLLATE clause isn't strictly necessary if you provide the string literal exactly as expected, but you did ask for the 'best' way, which I took to mean 'most comprehensive'.

My Czech mate Tomáš Zíka wrote about this and related issues in Expecting Subvertations.

0
4

what's the best way to safely compare output of a function that might be sql_variant with a varchar?

For SERVERPROPERTY that returns nvarchar, I suggest the work-around you already discovered plus an nvarchar constant instead of varchar.

The explicit cast 1) will return an nvarchar of the current database collation and 2) avoid the implicit conversion of the varchar constant to sql_variant. The nvarchar constant will also be the current database collation and match the explicit cast type, allowing more reliable equality comparison.

IF CAST(ServerProperty('Edition') AS nvarchar(128)) = N'SQL Azure'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.