使用临时表传递超1000个SQL IN参数遇问题及tempdb空间不足求助

为解决SQL IN子句参数超过1000个的限制，我创建了临时表存储所需账号数据：

drop table if EXISTS #k_temp_table
select i.account_value
into #k_temp_table
from 
(
    select distinct acnt.account_value
    from tablea a 
    join tableb b  on b.account_key = a.account_key
    outer apply fngetaccount(a.account_key) acnt
    where b.period_end_dt > '2016-12-31'
    group by acnt.identifier_value 
)i

select * from #k_temp_table 
-- 返回约4万条目标账号数据

遇到两个核心问题：

1. 将临时表作为子查询放入CTE时，脚本持续运行但无结果返回：

with Total as (
   select xyz...
   from abc
   where abc.account_value in (select account_value from #k_temp_table)
)

2. 尝试其他方案时触发tempdb空间不足错误：

Could not allocate space for object 'dbo.SORT temporary run storage: 155990860234752' in database 'tempdb' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

因仅拥有数据库只读权限，无法执行select * from tempdb.sys.allocation_units查看分配单元限制，需寻求可行解决方法。

针对CTE无结果返回的问题 #

• 用JOIN替代IN子句：IN子句处理大数据集时性能远不如JOIN，尤其是主表数据量较大时，替换后可避免子查询的性能瓶颈：

with Total as (
   select abc.xyz...
   from abc
   inner join #k_temp_table kt on abc.account_value = kt.account_value
)

• 给临时表添加索引：原临时表无索引，查询时会触发全表扫描，给account_value加非聚集索引可大幅提升匹配效率：

drop table if EXISTS #k_temp_table
select i.account_value
into #k_temp_table
from 
(
    select distinct acnt.account_value
    from tablea a 
    join tableb b  on b.account_key = a.account_key
    outer apply fngetaccount(a.account_key) acnt
    where b.period_end_dt > '2016-12-31'
    group by acnt.identifier_value 
)i

-- 添加非聚集索引
create nonclustered index idx_k_temp_account on #k_temp_table(account_value)

• 简化临时表生成逻辑：原逻辑同时用了distinct和group by，属于冗余去重，可合并逻辑减少计算量：

drop table if EXISTS #k_temp_table
select acnt.account_value
into #k_temp_table
from tablea a 
join tableb b  on b.account_key = a.account_key
outer apply fngetaccount(a.account_key) acnt
where b.period_end_dt > '2016-12-31'
group by acnt.account_value

针对tempdb空间不足的问题 #

• 缩减临时表数据量：过滤掉account_value为NULL的无效记录，减少存储占用：

drop table if EXISTS #k_temp_table
select acnt.account_value
into #k_temp_table
from tablea a 
join tableb b  on b.account_key = a.account_key
outer apply fngetaccount(a.account_key) acnt
where b.period_end_dt > '2016-12-31'
and acnt.account_value is not null
group by acnt.account_value

• 移除不必要的排序操作：ORDER BY、GROUP BY等操作会大量占用tempdb空间，检查查询逻辑，去掉非必需的排序，或仅在小数据集上执行排序。
• 改用表变量替代临时表：表变量（@k_temp_table）的存储行为与临时表不同，4万条数据规模下可降低tempdb压力，同时给表变量加主键自动生成索引：

declare @k_temp_table table(account_value varchar(50) primary key)

insert into @k_temp_table
select acnt.account_value
from tablea a 
join tableb b  on b.account_key = a.account_key
outer apply fngetaccount(a.account_key) acnt
where b.period_end_dt > '2016-12-31'
group by acnt.account_value

• 拆分查询分批处理：若主表数据量极大，将查询按账号范围拆分为多个小批次执行，避免一次性占用大量tempdb资源。

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