SQL Server 字符集介绍及修改方法演示

---

正确认识SQL Server的字符集

SQL Server 作为一个国际化产品，支持多语言环境。在 SQL Server 中，字符集被称为排序规则（即Collation）。排序规则不仅影响记录行的 sort 顺序，还影响中文显示是否乱码等。在 SQL Server 中，排序规则可在 3 处地方设置：

• 服务器级别 =>instances
• db库级别 =>databases
• 表列级别 =>columns

下图所示为 SQL Server 默认情况下对 Collation 继承的方式。

即在 SQL Server 软件安装时我们可设置服务器级别的排序规则，也就是 instances 的排序规则。如下所示是使用 T-SQL 查询当前 instances 的排序规则信息：

--查询当前SQL Server服务器的排序规则

SELECT SERVERPROPERTY(N'Collation')

(no column name)
--------------------------------------------------
Chinese_PRC_CI_AS

默认排序规则是 Chinese_PRC_CI_AS

当然你也可以使用 SSMS 查看服务器属性：


若在创建 Databases 时我们未指定排序规则，Databases 则会使用 Instances 的排序规则。如下所示是通过 sql 代码查询 SQL Server 中 Databases 的排序规则信息：

--通过目录视图 sys.databases 查询 Databases 的排序规则
SELECT name,collation_name FROM sys.databases WHERE name = N'STUDR'; //将STUDY替换为数据库名
name    collation_name
-------------------------------------------------
STUDY    Chinese_PRC_CI_AS

当然你也可以使用 SSMS 通过查看数据库属性得知数据库的字符集。

而表中的列（columns）默认情况是继承 Databases 的排序规则（除非在创建表时对列的排序规则进行指定），我们可通过目录视图 sys.columns 查询表中 columns 的排序规则信息。

--注意：只有字符型的列才会显示排序规则信息，非字符型的显示为NULL
SELECT name, collation_name FROM sys.columns where collation_name is NOT NULL

注意：SQL Server 的排序规则只影响字符型的列，如 char, varchar, text, nchar, nvarchar, ntext，因此目录视图 sys.columns 中非字符型的排序规则显示为 NULL 。

选择合适的SQL Server字符集

在安装SQL Server时，你可能困惑应该选择哪种字符集，SQL Server或者Windows的。官方推荐使用SQL Server的字符集，而非Windows的字符集。原因是，SQL Server字符集是基于Windows衍生出来的，同时保证SQL Server版本间的兼容性。
可通过如下命令查看当前SQL Server支持的排序规则：

SELECT * from ::fn_helpcollations()

注意字符集的名字缩写与对应的的描述，如 CI 表示不区分大小写。

错误使用SQL Server的字符集

我们不难理解：只需保持 SQL Server 中 3 处字符集设置的地方：Instances、Databases、Columns 设置一致即是正确的使用方式。

那么当 SQL Server 中 Instances 与 Databases 对排序规则设置不一致时，将直接导致临时表#或##不能正常使用（临时表的列默认继承 tempdb 的排序规则，而 tempdb 则继承了 Instances 的排序规则）。

所以，我们应尽可能的正确设置 SQL Server 排序规则：

• 正确的设置 SQL Server 排序规则 ，保持 Instances、Databases、Columns 中 3 处排序规则一致，默认字符集是 Chinese_PRC_CI_AS 。
• 尽可能使用 nvarchar 等 Unicode 类型，而非 varchar 类型 。

修改字符集

安装 SQL Server 时，默认字符集是 Chinese_PRC_CI_AS ，如果安装完成后，我们想更改字符集，应该怎样操作呢？

注：再安装 SQL Server 时，可配置字符集。

修改数据库字符集

本例演示将数据库 STUDY 字符集从 Chinese_PRC_CI_AS 修改成 Latin1_General_100_CS_AS_SC。

修改方法一：

查看数据库属性，然后按照下图步骤进行修改：

注意：需要断开其他链接，否则会报错如下：

修改方法二：

在查询分析器中，输入如下命令：

ALTER DATABASE [STUDY] COLLATE Chinese_PRC_CI_AS

命令执行成功后，通过 sql 代码查询 SQL Server 中 Databases 的排序规则信息：

--通过目录视图 sys.databases 查询 Databases 的排序规则
SELECT name,collation_name FROM sys.databases WHERE name = N'STUDR'; //将STUDY替换为数据库名
name     collation_name
-------------------------------------------------
STUDY    Latin1_General_100_CS_AS_SC

修改成功！

修改服务器字符集

本例演示将服务器字符集从 Chinese_PRC_CI_AS 修改成 Latin1_General_100_CS_AS_SC。

服务器字符集的修改稍微复杂些

1）查看数据库安装路径

补充：(Windows Service) 各版本的默认安装路径
1 SQL Server 2008 C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release
2 SQL Server 2012 C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012
3 SQL Server 2014 C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014
4 SQL Server 2016 C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQLServer2016

本例使用的是SQL Server 2012，默认安装路径为 C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

2）使用管理员权限打开DOS命令行

3）切换进数据库安装文件目录

cd C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012 

4）关闭SQL Server 后台服务

方式1：使用 Net stop 语句关闭 SQL Server 后台服务

Net stop STUDY

方式2：通过 Sql Server Configuration Manager 关闭 SQL Server 后台服务

5）运行语句修改字符集

Setup /QUIET /ACTION=REBUILDDATABASE /instancename=STUDY /SQLSYSADMINACCOUNTS=administrator /sapwd=123456 /sqlcollation=Latin1_General_100_CS_AS_SC 

格式说明:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts / [ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName）
注意: accounts 用户使用电脑管理员用户，CollationName 为需要变更的排序格式 。

6）待5）中命令执行完成后，执行如下命令检查服务器字符集是否修改成功

--查询当前SQL Server服务器的排序规则

SELECT SERVERPROPERTY(N'Collation')

(no column name)
--------------------------------------------------
Latin1_General_100_CS_AS_SC

修改成功！

 

参考文章：https://blog.csdn.net/wstoneh01/article/details/50588720