排序規(guī)則名稱格式示例：
Chinese_PRC_CI_AS
│       │   │  │
│       │   │  └─ KS: 寬度敏感 (Kana Sensitive)
│       │   └─ AI/AS: 重音不敏感/敏感 (Accent Insensitive/Sensitive)
│       └─ CI/CS: 大小寫不敏感/敏感 (Case Insensitive/Sensitive)
└─ 語言/地區(qū): Chinese_PRC, Latin1_General, SQL_Latin1 等

-- 方法1：查看服務(wù)器實(shí)例排序規(guī)則
SELECT SERVERPROPERTY('Collation') AS InstanceCollation;
GO

-- 方法2：通過系統(tǒng)數(shù)據(jù)庫查看
SELECT name, collation_name
FROM sys.databases
WHERE database_id <= 4;  -- 系統(tǒng)數(shù)據(jù)庫
GO

-- 方法3：查看數(shù)據(jù)庫默認(rèn)排序規(guī)則
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS CurrentDBCollation;
GO

-- 查看所有數(shù)據(jù)庫的排序規(guī)則
SELECT
    database_id AS '數(shù)據(jù)庫ID',
    name AS '數(shù)據(jù)庫名稱',
    collation_name AS '排序規(guī)則',
    CASE
        WHEN collation_name = SERVERPROPERTY('Collation') THEN '與實(shí)例一致'
        ELSE '與實(shí)例不一致'
    END AS '狀態(tài)'
FROM sys.databases
ORDER BY database_id;
GO

-- 查看特定表的列排序規(guī)則
SELECT
    t.name AS '表名',
    c.name AS '列名',
    ty.name AS '數(shù)據(jù)類型',
    c.max_length AS '最大長度',
    c.collation_name AS '排序規(guī)則',
    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

-- 查看所有可用的排序規(guī)則
SELECT
    name AS '排序規(guī)則名稱',
    description AS '描述',
    COLLATIONPROPERTY(name, 'CodePage') AS '代碼頁'
FROM sys.fn_helpcollations()
WHERE name LIKE 'Chinese%'
   OR name LIKE 'SQL_Latin%'
ORDER BY name;
GO

-- 演示錯(cuò)誤：跨庫 JOIN 不同排序規(guī)則
-- 假設(shè) DB1 使用 Chinese_PRC_CI_AS
-- 假設(shè) 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;  -- 這里會(huì)報(bào)錯(cuò)
GO

-- 錯(cuò)誤信息：
-- 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 子句指定統(tǒng)一排序規(guī)則
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：使用數(shù)據(jù)庫默認(rèn)排序規(guī)則
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

-- 實(shí)例級(jí)別排序規(guī)則：Chinese_PRC_CI_AS (大小寫不敏感)
-- 數(shù)據(jù)庫級(jí)別排序規(guī)則：Chinese_PRC_CS_AS (大小寫敏感)

-- 創(chuàng)建測試表
CREATE TABLE TestCase
(
    id INT,
    name NVARCHAR(50)
);
GO

-- 插入數(shù)據(jù)
INSERT INTO TestCase VALUES (1, 'abc');
INSERT INTO TestCase VALUES (2, 'ABC');
INSERT INTO TestCase VALUES (3, 'aBc');
GO

-- 在 Chinese_PRC_CI_AS (不敏感) 環(huán)境下
SELECT * FROM TestCase WHERE name = 'abc';
-- 返回 3 行：abc, ABC, aBc

-- 在 Chinese_PRC_CS_AS (敏感) 環(huán)境下
SELECT * FROM TestCase WHERE name = 'abc';
-- 返回 1 行：abc

-- 不同排序規(guī)則下的字符串比較

-- 代碼頁不兼容可能導(dǎo)致的問題
DECLARE @var1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS;
DECLARE @var2 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS;

SET @var1 = '測試';
SET @var2 = '測試';

-- 直接比較會(huì)報(bào)錯(cuò)
IF @var1 = @var2
    PRINT 'Equal';
ELSE
    PRINT 'Not Equal';

-- 錯(cuò)誤信息：
-- 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.

-- 解決方案：統(tǒng)一排序規(guī)則
IF @var1 COLLATE Chinese_PRC_CI_AS = @var2 COLLATE Chinese_PRC_CI_AS
    PRINT 'Equal';
ELSE
    PRINT 'Not Equal';

-- 創(chuàng)建測試表
CREATE TABLE TestIndex
(
    id INT PRIMARY KEY,
    col1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS,
    col2 VARCHAR(50) COLLATE Chinese_PRC_CS_AS
);
GO

-- 創(chuàng)建索引
CREATE INDEX idx_col1 ON TestIndex(col1);
CREATE INDEX idx_col2 ON TestIndex(col2);
GO

-- 查詢時(shí)可能無法使用索引
SELECT * FROM TestIndex
WHERE col1 = 'test';  -- 可以使用索引

-- 如果與不同排序規(guī)則的列比較
SELECT * FROM TestIndex
WHERE col1 = col2 COLLATE Chinese_PRC_CS_AS;  -- 可能無法使用索引，導(dǎo)致掃描

-- 查看當(dāng)前實(shí)例排序規(guī)則
DECLARE @InstanceCollation NVARCHAR(128);
SET @InstanceCollation = CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128));
PRINT '實(shí)例排序規(guī)則: ' + @InstanceCollation;
GO

-- 創(chuàng)建與實(shí)例相同排序規(guī)則的數(shù)據(jù)庫
CREATE DATABASE TestDB_SameCollation
COLLATE Chinese_PRC_CI_AS;
GO

-- 創(chuàng)建與實(shí)例不同排序規(guī)則的數(shù)據(jù)庫
CREATE DATABASE TestDB_DiffCollation
COLLATE Chinese_PRC_CS_AS;
GO

-- 驗(yàn)證排序規(guī)則
SELECT
    name AS '數(shù)據(jù)庫名稱',
    collation_name AS '排序規(guī)則',
    CASE
        WHEN collation_name = SERVERPROPERTY('Collation') THEN '與實(shí)例一致'
        ELSE '與實(shí)例不一致'
    END AS '狀態(tài)'
FROM sys.databases
WHERE name IN ('TestDB_SameCollation', 'TestDB_DiffCollation');
GO

-- 在相同排序規(guī)則的數(shù)據(jù)庫中創(chuàng)建表
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

-- 在不同排序規(guī)則的數(shù)據(jù)庫中創(chuàng)建表
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（會(huì)報(bào)錯(cuò)）
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

-- 預(yù)期錯(cuò)誤：
-- 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

-- 臨時(shí)表使用 tempdb 的排序規(guī)則，可能與用戶數(shù)據(jù)庫不同

-- 查看當(dāng)前數(shù)據(jù)庫排序規(guī)則
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS CurrentDBCollation;
GO

-- 查看 tempdb 排序規(guī)則
SELECT DATABASEPROPERTYEX('tempdb', 'Collation') AS TempDBCollation;
GO

-- 創(chuàng)建臨時(shí)表并測試
CREATE TABLE #TempTable
(
    id INT,
    code VARCHAR(20)
);
GO

-- 插入數(shù)據(jù)
INSERT INTO #TempTable VALUES (1, 'A001'), (2, 'A002');
GO

-- 創(chuàng)建用戶表
CREATE TABLE UserTable
(
    id INT,
    code VARCHAR(20)
);
GO
INSERT INTO UserTable VALUES (1, 'A001'), (2, 'A002');
GO

-- 如果排序規(guī)則不同，JOIN 可能失敗
SELECT t.id, u.code
FROM #TempTable t
INNER JOIN UserTable u ON t.code = u.code;
GO

-- 解決方案：明確指定排序規(guī)則
SELECT t.id, u.code
FROM #TempTable t
INNER JOIN UserTable u
    ON t.code COLLATE DATABASE_DEFAULT = u.code COLLATE DATABASE_DEFAULT;
GO

-- 測試排序規(guī)則對(duì)字符串函數(shù)的影響

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：重建數(shù)據(jù)庫為統(tǒng)一排序規(guī)則

-- 步驟1：備份數(shù)據(jù)庫
BACKUP DATABASE TestDB_DiffCollation
TO DISK = 'C:\Backup\TestDB_DiffCollation.bak';
GO

-- 步驟2：導(dǎo)出所有數(shù)據(jù)和對(duì)象
-- 使用 SQL Server Management Studio 的生成腳本功能
-- 或使用 bcp 命令導(dǎo)出數(shù)據(jù)

-- 步驟3：刪除數(shù)據(jù)庫
DROP DATABASE TestDB_DiffCollation;
GO

-- 步驟4：使用統(tǒng)一排序規(guī)則創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE TestDB_DiffCollation
COLLATE Chinese_PRC_CI_AS;
GO

-- 步驟5：導(dǎo)入數(shù)據(jù)和對(duì)象

-- 在查詢中顯式指定排序規(guī)則

-- 跨庫查詢
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 指定排序規(guī)則
SELECT name FROM users
ORDER BY name COLLATE Chinese_PRC_CI_AS;

-- 修改現(xiàn)有列的排序規(guī)則

-- 注意：修改列排序規(guī)則會(huì)重建表，大表操作需要謹(jǐn)慎

-- 示例：修改列排序規(guī)則
ALTER TABLE TestTable
ALTER COLUMN col1 VARCHAR(50) COLLATE Chinese_PRC_CI_AS;
GO

-- 查看列排序規(guī)則修改結(jié)果
SELECT name, collation_name
FROM sys.columns
WHERE object_id = OBJECT_ID('TestTable');
GO

-- 創(chuàng)建計(jì)算列用于跨庫連接

CREATE TABLE TableA
(
    id INT PRIMARY KEY,
    code VARCHAR(20),
    -- 計(jì)算列：統(tǒng)一排序規(guī)則
    code_normalized AS code COLLATE Chinese_PRC_CI_AS PERSISTED
);
GO

-- 創(chuàng)建表
CREATE TABLE TableB
(
    id INT PRIMARY KEY,
    code VARCHAR(20) COLLATE Chinese_PRC_CS_AS
);
GO

-- 使用計(jì)算列進(jìn)行連接
SELECT a.id, b.id
FROM TableA a
INNER JOIN TableB b ON a.code_normalized = b.code COLLATE Chinese_PRC_CI_AS;
GO

-- 建議在存儲(chǔ)過程和視圖中使用 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

-- 監(jiān)控因排序規(guī)則導(dǎo)致的查詢警告

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. 實(shí)例級(jí)排序規(guī)則
PRINT '=== 實(shí)例級(jí)排序規(guī)則 ===';
SELECT
    SERVERPROPERTY('Collation') AS InstanceCollation,
    SERVERPROPERTY('Edition') AS ServerEdition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion;
GO

-- 2. 所有數(shù)據(jù)庫排序規(guī)則
PRINT '=== 所有數(shù)據(jù)庫排序規(guī)則 ===';
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. 不一致數(shù)據(jù)庫詳情
PRINT '=== 與實(shí)例排序規(guī)則不一致的數(shù)據(jù)庫 ===';
SELECT
    name AS DatabaseName,
    collation_name AS DatabaseCollation,
    SERVERPROPERTY('Collation') AS InstanceCollation
FROM sys.databases
WHERE collation_name <> SERVERPROPERTY('Collation')
  AND database_id > 4;  -- 排除系統(tǒng)數(shù)據(jù)庫
GO

-- 4. 列級(jí)別排序規(guī)則統(tǒng)計(jì)
PRINT '=== 列級(jí)別排序規(guī)則統(tǒng)計(jì) ===';
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  -- 排除系統(tǒng)表
  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

-- 生成修復(fù)建議腳本

DECLARE @InstanceCollation NVARCHAR(128);
SET @InstanceCollation = CAST(SERVERPROPERTY('Collation') AS NVARCHAR(128));

-- 生成修改數(shù)據(jù)庫排序規(guī)則的腳本
SELECT
    'ALTER DATABASE [' + name + '] COLLATE ' + @InstanceCollation + ';' AS FixScript,
    '注意：修改數(shù)據(jù)庫排序規(guī)則不影響現(xiàn)有列，需要單獨(dú)修改列' AS Warning
FROM sys.databases
WHERE collation_name <> @InstanceCollation
  AND database_id > 4;

-- 生成修改列排序規(guī)則的腳本
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,
    '注意：修改列排序規(guī)則會(huì)重建表，大表操作需謹(jǐn)慎' 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

-- 查看執(zhí)行計(jì)劃中的排序規(guī)則轉(zhuǎn)換警告
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;

-- 測試環(huán)境：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';

-- 測試環(huán)境：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';

-- 測試環(huán)境：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';

-- 測試環(huán)境：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"