'COLLATE SQL_Latin1_General_CP1_CI_AS' 有什么作用?

2021-12-05 00:00:00 database tsql sql-server collation

我有一个 SQL 查询来在 SQLServer 中创建数据库,如下所示:

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

create database yourdb
on
( name = 'yourdb_dat',
  filename = 'c:program filesmicrosoft sql servermssql.1mssqldatayourdbdat.mdf',
  size = 25mb,
  maxsize = 1500mb,
  filegrowth = 10mb )
log on
( name = 'yourdb_log',
  filename = 'c:program filesmicrosoft sql servermssql.1mssqldatayourdblog.ldf',
  size = 7mb,
  maxsize = 375mb,
  filegrowth = 10mb )
COLLATE SQL_Latin1_General_CP1_CI_AS;
go

运行良好.

虽然 SQL 的其余部分很明显,但我对 COLLATE SQL_Latin1_General_CP1_CI_AS 的功能感到非常困惑.

While rest of the SQL is clear to be I am quite confused about the functionality of 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?

推荐答案

它设置数据库服务器如何排序(比较文本片段).在这种情况下:

It sets how the database server sorts (compares pieces of text). in this case:

SQL_Latin1_General_CP1_CI_AS

分解成有趣的部分:

  1. latin1 使服务器使用字符集 latin 1 处理字符串,基本上是 ascii
  2. CP1 代表代码页 1252
  3. CI 不区分大小写的比较,所以 'ABC' 将等于 'abc'
  4. AS 区分重音,所以 'ü' 不等于 'u'
  1. latin1 makes the server treat strings using charset latin 1, basically ascii
  2. CP1 stands for Code Page 1252
  3. CI case insensitive comparisons so 'ABC' would equal 'abc'
  4. AS accent sensitive, so 'ü' does not equal 'u'

PS 有关更多详细信息,请务必阅读@solomon-rutzky 的回答.

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

相关文章