MSSQL SQL优化需求：移除CASE WHEN语句的替代方案咨询

Got it, let's tackle this. Your original query uses CASE WHEN to convert potential NULLs from the left join into 0/1 values for primary and foreign key flags. We can simplify this by leveraging built-in functions like ISNULL() instead of CASE, and optionally wrap the subquery in a CTE for better readability. Here are a couple of optimized approaches:

Approach 1: Replace CASE with ISNULL (Simplest Fix) #

This directly swaps out the CASE statements with ISNULL(), which handles the NULL scenario from the left join cleanly:

SELECT 
    C.COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY,
    ISNULL(I.IS_PRIMARY, 0) AS IS_PRIMARY,
    ISNULL(I.IS_FOREIGN, 0) AS IS_FOREIGN
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN (
    SELECT 
        COLUMN_NAME,
        OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') AS IS_PRIMARY,
        OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsForeignKey') AS IS_FOREIGN
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = 'MYTABLE'
) I ON C.COLUMN_NAME = I.COLUMN_NAME
WHERE C.TABLE_NAME = 'MYTABLE'

Why this works: #

When a column isn't part of a primary or foreign key, the left join returns NULL for IS_PRIMARY and IS_FOREIGN. ISNULL() converts those NULLs to 0, while leaving valid 1 values unchanged—exactly the logic your original CASE statements implemented, but with more concise syntax.

Approach 2: Use a CTE for Improved Readability #

If you want to make the query structure more modular and easier to maintain, wrap the key column logic in a CTE:

WITH KeyColumnFlags AS (
    SELECT 
        COLUMN_NAME,
        OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') AS IS_PRIMARY,
        OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsForeignKey') AS IS_FOREIGN
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = 'MYTABLE'
)
SELECT 
    C.COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    IS_NULLABLE,
    COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY,
    ISNULL(kcf.IS_PRIMARY, 0) AS IS_PRIMARY,
    ISNULL(kcf.IS_FOREIGN, 0) AS IS_FOREIGN
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN KeyColumnFlags kcf ON C.COLUMN_NAME = kcf.COLUMN_NAME
WHERE C.TABLE_NAME = 'MYTABLE'

Why this helps: #

The CTE (KeyColumnFlags) isolates the logic for fetching primary/foreign key flags into a named block. This makes the main query easier to scan, and if you ever need to adjust how key columns are identified, you only have to modify the CTE instead of digging into the join clause.

Bonus Alternative (SQL Server 2012+) #

If you're running SQL Server 2012 or later, you could also use IIF() as another concise replacement for CASE:

IIF(I.IS_PRIMARY = 1, 1, 0) AS IS_PRIMARY

That said, ISNULL() is slightly more efficient here since it directly targets the NULL case we're handling, rather than evaluating a conditional.

All these approaches produce identical results to your original query, but with cleaner, more maintainable syntax that removes the need for CASE WHEN.

内容的提问来源于stack exchange，提问作者Brice Chaponneau