April 25, 2014 at 2:45 pm
Hello all,
So I have to with this DB that was created in FRENCH_CI_AS while the tempdb is in SQL_Latin1_General_CP1_CI_AS.
This DB was not created only for my app, it's actually maintained by an Access application and VBA code. The person who created the tables used spaces in tables and column names so I need to use [] for everything in my SQL code. I don't think the collation has any relation to this but I say it just in case it does.
As far as data is concerned, it obviously contains french characters with accents (like é, à ê) and it's probably why the person who created the DB thought he had to make it FRENCH_CI_AS.
But this is quite a problem for me when dealing with temp tables in stored procedures. I keep getting collation errors in sql statements that compare columns and I am force to specify the collation on the select statements but that makes execution very slow. I actually saw while testing something today that an update query can go from milliseconds to over 4 seconds when collation is specified on the column names.
So I am wondering if changing the collation from FRENCH_CI_AS to SQL_Latin1_General_CP1_CI_AS on my database will create issues. Is it going to affect and/or change my data in any way ? And what about the Access application and its VBA code, will they start "acting" or is the collation transparent to them ?
April 26, 2014 at 8:13 am
If you change the database's default collation, then all that will happen is that new tables get the new collation by default. Nothing else changes.
If you want to change the collation of the existing data, you need to run an ALTER TABLE ... ALTER COLUMN on every single varchar/char column in the entire database.
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
April 26, 2014 at 8:36 am
Gail,
Thanks for answering my question. Having to change exsting columns is not a big issue, I can generate a script that will do that. But will this change the current data or the type of characters I can use in those columns ?
Will I lose the french accents and/or be unable to use them in new or updated records ?
April 26, 2014 at 11:11 am
You'll also need to drop any constraints on the columns before you alter them, see books online for more detail
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 26, 2014 at 12:38 pm
Perry Whittle (4/26/2014)
You'll also need to drop any constraints on the columns before you alter them, see books online for more detail
And possibly indexes as well.
As for data, offhand I don't know. Test out a column in a dev environment and see.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply