Is the [sysname] SQL Server System Data Type Alias Name Case-Insensitive?

(last updated: 2021-07-02 @ 19:55 EST / 2021-07-02 @ 23:55 UTC )

The sysname data type (in SQL Server) is a system-provided, though not built-in, alias for NVARCHAR(128). This alias is used for nearly all identifiers (i.e. names of tables, columns, indexes, databases, logins, etc.), starting in SQL Server 7.0 (for a full list of rules and exceptions, please see: Completely Complete List of Rules for T-SQL Identifiers ).

Because sysname is used for most identifiers, I tend to use it for variables and column names when writing code that deals with system objects. There have been a few occasions over the years that I’ve gotten an error from specifying SysName with a casing that was not all lower-case (though I don’t recall that ever happening with any of the built-in datatypes such as INT or VarChar). So, I’ve gotten into the habit of specifying sysname in all lower-case, even though I prefer to use all upper-case for all other built-in data types.

Over time I’ve used a variety of SQL Server versions with a variety of instance-level collations. This has lead me to conclude that sysname had to be all lower-case in some earlier versions if the instance-level collation was either binary or case-sensitive. However, it was not clear exactly which versions and what scenarios truly affected the behavior of sysname name resolution, so I played it safe and continued to always specify that particular data type in all lower-case, even leaving comments in scripts that it must remain as all lower-case (just in case someone goes through and tries to make it upper-case to be consistent with the other data types).

But then, while researching another topic, I recently found the following in some old documentation ( Breaking Changes to Database Engine Features in SQL Server 2005 ):

System Objects and Metadata
System object name and system type name collation matching
In earlier versions of SQL Server, system object and system type names are matched against the collation of the master database. In SQL Server 2005, system object names and system type names are automatically cast to correspond to the collation of the current database. If references to these objects in your script or applications do not match how they appear in the catalog and the current database has a case-sensitive collation, the script or application may fail. For example, the statement EXEC SP_heLP will fail if the current database has a case-sensitive collation.

Interesting. That certainly sounds like a possible explanation for what I (and others) have encountered. But, just to be safe we should probably run some tests.

Testing

Versions

I tested with the following versions of SQL Server:

7.0 RTM (7.00.623)
2000 RTM (8.00.194)
2005 RTM (9.00.1399.06)
2008 SP4-GDR (10.0.6556.0)
2012 SP4-GDR (11.0.7507.2) -- LocalDB
2017 CU22-GDR (14.0.3370.1)
2019 CU8-GDR (15.0.4083.2)

Test Query

I executed the following statements:

-- First SELECT does not work on SQL Server 7.0, but also is not
-- needed there.
SELECT @@VERSION AS [Version],
       SERVERPROPERTY(N'Collation') AS [Instance-level],
       DB_NAME() AS [DatabaseName],
       DATABASEPROPERTYEX(DB_NAME(), N'Collation') AS [Database-level];
SELECT CONVERT(nChar, 8) AS [NChar];
GO
SELECT CONVERT(sysName, 'h') AS [SysName];
GO
EXEC dbo.SP_help;

Results

Errors that I ran into, depending on collation and SQL Server version:

For SELECT CONVERT(nChar, 8);

n/a (it never failed)
For SELECT CONVERT(sysName, 'h');
Msg 243, Level 16, State 1, Line XXXXX
Type sysName is not a defined system type.
For EXEC dbo.SP_help;
Msg 2812, Level 16, State 62, Line XXXXX
Could not find stored procedure 'dbo.SP_help'.

SQL Server 7.0

In the [master] database using a binary collation ( Unicode comparison style: case-sensitive )
- nChar : Success
- sysName : Error
- SP_help : Error
In a user database
- n/a (SQL Server 7.0 has a single, instance-wide collation for all databases, columns, and expressions)

SQL Server 2000

In the [master] database using a binary collation ( Hebrew_BIN )
- nChar : Success
- sysName : Error
- SP_help : Error
In a user database using a case-insensitive collation ( Latin1_General_CI_AS )
- nChar : Success
- sysName : Success (different from previous versions)
- SP_help : Error

sysName errors in the binary database but not in the case-insensitive database, hence it’s tied to the current database.

SP_help errors even in the case-insensitive database, hence it’s tied to the instance-level collation.

SQL Server 2005

In the [master] database using a binary collation ( Hebrew_BIN2 )
- nChar : Success
- sysName : Error
- SP_help : Error
In a user database using a case-insensitive collation ( Latin1_General_CI_AS )
- nChar : Success
- sysName : Success
- SP_help : Success (different from previous versions)

sysName behavior has not changed.

SP_help still errors in the binary database, but now succeeds in the case-insensitive database, hence it’s tied to the database-level collation.

SQL Server 2008

In the [master] database using a binary collation ( Latin1_General_100_BIN )
- nChar : Success
- sysName : Success (different from previous versions)
- SP_help : Error
In a user database using a case-insensitive collation ( Latin1_General_100_CI_AS )
- nChar : Success
- sysName : Success
- SP_help : Success

sysName now succeeds in the binary database, hence it’s always case-insensitive and not tied to any collation.

SP_help behavior has not changed.

SQL Server 2012

In the [master] database using a case-insensitive collation ( SQL_Latin1_General_CP1_CI_AS )
- nChar : Success
- sysName : Success
- SP_help : Success
In a user database using a binary collation ( Hebrew_100_BIN2 )
- nChar : Success
- sysName : Success
- SP_help : Error

SQL Server 2017

In the [master] database using a binary collation ( Hebrew_100_BIN2 )
- nChar : Success
- sysName : Success
- SP_help : Error
In a user database using a case-insensitive collation ( Latin1_General_100_CI_AS_SC )
- nChar : Success
- sysName : Success
- SP_help : Success

SQL Server 2019

In the [master] database using a binary collation ( Latin1_General_100_BIN2_UTF8 )
- nChar : Success
- sysName : Success
- SP_help : Error
In a user database using a case-insensitive collation ( SQL_Latin1_General_CP1_CI_AS )
- nChar : Success
- sysName : Success
- SP_help : Success

Summary

As we saw in the results in the previous section, the behavior starting in SQL Server 2008 has not changed (at least not as of SQL Server 2019), so we can simplify by calling it “2008 and newer”.

The following chart summarizes the results. Behaviors that changed from the previous version have been italicized.

SQL Server Version	Built-in Data Type Names	System Aliases (i.e. `sysname`)	System Object Names
7.0	Always case-insensitive	Tied to instance- level collation	Tied to instance- level collation
2000	Always case-insensitive	Tied to current database	Tied to instance- level collation
2005	Always case-insensitive	Same as 2000 (no change)	Tied to current database
2008 and newer	Always case-insensitive	Always case-insensitive	Same as 2005 (no change)

Conclusion

The built-in data type names have always been case-insensitive (at least since SQL Server 7.0).
System object names were tied to the instance-level collation through SQL Server 2000, then tied to the current database collation since SQL Server 2005.
The sysname data type alias name was tied to the instance-level collation (at least through SQL Server 7.0), then tied to the current database in SQL Server 2000 and 2005, and finally has been case-insensitive since SQL Server 2008.
Of less importance (due to there being minimal practical value, but just to have it stated), the documentation quoted at the beginning of this post is:
1. correct regarding system object names using the collation of the current database starting in SQL Server 2005.
2. incorrect regarding system type names using the collation of the current database starting in SQL Server 2005 (if they meant the sysname data type alias, then that was tied to the current database starting in SQL Server 2000; if they meant the built-in data types, then they were never tied to the current database, or even to the instance).

MEANING: As long as you are working with SQL Server 2008 or newer, all data type names, including sysname, are always case-insensitive, regardless of instance-level or database-level collations.