Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS?
I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. Should not be a problem?
Thank you very much.
Collation determines two things.
(NB: SQL Server 2012 also introduces _SC, supplementary character, collations for UTF-16 encoding rather than UCS-2 but these are not relevant to your question here)
SELECT *
FROM sys.fn_helpcollations()
WHERE name LIKE '%Slovak%CI%AS'
Returns the following information
name description
------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Slovak_CI_AS Slovak, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Slovak_100_CI_AS Slovak-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
SQL_Slovak_CP1250_CI_AS Slovak, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 94 on Code Page 1250 for non-Unicode Data
So on 2012 there are two windows collations and one SQL Collation. The description doesn't indicate the code page for the windows versions but that they are all the same can be seen from below.
CREATE TABLE #Collations
(
code TINYINT PRIMARY KEY,
SQL_Slovak_CP1250_CI_AS CHAR(1) COLLATE SQL_Slovak_CP1250_CI_AS,
Slovak_CI_AS CHAR(1) COLLATE Slovak_CI_AS,
Slovak_100_CI_AS CHAR(1) COLLATE Slovak_100_CI_AS,
/*Included just to show an example of a different code page*/
Latin1_General_CI_AS CHAR(1) COLLATE Latin1_General_CI_AS
);
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1), --2
E02(N) AS (SELECT 1 FROM E00 a, E00 b), --4
E04(N) AS (SELECT 1 FROM E02 a, E02 b), --16
E08(N) AS (SELECT 1 FROM E04 a, E04 b) --256
INSERT INTO #Collations
(code)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1
FROM E08
UPDATE #Collations
SET SQL_Slovak_CP1250_CI_AS = CAST(code AS BINARY(1)),
Slovak_CI_AS = CAST(code AS BINARY(1)),
Slovak_100_CI_AS = CAST(code AS BINARY(1)),
Latin1_General_CI_AS = CAST(code AS BINARY(1))
SELECT *
FROM #Collations
Or, alternatively, using the COLLATIONPROPERTY function
SELECT COLLATIONPROPERTY('SQL_Slovak_CP1250_CI_AS', 'CodePage') AS SQL_Slovak_CP1250_CI_AS,
COLLATIONPROPERTY('Slovak_CI_AS', 'CodePage') AS Slovak_CI_AS,
COLLATIONPROPERTY('Slovak_100_CI_AS', 'CodePage') AS Slovak_100_CI_AS,
COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage') AS Latin1_General_CI_AS
Returns
+-------------------------+--------------+------------------+----------------------+
| SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS | Latin1_General_CI_AS |
+-------------------------+--------------+------------------+----------------------+
| 1250 | 1250 | 1250 | 1252 |
+-------------------------+--------------+------------------+----------------------+
So as far as point 1 is concerned the collation makes no difference for SQL_Slovak_CP1250_CI_AS vs Slovak_CI_AS.
For point 2 see Comparing SQL collations to Windows collations. The Collation semantics section has some discussion about how the two differ but skipping ahead to Recommendations
SQL collations are provided for backward compatibility with earlier versions of SQL Server. Windows collations provide consistent string comparisons for both Unicode and for non-Unicode text in SQL Server that are also consistent with string comparisons in the Windows operating system. For all these reasons, Windows collations are preferred unless there are backward compatibility issues or specific performance issues that require a SQL collation.
So you should be using the Windows Collations anyway unless you have some specific backward compatibility need. Also I would use Slovak_100_CI_AS as this is more recent and avoids issues like this.
To see examples of specifics of how the comparison semantics differ between the three collations you can use the temporary table created earlier and try
SELECT code,
/*Exclude some characters that will mess up the formatting*/
CASE WHEN code IN (9,10,12,13) THEN '' ELSE SQL_Slovak_CP1250_CI_AS END AS [Char],
DENSE_RANK() OVER (ORDER BY SQL_Slovak_CP1250_CI_AS) AS SQL_Slovak_CP1250_CI_AS,
DENSE_RANK() OVER (ORDER BY Slovak_CI_AS) AS Slovak_CI_AS,
DENSE_RANK() OVER (ORDER BY Slovak_100_CI_AS) AS Slovak_100_CI_AS
FROM #Collations
ORDER BY SQL_Slovak_CP1250_CI_AS
That yields the following results
+------+------+-------------------------+--------------+------------------+
| code | Char | SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS |
+------+------+-------------------------+--------------+------------------+
| 0 | | 1 | 1 | 1 |
| 1 | | 2 | 2 | 2 |
| 2 | | 3 | 3 | 3 |
| 3 | | 4 | 4 | 4 |
| 4 | | 5 | 5 | 5 |
| 5 | | 6 | 6 | 6 |
| 6 | | 7 | 7 | 7 |
| 7 | | 8 | 8 | 8 |
| 8 | | 9 | 9 | 9 |
| 14 | | 10 | 10 | 10 |
| 15 | | 11 | 11 | 11 |
| 16 | | 12 | 12 | 12 |
| 17 | | 13 | 13 | 13 |
| 18 | | 14 | 14 | 14 |
| 19 | | 15 | 15 | 15 |
| 20 | | 16 | 16 | 16 |
| 21 | | 17 | 17 | 17 |
| 22 | | 18 | 18 | 18 |
| 23 | | 19 | 19 | 19 |
| 24 | | 20 | 20 | 20 |
| 25 | | 21 | 21 | 21 |
| 26 | | 22 | 22 | 22 |
| 27 | | 23 | 23 | 23 |
| 28 | | 24 | 24 | 24 |
| 29 | | 25 | 25 | 25 |
| 30 | | 26 | 26 | 26 |
| 31 | | 27 | 27 | 27 |
| 127 | | 28 | 28 | 28 |
| 129 | | 29 | 29 | 106 |
| 131 | | 30 | 30 | 107 |
| 136 | | 31 | 31 | 108 |
| 144 | | 32 | 32 | 109 |
| 152 | | 33 | 33 | 110 |
| 39 | ' | 34 | 34 | 29 |
| 45 | - | 35 | 35 | 30 |
| 173 | | 36 | 36 | 1 |
| 150 | – | 37 | 37 | 31 |
| 151 | — | 38 | 38 | 32 |
| 32 | | 39 | 1 | 1 |
| 160 | | 40 | 39 | 33 |
| 9 | | 41 | 40 | 34 |
| 10 | | 42 | 41 | 35 |
| 11 | | 43 | 42 | 36 |
| 12 | | 44 | 43 | 37 |
| 13 | | 45 | 44 | 38 |
| 33 | ! | 46 | 45 | 39 |
| 34 | " | 47 | 46 | 40 |
| 35 | # | 48 | 47 | 41 |
| 36 | $ | 49 | 48 | 42 |
| 37 | % | 50 | 49 | 43 |
| 38 | & | 51 | 50 | 44 |
| 40 | ( | 52 | 51 | 45 |
| 41 | ) | 53 | 52 | 46 |
| 42 | * | 54 | 53 | 47 |
| 44 | , | 55 | 54 | 48 |
| 46 | . | 56 | 55 | 49 |
| 47 | / | 57 | 56 | 50 |
| 58 | : | 58 | 57 | 51 |
| 59 | ; | 59 | 58 | 52 |
| 63 | ? | 60 | 59 | 53 |
| 64 | @ | 61 | 60 | 54 |
| 91 | [ | 62 | 61 | 55 |
| 92 | \ | 63 | 62 | 56 |
| 93 | ] | 64 | 63 | 57 |
| 94 | ^ | 65 | 64 | 58 |
| 95 | _ | 66 | 65 | 59 |
| 96 | ` | 67 | 66 | 60 |
| 123 | { | 68 | 67 | 61 |
| 124 | | | 69 | 68 | 62 |
| 125 | } | 70 | 69 | 63 |
| 126 | ~ | 71 | 70 | 64 |
| 166 | ¦ | 72 | 71 | 65 |
| 168 | ¨ | 73 | 72 | 66 |
| 180 | ´ | 74 | 73 | 67 |
| 184 | ¸ | 75 | 74 | 68 |
| 161 | ˇ | 76 | 75 | 69 |
| 162 | ˘ | 77 | 76 | 70 |
| 255 | ˙ | 78 | 77 | 71 |
| 178 | ˛ | 79 | 78 | 72 |
| 189 | ˝ | 80 | 79 | 73 |
| 145 | ‘ | 81 | 80 | 74 |
| 146 | ’ | 82 | 81 | 75 |
| 130 | ‚ | 83 | 82 | 76 |
| 147 | “ | 84 | 83 | 77 |
| 148 | ” | 85 | 84 | 78 |
| 132 | „ | 86 | 85 | 79 |
| 139 | ‹ | 87 | 86 | 80 |
| 155 | › | 88 | 87 | 81 |
| 43 | + | 89 | 88 | 84 |
| 60 | < | 90 | 89 | 85 |
| 61 | = | 91 | 90 | 86 |
| 62 | > | 92 | 91 | 87 |
| 177 | ± | 93 | 92 | 88 |
| 171 | « | 94 | 93 | 89 |
| 187 | » | 95 | 94 | 90 |
| 215 | × | 96 | 95 | 91 |
| 247 | ÷ | 97 | 96 | 92 |
| 164 | ¤ | 98 | 97 | 82 |
| 167 | § | 99 | 98 | 93 |
| 169 | © | 100 | 99 | 94 |
| 172 | ¬ | 101 | 100 | 95 |
| 174 | ® | 102 | 101 | 96 |
| 176 | ° | 103 | 102 | 97 |
| 181 | µ | 104 | 103 | 98 |
| 182 | ¶ | 105 | 104 | 99 |
| 183 | · | 106 | 105 | 100 |
| 134 | † | 107 | 106 | 102 |
| 135 | ‡ | 108 | 107 | 103 |
| 149 | • | 109 | 108 | 104 |
| 133 | … | 110 | 109 | 101 |
| 137 | ‰ | 111 | 110 | 105 |
| 128 | € | 112 | 111 | 83 |
| 48 | 0 | 113 | 112 | 111 |
| 49 | 1 | 114 | 113 | 112 |
| 50 | 2 | 115 | 114 | 113 |
| 51 | 3 | 116 | 115 | 114 |
| 52 | 4 | 117 | 116 | 115 |
| 53 | 5 | 118 | 117 | 116 |
| 54 | 6 | 119 | 118 | 117 |
| 55 | 7 | 120 | 119 | 118 |
| 56 | 8 | 121 | 120 | 119 |
| 57 | 9 | 122 | 121 | 120 |
| 97 | a | 123 | 122 | 121 |
| 65 | A | 123 | 122 | 121 |
| 193 | Á | 124 | 123 | 122 |
| 225 | á | 124 | 123 | 122 |
| 226 | â | 125 | 124 | 123 |
| 194 | Â | 125 | 124 | 123 |
| 195 | Ă | 126 | 125 | 124 |
| 227 | ă | 126 | 125 | 124 |
| 185 | ą | 127 | 126 | 125 |
| 165 | Ą | 127 | 126 | 125 |
| 196 | Ä | 128 | 127 | 126 |
| 228 | ä | 128 | 127 | 126 |
| 66 | B | 129 | 128 | 127 |
| 98 | b | 129 | 128 | 127 |
| 99 | c | 130 | 129 | 128 |
| 67 | C | 130 | 129 | 128 |
| 230 | ć | 131 | 130 | 129 |
| 198 | Ć | 131 | 130 | 129 |
| 199 | Ç | 132 | 131 | 130 |
| 231 | ç | 132 | 131 | 130 |
| 232 | č | 133 | 132 | 131 |
| 200 | Č | 133 | 132 | 131 |
| 68 | D | 134 | 133 | 132 |
| 100 | d | 134 | 133 | 132 |
| 239 | ď | 135 | 134 | 133 |
| 207 | Ď | 135 | 134 | 133 |
| 208 | Đ | 136 | 135 | 134 |
| 240 | đ | 136 | 135 | 134 |
| 101 | e | 137 | 136 | 135 |
| 69 | E | 137 | 136 | 135 |
| 235 | ë | 138 | 137 | 136 |
| 203 | Ë | 138 | 137 | 136 |
| 201 | É | 139 | 138 | 137 |
| 233 | é | 139 | 138 | 137 |
| 236 | ě | 140 | 139 | 138 |
| 204 | Ě | 140 | 139 | 138 |
| 202 | Ę | 141 | 140 | 139 |
| 234 | ę | 141 | 140 | 139 |
| 70 | F | 142 | 141 | 140 |
| 102 | f | 142 | 141 | 140 |
| 103 | g | 143 | 142 | 141 |
| 71 | G | 143 | 142 | 141 |
| 72 | H | 144 | 143 | 142 |
| 104 | h | 144 | 143 | 142 |
| 105 | i | 145 | 144 | 143 |
| 73 | I | 145 | 144 | 143 |
| 237 | í | 146 | 145 | 144 |
| 205 | Í | 146 | 145 | 144 |
| 206 | Î | 147 | 146 | 145 |
| 238 | î | 147 | 146 | 145 |
| 74 | J | 148 | 147 | 146 |
| 106 | j | 148 | 147 | 146 |
| 107 | k | 149 | 148 | 147 |
| 75 | K | 149 | 148 | 147 |
| 76 | L | 150 | 149 | 148 |
| 108 | l | 150 | 149 | 148 |
| 229 | ĺ | 151 | 150 | 149 |
| 197 | Ĺ | 151 | 150 | 149 |
| 188 | Ľ | 152 | 151 | 150 |
| 190 | ľ | 152 | 151 | 150 |
| 179 | ł | 153 | 152 | 151 |
| 163 | Ł | 153 | 152 | 151 |
| 77 | M | 154 | 153 | 152 |
| 109 | m | 154 | 153 | 152 |
| 110 | n | 155 | 154 | 153 |
| 78 | N | 155 | 154 | 153 |
| 209 | Ń | 156 | 155 | 154 |
| 241 | ń | 156 | 155 | 154 |
| 242 | ň | 157 | 156 | 155 |
| 210 | Ň | 157 | 156 | 155 |
| 79 | O | 158 | 157 | 156 |
| 111 | o | 158 | 157 | 156 |
| 214 | Ö | 159 | 158 | 157 |
| 246 | ö | 159 | 158 | 157 |
| 211 | Ó | 160 | 159 | 158 |
| 243 | ó | 160 | 159 | 158 |
| 213 | Ő | 161 | 160 | 159 |
| 245 | ő | 161 | 160 | 159 |
| 212 | Ô | 162 | 161 | 160 |
| 244 | ô | 162 | 161 | 160 |
| 112 | p | 163 | 162 | 161 |
| 80 | P | 163 | 162 | 161 |
| 81 | Q | 164 | 163 | 162 |
| 113 | q | 164 | 163 | 162 |
| 114 | r | 165 | 164 | 163 |
| 82 | R | 165 | 164 | 163 |
| 192 | Ŕ | 166 | 165 | 164 |
| 224 | ŕ | 166 | 165 | 164 |
| 248 | ř | 167 | 166 | 165 |
| 216 | Ř | 167 | 166 | 165 |
| 83 | S | 168 | 167 | 166 |
| 115 | s | 168 | 167 | 166 |
| 156 | ś | 169 | 168 | 167 |
| 140 | Ś | 169 | 168 | 167 |
| 170 | Ş | 170 | 169 | 168 |
| 186 | ş | 170 | 169 | 168 |
| 223 | ß | 171 | 170 | 169 |
| 138 | Š | 172 | 171 | 170 |
| 154 | š | 172 | 171 | 170 |
| 84 | T | 173 | 172 | 171 |
| 116 | t | 173 | 172 | 171 |
| 157 | ť | 174 | 173 | 172 |
| 141 | Ť | 174 | 173 | 172 |
| 254 | ţ | 175 | 174 | 173 |
| 222 | Ţ | 175 | 174 | 173 |
| 153 | ™ | 176 | 175 | 174 |
| 85 | U | 177 | 176 | 175 |
| 117 | u | 177 | 176 | 175 |
| 220 | Ü | 178 | 177 | 176 |
| 252 | ü | 178 | 177 | 176 |
| 250 | ú | 179 | 178 | 177 |
| 218 | Ú | 179 | 178 | 177 |
| 217 | Ů | 180 | 179 | 178 |
| 249 | ů | 180 | 179 | 178 |
| 251 | ű | 181 | 180 | 179 |
| 219 | Ű | 181 | 180 | 179 |
| 118 | v | 182 | 181 | 180 |
| 86 | V | 182 | 181 | 180 |
| 87 | W | 183 | 182 | 181 |
| 119 | w | 183 | 182 | 181 |
| 120 | x | 184 | 183 | 182 |
| 88 | X | 184 | 183 | 182 |
| 89 | Y | 185 | 184 | 183 |
| 121 | y | 185 | 184 | 183 |
| 221 | Ý | 186 | 185 | 184 |
| 253 | ý | 186 | 185 | 184 |
| 122 | z | 187 | 186 | 185 |
| 90 | Z | 187 | 186 | 185 |
| 191 | ż | 188 | 187 | 186 |
| 175 | Ż | 188 | 187 | 186 |
| 159 | ź | 189 | 188 | 187 |
| 143 | Ź | 189 | 188 | 187 |
| 142 | Ž | 190 | 189 | 188 |
| 158 | ž | 190 | 189 | 188 |
+------+------+-------------------------+--------------+------------------+
