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.
CREATE PROCEDURE dbo.SearchForStuff
DECLARE @DynamicCollation sysname = N'Latin1General100CIAS_SC';
IF (@CaseSensitiveSearch = 1)
SET @DynamicCollation = N'Latin1General100CSAS_SC';
ORDER BY [SomeColumn] COLLATE @DynamicCollation;
This would often be used to switch between various locale / cultures (e.g. Latin1General vs French vs GermanPhoneBook 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
DATABASEDEFAULT 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 DATABASEDEFAULT 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.