sql-servert-sqlsql-server-2014

Can you set collation of T-SQL Variable?

I've searched high and low but can't find an answer, can you set the collation of a variable? According to the MS documentation, it seems that it's only possible on SQL Azure:

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
DECLARE
{{ @local_variable [AS] data_type } [ =value [ COLLATE ] ] } [,...n]

Currently I have to do this:

DECLARE @Test nvarchar(10) = N'Crud';

IF ( @Test = N'Crud' COLLATE Latin1_General_CS_AI )
    Print N'Crud';
IF ( @Test = N'cRud' COLLATE Latin1_General_CS_AI )
    Print N'cRud';
IF ( @Test = N'crUd' COLLATE Latin1_General_CS_AI )
    Print N'crUd';
IF ( @Test = N'cruD' COLLATE Latin1_General_CS_AI )
    Print N'cruD';

When what I'd like to do is this:

DECLARE @Test nvarchar(10) = N'Crud' COLLATE Latin1_General_CS_AI;

IF ( @Test = N'Crud' )
    Print N'Crud';
IF ( @Test = N'cRud' )
    Print N'cRud';
IF ( @Test = N'crUd' )
    Print N'crUd';
IF ( @Test = N'cruD' )
    Print N'cruD';

I'm guessing the answer is no but I wanted to confirm and at the very least, someone else ever needing this info will get a definitive answer.

Much appreciated.

Solution

  • Well, you're guessing correctly.
    In most SQL Server systems, (meaning, not including Azure SQL Data Warehouse and Parallel Data Warehouse) A collation can be set on four levels:

    1. The default collation of the SQL Server instance:

      The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases.

    2. The default collation of a specific database:

      You can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default collation of the database. You can also specify a collation when you create a database using SQL Server Management Studio. If you do not specify a collation, the database is assigned the default collation of the instance of SQL Server.

    3. You can set a collation for a table's column:

      You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.

    4. You can set a collation for a specific expression using the Collate clause:

      You can use the COLLATE clause to apply a character expression to a certain collation. Character literals and variables are assigned the default collation of the current database. Column references are assigned the definition collation of the column.

    So yes, with the exception of Azure SQL Data Warehouse and Parallel Data Warehouse, you can't set a collation on a local scalar variable.