“COLLATE SQL_Latin1_General_CP1_CI_AS”是什么作用？

Question

我有一个SQL查询用于在SQLServer中创建数据库，如下所示：

create database yourdb
on
( name = 'yourdb_dat',
  filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf',
  size = 25mb,
  maxsize = 1500mb,
  filegrowth = 10mb )
log on
( name = 'yourdb_log',
  filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf',
  size = 7mb,
  maxsize = 375mb,
  filegrowth = 10mb )
COLLATE SQL_Latin1_General_CP1_CI_AS;
go

这段代码可以正常运行。

虽然SQL的其余部分很清楚，但我对COLLATE SQL_Latin1_General_CP1_CI_AS的功能感到相当困惑。

有人能给我解释一下吗？另外，我想知道以这种方式创建数据库是否是最佳实践？

- Thunder

Answer 1

它设置了数据库服务器如何排序（比较文本片段）。在这种情况下：

SQL_Latin1_General_CP1_CI_AS

分解成有趣的部分：

1. latin1 使服务器使用字符集 latin1 （基本等同于 ASCII）处理字符串
2. CP1 代表代码页 1252
3. CI 表示不区分大小写的比较，因此 'ABC' 等于 'abc'
4. AS 区分重音符号，所以 'ü' 不等于 'u'

附加说明: 要获取更详细的信息，请务必阅读 @solomon-rutzky 的回答。

- Kris

---

这与 SQL_Latin1_General_CI_AS 有什么区别？具体来说，CP1 让我感到好奇。 - Kad

@Kad：似乎没有 SQL_Latin1_General_CI_AS 这个排序规则，但确实有 Latin1_General_CI_AS。请参见 SELECT * FROM fn_helpcollations() where name IN ('SQL_Latin1_General_CP1_CI_AS','Latin1_General_CI_AS','SQL_Latin1_General_CI_AS');。这两种排序规则在排序和比较方面存在微妙的差异。请查看 http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as。 - Riley Major

@Kad: CP1代表代码页1252。代码页是一个查找表，用于将十六进制值映射到字符集中的特定字符。在微软子文化中，CP1是CP1252的简称。Windows是唯一一种本土使用CP1252的平台，因为它是从DOS时代延续下来的。虽然它非常类似于ISO 8859-1，但它们并不相同。在映射字符方面存在差异，例如欧元符号和其他一些不在ISO 8859-1中的字符。 - slartibartfast

@RileyMajor 存档链接，因原链接已失效 - Arno Hilke

@Chanky 根据您所说的“alternative”的确切含义，您可能正在寻找 Latin1_General_100_CI_AS_SC_UTF8 排序规则。请参阅我关于 UTF-8 排序规则的帖子：SQL Server 2019 中的本地 UTF-8 支持：救世主还是伪先知？。 - Solomon Rutzky

Answer 2

请注意，已接受的答案有些不完整。是的，在最基本的水平上，排序Collation可以处理。但是，所选Collation定义的比较规则在许多地方使用，而不仅仅是用户查询用户数据。

如果“COLLATE SQL_Latin1_General_CP1_CI_AS是什么？”的意思是“CREATE DATABASE语句中的COLLATE子句是做什么的？”，那么：

CREATE DATABASE语句中的COLLATE {collation_name}子句指定了数据库的默认Collation，并且不是服务器；数据库级别和服务器级别的默认Collations控制不同的内容。

服务器（即实例）级别控制：

-系统数据库的数据库级别Collation：master，model，msdb和tempdb。 -由于控制了tempdb的DB级别Collation，因此它是全局和本地临时表字符串列的默认Collation，但不包括表变量。 -由于控制了master的DB级别Collation，因此它是用于服务器级别数据的Collation，例如数据库名称（即sys.databases中的name列）、登录名等。 -处理参数/变量名称 -处理游标名称 -处理GOTO标签 -缺少COLLATE子句时新创建的数据库使用的默认Collation

数据库级别控制：

• 新建字符串列(CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, 和 NTEXT -- 但不包括 TEXT 或 NTEXT)时，COLLATE 子句未在列定义中出现时使用的默认排序规则。适用于 CREATE TABLE 和 ALTER TABLE ... ADD 语句。
• 字符串字面量（例如 'some text'）和字符串变量（例如 @StringVariable）使用的默认排序规则。这个排序规则仅在将字符串和变量与其他字符串和变量进行比较时使用。当将字符串/变量与列进行比较时，列的排序规则将被使用。
• 数据库级别元数据所使用的排序规则，例如对象名称（即 sys.objects），列名称（即 sys.columns），索引名称（即 sys.indexes）等。
• 数据库级别对象所使用的排序规则：表、列、索引等。

此外：

• ASCII 是一种使用8位编码（常用；严格来说，“ASCII”是指字符值为0-127的7位编码，“ASCII扩展”是指字符值为0-255的8位编码）。这个字符组在不同文化中是相同的。
• 代码页是扩展ASCII的“扩展”部分，控制值为128-255的字符使用哪些值。这个字符组在每个文化中都是不同的。
• Latin1 并不意味着 “ASCII”，因为标准ASCII只覆盖了0-127的值，而所有的代码页（可以在 SQL Server 中表示，甚至 NVARCHAR）都将这128个值映射到相同的字符上。

如果 "What does COLLATE SQL_Latin1_General_CP1_CI_AS do?" 的意思是 "这种排序规则具体做什么？"，那么：

• 由于名称以 SQL_ 开头，所以这是一个 SQL Server 排序规则，而不是 Windows 排序规则。虽然它们没有正式弃用，但已经过时，并主要用于与 SQL Server 2000 兼容。不幸的是，在使用美国英语作为语言的操作系统上安装时，SQL_Latin1_General_CP1_CI_AS 是默认排序规则，因此非常常见。如果可能的话，应避免使用这些排序规则。

Windows排序规则（名称不以SQL_开头的）是较新、更加功能强大的，对于相同值在VARCHAR和NVARCHAR之间进行排序时具有一致性，并且正在更新其他/修正排序权重和大写/小写映射。这些排序规则也没有 SQL Server 排序规则潜在的性能问题：混合使用 VARCHAR 和 NVARCHAR 类型时索引的影响。

Latin1_General 是区域设置/语言环境。

• 对于 NCHAR、NVARCHAR 和 NTEXT 数据，它决定了用于排序和比较的语言规则。
• 对于 CHAR、VARCHAR 和 TEXT 数据（列、文字和变量），它决定了：
  • 用于排序和比较的语言规则。
  • 用于编码字符的代码页。例如，Latin1_General 排序规则使用代码页 1252，Hebrew 排序规则使用代码页 1255，依此类推。

CP{代码页} 或 {版本号}

• 对于SQL Server排序规则： CP{代码页} 是决定哪些字符映射到值 128-255 的8位代码页。虽然有四个双字节字符集（DBCS）的代码页可以使用2个字节组合来创建超过256个字符，但这些代码页对于SQL Server排序规则是不可用的。

• 对于Windows排序规则： {版本号}，尽管不是所有排序规则名称中都有，但它指的是引入该排序规则的 SQL Server 版本（大部分情况下）。没有版本号的Windows排序规则是版本80（表示 SQL Server 2000，因为它是8.0版本）。并非所有版本的SQL Server都配备了新的排序规则，因此版本号中存在间隙。有一些是90（适用于SQL Server 2005，即版本9.0），大多数是100（适用于SQL Server 2008，版本10.0），而一小部分是140（适用于SQL Server 2017，版本14.0）。

  我说“在很大程度上”的原因是，以_SC结尾的排序规则是在SQL Server 2012（版本11.0）中引入的，但底层数据并不新，它们只是为内置函数添加了支持辅助字符的功能。因此，这些结尾存在于版本90和100的排序规则中，但仅从SQL Server 2012开始。

接下来是灵敏度，可以是以下任意组合，但始终按此顺序指定：

• CS = 区分大小写或CI = 不区分大小写
• AS = 区分重音符号或AI = 不区分重音符号
• KS = 区分假名类型或缺失=不区分假名类型
• WS = 区分宽度或缺失=不区分宽度
• VSS = 变异选择器敏感（仅适用于版本140排序规则）或缺失=不敏感变异选择器

可选的最后一部分：

• _SC结尾表示“支持补充字符”。 “支持”仅影响内置函数如何解释代理对（这是补充字符在UTF-16中的编码方式）。没有结尾_SC（或中间的_140_），内置函数不会看到单个补充字符，而是会看到两个组成代理对的无意义代码点。此结尾可添加到任何非二进制、版本90或100排序规则中。
• _BIN或_BIN2结尾表示“二进制”排序和比较。数据仍然以相同的方式存储，但没有语言规则。此结尾永远不与5种灵敏度或_SC组合使用。_BIN是旧样式，而_BIN2是更新、更准确的样式。如果使用SQL Server 2005或更新版本，请使用_BIN2。有关_BIN和_BIN2之间差异的详细信息，请参见：Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2)。

• _UTF8是SQL Server 2019中的新选项。它是一种8位编码，允许在VARCHAR和CHAR数据类型（但不允许在已弃用的TEXT数据类型中）存储Unicode数据。此选项只能用于支持补充字符的排序规则上（即名称中带有_SC的90或100版排序规则和140版排序规则）。还有一个单独的二进制_UTF8排序规则（_BIN2，而不是_BIN）。

  请注意：UTF-8是为与设置为8位编码但想支持Unicode的环境/代码兼容而设计/创建的。尽管有一些情况下，UTF-8可以比NVARCHAR提供高达50％的空间节省，但这是一个副作用，并且会对许多/大多数操作产生轻微的性能影响。如果您需要此功能以实现兼容性，则可以接受成本。如果您想要此功能来节省空间，则最好进行测试，再进行测试。测试应包括所有功能，而不仅限于少量数据行。请注意，当所有列和数据库本身都使用具有_UTF8排序规则的VARCHAR数据（列、变量、字符串文字）时，UTF-8排序规则的效果最佳。这是任何人在使用此功能以实现兼容性时自然的状态，但不适用于那些希望将其用于节省空间的人。在混合使用具有_UTF8排序规则的VARCHAR数据与使用非_UTF8排序规则的VARCHAR数据或NVARCHAR数据时，请小心，因为可能会出现奇怪的行为/数据丢失。有关新UTF-8排序规则的更多详细信息，请参见： SQL Server 2019中的本机UTF-8支持：救世主还是伪先知？

- Solomon Rutzky

---

虽然我为它包含了如此多的信息和努力而点赞，但我的答案绝对不是错误的（数据库存储数据，数据库服务器对这些数据进行操作，排序是一种操作）。我在简洁性和数学严密性之间做出了选择，因为提问者可能只是想要足够的信息，而不是所有可能的信息。 - Kris

嗨@Kris。谢谢你。公平地说，我没有说你的回答完全错误，只是非常不完整。我已经更新了内容，希望能够澄清问题。我明白你的意思，但是OP问的是CREATE DATABASE的COLLATE子句是什么意思。你只提到了它可能做的几件事情之一。为什么你要假定OP只想知道答案的10%？如果所有信息都呈现出来，每个人可以决定要采取多少信息。但是，如果只给出一些信息，那么选择就是他们做的。我选择提供尽可能多的信息，因为大部分信息并不广为人知。（续） - Solomon Rutzky

说到排序（和编码），大多数人所了解的要么不完整，要么是错误的。因此，大多数人不知道足够多的信息，或者认为自己已经知道一些东西，但实际上是完全错误的。当人们拥有所有信息时，他们可以做出更好的决策，因此我觉得最好提供尽可能完整的答案。如果你选择简洁，那么当读者在大小写敏感或二进制排序的数据库中遇到语法解析错误等问题时，他们可能会感到困惑，因为名称解析没有被提及。因此，虽然你关于排序的说法是正确的，但我认为这本身是具有误导性的。 - Solomon Rutzky

我明白您的意思，但我想提供足够的信息而不是过多的信息。过多的信息对很多人来说很快就会变得太复杂了。如果我在某种情况下没有提供足够的信息，我会期待后续问题。(我也没想到这个话题会引起这么多关注) - Kris

@Kris，我一直想说“谢谢！”因为您展现出了成熟和专业的态度。我有些习惯于人们会因为被指出错误而感到个人冒犯，然后变得“难以相处”（甚至更加困难）。但是，您对我的“被接受的答案是错误的”做出了理性的回应，激励我放缓自己的语气，并应该为其他人树立正确沟通和高效交流的榜样。 - Solomon Rutzky

谢谢你的夸奖，很高兴听到我对你有一定的积极影响。不过，我其实很享受被“错误”的情况，因为它为学习新事物打开了机会，这是非常棒的！ - Kris

Answer 3

CP1 表示“代码页 1” - 从技术上讲，这相当于代码页 1252。

- Chris Halcrow

Answer 4

COLLATE 关键词用于指定字符串值使用的字符集和规则（顺序，比对规则）。

例如，在您的情况下，您使用的是大小写不敏感 (CI) 和重音敏感 (AS) 的拉丁规则。

您可以参考这个文档。

- il_guru

Answer 5

这指定了数据库的默认排序规则。在数据库表中创建的每个文本字段都将使用该排序规则，除非您指定其他排序规则。

一个数据库总是有一个默认的排序规则。如果你没有指定任何排序规则，则使用 SQL Server 实例的默认排序规则。

您使用的排序规则的名称表明它使用 Latin1 代码页1，不区分大小写（CI）并且区分重音符号（AS）。这种排序规则在美国使用，因此它将包含在美国使用的排序规则。

排序规则决定如何比较文本值的相等性和相似性，以及在排序时如何进行比较。代码页用于存储非 Unicode 数据，例如 varchar 字段。

- Guffa

---

错误（您不能“不”指定排序规则，尽管可以接受默认值）错误（它也用于Unicode数据） - RichardTheKiwi

@Richard，也就是cyberkiwi：请查看文档：http://msdn.microsoft.com/en-us/library/ms176061.aspx 指定排序规则是可选的。代码页不用于存储Unicode数据，因为它以16位Unicode代码点的形式存储，而不是8位代码页索引的形式。 - Guffa

我读错了你的答案，但它仍然是错误的。数据库始终具有默认排序规则 = SERVER排序规则，而不是特定的 Latin1_General_CI_AS。现在我读错了，因为我半期望该语句涉及 SERVER排序规则，其中确实需要在 UI 中接受默认值。对于第二点，您似乎 暗示 排序规则 不用于 排序 Unicode 数据（尽管您在最后两个句子中从“排序”切换到“存储”）。Unicode 文本数据也遵循排序规则。 - RichardTheKiwi

@Richard，也就是cyberkiwi：我已经修改了关于默认排序规则的段落，以便与我链接到的具体文档相对应。（这取决于服务器版本。）关于第二点，我不知道如何更清楚地表达了。文本中说，在存储非Unicode数据时使用代码页。代码页既不用于确定Unicode数据的排序，也不用于非Unicode数据的排序。 - Guffa