Blog Post

What’s in a Name?: Inside the Wacky World of T-SQL Identifiers

,

thingamajig-and-whatchamacallit

(last updated: 2018-04-28)

Today we are going to take a look into an area that you probably are thinking has very little to see: T-SQL Identifiers (i.e. entity names). Most of the time the rules governing how you name things in SQL Server are pretty simple, but there are several nuances that you might want to be aware of.

The Basics

To start with, most names (but not all) are of type sysname. sysname is an alias (think User-Defined Data Type / UDDT) for NVARCHAR(128). You can see this using the following query:

SELECT alias.[name] AS [AliasName],
       base.[name] AS [BaseName],
       alias.[max_length] AS [MaxSize]
FROM   sys.types alias
INNER JOIN sys.types base
        ON base.[user_type_id] = alias.[system_type_id]
WHERE  alias.[name] = N'sysname'

which returns:

AliasName    BaseName    MaxSize
sysname      nvarchar    256

Please note that MaxSize is expressed in sys.types in terms of bytes, not characters. Because this is NVARCHAR instead of VARCHAR, the max of 128 “characters” (i.e. 16-bit Code Points) equates to a max of 256 bytes.

The Rules (According to Documentation)

The official Microsoft documentation for Database Identifiers states the following (reordered to make more sense):

  • There are two types of identifiers: Regular and Delimited
    • Regular Identifiers
      • Do not need to be enclosed by delimiters
      • First character:
        • Any “letter” according to Unicode 3.2 classification
        • underscore ( _ ), at sign ( @ ), or number sign ( # )
          • A regular identifier that starts with the at sign (@) always denotes a local variable or parameter and cannot be used as the name of any other type of object.
          • An identifier that starts with a number sign ( # ) denotes a temporary table or procedure.
          • An identifier that starts with double number signs ( ## ) denotes a global temporary object.

          Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.

      • Subsequent characters:
        • Any “letter” according to Unicode 3.2 classification
        • Any “decimal” number according to Unicode 3.2 classification
        • underscore ( _ ), at sign ( @ ), number sign ( # ), or dollar sign ( $ )
      • No embedded spaces or special characters
      • No supplementary characters
      • Cannot be a T-SQL reserved word
    • Delimited Identifiers
      • Do need to be enclosed by delimiters (due to not conforming to rules for identifiers)
      • Delimiters are either double-quotes ( ) or square brackets ( [ ] )
  • Collation
    • Instance-level items (Databases, Logins, etc) governed by Instance default Collation
    • Database-level items (Users, Schemas, Tables, etc) governed by Database default Collation
  • Length
    • 1 – 128 characters in most cases
    • 1 – 116 characters for local temporary tables
  • Variable and Parameter names must follow rules of identifiers

That all seems simple and straight-forward enough. So what’s the problem, if there even is one?

Well, I’m glad you asked. There are several issues with these rules:

  1. It is sometimes unclear what they mean by “indetifiers” when that term is used by itself since they have named two types of identifiers.
  2. For example, what exactly is being stated concerning variable and parameter names? Is it only max length?
  3. What exactly are the valid “letters” and “decimal” numbers according to Unicode 3.2?
  4. For variables, parameters, and local and global temporary objects, does the prefix that designates their type (i.e. the @, #, and ## prefixes) count as part of the identifier or not?
  5. What impact, exactly, does Collation have? Is it the usual sensitivities and locale-specific linguistic rules?
  6. Has anything been left out?

Investigation

Valid Characters

Finding the exact list of valid characters turned out to be an entire project in itself. For those details, please see the following three posts:

  1. The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 1
  2. The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2
  3. The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)

Max. (and a few Min.) Lengths

First we can try to find all tables with name columns that are either not sysname or NVARCHAR, or don’t have a “maxsize” of 256:

SELECT ao.[name], ao.[type_desc], ac.*
FROM   sys.all_columns ac
INNER JOIN sys.all_objects ao
        ON ao.[object_id] = ac.[object_id]
WHERE ao.[is_ms_shipped] = 1
AND   ac.[name] = N'name'
AND  (
       ac.[user_type_id] NOT IN (256, 231)
   OR  ac.[max_length] <> 256
     )
ORDER BY  ao.[name], ac.[column_id];
/*
name                            max_length
---------------------------     ----------
assembly_files                  520
dm_server_audit_status          512
dm_tran_active_transactions     64
dm_xe_sessions                  512
*/

The results of that query indicate that Assembly Files, Transactions, Server Audit Status, and Extended Event Sessions should be investigated. Fortunately “Server Audit Status” and “Extended Event Sessions” were both false-positives. The reason that they were false positives is that they do not contain the source values. In both cases, the tables that hold the source values — sys.server_audits and sys.server_event_sessions, respectively — do use sysname and NVARCHAR(128), respectively.

That leaves sys.assembly_files and BEGIN TRAN / SAVE TRAN.

Assembly Files

According to the documentation for sys.assembly_files, the datatype is NVARCHAR(260) , which matches the max_length of 520 that the query above returned. But, the documentation for ALTER ASSEMBLY, which is how you add files to Assemblies, doesn’t say anything about either a max length or why it isn’t the standard 128. However, it does provide a clue in the Arguments section where it describes the [ ADD FILE FROM { client_file_specifier [ AS file_name]... clause:

client_file_specifier specifies the location from which to upload the file…If file_name is not specified, the file_name part of client_file_specifier is used as file_name.

Meaning, if you add a file from a file path instead of providing a VARBINARY literal (i.e. hex bytes), and you do not provide a custom AS file_name, then the full path to the file being loaded will be used as the “name” (this also happens when creating an Assembly from a file path). So then why the value of 260? That comes from the default maximum length of a path, which is 260 (see Naming Files, Paths, and Namespaces: Maximum Path Length Limitation for details).

Now we just need to verify that info. We need an actual Assembly to add a file to since the built-in CLR types Assembly, Microsoft.SqlServer.Types, does not allow for files to be added to it. So, I used the main SQL# Assembly from my SQL# SQLCLR library:

DECLARE @NumLetters INT = 259;
DECLARE @SQL16 NVARCHAR(MAX);
SET @SQL16 = N'
ALTER ASSEMBLY [SQL#] ADD FILE FROM 0x0102 AS N'''
    + REPLICATE(N'p', @NumLetters) + N''';
ALTER ASSEMBLY [SQL#] DROP FILE N'''
    + REPLICATE(N'p', @NumLetters) + N''';
';
PRINT @SQL16;
EXEC (@SQL16);
/* -- Using a @NumLetters value of 260 gets the following error:
Msg 6236, Level 16, State 2, Line XXXXX
ALTER ASSEMBLY failed because filename 'ppp...ppp' is too long.
*/

It seems that 259 is the max length that can be used. Why? Going back to that “Naming Files…” documentation (linked above), it states:

For example, the maximum path on drive D is “D:\some 256-character path string<NUL>” where “<NUL>” represents the invisible terminating null character for the current system codepage. (The characters < > are used here for visual clarity and cannot be part of a valid path string.)

Meaning: the 260th character is the “<NUL>” string terminator (i.e. NCHAR(0) ).

Transaction Names

Some quick tests to verify that following the rules does work:

BEGIN TRAN _4; SAVE TRAN [a]; COMMIT;
BEGIN TRAN h$; SAVE TRAN [a]; COMMIT;
BEGIN TRAN #4; SAVE TRAN [a]; COMMIT;
BEGIN TRAN h@4; SAVE TRAN [a]; COMMIT;

And a couple of tests to verify that not following the rules causes an error:

BEGIN TRAN 4;
BEGIN TRAN $h;

Both of the queries above get the following error:

Msg 102, Level 15, State 1, Line XXXXX

Incorrect syntax near …

The documentation for BEGIN TRANSACTION and SAVE TRANSACTION both state that:

  1. The max size of a transaction name is 32 characters
  2. Literals / constants (i.e. regular identifiers) over 32 characters are not allowed
  3. Variables containing over 32 characters are silently truncated
  4. transaction_name is always case sensitive, even when the instance of SQL Server is not case sensitive.”

First we test the regular identifier:

BEGIN TRAN _23456789a123456789b123456789c12; SAVE TRAN [a]; COMMIT;
-- Success
BEGIN TRAN _23456789a123456789b123456789c123; SAVE TRAN [a]; COMMIT;
/*
Msg 103, Level 15, State 2, Line XXXXX
The identifier that starts with '_23456789a123456789b123456789c123' is
    too long. Maximum length is 32.
*/

Next we test a variable containing the transaction name:

DECLARE @TranName NVARCHAR(50);
SET @TranName = N'123456789a123456789b123456789c123456789d';
BEGIN TRAN @TranName; SAVE TRAN [a]; ROLLBACK TRAN @TranName;
SELECT [Transaction Name], *
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction Name] LIKE N'1234%';
-- 123456789a123456789b123456789c12

Finally we test the case-sensitivity of the name by executing on a case-insensitive instance (I executed on an instance of SQL Server Express LocalDB as they are always case-insensitive):

BEGIN TRAN tr; SAVE TRAN a; ROLLBACK TRAN tr;
-- Success!
SELECT 1 WHERE 'a' = 'A';
-- 1
BEGIN TRAN tr; SAVE TRAN a; ROLLBACK TRAN TR;
/*
Msg 6401, Level 16, State 1, Line XXXXX
Cannot roll back TR. No transaction or savepoint of that name was found.
*/

But wait. Are transaction names really being handled as case-sensitive, or is this really a binary (i.e. “ordinal” in .NET) comparison? Those two types of comparisons are not the same:

DECLARE @Composed NVARCHAR(10) = NCHAR(0x00DC),
        @Decomposed NVARCHAR(10) = N'U' + NCHAR(0x0308);
SELECT @Composed, @Decomposed;
-- Ü   Ü
SELECT 1
WHERE  @Composed = @Decomposed COLLATE Latin1_General_100_CS_AS;
-- 1
BEGIN TRAN @Composed; SAVE TRAN a; ROLLBACK TRAN @Decomposed;
/*
Msg 6401, Level 16, State 1, Line XXXXX
Cannot roll back Ü. No transaction or savepoint of that name was found.
*/

Ah, so they are actually using a binary / ordinal comparison, and not a case-sensitive comparison.

Database Names

The documentation for CREATE DATABASE states:

database_name can be a maximum of 128 characters, unless a logical name is not specified for the log file. If a logical log file name is not specified, SQL Server generates the logical_file_name and the os_file_name for the log by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical file name is no more than 128 characters.

Let’s test:

DECLARE @NumLetters INT = 124;
DECLARE @SQL17 NVARCHAR(MAX);
SET @SQL17 = N'
CREATE DATABASE ' + REPLICATE(N'q', @NumLetters) + N';
   SELECT [file_id], [type_desc], [name], [physical_name]
   FROM ' + REPLICATE(N'q', @NumLetters) + N'.sys.database_files;
DROP DATABASE ' + REPLICATE(N'q', @NumLetters) + N';
';
PRINT @SQL17;
EXEC (@SQL17);
/* -- Using a @NumLetters value of 125 gets the following error:
Msg 407, Level 16, State 1, Line XXXXX
Internal error. The string routine in file
      sql\ntdbms\storeng\dfs\manager\filemgr.cpp,
      line 748 failed with HRESULT 0x8007007a.
*/

That is not exactly a well-handled error ;-). Still, it appears that the true max name size when not specifying the logical name is actually 124 characters, not 123. This makes sense since the system generated logical name will be the database name plus _log, which is 4 characters, so the total is 128 characters.

Local Temporary Tables and Stored Procedures

First we can test the minimum length for Tables:

CREATE TABLE # ([Col1] INT);
SELECT * FROM #;
SELECT * FROM tempdb.sys.objects WHERE [name] LIKE N'#[_]%';
-- #__...__00000000001F
DROP TABLE #;

And then for Stored Procedures:

GO
CREATE PROCEDURE #
AS
SET NOCOUNT ON;
GO
EXEC #;
SELECT * FROM tempdb.sys.objects WHERE [name] LIKE N'#[_]%';
-- #__...___0000187B
DROP PROCEDURE #;

Next we test for maximum length and Collation scope:

SELECT 1 WHERE 'a' = 'A';
-- 1
DECLARE @NumLetters INT = 115;
DECLARE @SQL1 NVARCHAR(MAX);
SET @SQL1 = N'CREATE TABLE #A' + REPLICATE(N'a', (@NumLetters - 1))
          + N' ([Col1] INT);
SELECT * FROM #' + REPLICATE(N'a', @NumLetters) + N';
';
PRINT @SQL1;
EXEC (@SQL1);
/* -- Using a @NumLetters value of 116 gets the following error:
Msg 193, Level 15, State 1, Line XXXXX
The object or column name starting with '#Aaa...aaa' is too long.
    The maximum length is 116 characters.
*/

In a case-insensitive Database on an instance using a binary Collation, the test above returns a 1 from the “SELECT 1“, but the “SELECT * FROM #...” fails since the casing of the first letter is different between the CREATE and SELECT.

In a Database using a binary Collation on a case-insensitive instance, the test above returns nothing from the “SELECT 1“, but the “SELECT * FROM #...” succeeds, even with the casing of the first letter being different between the CREATE and SELECT.

All three of these tests prove that the first character of the name is the number sign ( # ). This is why we could only add 115 characters to the name instead of 116.

Global Temporary Tables and Stored Procedures

First we can test the minimum length for Tables:

CREATE TABLE ## ([Col1] INT);
SELECT * FROM ##;
SELECT * FROM tempdb.sys.objects WHERE [name] LIKE N'##';
-- ##
DROP TABLE ##;

And then for Stored Procedures:

GO
CREATE PROCEDURE ##
AS
SET NOCOUNT ON;
SELECT 22;
GO
EXEC ##;
-- 22
SELECT * FROM tempdb.sys.objects WHERE [name] LIKE N'##';
-- ##
DROP PROCEDURE ##;

Next we test for maximum length and Collation scope:

SELECT 5 WHERE 'b' = 'B';
-- 5
DECLARE @NumLetters INT = 126;
DECLARE @SQL5 NVARCHAR(MAX);
SET @SQL5 = N'CREATE TABLE ##E' + REPLICATE(N'e', (@NumLetters - 1))
              + N' ([Col1] INT);
SELECT * FROM ##' + REPLICATE(N'e', @NumLetters) + N';';
PRINT @SQL5;
EXEC (@SQL5);
SET @SQL5 = N'DROP TABLE ##E' + REPLICATE(N'e', (@NumLetters - 1)) + N';';
PRINT @SQL5;
EXEC (@SQL5);
/* -- Using a @NumLetters value of 127 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with '##Ee...eee' is too long.
    Maximum length is 128.
*//* -- Using @NumLetters = 127 on a binary or case-sensitive Instance gets:
Msg 208, Level 16, State 0, Line XXXXX
Invalid object name '##eee...eee'.
*/

In a case-insensitive Database on an instance using a binary Collation, the test above returns a 5 from the “SELECT 5“, but the “SELECT * FROM ##...” fails since the casing of the first letter is different between the CREATE and SELECT.

In a Database using a binary Collation on a case-insensitive instance, the test above returns nothing from the “SELECT 5“, but the “SELECT * FROM ##...” succeeds, even with the casing of the first letter being different between the CREATE and SELECT.

All three of these tests prove that the first two characters of the name are the number signs ( ## ). This is why we could only add 126 characters to the name instead of 127.

#” as a Starting Character

Test if anything other than temporary tables / stored procedures can start with a number sign ( # ).

The following tests all succeed:

EXEC (N'CREATE TABLE #T (#Col1 INT);');
EXEC (N'CREATE TABLE #CharTest (Col1 INT);
CREATE INDEX #T ON #CharTest ([Col1]);');
EXEC (N'CREATE SCHEMA #SchemaTest;');
EXEC (N'DROP SCHEMA #SchemaTest;');

The following tests all fail:

EXEC (N'CREATE VIEW #T AS SELECT * FROM sys.objects;');
/*
Msg 4103, Level 15, State 1, Line XXXXX
"#T": Temporary views are not allowed.
*/EXEC (N'CREATE TRIGGER #TR ON dbo.CharTest AFTER INSERT AS PRINT 5;');
/*
Msg 208, Level 16, State 77, Procedure #XXXXX,
    Line 1 [Batch Start Line YYYYY]
Invalid object name '#TR'.
*/EXEC (N'ALTER TABLE dbo.CharTest ADD CONSTRAINT #CK
           DEFAULT (1) FOR [Col1];');
/*
Msg 8166, Level 16, State 0, Line XXXXX
Constraint name '#CK' not permitted. Constraint names cannot begin with
    a number sign (#).
*/

Variables and Table Variables

First we can test the minimum length for Variables:

DECLARE @ INT;
SET @ = 5;
SELECT @;
-- 5

And then for Table Variables:

DECLARE @ TABLE ([Col11] INT);
INSERT INTO @ ([Col11]) VALUES (11);
SELECT * FROM @;
-- 11

Next we test for maximum length and Collation scope:

SELECT 10 WHERE 'c' = 'C';
-- 10
DECLARE @NumLetters INT = 127;
DECLARE @SQL10 NVARCHAR(MAX);
SET @SQL10 = N'DECLARE @I' + REPLICATE(N'i', (@NumLetters - 1)) + N' INT;
SET @' + REPLICATE(N'i', @NumLetters) + N' = 1234;';
PRINT @SQL10;
EXEC (@SQL10);
/* -- Using a @NumLetters value of 128 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with '@Iii...iii' is too long.
    Maximum length is 128.
*//* -- Using @NumLetters = 127 on a binary or case-sensitive Instance gets:
Msg 137, Level 15, State 1, Line XXXXX
Must declare the scalar variable "@iii...iii".
*/

In a case-insensitive Database on an instance using a binary Collation, the test above returns a 10 from the “SELECT 10“, but the “SELECT * FROM @...” fails since the casing of the first letter is different between the CREATE and SELECT.

In a Database using a binary Collation on a case-insensitive instance, the test above returns nothing from the “SELECT 10“, but the “SELECT * FROM @...” succeeds, even with the casing of the first letter being different between the CREATE and SELECT.

All three of these tests prove that the first character of the name is the at sign ( @ ). This is why we could only add 127 characters to the name instead of 128.

@” as a Starting Character

Test if anything other than variables / parameters can start with an at sign ( @ ).

The following tests all fail:

EXEC (N'CREATE TABLE dbo.@T ([Col1] INT);');
EXEC (N'CREATE TABLE #T (@Col1 INT);');
EXEC (N'CREATE USER @Bob WITHOUT LOGIN;');
EXEC (N'GOTO @Error;');

Each of those tests produce the following error:

Msg 102, Level 15, State 1, Line XXXXX

Incorrect syntax near ‘@…’.

Columns and Column-aliases

First we test the max length of column names:

DECLARE @NumLetters INT = 128;
DECLARE @SQL8 NVARCHAR(MAX);
SET @SQL8 = N'CREATE TABLE #qwerty ([' + REPLICATE(N'g', @NumLetters)
           + N'] INT);';
PRINT @SQL8;
EXEC (@SQL8);
/* -- Using a @NumLetters value of 129 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with 'ggg...ggg' is too long.
    Maximum length is 128.
*/

And then we test the max length of column-alias names:

DECLARE @NumLetters INT = 128;
DECLARE @SQL9 NVARCHAR(MAX);
SET @SQL9 = N'SELECT 1 AS [' + REPLICATE(N'h', @NumLetters) + N'];';
PRINT @SQL9;
EXEC (@SQL9);
/* -- Using a @NumLetters value of 129 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with 'hhh...hhh' is too long.
    Maximum length is 128.
*/

Table-aliases

Test for maximum length and Collation scope:

DECLARE @NumLetters INT = 128;
DECLARE @SQL12 NVARCHAR(MAX);
SET @SQL12 = N'CREATE TABLE #TableAlias ([Col1] INT);
SELECT K' + REPLICATE(N'k', (@NumLetters - 1)) + N'.* FROM #TableAlias '
            + REPLICATE(N'k', @NumLetters) + N';';
PRINT @SQL12;
EXEC (@SQL12);
/* -- Using a @NumLetters value of 129 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with 'kkk...kkk' is too long.
    Maximum length is 128.
*//* -- Using @NumLetters = 128 on a binary or case-sensitive Instance gets:
Msg 107, Level 15, State 1, Line XXXXX
The column prefix 'Kkk...kkk' does not match with a table name or
    alias name used in the query.
*/

In a case-insensitive Database on an instance using a binary Collation (hence TempDB uses a binary Collation), the test above returns an empty result set, even with the casing of the first letter being different between the creation and usage of the table alias.

In a Database using a binary Collation on a case-insensitive instance (hence TempDB uses a case-insensitive Collation), the test above fails since the casing of the first letter is different between the creation and usage of the table alias.

Indexes

Test for maximum length:

DECLARE @NumLetters INT = 128;
DECLARE @SQL11 NVARCHAR(MAX);
SET @SQL11 = N'CREATE TABLE #Index ([Col1] INT); CREATE INDEX ['
             + REPLICATE(N'j', @NumLetters) + N'] ON #Index ([Col1]);';
PRINT @SQL11;
EXEC (@SQL11);
/* -- Using a @NumLetters value of 129 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with 'jjj...jjj' is too long.
    Maximum length is 128.
*/

Cursors

Test for maximum length and Collation scope of the cursor name (not the referenced query):

DECLARE @NumLetters INT = 128;
DECLARE @SQL13 NVARCHAR(MAX);
SET @SQL13 = N'DECLARE M' + REPLICATE(N'm', (@NumLetters - 1))
                           + N' CURSOR FOR SELECT * FROM sys.objects;
OPEN ' + REPLICATE(N'm', @NumLetters) + N';
CLOSE ' + REPLICATE(N'm', @NumLetters) + N';
DEALLOCATE ' + REPLICATE(N'm', @NumLetters) + N';
';
PRINT @SQL13;
EXEC (@SQL13);
/* -- Using a @NumLetters value of 129 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with 'mmm...mmm' is too long.
    Maximum length is 128.
*//* -- If not in a case-INsensitive Collation Instance
Msg 16916, Level 16, State 1, Line XXXXX
A cursor with the name 'mmm...mmm' does not exist.
*/

In a case-insensitive Database on an instance using a binary Collation, the test above fails since the casing of the first letter is different between the creation and usage of the cursor.

In a Database using a binary Collation on a case-insensitive instance, the test above does not produce an error, even with the casing of the first letter being different between the creation and usage of the cursor.

GOTO Labels

Test for maximum length and Collation scope:

DECLARE @NumLetters INT = 128;
DECLARE @SQL13 NVARCHAR(MAX);
SET @SQL13 = N'SELECT 1; GOTO L' + REPLICATE(N'l', (@NumLetters - 1))
         + N'; SELECT 2; ' + REPLICATE(N'l', @NumLetters) + N': SELECT 3;';
PRINT @SQL13;
EXEC (@SQL13);
-- 1
-- 3
/* -- Using a @NumLetters value of 129 gets the following error:
Msg 103, Level 15, State 4, Line XXXXX
The identifier that starts with 'lll...lll' is too long.
    Maximum length is 128.
*//* -- If not on a case-INsensitive Collation Instance
Msg 133, Level 15, State 1, Line XXXXX
A GOTO statement references the label 'Lll...lll' but the label
    has not been declared.
*/

In a case-insensitive Database on an instance using a binary Collation, the test above fails since the casing of the first letter is different between the creation and usage of the label.

In a Database using a binary Collation on a case-insensitive instance, the test above does not produce an error, even with the casing of the first letter being different between the creation and usage of the label.

Contained Databases

In a “Contained” Database (i.e. CONTAINMENT = PARTIAL ), the Collation scope of various items changes. There is also a new option to use along with the COLLATE keyword: CATALOG_DEFAULT. In a non-contained Database, CATALOG_DEFAULT is equivalent to DATABASE_DEFAULT. But, in a contained Database, CATALOG_DEFAULT always translates to Latin1_General_100_CI_AS_WS_KS_SC.

The Collation of the following items changes to CATALOG_DEFAULT in a contained Database:

  • Parameter and Variable (including Table Variable) names
  • Temporary metadata
  • Cursor names
  • GOTO Labels
  • Database-level metadata

Please see the documentation for Contained Database Collations for more details.

(I will update this post later with test queries for Contained Databases…Someday)

thingamajig-and-whatchamacallit_official_600x310

The Rules (According to SQL Server)

The final list of rules, based on the testing shown above, is too much to list here. So, that list has been moved to its own page:

Completely Complete List of Rules for T-SQL Identifiers

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating