Always use the right case for data type names in SQL Server

By: Aaron Bertrand   |   Updated: 2021-10-04   |   Comments (1)   |   Related: > Data Types

Problem

We all have coding conventions that we have learned and adopted over the years and, trust me, we can be stubborn about them once they're part of our muscle memory. For a long time, I would always uppercase data type names, like INT, VARCHAR, and DATETIME. Then I came across a scenario where this wasn't possible anymore: a case-sensitive instance. In a recent post, Solomon Rutzky suggested:

• 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 have a counter-example that has led me to be much more careful about always matching the case found in sys.types.

Solution

To demonstrate the scenario, I came across a while back, I created a Docker container with SQL Server running under Hebrew_100_BIN2 collation:

docker run -i -e ACCEPT_EULA=Y -e SA_PASSWORD=Turk3yT1m3 
    -e MSSQL_COLLATION=Hebrew_100_BIN2 
    -p 1433:1433 -d 
    mcr.microsoft.com/mssql/server:2017-latest

I then tried what Solomon used to reproduce the problem with sysname on certain versions of SQL Server:

DECLARE @x SysName;

This worked without issue, as he suggested. The issue I had come across previously involved one of the new CLR-based types, geography. So, I tried the same:

DECLARE @x GeoGraphy;

This, too, worked without issue. So, I agree that, for specific scenarios, type names are case insensitive. Declaring a variable is one scenario where built-in types do not have to match exactly what is in sys.types.

But there are other scenarios where you have to tread more lightly. One is calling methods on types, like geometry or geography. If you run the following, it will work fine:

SELECT geography::Point(10,-20,4120);

But if you believe the type name is case insensitive and run this, it will fail:

SELECT GeoGraphy::Point(10,-20,4120);

Result:

Msg 243, Level 16, State 4
Type GeoGraphy is not a defined system type.

Alias Type Example

If we go beyond the built-in SQL Server data types, you will find that they, too, are case sensitive in some scenarios. Let's create a very simple alias type:

CREATE TYPE dbo.Email FROM nvarchar(320);

Now if we try to declare a variable, column, or parameter with this data type in upper case, they all fail:

CREATE TABLE dbo.foo(e dbo.EMAIL);
GO
DECLARE @e dbo.EMAIL;
GO
CREATE PROCEDURE dbo.x @e dbo.EMAIL AS PRINT 1;
GO

Result:

Msg 2715, Level 16, State 3, Procedure x
Column, parameter, or variable #1: Cannot find data type dbo.EMAIL.
Msg 2715, Level 16, State 3
Column, parameter, or variable #1: Cannot find data type dbo.EMAIL.
Parameter or variable '@e' has an invalid data type.
Msg 2715, Level 16, State 6
Column, parameter, or variable #1: Cannot find data type dbo.EMAIL.
Parameter or variable '@e' has an invalid data type.

Table Type Example

Same with a table type:

CREATE TYPE dbo.Ints AS TABLE(i int PRIMARY KEY);
GO
DECLARE @x dbo.INTS;

Result:

Msg 2715, Level 16, State 3
Column, parameter, or variable #1: Cannot find data type dbo.INTS.
Parameter or variable '@x' has an invalid data type.

Clearly, calling methods on types and referencing alias or user-defined types can still be bound to instance collation settings, and this isn't something you'll catch if you develop on a case insensitive collation. This is one of the reasons I push developers to use binary collations – so they come across these issues long before production.

Conclusion

For built-in data types, always use lower case. For anything user-defined (like alias or CLR types), rely on IntelliSense and/or verify that the case matches what's defined in sys.types. This can prevent you from being surprised when your database gets deployed to an instance with a case- or binary-sensitive collation, even if in most contexts you are safe.

Next Steps

See these tips and other resources about case sensitivity and collation in SQL Server:

• Is the [sysname] SQL Server System Data Type Alias Name Case-Insensitive?
• #BackToBasics: Why I use lower case for data type names (now)
• SQL Server Case Sensitive Collations and DMVs
• Case Sensitive Search on a Case Insensitive SQL Server
• SQL Server Collation Overview and Examples
• Changing SQL Server Collation After Installation

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips