SQL Server 字符集验证的实现

排序规则名称格式示例：
chinese_prc_ci_as
│       │   │  │
│       │   │  └─ ks: 宽度敏感 (kana sensitive)
│       │   └─ ai/as: 重音不敏感/敏感 (accent insensitive/sensitive)
│       └─ ci/cs: 大小写不敏感/敏感 (case insensitive/sensitive)
└─ 语言/地区: chinese_prc, latin1_general, sql_latin1 等

-- 方法1：查看服务器实例排序规则
select serverproperty('collation') as instancecollation;
go

-- 方法2：通过系统数据库查看
select name, collation_name
from sys.databases
where database_id <= 4;  -- 系统数据库
go

-- 方法3：查看数据库默认排序规则
select databasepropertyex(db_name(), 'collation') as currentdbcollation;
go

-- 查看所有数据库的排序规则
select
    database_id as '数据库id',
    name as '数据库名称',
    collation_name as '排序规则',
    case
        when collation_name = serverproperty('collation') then '与实例一致'
        else '与实例不一致'
    end as '状态'
from sys.databases
order by database_id;
go

-- 查看特定表的列排序规则
select
    t.name as '表名',
    c.name as '列名',
    ty.name as '数据类型',
    c.max_length as '最大长度',
    c.collation_name as '排序规则',
    c.is_nullable as '可空'
from sys.tables t
inner join sys.columns c on t.object_id = c.object_id
inner join sys.types ty on c.system_type_id = ty.system_type_id
where t.type_desc = 'user_table'
  and c.collation_name is not null
order by t.name, c.column_id;
go

-- 查看所有可用的排序规则
select
    name as '排序规则名称',
    description as '描述',
    collationproperty(name, 'codepage') as '代码页'
from sys.fn_helpcollations()
where name like 'chinese%'
   or name like 'sql_latin%'
order by name;
go

-- 演示错误：跨库 join 不同排序规则
-- 假设 db1 使用 chinese_prc_ci_as
-- 假设 db2 使用 chinese_prc_cs_as

use db1;
go

select a.id, b.name
from table_a a
inner join db2.dbo.table_b b on a.col1 = b.col1;  -- 这里会报错
go

-- 错误信息：
-- msg 468, level 16, state 9, line 1
-- cannot resolve the collation conflict between "chinese_prc_ci_as" and "chinese_prc_cs_as" in the equal to operation.

-- 方案1：使用 collate 子句指定统一排序规则
select a.id, b.name
from db1.dbo.table_a a
inner join db2.dbo.table_b b
    on a.col1 collate chinese_prc_ci_as = b.col1 collate chinese_prc_ci_as;
go

-- 方案2：使用数据库默认排序规则
select a.id, b.name
from db1.dbo.table_a a
inner join db2.dbo.table_b b
    on a.col1 = b.col1 collate database_default;
go

-- 实例级别排序规则：chinese_prc_ci_as (大小写不敏感)
-- 数据库级别排序规则：chinese_prc_cs_as (大小写敏感)

-- 创建测试表
create table testcase
(
    id int,
    name nvarchar(50)
);
go

-- 插入数据
insert into testcase values (1, 'abc');
insert into testcase values (2, 'abc');
insert into testcase values (3, 'abc');
go

-- 在 chinese_prc_ci_as (不敏感) 环境下
select * from testcase where name = 'abc';
-- 返回 3 行：abc, abc, abc

-- 在 chinese_prc_cs_as (敏感) 环境下
select * from testcase where name = 'abc';
-- 返回 1 行：abc

-- 不同排序规则下的字符串比较

-- 代码页不兼容可能导致的问题
declare @var1 varchar(50) collate chinese_prc_ci_as;
declare @var2 varchar(50) collate sql_latin1_general_cp1_ci_as;

set @var1 = '测试';
set @var2 = '测试';

-- 直接比较会报错
if @var1 = @var2
    print 'equal';
else
    print 'not equal';

-- 错误信息：
-- msg 468, level 16, state 9, line 3
-- cannot resolve the collation conflict between "chinese_prc_ci_as" and "sql_latin1_general_cp1_ci_as" in the equal to operation.

-- 解决方案：统一排序规则
if @var1 collate chinese_prc_ci_as = @var2 collate chinese_prc_ci_as
    print 'equal';
else
    print 'not equal';

-- 创建测试表
create table testindex
(
    id int primary key,
    col1 varchar(50) collate chinese_prc_ci_as,
    col2 varchar(50) collate chinese_prc_cs_as
);
go

-- 创建索引
create index idx_col1 on testindex(col1);
create index idx_col2 on testindex(col2);
go

-- 查询时可能无法使用索引
select * from testindex
where col1 = 'test';  -- 可以使用索引

-- 如果与不同排序规则的列比较
select * from testindex
where col1 = col2 collate chinese_prc_cs_as;  -- 可能无法使用索引，导致扫描

-- 查看当前实例排序规则
declare @instancecollation nvarchar(128);
set @instancecollation = cast(serverproperty('collation') as nvarchar(128));
print '实例排序规则: ' + @instancecollation;
go

-- 创建与实例相同排序规则的数据库
create database testdb_samecollation
collate chinese_prc_ci_as;
go

-- 创建与实例不同排序规则的数据库
create database testdb_diffcollation
collate chinese_prc_cs_as;
go

-- 验证排序规则
select
    name as '数据库名称',
    collation_name as '排序规则',
    case
        when collation_name = serverproperty('collation') then '与实例一致'
        else '与实例不一致'
    end as '状态'
from sys.databases
where name in ('testdb_samecollation', 'testdb_diffcollation');
go

-- 在相同排序规则的数据库中创建表
use testdb_samecollation;
go
create table tablea
(
    id int identity primary key,
    code varchar(20),
    name nvarchar(50)
);
go
insert into tablea (code, name) values ('a001', '名称1'), ('a002', '名称2');
go

-- 在不同排序规则的数据库中创建表
use testdb_diffcollation;
go
create table tableb
(
    id int identity primary key,
    code varchar(20),
    value int
);
go
insert into tableb (code, value) values ('a001', 100), ('a003', 300);
go

-- 测试跨库 join（会报错）
use testdb_samecollation;
go
select a.id, a.code, b.value
from tablea a
inner join testdb_diffcollation.dbo.tableb b on a.code = b.code;
go

-- 预期错误：
-- msg 468, level 16, state 9
-- cannot resolve the collation conflict between "chinese_prc_ci_as" and "chinese_prc_cs_as" in the equal to operation.

-- 正确的跨库 join 方式
select a.id, a.code, b.value
from tablea a
inner join testdb_diffcollation.dbo.tableb b
    on a.code collate chinese_prc_ci_as = b.code collate chinese_prc_ci_as;
go

-- 临时表使用 tempdb 的排序规则，可能与用户数据库不同

-- 查看当前数据库排序规则
select databasepropertyex(db_name(), 'collation') as currentdbcollation;
go

-- 查看 tempdb 排序规则
select databasepropertyex('tempdb', 'collation') as tempdbcollation;
go

-- 创建临时表并测试
create table #temptable
(
    id int,
    code varchar(20)
);
go

-- 插入数据
insert into #temptable values (1, 'a001'), (2, 'a002');
go

-- 创建用户表
create table usertable
(
    id int,
    code varchar(20)
);
go
insert into usertable values (1, 'a001'), (2, 'a002');
go

-- 如果排序规则不同，join 可能失败
select t.id, u.code
from #temptable t
inner join usertable u on t.code = u.code;
go

-- 解决方案：明确指定排序规则
select t.id, u.code
from #temptable t
inner join usertable u
    on t.code collate database_default = u.code collate database_default;
go

-- 测试排序规则对字符串函数的影响

declare @str1 varchar(50) collate chinese_prc_ci_as;
declare @str2 varchar(50) collate chinese_prc_cs_as;

set @str1 = 'teststring';
set @str2 = 'teststring';

-- 大小写敏感测试
select
    case
        when @str1 collate chinese_prc_ci_as = @str2 collate chinese_prc_ci_as
        then 'ci_as: 相等'
        else 'ci_as: 不相等'
    end as ci_result,
    case
        when @str1 collate chinese_prc_cs_as = @str2 collate chinese_prc_cs_as
        then 'cs_as: 相等'
        else 'cs_as: 不相等'
    end as cs_result;
go

-- 排序测试
select
    'a' as char1,
    'a' as char2,
    case
        when 'a' collate chinese_prc_ci_as < 'a' collate chinese_prc_ci_as
        then 'a < a (ci)'
        else 'a >= a (ci)'
    end as ci_sort,
    case
        when 'a' collate chinese_prc_cs_as < 'a' collate chinese_prc_cs_as
        then 'a < a (cs)'
        else 'a >= a (cs)'
    end as cs_sort;
go

-- 方案1：重建数据库为统一排序规则

-- 步骤1：备份数据库
backup database testdb_diffcollation
to disk = 'c:\backup\testdb_diffcollation.bak';
go

-- 步骤2：导出所有数据和对象
-- 使用 sql server management studio 的生成脚本功能
-- 或使用 bcp 命令导出数据

-- 步骤3：删除数据库
drop database testdb_diffcollation;
go

-- 步骤4：使用统一排序规则创建数据库
create database testdb_diffcollation
collate chinese_prc_ci_as;
go

-- 步骤5：导入数据和对象

-- 在查询中显式指定排序规则

-- 跨库查询
select a.col1, b.col2
from db1.dbo.tablea a
inner join db2.dbo.tableb b
    on a.join_col collate database_default = b.join_col collate database_default;

-- 字符串比较
declare @var1 varchar(50) = 'test';
declare @var2 varchar(50) = 'test';

if @var1 collate chinese_prc_ci_as = @var2 collate chinese_prc_ci_as
    print 'equal';

-- order by 指定排序规则
select name from users
order by name collate chinese_prc_ci_as;

-- 修改现有列的排序规则

-- 注意：修改列排序规则会重建表，大表操作需要谨慎

-- 示例：修改列排序规则
alter table testtable
alter column col1 varchar(50) collate chinese_prc_ci_as;
go

-- 查看列排序规则修改结果
select name, collation_name
from sys.columns
where object_id = object_id('testtable');
go

-- 创建计算列用于跨库连接

create table tablea
(
    id int primary key,
    code varchar(20),
    -- 计算列：统一排序规则
    code_normalized as code collate chinese_prc_ci_as persisted
);
go

-- 创建表
create table tableb
(
    id int primary key,
    code varchar(20) collate chinese_prc_cs_as
);
go

-- 使用计算列进行连接
select a.id, b.id
from tablea a
inner join tableb b on a.code_normalized = b.code collate chinese_prc_ci_as;
go

-- 建议在存储过程和视图中使用 database_default

create view vw_crossdatabasejoin as
select
    a.id as id_a,
    b.id as id_b,
    a.col1,
    b.col2
from db1.dbo.tablea a
inner join db2.dbo.tableb b
    on a.join_key collate database_default = b.join_key collate database_default;
go

-- 监控因排序规则导致的查询警告

select
    st.text as sqltext,
    substring(st.text, (qs.statement_start_offset/2)+1,
        ((case qs.statement_end_offset
            when -1 then datalength(st.text)
            else qs.statement_end_offset
        end - qs.statement_start_offset)/2) + 1) as statementtext,
    qp.query_plan,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count as avg_elapsed_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where st.text like '%collate%'
   or qp.query_plan.exist('//*[local-name()="planaffectingconvert"]') = 1
order by avg_elapsed_time desc;
go

-- sql server 字符集诊断脚本

-- 1. 实例级排序规则
print '=== 实例级排序规则 ===';
select
    serverproperty('collation') as instancecollation,
    serverproperty('edition') as serveredition,
    serverproperty('productversion') as productversion;
go

-- 2. 所有数据库排序规则
print '=== 所有数据库排序规则 ===';
select
    database_id,
    name as databasename,
    collation_name as collation,
    compatibility_level as compatibilitylevel,
    state_desc as state,
    case
        when collation_name = serverproperty('collation') then '✓ 一致'
        else '✗ 不一致'
    end as matchinstance
from sys.databases
order by database_id;
go

-- 3. 不一致数据库详情
print '=== 与实例排序规则不一致的数据库 ===';
select
    name as databasename,
    collation_name as databasecollation,
    serverproperty('collation') as instancecollation
from sys.databases
where collation_name <> serverproperty('collation')
  and database_id > 4;  -- 排除系统数据库
go

-- 4. 列级别排序规则统计
print '=== 列级别排序规则统计 ===';
select
    object_name(c.object_id) as tablename,
    c.name as columnname,
    ty.name as datatype,
    c.collation_name as columncollation,
    databasepropertyex(db_name(), 'collation') as databasecollation,
    case
        when c.collation_name = databasepropertyex(db_name(), 'collation') then '✓ 一致'
        else '✗ 不一致'
    end as matchdatabase
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.types ty on c.system_type_id = ty.system_type_id
where t.is_ms_shipped = 0  -- 排除系统表
  and c.collation_name is not null
order by object_name(c.object_id), c.column_id;
go

-- 5. 不同代码页的列
print '=== 使用不同代码页的列 ===';
select
    db_name() as databasename,
    object_name(c.object_id) as tablename,
    c.name as columnname,
    c.collation_name as collation,
    collationproperty(c.collation_name, 'codepage') as codepage
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.is_ms_shipped = 0
  and c.collation_name is not null
group by c.collation_name, collationproperty(c.collation_name, 'codepage')
order by codepage;
go

-- 生成修复建议脚本

declare @instancecollation nvarchar(128);
set @instancecollation = cast(serverproperty('collation') as nvarchar(128));

-- 生成修改数据库排序规则的脚本
select
    'alter database [' + name + '] collate ' + @instancecollation + ';' as fixscript,
    '注意：修改数据库排序规则不影响现有列，需要单独修改列' as warning
from sys.databases
where collation_name <> @instancecollation
  and database_id > 4;

-- 生成修改列排序规则的脚本
select
    'alter table [' + schema_name(t.schema_id) + '].[' + t.name + '] ' +
    'alter column [' + c.name + '] ' +
    upper(ty.name) +
    case
        when ty.name in ('varchar', 'char', 'nvarchar', 'nchar') then '(' + cast(c.max_length as varchar) + ')'
        else ''
    end +
    ' collate ' + @instancecollation + ';' as fixscript,
    '注意：修改列排序规则会重建表，大表操作需谨慎' as warning
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.types ty on c.user_type_id = ty.user_type_id
where t.is_ms_shipped = 0
  and c.collation_name is not null
  and c.collation_name <> @instancecollation
order by schema_name(t.schema_id), t.name, c.column_id;
go

-- 查看执行计划中的排序规则转换警告
select
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count as avg_elapsed_time,
    st.text as sqltext,
    qp.query_plan
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) st
cross apply sys.dm_exec_query_plan(qs.plan_handle) qp
where qp.query_plan.exist('//*[local-name()="sort"][@implicit="1"]') = 1
   or st.text like '%collate%'
order by avg_elapsed_time desc;

-- 测试环境：testdb_samecollation (chinese_prc_ci_as)
create table testcasesensitivity (
    id int,
    name varchar(50)
);
insert into testcasesensitivity values (1, 'abc'), (2, 'abc'), (3, 'abc');

select count(*) from testcasesensitivity where name = 'abc';

-- 测试环境：testdb_diffcollation (chinese_prc_cs_as)
create table testcasesensitivity_cs (
    id int,
    name varchar(50)
);
insert into testcasesensitivity_cs values (1, 'abc'), (2, 'abc'), (3, 'abc');

select count(*) from testcasesensitivity_cs where name = 'abc';

-- 测试环境：testdb_chinese_bin (chinese_prc_bin)
create table testcasebin (id int, name varchar(50));
insert into testcasebin values (1, 'abc'), (2, 'abc'), (3, '测试');

select count(*) from testcasebin where name = 'abc';

-- 测试环境：testdb_latin1_ci_as
create table testlatin1 (id int, content varchar(50));
insert into testlatin1 values (1, 'test'), (2, '中文测试');

select * from testlatin1 where content = 'test';

-- 测试：chinese_prc_bin 与 latin1_general_ci_as 跨库 join
select a.id
from testdb_latin1_ci_as.dbo.testlatin1 a
inner join testdb_chinese_bin.dbo.tablebin b on a.content = b.code;

msg 468, level 16, state 9
cannot resolve the collation conflict between "chinese_prc_bin" and "latin1_general_ci_as"