多CTE+无主键表变量的OPENQUERY查询失败，添加TOP/主键/单CTE则正常的原因分析

先把你的查询场景整理清楚，方便对照分析：

场景复现 #

❌ 失败的查询（多CTE+无主键表变量+无TOP） #

declare @dates table (dt date)
insert into @dates (dt) values (cast('2025-01-22' as date)),(cast('2025-01-01' as date));
WITH Inner_CTE AS (
 SELECT TRY_CAST(aDate AS DATE) AS aDate, Id as Id
 FROM OPENQUERY ({{linkedServer}}, 'SELECT aDate , Id FROM database.table ')
 WHERE aDate IS NOT NULL AND TRY_CAST(aDate AS DATE) IN (SELECT * FROM @dates)
), Outer_CTE AS (
 SELECT aDate as aDate , Id as Id, DATEADD(day, 1, aDate ) AS tomorrow
 FROM Inner_CTE
)
SELECT ic.aDate , ic.Id as Id, oc.tomorrow, DATEADD(day, 1, oc.tomorrow) AS overmorrow
FROM Outer_CTE oc
JOIN Inner_CTE ic ON oc.aDate = ic.aDate and oc.Id= ic.Id

错误信息：

{{linkedServer}}" returned message "ORA-01403: no data found". Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "{{linkedServer}}

✅ 成功的三种场景 #

1. 表变量添加主键

declare @dates table (dt date primary key)
insert into @dates (dt) values (cast('2025-01-22' as date)),(cast('2025-01-01' as date));
WITH Inner_CTE AS (
 SELECT TRY_CAST(aDate AS DATE) AS aDate, Id as Id
 FROM OPENQUERY ({{linkedServer}}, 'SELECT aDate , Id FROM database.table ')
 WHERE aDate IS NOT NULL AND TRY_CAST(aDate AS DATE) IN (SELECT * FROM @dates)
), Outer_CTE AS (
 SELECT aDate as aDate , Id as Id, DATEADD(day, 1, aDate ) AS tomorrow
 FROM Inner_CTE
)
SELECT ic.aDate , ic.Id as Id, oc.tomorrow, DATEADD(day, 1, oc.tomorrow) AS overmorrow
FROM Outer_CTE oc
JOIN Inner_CTE ic ON oc.aDate = ic.aDate and oc.Id= ic.Id

2. 给Inner_CTE添加TOP

declare @dates table (dt date)
insert into @dates (dt) values (cast('2025-01-22' as date)),(cast('2025-01-01' as date));
WITH Inner_CTE AS (
 SELECT top 1000000 TRY_CAST(aDate AS DATE) AS aDate, Id as Id
 FROM OPENQUERY ({{linkedServer}}, 'SELECT aDate , Id FROM database.table ')
 WHERE aDate IS NOT NULL AND TRY_CAST(aDate AS DATE) IN (SELECT * FROM @dates)
), Outer_CTE AS (
 SELECT aDate as aDate , Id as Id, DATEADD(day, 1, aDate ) AS tomorrow
 FROM Inner_CTE
)
SELECT ic.aDate , ic.Id as Id, oc.tomorrow, DATEADD(day, 1, oc.tomorrow) AS overmorrow
FROM Outer_CTE oc
JOIN Inner_CTE ic ON oc.aDate = ic.aDate and oc.Id= ic.Id

3. 仅使用单个CTE

declare @dates table (dt date)
insert into @dates (dt) values (cast('2025-01-22' as date)),(cast('2025-01-01' as date));
WITH Inner_CTE AS (
 SELECT TRY_CAST(aDate AS DATE) AS aDate, Id AS Id
 FROM OPENQUERY ({{linkedServer}}, 'SELECT aDate , Id FROM database.table ')
 WHERE aDate IS NOT NULL AND TRY_CAST(aDate AS DATE) IN (SELECT * FROM @dates)
)
SELECT aDate as aDate , Id as Id, DATEADD(day, 1, aDate) AS tomorrow
FROM Inner_CTE

核心原因分析 #

1. 失败场景的问题根源 #

当你使用无主键的表变量+多CTE重复引用时，SQL Server查询优化器会踩两个关键的坑：

• 表变量统计信息缺失：无主键的表变量，SQL Server默认会估计它只有1行数据。优化器基于这个错误的估计，会选择把IN (SELECT * FROM @dates)的筛选条件推送到Oracle服务器执行。但TRY_CAST是SQL Server专属函数，Oracle无法识别，推送的筛选条件逻辑完全走样，导致Oracle返回空结果，触发ORA-01403。
• CTE的重复执行：CTE默认是“不物化”的，会被优化器直接展开到主查询里。Inner_CTE被Outer_CTE和最终的JOIN两次引用，优化器会重复执行OPENQUERY两次，第二次执行时Oracle的游标或查询上下文可能出现异常，导致返回无数据。

2. 为什么加TOP N能解决？ #

哪怕你加的TOP N远大于实际行数，这个操作都会给优化器一个明确指令：必须先把OPENQUERY的结果全量拉到SQL Server本地，再做后续处理。

• 这强制优化器放弃“推送筛选到Oracle”的错误计划，转而在本地完成@dates的匹配和CTE计算，彻底避开了跨服务器的函数逻辑差异，自然不会触发错误。

3. 为什么给表变量加主键能解决？ #

给表变量加主键后，SQL Server会发生两个关键变化：

• 生成准确的统计信息，不再把@dates的行数低估为1行，优化器能正确判断数据量。
• 优化器会选择更合理的执行计划：先把OPENQUERY的结果拉到本地，再和@dates做匹配，而不是强行推送筛选条件到Oracle。
• 主键自带的索引也会让@dates的查询效率更高，避免逐行处理时的跨服务器交互异常。

4. 为什么单CTE能解决？ #

单CTE场景下，Inner_CTE只被引用一次，优化器会选择一次性把OPENQUERY的结果拉到本地，完成筛选和计算后直接返回结果，不会因为多次引用而重复执行OPENQUERY，也就避免了跨服务器的多次交互异常。

额外验证 #

你提到把WHERE子句移到OPENQUERY里就正常，这也完全符合上面的分析：当筛选逻辑完全在Oracle端执行时，不存在跨服务器的函数逻辑差异，自然能正确返回数据。但因为@dates是动态的，这种方式不适用，所以用TOP、主键或单CTE的方式，本质都是让SQL Server优化器选择“本地处理”的执行计划，避开跨服务器筛选的坑。

内容来源于stack exchange