在Transact-SQL的WHERE子句中定义变量复用至SELECT可行吗？

Absolutely! This is a common optimization scenario in T-SQL, and there are a few clean, reliable ways to avoid repeating expensive XML conversion operations across your WHERE and SELECT clauses. Here are the most practical approaches:

1. Use CROSS APPLY (or OUTER APPLY) to Precompute the Conversion #

This is my go-to method for multi-row scenarios. CROSS APPLY lets you calculate the converted XML value once per row, then reference that result in both your filter and selection logic.

Example: #

Original (redundant conversion):

SELECT
    Id,
    XmlData.value('(/Root/Value)[1]', 'NVARCHAR(100)') AS ExtractedValue
FROM
    YourTable
WHERE
    XmlData.value('(/Root/Value)[1]', 'NVARCHAR(100)') = 'TargetValue'

Optimized (single conversion per row):

SELECT
    t.Id,
    x.ExtractedValue
FROM
    YourTable t
CROSS APPLY (
    -- Compute the conversion once here
    SELECT t.XmlData.value('(/Root/Value)[1]', 'NVARCHAR(100)') AS ExtractedValue
) x
WHERE
    x.ExtractedValue = 'TargetValue'

The query optimizer will recognize that ExtractedValue is computed once and reused, eliminating the duplicate conversion overhead. Use OUTER APPLY instead if you need to retain rows where the XML conversion might return NULL.

2. Use a CTE (Common Table Expression) #

CTEs work similarly to APPLY for precomputing values, and they can make your query more readable if you have multiple derived values to calculate.

Example: #

WITH PrecomputedXml AS (
    SELECT
        Id,
        XmlData.value('(/Root/Value)[1]', 'NVARCHAR(100)') AS ExtractedValue
    FROM
        YourTable
)
SELECT
    Id,
    ExtractedValue
FROM
    PrecomputedXml
WHERE
    ExtractedValue = 'TargetValue'

Just like with APPLY, the conversion happens once during the CTE execution, and the result is reused in the subsequent SELECT and WHERE clauses.

3. Use Variables for Single-Row Scenarios #

If you're working with a single row (e.g., filtering by a unique ID), you can store the converted value in a variable first, then reference it in both your filter and selection.

Example: #

DECLARE @ExtractedValue NVARCHAR(100);

-- Compute the conversion once and store it
SELECT @ExtractedValue = XmlData.value('(/Root/Value)[1]', 'NVARCHAR(100)')
FROM YourTable
WHERE Id = 123;

-- Use the variable in both WHERE and SELECT
SELECT
    Id,
    @ExtractedValue AS ExtractedValue
FROM
    YourTable
WHERE
    Id = 123 AND @ExtractedValue = 'TargetValue';

This is great for isolated single-row queries, but not ideal for multi-row datasets.

A Quick Note on Query Optimizer Behavior #

While SQL Server's optimizer sometimes detects and eliminates redundant expressions, relying on this isn't reliable—especially with complex XML operations (like XQuery queries or large XML documents). Explicitly precomputing the value with APPLY or a CTE ensures you avoid duplicate overhead every time.

内容的提问来源于stack exchange，提问作者Brad Nelson