6

I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.

So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:

Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
 foreach-object 
    { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }

Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).

Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.

2 Answers 2

8

Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation

Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.

Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:

Collation error in Jira when the database and server/default collation do not match in SQL Server

Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:

The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.

whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).

My advice: don't change anything! You even said:

a database that has been in production for a few years

If it has been in production for years without running into problems, then there are likely no problems :-)

3
  • @TonyHinkle can you please confirm what your instance-level collation is?..i am curious if you were about to change the Jira DB to be the same as the instance-level collation or different from it. I suspect different because the collation that the DB currently is just happens to be the default that most people go with. Mar 2, 2019 at 0:48
  • @SolomonRutzky The collation on the instance is SQL_Latin1_General_CP1_CI_AS Mar 2, 2019 at 12:23
  • @TonyHinkle, my environment uses the SQL_Latin1_General_CP437_CI_AI on only the jira databases and that has worked fine for years even as we have added thousands of employees. So really, unless something is broke, Solomon's advice is sage. If you need to, see How to fix the collation of a Microsoft SQL Server Confluence database and adjust for the collation you are using.
    – clifton_h
    Mar 2, 2019 at 15:45
4

This is too long for a comment but I want to call out the potential for data loss in char/varchar columns when changing the code page. Data may be mapped to a different character than the source code page or a '?' when no character exists at all. Importantly, no errors will be reported during the migration if data loss occurs.

This script shows the mapping for non-ASCII characters going from the 1252 to 437 code page:

WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 127 AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,CP1252_Characters AS (
        SELECT
              num AS CodePoint
            , CHAR(num) COLLATE SQL_Latin1_General_CP1_CI_AS AS CP1252_Character
        FROM t1k
        WHERE num < 256
    )
SELECT
      CP1252_Characters.CodePoint AS CP1252_CodePoint
    , CP1252_Characters.CP1252_Character
    , CP1252_Characters.CP1252_Character COLLATE SQL_Latin1_General_CP437_CI_AI AS Converted_CP457Character
FROM CP1252_Characters;

Note the absence of the Euro symbol in the 437 code page, for example. I hope you didn't have any Jira tickets containing this currency symbol :-)

+------------------+------------------+--------------------------+
| CP1252_CodePoint | CP1252_Character | Converted_CP457Character |
+------------------+------------------+--------------------------+
|              128 | €                | ?                        |
|              129 |                 | ?                        |
|              130 | ‚                | ,                        |
|              131 | ƒ                | ƒ                        |
|              132 | „                | ,                        |
|              133 | …                | .                        |
|              134 | †                | +                        |
|              135 | ‡                | ╪                        |
|              136 | ˆ                | ^                        |
|              137 | ‰                | %                        |
|              138 | Š                | S                        |
|              139 | ‹                | <                        |
|              140 | Π               | O                        |
|              141 |                 | ?                        |
|              142 | Ž                | Z                        |
|              143 |                 | ?                        |
|              144 |                 | ?                        |
|              145 | ‘                | `                        |
|              146 | ’                | '                        |
|              147 | “                | "                        |
|              148 | ”                | "                        |
|              149 | •                |                         |
|              150 | –                | -                        |
|              151 | —                | -                        |
|              152 | ˜                | ~                        |
|              153 | ™                | T                        |
|              154 | š                | s                        |
|              155 | ›                | >                        |
|              156 | œ                | o                        |
|              157 |                 | ?                        |
|              158 | ž                | z                        |
|              159 | Ÿ                | Y                        |
|              160 |                  |                          |
|              161 | ¡                | ¡                        |
|              162 | ¢                | ¢                        |
|              163 | £                | £                        |
|              164 | ¤                |                         |
|              165 | ¥                | ¥                        |
|              166 | ¦                | ▌                        |
|              167 | §                |                         |
|              168 | ¨                | "                        |
|              169 | ©                | c                        |
|              170 | ª                | ª                        |
|              171 | «                | «                        |
|              172 | ¬                | ¬                        |
|              173 | ­                | -                        |
|              174 | ®                | r                        |
|              175 | ¯                | _                        |
|              176 | °                | °                        |
|              177 | ±                | ±                        |
|              178 | ²                | ²                        |
|              179 | ³                | 3                        |
|              180 | ´                | '                        |
|              181 | µ                | µ                        |
|              182 | ¶                |                         |
|              183 | ·                | ·                        |
|              184 | ¸                | ,                        |
|              185 | ¹                | 1                        |
|              186 | º                | º                        |
|              187 | »                | »                        |
|              188 | ¼                | ¼                        |
|              189 | ½                | ½                        |
|              190 | ¾                | _                        |
|              191 | ¿                | ¿                        |
|              192 | À                | A                        |
|              193 | Á                | A                        |
|              194 | Â                | A                        |
|              195 | Ã                | A                        |
|              196 | Ä                | Ä                        |
|              197 | Å                | Å                        |
|              198 | Æ                | Æ                        |
|              199 | Ç                | Ç                        |
|              200 | È                | E                        |
|              201 | É                | É                        |
|              202 | Ê                | E                        |
|              203 | Ë                | E                        |
|              204 | Ì                | I                        |
|              205 | Í                | I                        |
|              206 | Î                | I                        |
|              207 | Ï                | I                        |
|              208 | Ð                | D                        |
|              209 | Ñ                | Ñ                        |
|              210 | Ò                | O                        |
|              211 | Ó                | O                        |
|              212 | Ô                | O                        |
|              213 | Õ                | O                        |
|              214 | Ö                | Ö                        |
|              215 | ×                | x                        |
|              216 | Ø                | O                        |
|              217 | Ù                | U                        |
|              218 | Ú                | U                        |
|              219 | Û                | U                        |
|              220 | Ü                | Ü                        |
|              221 | Ý                | Y                        |
|              222 | Þ                | _                        |
|              223 | ß                | ß                        |
|              224 | à                | à                        |
|              225 | á                | á                        |
|              226 | â                | â                        |
|              227 | ã                | a                        |
|              228 | ä                | ä                        |
|              229 | å                | å                        |
|              230 | æ                | æ                        |
|              231 | ç                | ç                        |
|              232 | è                | è                        |
|              233 | é                | é                        |
|              234 | ê                | ê                        |
|              235 | ë                | ë                        |
|              236 | ì                | ì                        |
|              237 | í                | í                        |
|              238 | î                | î                        |
|              239 | ï                | ï                        |
|              240 | ð                | d                        |
|              241 | ñ                | ñ                        |
|              242 | ò                | ò                        |
|              243 | ó                | ó                        |
|              244 | ô                | ô                        |
|              245 | õ                | o                        |
|              246 | ö                | ö                        |
|              247 | ÷                | ÷                        |
|              248 | ø                | o                        |
|              249 | ù                | ù                        |
|              250 | ú                | ú                        |
|              251 | û                | û                        |
|              252 | ü                | ü                        |
|              253 | ý                | y                        |
|              254 | þ                | _                        |
|              255 | ÿ                | ÿ                        |
+------------------+------------------+--------------------------+
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.