首先,为这么长的答案道歉,因为我觉得当人们谈论排序,排序,代码页等术语时仍然存在很多困惑.

从BOL开始：

Collations in sql Server provide sorting rules,case,and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of sql Server,restoring a database backup,or connecting server to client databases,it is important that you understand the locale requirements,sorting order,and case and accent sensitivity of the data that you will be working with.

这意味着Collat​​ion非常重要,因为它指定了有关如何对数据的字符串进行排序和比较的规则.

注意：有关COLLATIONPROPERTY的更多信息

现在让我们首先了解差异……

在T-sql下运行：

SELECT *
FROM::fn_helpcollations()
WHERE NAME IN (
        'sql_Latin1_General_CP1_CI_AS','Latin1_General_CI_AS'
        )
GO

SELECT 'sql_Latin1_General_CP1_CI_AS' AS 'Collation',COLLATIONPROPERTY('sql_Latin1_General_CP1_CI_AS','CodePage') AS 'CodePage','LCID') AS 'LCID','ComparisonStyle') AS 'ComparisonStyle','Version') AS 'Version'

UNION ALL

SELECT 'Latin1_General_CI_AS' AS 'Collation',COLLATIONPROPERTY('Latin1_General_CI_AS','Version') AS 'Version'
GO

结果将是：

看看上面的结果,唯一的区别是两个排序规则之间的排序顺序.但这不是真的,你可以看到为什么如下：

测试1：

--Clean up prevIoUs query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_sql_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_sql_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS 
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1,1),Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it 
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('Kin_Tester1')

-- Create second table using collation sql_Latin1_General_CP1_CI_AS 
CREATE TABLE Table_sql_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1,Comments VARCHAR(50) COLLATE sql_Latin1_General_CP1_CI_AS
    )

-- add some data to it 
INSERT INTO Table_sql_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_sql_Latin1_General_CP1_CI_AS (Comments)
VALUES ('Kin_Tester1')

--Now try to join both tables
SELECT *
FROM Table_Latin1_General_CI_AS LG
INNER JOIN Table_sql_Latin1_General_CP1_CI_AS SLG ON LG.Comments = SLG.Comments
GO

测试1的结果：

Msg 468,Level 16,State 9,Line 35
Cannot resolve the collation conflict between "sql_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

从上面的结果我们可以看出,我们不能直接比较具有不同排序规则的列的值,您必须使用COLLATE来比较列值.

测试2：

主要的区别在于表现,正如Erland Sommarskog在this discussion on msdn所指出的那样.

--Clean up prevIoUs query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_sql_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_sql_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS 
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it 
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_tester1')

-- Create second table using collation sql_Latin1_General_CP1_CI_AS 
CREATE TABLE Table_sql_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1,Comments VARCHAR(50) COLLATE sql_Latin1_General_CP1_CI_AS
    )

-- add some data to it 
INSERT INTO Table_sql_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_sql_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_tester1')

—在两个表上创建索引

CREATE INDEX IX_LG_Comments ON  Table_Latin1_General_CI_AS(Comments)
go
CREATE INDEX IX_SLG_Comments ON  Table_sql_Latin1_General_CP1_CI_AS(Comments)

—运行查询

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments = 'kin_test1'
GO

—这将有IMPLICIT转换

—运行查询

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_sql_Latin1_General_CP1_CI_AS WHERE Comments = 'kin_test1'
GO

—这不会有IMPLICIT转换

隐式转换的原因是因为我有我的数据库&作为sql_Latin1_General_CP1_CI_AS和表Table_Latin1_General_CI_AS的服务器排序规则将列注释定义为带有COLLATE Latin1_General_CI_AS的VARCHAR(50),因此在查找期间sql Server必须执行IMPLICIT转换.

测试3：

使用相同的设置,现在我们将varchar列与nvarchar值进行比较,以查看执行计划中的更改.

– 运行查询

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments =  (SELECT N'kin_test1' COLLATE Latin1_General_CI_AS)
GO

– 运行查询

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_sql_Latin1_General_CP1_CI_AS WHERE Comments = N'kin_test1'
GO

请注意,第一个查询能够执行索引查找,但必须执行隐式转换,而第二个查询执行索引扫描,在扫描大型表时,在性能方面证明效率低下.

结论：

>以上所有测试都表明,对数据库服务器实例进行正确的排序规则非常重要.
> sql_Latin1_General_CP1_CI_AS是一个sql排序规则,其中包含允许您对unicode和非unicode排序数据的规则不同.
> sql collat​​ion在比较unicode和非unicode数据时将无法使用Index,如上面的测试所示,在将nvarchar数据与varchar数据进行比较时,它会进行Index scan而不是seek.
> Latin1_General_CI_AS是一个Windows排序规则,其中包含允许您为unicode和非unicode排序数据的规则相同.
>在比较unicode和非unicode数据时,Windows排序规则仍然可以使用Index(上面的示例中的索引查找),但是您会看到轻微的性能损失.
>强烈建议阅读Erland Sommarskog回答他指出的连接项目.

This will allow me to not have problems with #temp tables,but are there pitfalls?

看我上面的答案.

Would I lose any functionality or features of any kind by not using a “current” collation of sql 2008?

这完全取决于您所指的功能/特性.整理是存储和分类数据.

What about when we move (e.g. in 2 years ) from 2008 to sql 2012? Will I have problems then? Would I at some point be forced to go to Latin1_General_CI_AS?

不能担保！随着事情可能发生变化,并且总是很好地与微软的建议一致,你需要了解你的数据和我上面提到的陷阱.另请参阅this和this连接项目.

I read that some DBA’s script complete the rows of complete databases,and then run the insert script into the database with the new collation – I’m very scared and wary of this – would you recommend doing this?

如果要更改排序规则,则此类脚本非常有用.我发现自己多次更改数据库的排序规则以匹配服务器排序规则,并且我有一些脚本非常简洁.如果您需要,请告诉我.

参考文献：

> Storage and Performance Effects of Unicode
> Working with Collations
> Collation and Unicode Support
> Code Page Architecture