March 30, 2015 at 5:50 am
Gents,
Can someone shed a light on the following;
We are consolidating some old SQL server-environments from 'OLD' to 'NEW' and one of our vendors is protesting on behalve of the collation we use on our 'NEW' SQL server.
Our old server (SQL 2005) contains databases with collation SQL_Latin1_General_CP1_CI_AS
Our new server (2014) has the standard collation Latin1_General_CI_AS
Both collations have CI and AS
From experience I know different databases can reside next to eachother on the same Instance.
The only problem could be ('could be !!') the use of TempDB with a high volume of transaction to be executured in TempDB and choosing for Snapshot Isolation Level ....
The applicliation the databases belong to is very static, hardly updated, and questioned only several time per hour (so no TempDB issue I guess).
Can someone advice me using different databases using a different collation running on the same instance (or point me to some info on the www) .
Regards, Guus
March 30, 2015 at 9:11 pm
Multiple collations need not be an issue - it all depends on how the applications are written. SQL Server is happy to use as many collations as you can imagine.
In my experience, an issue arises when, for instance, temporary tables are created without specifying the collation of the columns. When you do not specify the collation for temporary tables, columns in that table get the same collation as Tempdb. If that is different to the columns in the user database AND you try to compare value (such as in a ON OR a WHERE condition), without specifying the collation to use for the comparison, then you get an error.
The following should demonstrate the issue...
CREATE TABLE dbo.TEST
(Column1 VARCHAR (10) Collate LATIN1_GENERAL_CI_AS)
go
CREATE TABLE #TEMP
(Column1 VARCHAR (10) Collate sql_latin1_general_cp1_ci_as)
SELECT * FROM test t1
inner join #Temp t2
on t1.Column1 = t2.Column1 /* collate LATIN1_GENERAL_CI_AS */
If you un-comment the last bit of the join condition, the query will run properly
March 31, 2015 at 2:19 am
Yes, as described by happycat59 SQL SERVER instance have multiple collations. You can also change the collation to your desire using "Alter database" command. But one should know what settings/collations they are using and why?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 31, 2015 at 6:03 am
Thanks for your reply !!
@Free_Macot
Whe you use the "ALTER DATABASE" the database will be altered but the content will not !! only the newly created objects will be created with the new collation !! (so be aware).
Guus
March 31, 2015 at 6:58 am
Yes, it will not change untill you do reindex!
Refer following article for further details:
http://sqlmag.com/database-performance-tuning/seven-step-process-changing-database-collation
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 31, 2015 at 7:34 am
I remember having a lot of problems when the default collation changed from 2005 to 2008.
Many of them were solved by using COLLATE DATABASE_DEFAULT. Just a hint, not more 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply