不同排序规则（AI/AS）的表执行EXCEPT对比报错，求解决方案

The error you're hitting comes down to a simple rule: the EXCEPT operation requires all matching columns from your two queries to use identical collation rules. Since your tables use conflicting settings (Arabic_CI_AS vs Arabic_100_CI_AI), SQL Server can't reliably compare string values between them. Here are two practical fixes to resolve this:

1. Explicitly Standardize Collation in the Query #

You can force one side of the EXCEPT to match the collation of the other using the COLLATE clause directly in your select statement. Choose whichever collation aligns with your comparison needs (either match t1's rule to t2's, or vice versa):

Option A: Convert t2's columns to match t1's collation #

SELECT ID, FIRST, FATHER, GRAND FROM t1
EXCEPT
SELECT 
    ID, 
    FIRST COLLATE Arabic_CI_AS, 
    FATHER COLLATE Arabic_CI_AS, 
    GRAND COLLATE Arabic_CI_AS 
FROM t2;

Option B: Convert t1's columns to match t2's collation #

SELECT 
    ID, 
    FIRST COLLATE Arabic_100_CI_AI, 
    FATHER COLLATE Arabic_100_CI_AI, 
    GRAND COLLATE Arabic_100_CI_AI 
FROM t1
EXCEPT
SELECT ID, FIRST, FATHER, GRAND FROM t2;

2. Permanently Align Table Column Collations #

If you’ll run this comparison regularly, it’s better to make the collations match at the table level to avoid repeating the COLLATE clause every time. For example, to update t2's columns to use t1's collation:

-- Replace VARCHAR(50) with your actual column data type and length
ALTER TABLE t2
ALTER COLUMN FIRST VARCHAR(50) COLLATE Arabic_CI_AS NOT NULL;

ALTER TABLE t2
ALTER COLUMN FATHER VARCHAR(50) COLLATE Arabic_CI_AS NOT NULL;

ALTER TABLE t2
ALTER COLUMN GRAND VARCHAR(50) COLLATE Arabic_CI_AS NOT NULL;

Note: Adjust the data type/length and NOT NULL constraint to match your actual table schema. Always back up your data before making schema changes.

Why This Happens #

Collation rules dictate how string values are compared and sorted. AS (accent-sensitive) treats accented characters as distinct from their non-accented equivalents, while AI (accent-insensitive) does not. For EXCEPT to work correctly, SQL Server needs a consistent set of rules to determine whether two rows are identical.

内容的提问来源于stack exchange，提问作者Baban Jamal