Lo and behold:
Command(s) completed successfully.
Okay, so what if we call it (with code that doesn't get any of the case wrong, of course)?
DECLARE @tt dbo.tvpCustomer; INSERT @tt VALUES(1, N'Bob', N'Frank'); EXEC dbo.AddCustomers @TVP = @tt;
The engine must look in a different place now, because this yields:
Msg 208, Level 16, State 1, Procedure AddCustomers Invalid object name 'dbo.customers'.
This is an even bigger problem, of course, because the code was deployed successfully, and now the error might not happen until an end user tries to add (or become) a customer...
A LITTLE SIMPLER.
Here's an even simpler set of code that I wanted to try on each database/instance combination:
-- wrong case for variable DECLARE @foo INT; SELECT @FOO; GO CREATE TABLE dbo.foo(bar INT); GO -- wrong case for column name SELECT BAR FROM dbo.foo; GO -- wrong case for object name SELECT bar FROM dbo.FOO; GO -- wrong case for database name USE Master; GO
Here's how they worked out:
|Database / Instance||variable||column||object||database|
|CI / CI||OK||OK||OK||OK|
|CS / CI||OK||Invalid column||Invalid object||OK|
|CI / CS||Must declare variable||OK||OK||Database does not exist|
|CS / CS||Must declare variable||Invalid column||Invalid object||Database does not exist|
The reasons for these patterns quickly become fairly evident: variables use instance collation, object and column names use the collation of
sys.objects etc. (which use database collation), and database names use
sys.databases (which uses instance collation). If we look at those in each case:
SELECT collation_name FROM sys.all_columns WHERE name = N'name' AND [object_id] = OBJECT_ID(N'sys.objects'); SELECT collation_name FROM master.sys.all_columns WHERE name = N'name' AND [object_id] = OBJECT_ID(N'sys.databases');
This is what they look like:
|Database / Instance||sys.all_objects||sys.all_columns|
|CI / CI||SQL_Latin1_General_CP1_CI_AS||SQL_Latin1_General_CP1_CI_AS|
|CS / CI||SQL_Latin1_General_CP1_CS_AS||SQL_Latin1_General_CP1_CI_AS|
|CI / CS||SQL_Latin1_General_CP1_CI_AS||SQL_Latin1_General_CP1_CS_AS|
|CS / CS||SQL_Latin1_General_CP1_CS_AS||SQL_Latin1_General_CP1_CS_AS|
There are some peculiarities, though. For example, with system objects, you can get the case of a column name wrong no problem, even in a case sensitive database on a case sensitive instance. This works no problem:
SELECT NAME FROM sys.objects;
However if you get the case wrong for the view itself instead of the column:
SELECT name FROM sys.Objects;
Still an error message:
Msg 208, Level 16, State 1 Invalid object name 'sys.Objects'.
Long story short, a lot of things can go wrong when you develop in a case insensitive collation but code might ultimately get deployed to a case sensitive database or instance (or both). In most cases the errors will be caught at deployment time, but in a few, the objects could be created successfully anyway, and problems would not be detected until testing/QA time (or, worse, even later). This is one of those things that is fairly easy to get right from the start, and a lot more cumbersome to fix later.
There are some code analysis tools that can check for things like this; when I pinged George Mastros (@gmmastros), he agreed that it was a good idea, and is going to try to put it in an upcoming release of SQLCop.
But you can prevent these problems far earlier than that, if you're willing to do it. I have long been a proponent of developing - at least for code that does not cross databases or servers - on a case sensitive collation. This makes sure these errors are caught up front, and not six months from now when a database is migrated or consolidated. "But won't that slow me down?" Initially, maybe, a little. But in all honesty, IntelliSense (or tools like Redgate SQL Prompt) should prevent you from ever having to type this stuff correctly on your own anyway.
[See an index of all bad habits / best practices posts]