Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Allow collation set by variable when using the COLLATE clause (at least in expressions)

Currently collations can only be specified as T-SQL identifiers, not as variable, string literals / constants, or expressions. This is fine for the CREATE DATABASE command, usually fine for CREATE TABLE / ALTER TABLE, but when using COLLATE in an expression it would really help. Currently people need to resort to dynamic SQL if needing non-static sorting / comparison options.

For example:

-----------------------------------------
CREATE PROCEDURE dbo.SearchForStuff
(
@SearchTerm NVARCHAR(500),
@CaseSensitiveSearch BIT
)

DECLARE @DynamicCollation sysname = N'Latin1_General_100_CI_AS_SC';

IF (@CaseSensitiveSearch = 1)
BEGIN
SET @DynamicCollation = N'Latin1_General_100_CS_AS_SC';
END;

SELECT [Something]
FROM dbo.Somewhere
ORDER BY [SomeColumn] COLLATE @DynamicCollation;
-----------------------------------------

This would often be used to switch between various locale / cultures (e.g. Latin1_General vs French vs German_PhoneBook vs etc).

Yes, allowing for the collation to be set in a variable might complicate optimization in the same way that using variables in predicates currently does. But this can be a documented consequence of using a variable, and perhaps there might be a way to augment OPTIMIZE FOR to account for collations set via variable in the query. Either way, I (and I'm sure many others), would be fine with whatever negative impact there might be just to have the option to do this when the need arises. I would rather have a choice that I can make rather than be forced to always be forced to use dynamic SQL, which is more error-prone and complicates security (due to breaking the ownership chain).

A secondary benefit would be if a variable is allowed in a CREATE TABLE statement. For example:

----------------------------------------------------
DECLARE @InstanceCollation sysname;
SELECT @InstanceCollation = CONVERT(sysname, SERVERPROPERTY('Collation'));

CREATE TABLE #TempDatabaseList
(
[database_id] INT NOT NULL,
[name] sysname COLLATE @InstanceCollation
...
);

----------------------------------------------------

DATABASE_DEFAULT does not work (at least not in an ideal sense) in the above context because [tempdb] and the local DB might be using different collations. Using DATABASE_DEFAULT is what folks typically do, but in this case I might really want to stick with the collation being used in sys.databases and not force it into the collation of the DB that the code is running in. For example, please see "Collation issue with CommandLog #151" ( https://github.com/olahallengren/sql-server-maintenance-solution/issues/151 ).

But using a variable in a CREATE TABLE / ALTER TABLE statement is of secondary importance. If it was only possible to get this working in the context of an expression, that would be good enough.

2 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Solomon Rutzky shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base