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

Posted on by Solomon Rutzky

(last updated: 2021-07-05 @ 14:00 EST / 2021-07-05 @ 18:00 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) 1

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 1 )
    • 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 1		 Tied to instance-
level collation
2000 Always
case-insensitive		 Tied to current
database 1		 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

  1. The built-in data type names have always been case-insensitive (at least since SQL Server 7.0).

  2. 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.

  3. 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.

  4. 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.

Post Update History
  • 2021-07-05 @ 14:00 EST / 2021-07-05 @ 18:00 UTC — Added footnote regarding whether sysname behavior is tied to instance-level or database-level collation in SQL Server 7.0.

  1. Due to SQL Server 7.0 having a single, instance-wide collation that governs all databases, it would certainly seem valid to interpret sysname behavior as being tied to database-level collation instead of the instance-level collation. There’s actually no way to differentiate between the two (without looking at the source code, and I have no access to that). It could be that SQL Server 7.0 was coded to have sysname name resolution tied to the database-level collation. Since all databases use the same collation as the instance, the behavior that users observe is the same. The only difference (in which level of collation determines the behavior) would be:

    • If the behavior is tied to instance-level collation, then there was indeed a change made in SQL Server 2000 for the behavior to be tied to the database-level collation.
    • If the behavior is tied to database-level collation, then the only change (in SQL Server 2000, related to sysname) was allowing databases to use a collation that was different from the instance-level collation.

    This distinction is purely academic, and only mentioned in case someone was wondering about it. I chose to interpret the behavior as being tied to the instance-level collation because practically speaking, SQL Server 7.0 only has a single, instance-wide collation and the behavior of system object name resolution is definitely tied to the instance-level collation (because that did not change in SQL Server 2000).

    ↩ ↩ ↩ ↩

6 thoughts on “Is the [sysname] SQL Server System Data Type Alias Name Case-Insensitive?”

  2. 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.

    I don’t know about this … try geography.

    1. Hi Aaron. Thanks for mentioning that. I had forgotten about the CLR types and the possibility that they might behave differently. So, I tried all 3 CLR types with mixed-casing in SQL Server 2008, on an instance using a binary collation and in a database that was also using a binary collation. No errors. I even tried on SQL Server 2017, also with a binary collation at the instance and database levels, and again no errors. And, just to be safe, I tried all datatypes in SQL Server 2008 (except TEXT, NTEXT, IMAGE as I was using DECLARE to test them) and no errors. I will update this post later tonight to add that additional context.

  3. Sorry, to be more specific, it is not when declaring, it is when calling methods.

    SELECT GEOGRAPHY::POINT(1,1,4120);

    Also alias types and user-defined CLR types are subject to a similar problem to the original sysname issue:

    CREATE TYPE dbo.Email FROM nvarchar(320);
    GO
    DECLARE @email dbo.email;

  4. Not sure how POINT got upper cased there. On a binary collation, this succeeds:

    SELECT geography::Point(1,1,4120);

    This fails:

    SELECT GeoGraphy::Point(1,1,4120);

Leave a Reply to Aaron BertrandCancel reply