SQL Server 2012- Server collation and database collation

Question

I have SQL Server 2012 installed that is used for a few different applications. One of our applications needs to be installed, but the company is saying that:

The SQL collation isn't correct, it needs to be: SQL_Latin1_General_CP1_CI_AS

You can just uninstall the SQL Server Database Engine & upon reinstall select the right collation.

What possible reason would this company have to want to change the collation of the database engine itself?

Answer 1

What possible reason would this company have to want to change the collation of the database engine itself?

The other two answers are speaking in terms of Database-level Collation, not Instance-level Collation (i.e. "database engine itself"). The most likely reason that the vendor has for wanting a highly specific Collation (not just a case-insensitive one of your choosing, for example) is that, like most folks, they don't really understand how Collations work, but what they do know is that their application works (i.e. does not get Collation conflict errors) when the Instance and Database both have a Collation of SQL_Latin1_General_CP1_CI_AS, which is the Collation of their Instance and Database (that they develop the app on), because that is the default Collation when installing on an OS having English as its language.

I'm guessing that they have probably had some customers report problems that they didn't know how to fix, but narrowed it down to those Instances not having SQL_Latin1_General_CP1_CI_AS as the Instance / Server -level Collation. The Instance-level Collation controls not just tempdb meta-data (and default column Collation when no COLLATE keyword is specified when creating local or global temporary tables), which has been mentioned by others, but also name resolution for variables / parameters, cursors, and GOTO labels. Even if unlikely that they would be using GOTO statements, they are certainly using variables / parameters, and likely enough to be using cursors.

What this means is that they likely had problems in one or more of the following areas:

1. Collation conflict errors related to temporary tables:

   tempdb being in the Collation of the Instance does not always mean that there will be problems, even if the COLLATE keyword was never used in a CREATE TABLE #[#]... statement. Collation conflicts only occur when attempting to combine or compare two string columns. So assuming that they created a temporary table and used it in conjunction with a table in their Database, they would need to be JOINing on those string columns, or concatenating them, or combining them via UNION, or something along those lines. Under these circumstances, an error will occur if the Collations of the two columns are not identical.

2. Unexpected behavior:

   Comparing a string column of a table to a variable or parameter will use the Collation of the column. Given their requirement for you to use SQL_Latin1_General_CP1_CI_AS, this vendor is clearly expecting case-insensitive comparisons. Since string columns of temp tables (that were not created using the COLLATE keyword) take on the Collation of the Instance, if the Instance is using a binary or case-sensitive Collation, then their application will not be returning all of the data that they were expecting it to return.

3. Code compilation errors:

   Since the Instance-level Collation controls resolution of variable / parameter / cursor names, if they have inconsistent casing in any of their variable / parameter / cursor names, then errors will occur when attempting to execute the code. For example, doing this:

   DECLARE @CustomerID INT;
   SET @customerid = 5;
   

   would get the following error:

   Msg 137, Level 15, State 1, Line XXXXX
   Must declare the scalar variable "@customerid".

   Similarly, they would get:

   Msg 16916, Level 16, State 1, Line XXXXX
   A cursor with the name 'Customers' does not exist.

   if they did this:

   DECLARE customers CURSOR FOR SELECT 1 AS [Bob];
   OPEN Customers;
   

These problems are easy enough to avoid, simply by doing the following:

1. Specify the COLLATE keyword on string columns when creating temporary tables (local or global). Using COLLATE DATABASE_DEFAULT is handy if the Database itself is not guaranteed to have a particular Collation. But if the Collation of the Database is always the same, then you can specify either DATABASE_DEFAULT or the particular Collation. Though I suppose DATABASE_DEFAULT works in both cases, so maybe it's the easier choice.

2. Be consistent in casing of identifiers, especially variables / parameters. And to be more complete, I should mention that Instance-level meta-data is also affected by the Instance-level Collation (e.g. names of Logins, Databases, server-Roles, SQL Agent Jobs, SQL Agent Job Steps, etc). So being consistent with casing in all areas is the safest bet.

---

Am I being unfair in assuming that the vendor doesn't understand how Collations work? Well, according to a comment made by the O.P. on M.Ali's answer:

I got this reply from him: "It's the other way around, you need the new SQL instance collation to match the old SQL collation when attaching databases to it. The collation is used in the functioning of the database, not just something that gets set when it's created."

the answer is "no". There are two problems here:

1. No, the Collations of the source and destination Instances do not need to match when attaching a Database to a new Instance. In fact, you can even attach a system DB to an Instance that has a different Collation, thereby having a mismatch between the attached system DB and the Instance and the other system DBs.

2. It's unclear if "database" in that last sentence means actual Database or the Instance (sometimes people use the term "database" to refer to the RDBMS as a whole). If it means actual "Database", then that is entirely irrelevant because the issue at hand is the Instance-level Collation. But, if the vendor meant the Instance, then while true that the Collation is used in normal operations (as noted above), this only shows awareness of simple cause-effect relationship and not actual understanding. Actual understanding would lead to doing those simple fixes (noted above) such that the Instance-level Collation was a non-issue.

---

If needing to change the Collation of the Instance, please see:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

For more info on working with Collations / encodings / Unicode / etc, please visit:

Collations.Info

Answer 2

You can alter the database Collation even after you have created the database using the following query

USE master;
GO
ALTER DATABASE Database_Name
COLLATE Your_New_Collation;
GO

For more information on database collation Read here

Answer 3

Yes, you are able to set the collation at the database level. To do so, here is an example:

    USE master;
    GO
    ALTER DATABASE <DatabaseName>
    COLLATE SQL_Latin1_General_CP1_CI_AS;
    GO