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

Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation

Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation

There are currently two special (or pseudo?) collations: DATABASE_DEFAULT and CATALOG_DEFAULT. 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
(
[database_id] INT NOT NULL,
[name] sysname COLLATE INSTANCE_DEFAULT
...
);
----------------------------------------------------

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 "SERVER_DEFAULT") since "server" is ambiguous and could refer to the OS that the instance is running on. Please only use INSTANCE_DEFAULT and it is unambiguous, making it less likely to be misused and/or generate many duplicate forum questions.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base