'COLLATE SQL_Latin1_General_CP1_CI_AS'做什么?

I have an SQL query to create the database in SQLServer as given below:

我有一个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

It runs fine.

它运行良好。

While rest of the SQL is clear to be I am quite confused about the functionality of COLLATE SQL_Latin1_General_CP1_CI_AS.

尽管SQL的其余部分很清楚，但我对COLLATE SQL_Latin1_General_CP1_CI_AS的功能感到非常困惑。

Can anyone explain this to me? Also, I would like to know if creating the database in this way is a best practice?

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

5 个解决方案

#1

It sets how the database server sorts. in this case:

它设置数据库服务器的排序方式。在这种情况下:

SQL_Latin1_General_CP1_CI_AS

breaks up into interesting parts:

分解成有趣的部分:

1. latin1 makes the server treat strings using charset latin 1, basically ascii
2. latin1使服务器使用charset拉丁1(基本上是ascii)处理字符串。
3. CP1 stands for Code Page 1252
4. CP1代表代码页1252
5. CI case insensitive comparisons so 'ABC' would equal 'abc'
6. CI大小写不敏感的比较所以ABC等于ABC
7. AS accent sensitive, so 'ü' does not equal 'u'
8. 因为口音敏感，所以u不等于u

P.S. For more detailed information be sure to read @solomon-rutzky's answer.

请务必阅读@solomon-rutzky的答案。

#2

The CP1 means 'Code Page 1' - technically this translates to code page 1252

CP1的意思是“代码页1”——从技术上来说，这意味着代码页1252

#3

Please be aware that the accepted answer is a bit incomplete. Yes, at the most basic level Collation handles sorting. BUT, the comparison rules defined by the chosen Collation are used in many places outside of user queries against user data.

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

The COLLATE {collation_name} clause of the CREATE DATABASE statement specifies the default Collation of the Database, and not the Server; Database-level and Server-level default Collations control different things.

CREATE DATABASE语句的COLLATE {collation_name}子句指定数据库的默认排序，而不是服务器;数据库级和服务器级的默认排序控制不同的东西。

Server (i.e. Instance)-level controls:

服务器(例如实例)程度的控制:

• Database-level Collation for system Databases: master, model, msdb, and tempdb.
• 系统数据库的数据库级排序:master、model、msdb和tempdb。
• Due to controlling the DB-level Collation of tempdb, it is then the default Collation for string columns in temporary tables (global and local), but not table variables.
• 由于控制了tempdb的db的db级别排序，因此它是临时表(全局和局部)中的字符串列的默认排序，而不是表变量。
• Due to controlling the DB-level Collation of master, it is then the Collation used for Server-level data, such as Database names (i.e. name column in sys.databases), Login names, etc.
• 由于控制了master的db级排序，所以它是服务器级数据的排序，例如数据库名(即system . Database中的name列)、登录名等。
• Handling of parameter / variable names
• 处理参数/变量名
• Handling of cursor names
• 处理游标的名称
• Handling of GOTO labels
• 处理GOTO标签
• Default Collation used for newly created Databases when the COLLATE clause is missing
• 当缺少COLLATE子句时，用于新创建的数据库的默认排序规则

Database-level controls:

数据库级控制:

• Default Collation used for newly created string columns (CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, and NTEXT -- but don't use TEXT or NTEXT) when the COLLATE clause is missing from the column definition. This goes for both CREATE TABLE and ALTER TABLE ... ADD statements.
• 当列定义中缺少COLLATE子句时，用于新创建的字符串列(CHAR、VARCHAR、NCHAR、NVARCHAR、TEXT和NTEXT——但不要使用TEXT或NTEXT)的默认排序。这适用于CREATE TABLE和ALTER TABLE…添加语句。
• Default Collation used for string literals (i.e. 'some text') and string variables (i.e. @StringVariable). This Collation is only ever used when comparing strings and variables to other strings and variables. When comparing strings / variables to columns, then the Collation of the column will be used.
• 用于字符串文字的默认排序(例如。和字符串变量(例如@StringVariable)。这种排序只在比较字符串和变量与其他字符串和变量时使用。当比较字符串/变量与列时，将使用列的排序。
• The Collation used for Database-level meta-data, such as object names (i.e. sys.objects), column names (i.e. sys.columns), index names (i.e. sys.indexes), etc.
• 用于数据库级元数据的排序，如对象名称(即sys.object)、列名称(即sys.columns)、索引名称(即sys.indexes)等。
• The Collation used for Database-level objects: tables, columns, indexes, etc.
• 数据库级对象的排序:表、列、索引等。

Also:

另外:

• Collations starting with SQL_ are the old (and definitely obsolete, even if not officially deprecated) SQL Server specific Collations (created prior to SQL Server being able to make use of OS-level Collations).
• 以SQL_开头的排序规则是旧的(而且肯定已经过时了，即使没有正式废弃)SQL Server特定的排序规则(在SQL Server能够使用os级别的排序规则之前创建的)。
• All other Collations are Windows Collations and should be the ones being used.
• 所有其他排序都是Windows排序，应该是正在使用的。
• ASCII is an encoding which is 8-bit (for common usage; technically "ASCII" is 7-bit with characters 0 - 127, and "Extended ASCII" is 8-bit with characters 0 - 255)
• ASCII是一种8位的编码(通用);技术上来说，“ASCII”是7位，字符为0 - 127，“扩展ASCII”是8位，字符为0 - 255)
• Latin1 refers the culture / locale that determines:
  • Code Page for CHAR, VARCHAR, and TEXT data (columns, literals, and variables). The Code Page is the "extended" part of Extended ASCII, and controls which characters are used for values 128 - 255.
  • 字符、VARCHAR和文本数据(列、文本和变量)的代码页。代码页是扩展ASCII的“扩展”部分，它控制用于值128 - 255的字符。
  • The rules by which characters are sorted and compared. This covers both VARCHAR and NVARCHAR (i.e. Unicode) data.
  • 字符排序和比较的规则。这包括VARCHAR和NVARCHAR(即Unicode)数据。
• Latin1指的是用于确定:CHAR、VARCHAR和TEXT数据(列、文本和变量)的代码页的区域性/地区。代码页是扩展ASCII的“扩展”部分，它控制用于值128 - 255的字符。字符排序和比较的规则。这包括VARCHAR和NVARCHAR(即Unicode)数据。

#4

The COLLATE keyword specify what kind of character set and rules (order, confrontation rules) you are using for string values.

COLLATE关键字指定您使用的字符串值的字符集和规则(顺序、冲突规则)。

For example in your case you are using Latin rules with case insensitive (CI) and accent sensitive (AS)

例如，您使用的是不区分大小写(CI)和重音(AS)的拉丁规则

You can refer to this Documentation

您可以参考此文档

#5

This specifies the default collation for the database. Every text field that you create in tables in the database will use that collation, unless you specify a different one.

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

A database always has a default collation. If you don't specify any, the default collation of the SQL Server instance is used.

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

The name of the collation that you use shows that it uses the Latin1 code page 1, is case insensitive (CI) and accent sensitive (AS). This collation is used in the USA, so it will contain sorting rules that are used in the USA.

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

The collation decides how text values are compared for equality and likeness, and how they are compared when sorting. The code page is used when storing non-unicode data, e.g. varchar fields.

排序决定了如何比较文本值的平等性和相似性，以及在排序时如何比较它们。当存储非unicode数据(例如varchar字段)时，使用代码页。