主页 / dba / 问题 / 31860
Accepted
ms_devel
Asked: 2013-01-14 01:16:55 +0800 CST

sql_slovak_cp1250_ci_as 与 Slovak_CI_AS

  • 772

sql_slovak_cp1250_ci_as有人可以向我解释 collat​​ion和 collat​​ion之间有什么区别Slovak_CI_AS吗？

我有一个数据库设置为sql_slovak_cp1250_ci_as和共享主机只支持Slovak_CI_AS。应该没有问题吧？

非常感谢。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    排序规则决定了两件事。

    1. 用于非 Unicode 数据的代码页（以及因此可以存储的字符）。
    2. 所有文本数据的比较语义。

    （注意：SQL Server 2012 还引入了_SC, supplementary character , collat​​ions for UTF-16 encoding 而不是 UCS-2 但这些与您的问题无关）

    SELECT *
FROM   sys.fn_helpcollations()
WHERE  name LIKE '%Slovak%CI%AS'

    返回以下信息

    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

    所以在 2012 年有两个 windows 排序规则和一个 SQL 排序规则。该描述并未指示 Windows 版本的代码页，但它们都是相同的，可以从下面看到。

    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

    或者，或者，使用该COLLATIONPROPERTY功能

    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

    退货

    +-------------------------+--------------+------------------+----------------------+
| SQL_Slovak_CP1250_CI_AS | Slovak_CI_AS | Slovak_100_CI_AS | Latin1_General_CI_AS |
+-------------------------+--------------+------------------+----------------------+
|                    1250 |         1250 |             1250 |                 1252 |
+-------------------------+--------------+------------------+----------------------+

    因此，就第 1 点而言，排序规则对SQL_Slovak_CP1250_CI_ASvs没有任何影响Slovak_CI_AS。

    对于第 2 点，请参阅比较 SQL 排序规则与 Windows 排序规则。Collat ​​ion semantics部分讨论了两者的不同之处，但直接跳到Recommendations

    提供 SQL 排序规则是为了向后兼容早期版本的 SQL Server。Windows 排序规则为 SQL Server 中的 Unicode 和非 Unicode 文本提供一致的字符串比较，这也与 Windows 操作系统中的字符串比较一致。由于所有这些原因，Windows 排序规则是首选，除非存在向后兼容性问题或需要 SQL 排序规则的特定性能问题。

    因此，除非您有某些特定的向后兼容性需求，否则无论如何您都应该使用 Windows 排序规则。我也将使用Slovak_100_CI_AS它，因为它是较新的，并且可以避免这样的问题。

    要查看三个排序规则之间比较语义如何不同的具体示例，您可以使用之前创建的临时表并尝试

    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

    这会产生以下结果

    +------+------+-------------------------+--------------+------------------+
| 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 |
+------+------+-------------------------+--------------+------------------+
    • 13

相关问题