‘ COLLATE SQL_Latin1_General_CP1_CI_AS’是做什么的？

我有一个 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的功能感到非常困惑。

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

434812 次浏览
小开

整理一下关键字指定用于字符串值的字符集和规则(顺序、对抗规则)的类型。

例如，在您的情况下，您使用的是不区分大小写(线人)和重音(作为)的拉丁语规则

你可以参考这个 文件

小开
最佳答案

它设置数据库服务器如何排序(比较文本片段):

SQL_Latin1_General_CP1_CI_AS

分成有趣的部分:

  1. latin1使服务器使用字符集拉丁1(基本上是 ascii)处理字符串
  2. CP1代表代码页1252
  3. 不区分大小写的比较，所以“ ABC”等于“ ABC”
  4. AS重音敏感，所以‘ ü’不等于‘ u’

欲了解更多详细信息，请联系 阅读@Solomon-Rutzky 的回答。

小开

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

数据库始终具有默认排序规则。如果未指定任何排序规则，则使用 SQLServer 实例的默认排序规则。

您使用的排序规则的名称显示它使用的是 Latin1代码页1，不区分大小写(CI)和重音(AS)。此排序规则在美国使用，因此它将包含在美国使用的排序规则。

排序规则决定如何比较文本值是否相等和相似，以及在排序时如何比较它们。代码页用于存储非 unicode 数据，例如 varchar 字段。

小开

CP1的意思是“代码页1”-技术上翻译成代码页1252

小开

请注意，接受的答案有点不完整。是的，在最基本的级别上排序处理排序。但是，由所选的排序规则定义的比较规则在用户查询之外的许多地方用于对用户数据进行比较。

如果“ COLLATE SQL_Latin1_General_CP1_CI_AS做什么?”意味着“ CREATE DATABASE的 COLLATE子句做什么?”，那么:

CREATE DATABASE语句的 COLLATE {collation_name}子句指定 数据库的默认排序规则，没有指定服务器; 数据库级别和服务器级别的默认排序规则控制不同的内容。

服务器(即实例)级 控件:

  • 系统数据库的数据库级排序规则: master、 model、 msdb和 tempdb。
  • 由于控制了 tempdb的 DB 级排序规则，因此它是临时表(全局和局部)中字符串列的默认排序规则，但不是表变量。
  • 由于控制了 master的 DB 级排序规则，因此 服务器级别数据(如数据库名称(即 sys.databases中的 name列)、登录名等)使用的排序规则是 DB 级排序规则。
  • 参数/变量名的处理
  • 游标名称的处理
  • GOTO标签的处理
  • 缺少 COLLATE子句时用于新创建的数据库的默认排序规则

数据库级 控件:

  • 当列定义中缺少 COLLATE子句时，用于新创建的字符串列(CHAR、 VARCHAR、 NCHAR、 NVARCHAR、 TEXT和 NTEXT——但不要使用 TEXT或 NTEXT)的默认排序规则。这适用于 CREATE TABLE和 VARCHAR0语句。
  • 用于字符串文字(即 'some text')和字符串变量(即 @StringVariable)的默认排序规则。此排序规则仅在将字符串和变量与其他字符串和变量进行比较时使用。当将字符串/变量与列进行比较时，将使用列的排序规则。
  • 用于 数据库级别元数据的排序，例如对象名称(即 sys.objects)、列名称(即 sys.columns)、索引名称(即 sys.indexes)等。
  • 用于 数据库级别对象的排序规则: 表、列、索引等。

另外:

  • ASCII 是一种8位编码(通常使用; 技术上来说，“ ASCII”是7位，字符值为0-127，“ ASCII 扩展”是8位，字符值为0-255)。这个群体在不同文化中是相同的。
  • 代码页是扩展 ASCII 的“扩展”部分，控制值128-255使用哪些字符。这个群体因文化不同而不同。
  • Latin1是否意味着“ ASCII”，因为标准 ASCII 只涵盖值0-127，而 所有代码页(可以在 SQL Server 中表示，甚至 NVARCHAR)将这128个值映射到相同的字符。

如果“ What does COLLATE SQL_Latin1_General_CP1_CI_AS do?”意思是“ What does this special 胆量排序是做什么的?”，那么:

  • 因为名称以 SQL_开头，所以这是 SQLServer 排序规则，而不是 Windows 排序规则。即使没有正式废弃，它们也肯定已经过时了，而且主要用于 SQLServer2000之前的兼容性。不过，非常不幸的是，SQL_Latin1_General_CP1_CI_AS是非常普遍的，因为它是默认安装在操作系统上使用美国英语作为其语言。如果可能的话，应该尽量避免这些排序规则。

    Windows 排序规则(名称以 SQL_开头的 没有)更新，功能更强，对于相同的值，在 VARCHAR和 NVARCHAR之间具有一致的排序，并且正在使用附加/校正的排序权重和大写/小写映射进行更新。这些排序规则也没有 SQLServer 排序规则所具有的潜在性能问题: 混合 VARCHAR 和 NVARCHAR 类型对索引的影响。

  • Latin1_General是区域性/区域设置。
    • 对于 NCHAR、 NVARCHAR和 NTEXT数据，这决定了用于排序和比较的语言规则。
    • 对于 CHAR、 VARCHAR和 TEXT数据(列、文字和变量) ，这决定了:
      • 用于排序和比较的语言规则。
      • 用于对字符进行编码的代码页。例如，Latin1_General排序规则使用代码页1252，Hebrew排序规则使用代码页1255，等等。

  • CP{code_page}或 {version}

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

    • 对于 窗户排序规则: {version}虽然不存在于所有排序规则名称中，但是指引入排序规则的 SQLServer 版本(在大多数情况下)。名称中没有版本号的 Windows 排序规则是版本 80(意味着 SQL Server 2000是版本8.0)。并非所有版本的 SQLServer 都带有新的排序规则，因此版本号中存在空白。有一些是 90(SQL Server 2005，版本9.0) ，大多数是 100(SQL Server 2008，版本10.0) ，还有一小部分是 140(SQL Server 2017，版本14.0)。

      我说“在大多数情况下”是因为在 SQL Server 2012(版本11.0)中引入了以 _SC结尾的排序规则，但是底层数据并不是新的，它们只是为内置函数添加了对补充字符的支持。因此，这些结尾存在于版本 90和 100排序规则中，但仅从 SQLServer2012开始。

  • 接下来是敏感度，可以是以下任意组合，但总是按照这个顺序指定:
    • CS = 区分大小写或 CI = 不区分大小写
    • AS = 重音敏感或 AI = 重音不敏感
    • KS = 假名类型敏感或缺失 = 假名类型不敏感
    • WS = 宽度敏感或缺失 = 宽度不敏感
    • VSS = 变异选择器敏感(仅在版本140排序)或缺少 = 变异选择器不敏感

  • 可选的最后一件:

    • _SC在末尾的意思是“补充字符支持”。“支持”只影响内置函数解释代理项对的方式(代理项对是补充字符在 UTF-16中的编码方式)。如果末尾没有 _SC(或中间没有 _140_) ，内置函数就看不到一个补充字符，而是看到组成代理对的两个无意义的代码点。此结尾可以添加到任何非二进制、版本90或100排序规则中。
    • _BIN或 _BIN2在末尾意味着“二进制”排序和比较。数据仍然存储相同，但没有语言规则。这个结束从来没有结合任何5个敏感性或 _SC。_BIN是较老的风格，而 _BIN2是较新的，更准确的风格。如果使用 SQLServer2005或更新版本，请使用 _BIN2。有关 _BIN和 _BIN2之间差异的详细信息，请参阅: 各种二进制排序规则(区域性、版本和 BIN 与 BIN2)之间的差异。

    • 从 SQLServer2019开始，_UTF8是一个新选项。它是一种8位编码，允许 Unicode 数据存储在 VARCHAR和 CHAR数据类型中(但不能存储不推荐的 TEXT数据类型)。此选项只能用于支持补充字符的排序规则(即版本90或100的名称中包含 _SC的排序规则和版本140的排序规则)。还有一个单一的二进制 _UTF8排序规则(_BIN2，而不是 _BIN)。

      请注意: UTF-8是为了与环境/代码兼容而设计/创建的，这些环境/代码为8位编码设置，但希望支持 Unicode。尽管在一些场景中，UTF-8可以比 NVARCHAR节省高达50% 的空间，但这是一种副作用，并且在许多/大多数操作中都会对性能造成轻微影响。如果为了兼容性需要这样做，那么成本是可以接受的。如果你想节省空间，你最好再测试一次。测试包括所有功能，而不仅仅是几行数据。请注意，当所有列和数据库本身使用 VARCHAR数据(列、变量、字符串文字)和 _UTF8排序规则时，UTF-8排序规则工作得最好。这是任何人使用这个兼容性的自然状态，但不是那些希望使用它来节省空间。当使用 _UTF8排序规则与使用非 _UTF8排序规则或 NVARCHAR数据的 VARCHAR数据混合使用 VARCHAR 数据时，要小心，因为您可能会遇到奇怪的行为/数据丢失。有关新的 UTF-8排序规则的更多细节，请参见: 原生 UTF-8在 SQL Server 2019中的支持: 救世主还是假先知？


提交答案