I recently came across an organization who had very interesting infrastructure setup. Their business domains is analytics. They process millions of the records and how many clients who have case sensitive tags on their server which they want to measure. However, there are many places they do not want to care about case sensitivity. They had peta bytes of the data on their server. Let us learn about Creating Database with Different Collation on Server. When I saw their servers – I noticed that every SQL Server had two instances installed – 1) Case Sensitive and 2) Case Insensitive. After a while I asked them why do they have two instances on the same server with almost similar data – one with case sensitive and another with case insensitive instances.
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)
When will I get this kind of exposure, no one calls me :(.
I want to be a consultant…. I have wait for few more years.
Good article Pinal…
Sr. DBA :-? Senior in what ??????
Sr. DBA – was senior in experience of years … or atleast his title said so!
I wasn’t sure, if he deserves Sr. DBA title … thats what I was referring to.
It’s impossible for a person to know everything, but I except Jr developer to know some basics.
One more good article added to my knowledge repository , Thanks Pinal
The data would still be duplicated across the 2 databases…does adding a seperate instance consume more resources than 2 db on the same instance?
According to me (Let me clear, I am not DBA),
2 instance of same server will consume more resources as they are creating different process for each running instance.
Good Article… When something is not apparent or you have not come across it – “Google It!”.
sir please me about something collation
If columns can have different collations, then index columns keys can also I suppose? And then are dual databases even necessary? I guess we’d have to see the usage, but it seems to me the same data in two columns of a table with different indexes on the two columns would serve the different orderings needed with one table of data!
So what do you do with the tempdb? In cases when I have had different collated databases in the same instance, those that didn’t match the tempdb had problems with comparisons and other operations that involve the tempdb. I had to add COLLATE specifics to the WHERE clauses. This is not always possible with vendor databases.
Can I change an existing database’s collation? (different from master DB)
Thanks Pinal…. I learn something new everytime I visit your site.
Can we store Chinese data in database with Finnish_Swedish collation?
Server collation is different and the database collation is different.
What will be the impact.
1. You might get errors about collation while running a query which joins across two different collation database.
2. Temp Table operation might cause problem.
Good article thanks for that. I would like to dig a little deeper on reddy’s question.
Temp table problem caused by instance and database collation differences – can that be solved by just changing collation of the SQL instance or do I have to move database to new instance with correct collation. Are there any other potential issues?
Sir, How can we join the tables of two different servers in sql
How to change collate name in master database
It can only be done by Rebuilding System Databases. https://blog.sqlauthority.com/2008/10/06/sqlauthority-news-upgrade-sql-server-with-sa-renamed-rebuild-system-databases-sql-server-2008/