Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation
Add special collation INSTANCEDEFAULT to work like COLLATE DATABASEDEFAULT but uses instance's default collation
There are currently two special (or pseudo?) collations: DATABASEDEFAULT and CATALOGDEFAULT. These "special" collations are quite handy when working with collations that can be different depending on the context in which the code is executing. Some of those scenarios (of working with collations that are not always known ahead of time or the same across where the code is executing) involve working with instance-level objects. For example: Logins, Linked Servers, Server Audits, Server Audit Specifications, Credentials, Server DDL Triggers, Databases, and so on.
When working with instance-level meta-data, the Collation can't be hard-coded as it can be different between different instances, and even different between the instance and the databases on that instance. There is currently no way to specify the collation via a variable (however: https://feedback.azure.com/forums/908035-sql-server/suggestions/36197785-allow-collation-set-by-variable-when-using-the-col ).
Having a special collation for INSTANCE_DEFAULT would allow for the following:
CREATE TABLE #TempDatabaseList
[databaseid] INT NOT NULL,
[name] sysname COLLATE INSTANCEDEFAULT
Technically this could be done using dynamic SQL to get the instance-level collation, but the table would need to be created prior to the dynamic SQL being executed since local temp tables do not survive the end of a sub-process if they were created in that sub-process. So the local temp table would need to be created first and then the column could be added in the dynamic SQL. But this is just complicating a scenario that could be handled via a single word: INSTANCE_DEFAULT.
For example, please see "Collation issue with CommandLog #151" ( https://github.com/olahallengren/sql-server-maintenance-solution/issues/151 ).
And, PLEASE do not use the terminology of "SERVER" (as in "SERVERDEFAULT") since "server" is ambiguous and could refer to the OS that the instance is running on. Please only use INSTANCEDEFAULT and it is unambiguous, making it less likely to be misused and/or generate many duplicate forum questions.
Solomon Rutzky commented
Here are two more instances where this feature would fix a bug in a system view:
"Collation conflict … Could not use dbo.sysdac_instances" ( https://dba.stackexchange.com/a/133995/30859 )
"Error: 468 trying to connect to SQL server with SQL Server management studio" ( https://dba.stackexchange.com/a/239450/30859 )
Solomon Rutzky commented
Here is another example where this feature would fix a bug in a system stored procedure:
xp_logininfo gets "Msg 468, Level 16, State 9: Cannot resolve the collation conflict..." when DB collation doesn't match instance collation ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36324910-xp-logininfo-gets-msg-468-level-16-state-9-can )