存储过程WHERE子句使用SplitStrList2Table函数的执行及临时表影响咨询

Great question—this is a super common pitfall when working with table-valued functions (TVFs) in SQL, so let’s break it down clearly.

Will the function run once per row in the WHERE clause? #

It depends entirely on what kind of table-valued function SplitStrList2Table is:

• Inline Table-Valued Functions (ITVFs): These are defined with a single SELECT statement and act like parameterized views. The SQL optimizer can "unfold" the function’s logic and treat it as a join instead of a per-row call. In most cases, it will execute just once, not per row.
• Multi-Statement Table-Valued Functions (MS TVFs): These use a BEGIN/END block with multiple statements (like declaring variables, inserting into a table variable, etc.). The optimizer treats these as black boxes—each time the function is referenced in the WHERE clause (e.g., in an IN or EXISTS subquery), it will execute once per row of your main query. That’s the dreaded RBAR (Row By Agonizing Row) pattern, which can tank performance on large datasets.

If you’re unsure which type your function is, check its definition: ITVFs skip BEGIN/END and return directly from a SELECT statement.

Does using a temp table make a difference? #

Absolutely—this is the go-to fix to avoid per-row function calls, no matter what type of TVF you’re using. Here’s why:

1. Single execution: You run SplitStrList2Table once, store the results in a temp table (e.g., #SplitValues) or table variable, then join that temp table to your main query. No more repeated function invocations.
2. Better query plans: Temp tables can have statistics and even indexes added to them, which helps the optimizer generate a more efficient join plan than it could with a function buried in the WHERE clause.
3. Predictable performance: Even if your function is an ITVF, using a temp table removes ambiguity about how the optimizer will handle it—you know the split operation only happens once.

Example of "bad" vs "good" approaches: #

Bad (risk of per-row calls):

SELECT *
FROM YourMainTable
WHERE YourColumn IN (
    SELECT Value FROM dbo.SplitStrList2Table(@CommaSeparatedParams)
)

Good (single function execution):

-- First split parameters once and store results
CREATE TABLE #SplitValues (Value INT PRIMARY KEY) -- Add index for faster joins
INSERT INTO #SplitValues
SELECT Value FROM dbo.SplitStrList2Table(@CommaSeparatedParams)

-- Join to your main table
SELECT m.*
FROM YourMainTable m
JOIN #SplitValues s ON m.YourColumn = s.Value

-- Clean up
DROP TABLE #SplitValues

Quick bonus tip #

If you’re using SQL Server 2016 or later, consider replacing your custom split function with the built-in STRING_SPLIT function—it’s an optimized ITVF that’s almost always faster than custom implementations, and the optimizer handles it seamlessly.

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