Bad Habits: Being CarELesS about cAsE

[See an index of all bad habits / best practices posts]

As a frequent reviewer of queries, and a regular contributor to our answers site and dba.stackexchange.com, I come across a lot of code that seems to have been written, shall we say, quickly. There are a lot of transgressions of course, but one that I notice frequently is inconsistency in case. People will have a table like this:

CREATE TABLE dbo.MyTable
(
  PersonID INT,
  FirstName SYSNAME
);

And then a query like this:

SELECT personId, FIRSTName 
  FROM dbo.myTable
  ORDER BY firstname;

The code compiled and the query returned results, so party time, right?

This can cause problems.

For one, being inconsistent about case can lead to situations like this, where another user executes the exact same logical query, but uses different case:

SELECT PersonId, Firstname 
  FROM dbo.Mytable
  ORDER BY firstName;

There are a few subtle differences you might spot right away, and if you look at the plan cache, you'll see that you actually stored two copies of the plan (since the query_hash is case- and whitespace-sensitive). I talked about this a bit more thoroughly and showed an example late last year:

• Multiple Plans for an "Identical" Query

More importantly, though: neither query uses identifiers that exactly match the base table or its columns. And while the query may work on your machine, it will break if it gets deployed to a case-sensitive database or to a server using a case-sensitive collation.

PROOF.

I set up two SQL Server instances, one with the default SQL_Latin1_General_CP1_CI_AS collation, and one with its case-sensitive cousin, SQL_Latin1_General_CP1_CS_AS. On each instance, I set up two databases, one using each collation. What I wanted to do was simulate what would happen if I wrote code - sloppily, on purpose - in the best case scenario (case insensitive collation on a case insensitive instance), then tried to deploy it and run it in the other environments.

So, on that initial CI/CI instance, I created the following objects - a table and a matching TVP, along with a stored procedure that intentionally uses the wrong case for the table and column names, and even uses the wrong case for its own input parameter:

CREATE TABLE dbo.Customers
(
  CustomerID INT PRIMARY KEY,
  FirstName NVARCHAR(32) NOT NULL, 
  LastName NVARCHAR(32) NOT NULL
);
GO
 
CREATE TYPE dbo.tvpCustomer AS TABLE
(
  CustomerID INT PRIMARY KEY,
  FirstName NVARCHAR(32) NOT NULL,
  LastName NVARCHAR(32)
);
GO
 
CREATE PROCEDURE dbo.AddCustomers
  @TVP dbo.tvpcustomer READONLY
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.customers(customerid,firstname,lastname)
    SELECT customerid,firstname,lastname FROM @tvp;
END
GO

First, I tried deploying the same objects to the case sensitive database on the case insensitive server. I can't get very far, because as soon as I try to create the procedure, I get all kinds of problems:

Msg 2715, Level 16, State 3, Procedure AddCustomers
Column, parameter, or variable #1: Cannot find data type dbo.tvpcustomer.
Parameter or variable '@TVP' has an invalid data type.
Msg 1087, Level 16, State 1, Procedure AddCustomers
Must declare the table variable "@TVP".

I next tried the case insensitive database on the case sensitive server. There was only one error this time; interesting to note, though, that the error message uses the lower-case version of the table variable name (which comes from the procedure body), unlike above where it used the upper-case version (which comes from the parameter list):

Msg 1087, Level 15, State 2, Procedure AddCustomers
Must declare the table variable "@tvp".

The case sensitive database on the case sensitive server returned to the more verbose error messages above. So then I tried partially correcting the procedure in that environment, making the parameter casing match and the type name correct:

CREATE PROCEDURE dbo.AddCustomers
  @TVP dbo.tvpCustomer READONLY
--------------^ fixed this
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.customers(customerid,firstname,lastname)
    SELECT customerid,firstname,lastname FROM @TVP;
    ----------------------------- and this ----^^^
END
GO

I was expecting this to actually get created, perhaps with that warning about deferred name resolution, because it shouldn't have been able to find dbo.customers in the system metadata. But no, it matched the table regardless of case, but noticed that the column names didn't match:

Msg 207, Level 16, State 1, Procedure AddCustomers
Invalid column name 'customerid'.
Msg 207, Level 16, State 1, Procedure AddCustomers
Invalid column name 'firstname'.
Msg 207, Level 16, State 1, Procedure AddCustomers
Invalid column name 'lastname'.

So, this made me investigate another avenue: fixing the column names, but not the table name. Surely then the procedure wouldn't get created?

CREATE PROCEDURE dbo.AddCustomers
  @TVP dbo.tvpCustomer READONLY
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.customers(CustomerID,FirstName,LastName)
  --- fixed these -----^-------^^-^----^----^---^
    SELECT CustomerID,FirstName,LastName FROM @TVP;
  -- and --^-------^^-^----^----^---^
END
GO