January 20, 2014 at 4:23 am
Hi all
I've got a lot of databases that have been created using different collations and it's causing a few issues (one of them being having to force collation on the fly in the FROM clause or the tables won't join).
I can't seem to find anything on the web to say what would happen if I change the collation on them (or even if it's possible).
Does anyone know:-
1) if it's possible to change the collation?
2) what would happen if I did (assuming I can)?
January 20, 2014 at 4:48 am
Yes you can change it, it's a tonne of work. You'd have to run an ALTER TABLE ... ALTER COLUMN on every single char and varchar column in the database.
Doing so could have anything from no effects to changing sort order to causing errors in the apps. Test carefully before you change in production.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 4:52 am
Thanks Gail
This is a production environment which is why I'm asking before I do (or even suggest) anything.
I've just double checked a few databases, tables and columns and come across something odd.
All the databases appear to be the same (Latin1_General_CI_AS collations) and the coulmns I'm joing on are also the same.
If I try to run a query with a "normal" join, SSMS gives me an error about collation.
If I add the following after the ON section of the join:-
COLLATE SQL_Latin1_General_CP1_CS_AS
everything runs fine.
Any ideas why that would be?
January 20, 2014 at 5:21 am
If you're getting a collation error, you have two different collations. Check the columns, the database setting just defines the defaults
As for changing... Test on a non-production environment first! You wouldn't change columns from numeric to int without testing (I hope), same applies here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 6:02 am
That's what I thought, but I can't see where (not even at column level).
I'll have another look and probably come back later.
I have done that before :sick:, not doing it again!
January 20, 2014 at 6:25 am
I've checked all the relevant columns for the queries I use and they are all (including tables and databases) are on the same collation (Latin1_General_CI_AS).
Is there any way of listing the collation for each database and object so I can spot any that are different?
January 20, 2014 at 6:46 am
Collations aren't per database or object. The collation on the database is just the default for new columns, there's no collation on an object. Columns are what have collation defined on them. Query sys.columns, it should have a column specifying the collation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 6:52 am
So I've just found out.
If I viewed the extended properties for the relevant columns, I get this:-
Latin1_General_CI_AS
The General proeprties page shows me:-
SQL_Latin1_General_CP1_CI_AS
Looks like I do need to alter the collation level on this table (very carefully!).
I know I'll have to run some ALTER COLUMN SQL on this table, but what's the best way to go about it?
::edit::
Oh yeah, and what can I expect in the way of pitfalls?
I really don't want to walk into this blind.
January 20, 2014 at 6:55 am
What do you mean 'best way'?
To change collation, you need to run an ALTER TABLE ... ALTER COLUMN on the columns you're changing.
Test in a non-production environment first, make sure it doesn't break the application, some apps may expect a specific collation (sharepoint for example)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 7:06 am
I found this script:-
DECLARE @collation NVARCHAR(64)
SET @collation = 'Latin1_General_CI_AS'
SELECT
'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] '
+ 'ALTER COLUMN [' + COLUMN_NAME + '] '
+ DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') '
+ 'COLLATE ' + @collation + ' '
+ CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
FROM INFORMATION_SCHEMA.columns
WHERE COLLATION_NAME IS NOT NULL
AND COLLATION_NAME <> @collation
which appears to do what I want but I can't see how it looks at all the columns (although at a second look it seems to generate the scripts to make the changes, rather than making the changes directly).
Will it work?
In your experience, is it a change you would make?
January 20, 2014 at 7:21 am
richardmgreen1 (1/20/2014)
In your experience, is it a change you would make?
No. Not unless I had an exceptionally good reason.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 7:35 am
I found this script...
It's not even as simple as that, I'm afraid. Where you have primary or foreign key constraints on any of the columns you're changing, they need to be dropped first and recreated afterwards. And it all needs to be done in the correct order. You can download utilities to do this for you. One that works for me runs from Visual Studio. You run it and specify the server, database and new collation and it either implements the changes for you or generates a script for you to inspect before running it. I recommend the latter. I can't remember where I downloaded that from, and I can't find the author's name in the code, but you can try searching for it. As Gail mentioned, test first before going anywhere near a live server with it.
John
January 20, 2014 at 7:42 am
GilaMonster (1/20/2014)
richardmgreen1 (1/20/2014)
In your experience, is it a change you would make?No. Not unless I had an exceptionally good reason.
If you're getting collation conflict errors, you have three options:
(1) Insert COLLATE clauses throughout your code
(2) Rebuild your master database or reinstall SQLServer (assuming the conflict is with tempdb or another system database)
(3) Change the collation of every column in your database
All of the above involve a fair amount of pain - you just have to determine which one involves the least!
John
January 20, 2014 at 7:59 am
GilaMonster (1/20/2014)
richardmgreen1 (1/20/2014)
In your experience, is it a change you would make?No. Not unless I had an exceptionally good reason.
In that case, I'm going to leave well enough alone.
January 20, 2014 at 8:01 am
John Mitchell-245523 (1/20/2014)
GilaMonster (1/20/2014)
richardmgreen1 (1/20/2014)
In your experience, is it a change you would make?No. Not unless I had an exceptionally good reason.
If you're getting collation conflict errors, you have three options:
(1) Insert COLLATE clauses throughout your code
(2) Rebuild your master database or reinstall SQLServer (assuming the conflict is with tempdb or another system database)
(3) Change the collation of every column in your database
All of the above involve a fair amount of pain - you just have to determine which one involves the least!
John
I'm going to go with option 1.
I had thought it would improve some of the query times (in the long run.
That, and I'm a stickler for uniformity (if only in databases!)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply