Handle development issues when dealing with different collation for the instance/database setting

Question

Looking for some guidance on how we can handle the scripts/schemas when being run on different collation in sql server.

We have a process where multiple stored procs are created lets say with schema sales. Therefore all related tables and procs for that within database are stored as sales.table1, sales.table2 ... etc and stored procs like sales.us_proc1, sales.usp_proc2.... etc.

Now for default collation of database on SQL server instance with default collation the procs runs fine. However when we deploy same objects/procs on sql server with case sensitive collation instances same scripts starts failing.

What we can do as of now is have 2 sets of scripts. Say 1st for all non case sensitive server and other set for case sensitive.

But problem is these 2 sets of deployment will go on server which adds bunch of unwanted objects/procs etc..

Please suggest how should be handle such scenarios where single set of deployment for such scripts can be done irrespective of what the server/database collation might be?

Edit- Trying to update my question for few questions asked:

Scripts are failing with error message like below on case sensitive databases-

Msg207,Level 16, state1 - Invalid column name Timestamp - say for proc1

Msg207,Level 16, state1 - Invalid column name state- say for proc2

Yes we did created 2 sets of scripts which ideally does not seem helpful here as lot of objects are just getting deployed twice for case sensitive server

Also we are not creating database as the database would be there and we just need to deploy bunch of these scripts.

Also the collation ends with BIN for TempDB and our database where scripts are deployed.

As mentioned in answers for TempDB, i have gone through most of my scripts and it does not look like we have references to # temp tables in most of the stored procs. HOwever yes we do use local variables and table variables @. Does this also counts for same issues?

Answer 1

The best way to isolate a database from the instance collation is to make it a Partially Contained Database, which enables Contained Database Collations:

Since a design objective of contained databases is to make them self-contained, the dependence on the instance and tempdb collations must be severed. To do this, contained databases introduce the concept of the catalog collation. The catalog collation is used for system metadata and transient objects. Details are provided below.

In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed.

The database collation is retained, but is only used as the default collation for user data. By default, the database collation is equal to the model database collation, but can be changed by the user through a CREATE or ALTER DATABASE command as with non-contained databases.

Answer 2

Schemas have nothing to do with collation. Also, it would certainly help to know what is meant by "scripts starts failing". Please post the exact and full error message(s) to the question.

For the moment, based on the following two pieces of info:

1. The scripts are failing, not just returning unexpected / fewer results,

   and:

2. You said that you can have two sets of scripts, not that you already do have (or at least have tried)

I am guessing that the procs have inconsistent references to the table and/or schema names in terms of casing. For example, the actual object being Sales.Table1 but referenced in one or more procs as Sales.table1 (only difference is the t in Table1). That is not something that can be fixed via a COLLATE clause in a query. That requires fixing the code to use the proper casing. In fact, if you are going to deploy to instances that are case-sensitive, then it would be best to update your development (and test / QA / Staging) instances to also use a case-sensitive default collation at the database level. If your code can be deployed on instances using a binary collation, then perhaps use a binary collation. This will help you catch these types of errors very early in the development cycle.

Additionally, using a case-sensitive or even binary collation (ending in _BIN2, not _BIN) for the database and leaving the instance-level collation as case-insensitive (or just something different than the database default collation) will help you identify potential errors in queries related to temp tables.

In the end, you definitely shouldn't need multiple sets of scripts to handle different collations, especially if this is an issue with creating procedures that fail due to using a different casing on columns names but not tables (which results in an immediate error as it will find the table but not the column), or an issue with executing the procedure if there is a difference in casing for the table name as deferred name resolution will allow the proc to be created but will error upon execution.

NOW, regarding temp tables, the answer is not necessarily to use COLLATE DATABASE_DEFAULT. It all depends on what your app is trying to accomplish for each string column affected. (And FYI: this part is the same, even if you deploy the full database setting a specific collation and aren't having a problem running a script that creates schemas / objects in a DB with any random collation).

If any affected column uses the database's default collation (i.e. does not specify a collation in the CREATE TABLE statement), and the app doesn't require a particular collation for this specific operation/query, then yes, use COLLATE DATABASE_DEFAULT next to each predicate / concatenation / reference in the query containing the temp table.

However, if an affected column is given a specific collation when the table is created, or if the operation / query requires a specific collation (i.e. a filter that must be case-insensitive no matter what), then you should use COLLATE {specific_collation_name} next to each predicate / concatenation / reference in the query containing the temp table.

Based on info added to the question:

The scenario is pretty much what I had guessed based on the previous info:

1. Your scripts are being deployed to existing databases (outside of your control)
2. The errors are coming from column names being referenced in the stored procedures with different casing than they were created with.

Hence, the advice doesn't really change: You only need one set of scripts, the set with the column references matching the casing used when creating the tables. So if a table has a column named TimeStamp, then referencing that column as Timestamp in the code is an error (as it would be in many / most programming languages). You need to fix your source procs, leaving you will a single, correct set of scripts that will work everywhere.

You should also consider either changing the development instance (or just database at the very least, which is easier to change anyway) to use a binary collation. Or, deploy to an instance (or database) using a binary collation as part of your CI process (if you have one), or just as a matter of practice if you don't have an automated process. In either case, you will catch casing differences much earlier in the development process.

Not using temp tables will avoid collation differences in [tempdb]. As for table variables, those should be fine as their string columns, by default, use the database's default collation. You can still run into an issue if you have a string column set to a collation different from the database's default and compare it to a column in a table variable that wasn't created with that same collation. In that case, you just use COLLATE {collation_name} everywhere that issue is happening, specifying the collation used for the column from the user table.

Answer 3

Are you running into issues due to server vs database collation or due to database collation only? Within a database, collation should be ok. The issue is with tempdb which can have different collation, so that using temp tables and even worse temp procedures with params brings up issues. for tables and statements in procedures, the solution is to use

collate database_default

ie:

select * 
  from sales.table1 
  join #tempTable
    on table1.someTextColumn = #tempTable.anothertextColumn collate database_default