DBA mssql 解决排序规则冲突 QA prod 和开发配置都是一样的服务器排序规则 为啥开发环境的的存储过程需要 加这个COLLATE Chinese_PRC_CI_AS
背景mssql 解决排序规则冲突 QA prod 和开发配置都是一样的服务器排序规则 SQL_Latin1_General_CP1_CI_AS 数据库的实例属性 常规 维护排序规则SQL_Latin1_General_CP1_CI_AS 为啥开发环境的的存储过程需要 加这个COLLATE Chinese_PRC_CI_AS
--1. 查看当前数据库默认排序规则
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;
-- 通常返回：SQL_Latin1_General_CP1_CI_AS
--1. 查看当前数据库默认排序规则
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
c.collation_name AS ColumnCollation,
DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseDefaultCollation
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation');
--方法 2：批量生成修改脚本（适用于大量表）
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) +
' ALTER COLUMN ' + QUOTENAME(c.name) + ' ' +
TYPE_NAME(c.user_type_id) +
CASE
WHEN TYPE_NAME(c.user_type_id) IN ('char', 'varchar', 'binary', 'varbinary')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN TYPE_NAME(c.user_type_id) IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(10)) END + ')'
ELSE ''
END +
' COLLATE DATABASE_DEFAULT ' +
CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ';'
AS AlterScript
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')
ORDER BY t.name, c.column_id;
原来是第三方大聪明 把数据源编码格式变了！！！
这是一个很常见的 SQL Server 排序规则（Collation）冲突问题。虽然你提到 QA、Prod 和开发环境 **服务器的默认排序规则都是 `SQL_Latin1_General_CP1_CI_AS`**，并且数据库也显式设为相同排序规则，但开发环境仍需在存储过程中使用 `COLLATE Chinese_PRC_CI_AS`，这背后通常有以下几种原因：
---
### 🧠 一、**数据库对象（如列）的排序规则可能不同**
> **关键点：数据库的默认排序规则 ≠ 表/列的实际排序规则**
即使数据库的默认排序规则是 `SQL_Latin1_General_CP1_CI_AS`，但如果：
- 某些表是 **从其他服务器/数据库导入的**（比如通过 SSIS、脚本、备份还原等），
- 或者在创建列时**显式指定了不同的排序规则**（例如 `VARCHAR(50) COLLATE Chinese_PRC_CI_AS`），
那么这些列的实际排序规则就可能是 `Chinese_PRC_CI_AS`。
✅ **解决方法：**
```sql
-- 检查表中各列的实际排序规则
SELECT
t.name AS TableName,
c.name AS ColumnName,
c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND t.name = 'YourTableName';
```
如果某些列是 `Chinese_PRC_CI_AS`，而其他是 `SQL_Latin1_General_CP1_CI_AS`，那么在 `JOIN`、`WHERE`、`UNION`、`=`, `LIKE` 等操作时就会报错：
> **“无法解决列 '%.*ls' 与列 '%.*ls' 之间的排序规则冲突。”**
此时你必须用 `COLLATE` 强制统一，比如：
```sql
WHERE col1 COLLATE Chinese_PRC_CI_AS = col2
```
或更通用的做法：
```sql
WHERE col1 COLLATE DATABASE_DEFAULT = col2
```
---
### 🧠 二、**临时表或表变量继承了 `tempdb` 的排序规则**
> `tempdb` 的排序规则 = **实例（服务器）的默认排序规则**，即 `SQL_Latin1_General_CP1_CI_AS`。
但如果你在开发环境中创建了**用户表使用 `Chinese_PRC_CI_AS`**（比如从中文系统导出的脚本），然后和临时表（`#temp`）比较，就会出现排序冲突。
例如：
```sql
-- 开发表（可能来自中文环境）
CREATE TABLE dbo.MyTable (Name VARCHAR(50) COLLATE Chinese_PRC_CI_AS);
-- 临时表（继承 tempdb 排序规则 = SQL_Latin1_General_CP1_CI_AS）
CREATE TABLE #Temp (Name VARCHAR(50));
```
此时：
```sql
SELECT * FROM dbo.MyTable t1
JOIN #Temp t2 ON t1.Name = t2.Name; -- ❌ 排序规则冲突！
```
✅ **解决方案：**
- 在创建临时表时显式指定排序规则：
```sql
CREATE TABLE #Temp (Name VARCHAR(50) COLLATE Chinese_PRC_CI_AS);
```
- 或在比较时使用 `COLLATE`：
```sql
ON t1.Name COLLATE SQL_Latin1_General_CP1_CI_AS = t2.Name
```
> ⚠️ 之所以在 QA/Prod 没问题，可能是因为那些环境的表**从未使用过 `Chinese_PRC_CI_AS`**，或者所有对象都统一使用服务器默认规则。而开发环境可能混合了不同来源的数据。
---
### 🧠 三、**动态 SQL 或跨库查询涉及不同排序规则的数据库**
如果你的存储过程中：
- 调用了其他数据库（比如 `DevDB` vs `CommonDB`），而另一个数据库的排序规则不同，
- 或使用了 **链接服务器（Linked Server）**，目标服务器排序规则不同，
也会导致隐式转换冲突。
---
### 🧠 四、**SSMS 或工具生成的脚本可能默认带中文排序规则**
开发人员可能在中文 Windows + 中文 SQL Server Management Studio 环境下生成建表脚本，SSMS 有时会**自动添加 `COLLATE Chinese_PRC_CI_AS`**（尤其在早期版本或特定区域设置下）。
而 QA/Prod 是通过标准化部署脚本创建的，没有这个问题。
---
### ✅ 建议排查步骤：
1. **检查出问题的存储过程中涉及的所有表和列的排序规则**（用上面的 `sys.columns` 查询）。
2. **检查是否使用了临时表、表变量、CTE** —— 它们的排序规则来源不同。
3. **对比开发 vs QA/Prod 环境的表结构脚本**，看是否有 `COLLATE` 差异。
4. **不要依赖“服务器/数据库默认规则相同”就认为一切一致** —— 列级规则才是关键。
---
### 🛠️ 长期解决方案
- **统一环境构建方式**：所有环境使用相同的部署脚本，避免手动建表。
- **避免在列定义中硬编码排序规则**，除非必要。
- 使用 `COLLATE DATABASE_DEFAULT` 在比较时更安全（但需确保所有库规则一致）。
- 如果确实需要中文排序（如按拼音排序），建议**将整个实例/数据库设为 `Chinese_PRC_CI_AS`**，而不是混合使用。
---
### 💡 补充说明
- `SQL_Latin1_General_CP1_CI_AS` 是 SQL Server 早期的默认规则（区分大小写不敏感，适用于英语/西欧语言）。
- `Chinese_PRC_CI_AS` 是面向简体中文的规则，支持中文字符的正确排序和比较。
- 两者**不能隐式比较**，因为它们的代码页（Code Page）和排序算法不同。
---
要查看并统一 SQL Server 中列的排序规则，使其与数据库默认排序规则（如 SQL_Latin1_General_CP1_CI_AS）保持一致，可以按照以下步骤操作：
✅ 一、查看哪些列使用了非默认排序规则
1. 查看当前数据库默认排序规则
sql
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;
-- 通常返回：SQL_Latin1_General_CP1_CI_AS
2. 查找所有使用了 非数据库默认排序规则 的列
sql
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
c.collation_name AS ColumnCollation,
DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseDefaultCollation
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation');
✅ 这条语句会列出所有排序规则与数据库默认不一致的字符类型列（如
char,varchar,nvarchar,text等）。
✅ 二、统一修改列的排序规则（推荐方法）
⚠️ 注意事项
- 修改列排序规则需要重建索引（如果该列有索引）。
- 如果列被外键、约束、视图、函数等引用，需先处理依赖。
- 建议在维护窗口操作，并提前备份。
方法 1：逐列修改（适用于少量列）
sql
-- 示例：将表 MyTable 的 Name 列改为数据库默认排序规则
ALTER TABLE dbo.MyTable
ALTER COLUMN Name VARCHAR(50) COLLATE DATABASE_DEFAULT;
COLLATE DATABASE_DEFAULT会自动使用当前数据库的默认排序规则，比硬编码更安全。
如果列允许 NULL 或有默认值，要一并带上：
sql
ALTER TABLE dbo.MyTable
ALTER COLUMN Name VARCHAR(50) COLLATE DATABASE_DEFAULT NULL; -- 或 NOT NULL
方法 2：批量生成修改脚本（适用于大量表）
你可以用以下脚本生成所有需要修改的 ALTER 语句：
sql
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) +
' ALTER COLUMN ' + QUOTENAME(c.name) + ' ' +
TYPE_NAME(c.user_type_id) +
CASE
WHEN TYPE_NAME(c.user_type_id) IN ('char', 'varchar', 'binary', 'varbinary')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN TYPE_NAME(c.user_type_id) IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(10)) END + ')'
ELSE ''
END +
' COLLATE DATABASE_DEFAULT ' +
CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ';'
AS AlterScript
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')
ORDER BY t.name, c.column_id;
✅ 执行此查询后，会输出一系列
ALTER TABLE ... ALTER COLUMN ...语句。
👉 复制结果，在测试环境先验证，再在开发/生产执行。
✅ 三、处理索引和约束（关键！）
如果目标列上有索引、主键、唯一约束等，直接 ALTER COLUMN 会失败。
解决方案：
- 删除依赖对象（索引、约束等）
- 修改列排序规则
- 重建依赖对象
示例流程：
sql
-- 1. 删除索引（假设叫 IX_MyTable_Name）
DROP INDEX IX_MyTable_Name ON dbo.MyTable;
-- 2. 修改列
ALTER TABLE dbo.MyTable
ALTER COLUMN Name VARCHAR(50) COLLATE DATABASE_DEFAULT NOT NULL;
-- 3. 重建索引
CREATE NONCLUSTERED INDEX IX_MyTable_Name ON dbo.MyTable (Name);
🔍 可通过以下语句查找列上的索引：
sql
SELECT i.name AS IndexName, i.type_desc
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE c.object_id = OBJECT_ID('dbo.MyTable')
AND c.name = 'Name';
✅ 四、验证是否已统一
再次运行第一步的查询，确认结果为空：
sql
-- 应该返回 0 行
SELECT * FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation');
✅ 五、预防措施（避免未来再出现）
- 建表脚本不要硬编码
COLLATE Chinese_PRC_CI_AS，除非有强业务需求。 - 使用
COLLATE DATABASE_DEFAULT保持环境一致性。 - 在 CI/CD 流程中加入排序规则检查（例如用 SQL 脚本校验）。
- 开发环境尽量模拟生产环境的部署方式（避免手动建表）。
💡 补充：临时表怎么办？
如果你的存储过程中用到了临时表（#temp），而它和用户表排序规则冲突：
✅ 建议在创建临时表时显式指定与用户表一致的排序规则：
CREATE TABLE #Temp (
Name VARCHAR(50) COLLATE DATABASE_DEFAULT
);
-- =============================================
-- 自动统一数据库所有列排序规则为 DATABASE_DEFAULT
-- 并自动重建索引、主键、唯一约束
-- 注意：此脚本不处理外键、视图、函数依赖！
-- 建议在事务或维护窗口中运行
-- =============================================
SET NOCOUNT ON;
DECLARE @DatabaseCollation SYSNAME = CAST(DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS SYSNAME);
DECLARE @sql NVARCHAR(MAX);
DECLARE @objectName NVARCHAR(256);
DECLARE @indexName NVARCHAR(256);
DECLARE @isPrimaryKey BIT;
DECLARE @isUniqueConstraint BIT;
DECLARE @indexType NVARCHAR(60);
DECLARE @colList NVARCHAR(MAX);
DECLARE @dropScript NVARCHAR(MAX);
DECLARE @createScript NVARCHAR(MAX);
-- 创建临时表存储待修复的列及依赖索引
IF OBJECT_ID('tempdb..#ColumnsToFix') IS NOT NULL DROP TABLE #ColumnsToFix;
IF OBJECT_ID('tempdb..#DependentIndexes') IS NOT NULL DROP TABLE #DependentIndexes;
CREATE TABLE #ColumnsToFix (
SchemaName SYSNAME,
TableName SYSNAME,
ColumnName SYSNAME,
DataType SYSNAME,
MaxLength SMALLINT,
IsNullable BIT,
ObjectId INT,
ColumnId INT
);
CREATE TABLE #DependentIndexes (
ObjectId INT,
ColumnId INT,
IndexName SYSNAME,
IsPrimaryKey BIT,
IsUniqueConstraint BIT,
IndexType NVARCHAR(60),
IsDisabled BIT,
FillFactor TINYINT,
DataSpaceId INT
);
-- 1. 找出所有需要修改排序规则的列（非默认且有排序规则）
INSERT INTO #ColumnsToFix (SchemaName, TableName, ColumnName, DataType, MaxLength, IsNullable, ObjectId, ColumnId)
SELECT
SCHEMA_NAME(t.schema_id),
t.name,
c.name,
TYPE_NAME(c.user_type_id),
c.max_length,
c.is_nullable,
c.object_id,
c.column_id
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> @DatabaseCollation
AND ty.name IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext');
-- 2. 找出这些列上依赖的索引（包括主键、唯一约束）
INSERT INTO #DependentIndexes (ObjectId, ColumnId, IndexName, IsPrimaryKey, IsUniqueConstraint, IndexType, IsDisabled, FillFactor, DataSpaceId)
SELECT
ic.object_id,
ic.column_id,
i.name,
i.is_primary_key,
i.is_unique_constraint,
i.type_desc,
i.is_disabled,
i.fill_factor,
i.data_space_id
FROM sys.index_columns ic
JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE EXISTS (
SELECT 1 FROM #ColumnsToFix c
WHERE c.ObjectId = ic.object_id AND c.ColumnId = ic.column_id
);
-- 3. 生成 DROP 脚本（先删索引/约束）
DECLARE DropCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT
QUOTENAME(SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) AS ObjectName,
i.IndexName,
i.IsPrimaryKey,
i.IsUniqueConstraint
FROM #DependentIndexes i;
OPEN DropCursor;
FETCH NEXT FROM DropCursor INTO @objectName, @indexName, @isPrimaryKey, @isUniqueConstraint;
SET @sql = '';
WHILE @@FETCH_STATUS = 0
BEGIN
IF @isPrimaryKey = 1
SET @sql = @sql + 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT ' + QUOTENAME(@indexName) + ';';
ELSE IF @isUniqueConstraint = 1
SET @sql = @sql + 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT ' + QUOTENAME(@indexName) + ';';
ELSE
SET @sql = @sql + 'DROP INDEX ' + QUOTENAME(@indexName) + ' ON ' + @objectName + ';';
FETCH NEXT FROM DropCursor INTO @objectName, @indexName, @isPrimaryKey, @isUniqueConstraint;
END
CLOSE DropCursor;
DEALLOCATE DropCursor;
-- 执行 DROP
IF LEN(@sql) > 0
BEGIN
PRINT '-- 正在删除依赖的索引和约束...';
EXEC sp_executesql @sql;
PRINT '-- 删除完成。';
END
-- 4. 生成并执行 ALTER COLUMN 脚本
DECLARE AlterCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT
'ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) +
' ALTER COLUMN ' + QUOTENAME(ColumnName) + ' ' +
DataType +
CASE
WHEN DataType IN ('char', 'varchar', 'binary', 'varbinary') THEN
'(' + CASE WHEN MaxLength = -1 THEN 'MAX' ELSE CAST(MaxLength AS VARCHAR(10)) END + ')'
WHEN DataType IN ('nchar', 'nvarchar') THEN
'(' + CASE WHEN MaxLength = -1 THEN 'MAX' ELSE CAST(MaxLength / 2 AS VARCHAR(10)) END + ')'
ELSE ''
END +
' COLLATE DATABASE_DEFAULT ' +
CASE WHEN IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ';'
FROM #ColumnsToFix;
OPEN AlterCursor;
FETCH NEXT FROM AlterCursor INTO @sql;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- 执行: ' + @sql;
EXEC sp_executesql @sql;
FETCH NEXT FROM AlterCursor INTO @sql;
END
CLOSE AlterCursor;
DEALLOCATE AlterCursor;
-- 5. 重建索引/约束
-- 先重建主键和唯一约束（因为它们会影响普通索引）
DECLARE RebuildCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT
ic.ObjectId,
ic.IndexName,
ic.IsPrimaryKey,
ic.IsUniqueConstraint,
ic.IndexType,
ic.FillFactor,
ic.DataSpaceId,
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName
FROM #DependentIndexes ic
JOIN sys.objects o ON ic.ObjectId = o.object_id
ORDER BY ic.IsPrimaryKey DESC, ic.IsUniqueConstraint DESC; -- 先主键/唯一约束
OPEN RebuildCursor;
FETCH NEXT FROM RebuildCursor INTO @ObjectId, @indexName, @isPrimaryKey, @isUniqueConstraint, @indexType, @fillFactor, @DataSpaceId, @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 获取索引列列表
SET @colList = STUFF((
SELECT ', ' + QUOTENAME(c.name) +
CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = @ObjectId AND ic.index_id = (
SELECT index_id FROM sys.indexes WHERE object_id = @ObjectId AND name = @indexName
)
ORDER BY ic.key_ordinal
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
SET @createScript = '';
IF @isPrimaryKey = 1
BEGIN
SET @createScript = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
' ADD CONSTRAINT ' + QUOTENAME(@indexName) +
' PRIMARY KEY ' +
CASE WHEN @indexType = 'CLUSTERED' THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
'(' + @colList + ');';
END
ELSE IF @isUniqueConstraint = 1
BEGIN
SET @createScript = 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
' ADD CONSTRAINT ' + QUOTENAME(@indexName) +
' UNIQUE ' +
CASE WHEN @indexType = 'CLUSTERED' THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
'(' + @colList + ');';
END
ELSE
BEGIN
SET @createScript = 'CREATE ' +
CASE WHEN @indexType = 'CLUSTERED' THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
'INDEX ' + QUOTENAME(@indexName) +
' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
'(' + @colList + ')' +
CASE WHEN @fillFactor BETWEEN 1 AND 99 THEN ' WITH (FILLFACTOR = ' + CAST(@fillFactor AS VARCHAR(3)) + ')' ELSE '' END + ';';
END
PRINT '-- 重建: ' + @createScript;
EXEC sp_executesql @createScript;
FETCH NEXT FROM RebuildCursor INTO @ObjectId, @indexName, @isPrimaryKey, @isUniqueConstraint, @indexType, @fillFactor, @DataSpaceId, @SchemaName, @TableName;
END
CLOSE RebuildCursor;
DEALLOCATE RebuildCursor;
-- 6. 验证结果
PRINT '-- 验证：以下查询应返回 0 行';
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
c.collation_name AS CurrentCollation
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.collation_name IS NOT NULL
AND c.collation_name <> @DatabaseCollation;
PRINT '-- 排序规则统一完成！';
