Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 9 down vote accepted

Collation determines two things.

  1. The code page used (and hence characters that can be stored) for non unicode data.
  2. The comparison semantics for all textual data.

(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 |
+------+------+-------------------------+--------------+------------------+
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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