Blog Post

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.0Always
case-insensitive
Tied to instance-
level collation
Tied to instance-
level collation
2000Always
case-insensitive
Tied to current
database
Tied to instance-
level collation
2005Always
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.


Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating