The answer I received was very interesting – they wanted both the data processed individually even though they are separate and in that they have two different instances on the same server. The organization was dealing with hundreds of the servers and instances, I could not digest that they have two similar instances. When I asked if they ever considered, to create two separate database on the same instances instead of creating a new instance for just case sensitivity. The answer was something I did not expect at all. Here is what their Sr. DBA answered.
“Can we create a database with different collations on the same SQL Server Instance? We thought, the collation of the server is fixed during the installation and all the database on the system have to follow the same default collation.”
I took a deep breath and in their development database I ran following commands.
-- Create Case Sensitive Database CREATE DATABASE CaseSensitive COLLATE SQL_Latin1_General_CP1_CS_AS GO USE CaseSensitive GO SELECT * FROM sys.types GO -- Create Case In-Sensitive Database CREATE DATABASE CaseInSensitive COLLATE SQL_Latin1_General_CP1_CI_AS GO USE CaseInSensitive GO SELECT * FROM sys.types GO
The above command will create two different databases with Case-Sensitive and Case -Insensitive collations. When we retrieved the default datatypes from the database we can see that they have different collation for their data types. It is absolutely possible to database with a different collation on a same SQL Server Instance. It is also possible to create an individual column in a table with different collations from server instance and database as well.
Collation can be set at SQL Server instance Level, Database Level as well as Table Column Level.
Reference: Pinal Dave (https://blog.sqlauthority.com)