SQL Server Scalar UDF vs Multi-Statement TVF Execution
Short answer
The EXEC statement is invalid for scalar UDFs; use SELECT or SET instead. Multi-statement TVFs often cause inconsistent execution plans due to inaccurate row estimates. Switching to an inline TVF improves performance.
Question
You have a SQL database in Microsoft Fabric that contains the following functions:
A multi-statement table-valued function (TVF) named Sales.mstvf_OrderStatus() that returns order status information
A scalar user-defined function (UDF) named dbo.ufn_GetTaxMultiplier (@TaxAmt money, @StateCode char(2)) that returns a numeric multiplier used in tax calculations
Reporting queries frequently join Sales.mstvf_OrderStatus() to a table named Sales.SalesOrderHeader and return large result sets. A performance review shows that the queries produce inconsistent execution plans.
During a code review, a developer discovers that the following Transact-SQL statement produced an error.
EXEC @ret = ufn_GetTaxMultiplier @TaxAmt = 100.00, @StateCode = ‘WA’;
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
NOTE: Each correct selection is worth one point.
Key Takeaways
Scalar functions cannot be called with EXEC; they require SELECT or SET. Multi-statement TVFs hinder optimization by providing poor cardinality estimates, unlike inline TVFs.
Learn why EXEC fails for scalar UDFs and how multi-statement TVFs lead to inconsistent execution plans in SQL Server, with solutions using SELECT/SET and inline TVFs.
Common mistake: Learners often mistakenly believe EXEC works for scalar functions or that schema prefixes fix syntax errors. Others fail to recognize that multi-statement TVFs, despite being valid, are the root cause of erratic execution plans.
Expert Analysis
This scenario tests the distinction between Scalar User-Defined Functions (UDFs) and Table-Valued Functions (TVFs) in T-SQL, specifically regarding their invocation syntax and execution plan behavior.
1. Scalar UDF Invocation Syntax:
The code review error EXEC @ret = ufn_GetTaxMultiplier... is a classic trap. In T-SQL, scalar functions are not executed using the EXEC command. They must be invoked within a SELECT statement or assigned via a SET variable. The correct syntax would be: SELECT dbo.ufn_GetTaxMultiplier(100.00, 'WA'). Therefore, the statement "The EXEC statement is valid" is No.
2. Multi-Statement TVF Performance:
The question states that queries joining Sales.mstvf_OrderStatus produce inconsistent execution plans. This is a well-known characteristic of multi-statement table-valued functions (mSTVFs). Unlike inline TVFs (iTVFs), which are expanded into the outer query allowing the optimizer to see the full picture, mSTVFs are treated as black boxes. The SQL Server Optimizer typically assumes they return a fixed number of rows (often estimating 100 rows regardless of actual data volume), leading to poor cardinality estimates and suboptimal, inconsistent plans. While adding the dbo. schema prefix is good practice for resolution, it does not fix the fundamental performance issue of the mSTVF itself. However, the question asks if specific statements are true. Without seeing the exact statements in the image, we infer based on common exam patterns:
- If the statement is about using
EXECfor the scalar function: No. - If the statement is about the mSTVF causing plan inconsistency due to row estimation issues: Yes.
- If the statement suggests switching to an Inline TVF to fix the plan inconsistency: Yes.
Given the comment mentioning "one should not use [EXEC]... but it can be used... adding dbo will fix also," this reflects a misunderstanding. EXEC cannot be used for scalar UDFs at all; schema qualification only resolves name ambiguity, not syntax errors. The core insight is that scalar UDFs require SELECT/SET, while multi-statement TVFs cause optimization issues.
For the specific HOTSPOT items likely present:
- Can you use EXEC for the scalar UDF? No. It causes a syntax error.
- Does the mSTVF cause inconsistent plans? Yes, due to fixed row estimates.
- Is the current code correct? No.
Exam Strategy
When encountering HOTSPOT questions on T-SQL objects, immediately verify the invocation syntax: Scalar UDFs need SELECT/SET, iTVFs/mSTVFs are used in FROM clauses. For performance issues, distinguish between inline (optimizer-friendly) and multi-statement (black-box, poor estimates) TVFs. Always check if the error is syntactic (wrong keyword like EXEC) or logical (performance degradation).
Frequently Asked Questions
Can I use EXEC to call a scalar user-defined function?
Why do multi-statement TVFs cause inconsistent execution plans?
Discussion 2 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 2
-
Sr18 👍 1I mean, one should not use. But it can be used and adding dbo. schema will fix the issue also. So YES, YES, YES
-
pmajo 👍 1I think last should be NO, you should not use EXEC with user-definied function; you should use SELECT or SET.
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.