Performance differences in SQL Server inline table functions based on parameter passing methods

Data and Databases
1

I’m dealing with a weird performance issue in SQL Server and need help understanding what’s happening. I have an inline table function that behaves completely differently depending on how I pass parameters to it.

When I call the function with literal values like this:

SELECT empID 
FROM dbo.getEmployeeData('2020', 'ACTIVE')

It takes forever to run (about 17 seconds). But when I use declared variables:

DECLARE @yearParam varchar(4) = '2020'
DECLARE @statusParam varchar(12) = 'ACTIVE'

SELECT empID 
FROM dbo.getEmployeeData(@yearParam, @statusParam)

It runs super fast in just milliseconds. Same exact data and parameters but totally different performance.

I think SQL Server is creating different execution plans but I don’t understand why this happens or how to fix it. Has anyone seen this before? How can I make sure it always uses the fast execution plan?

Here’s my function code:

CREATE FUNCTION [dbo].[getEmployeeData]
(
    @yearParam varchar(4) = '%',
    @statusParam varchar(10) = '%'
)
RETURNS TABLE
AS
RETURN
(
    SELECT e.empID, e.lastName, e.firstName, e.department,
           d.deptName, d.location, e.hireDate, e.salary
    FROM dbo.tblEmployees AS e
    LEFT JOIN dbo.getCurrentDepartments('MAIN') AS dept ON e.empID = dept.empID
    INNER JOIN dbo.getStatusHistory(@yearParam) AS s ON e.empID = s.empID
    WHERE (s.statusCode LIKE @statusParam)
      AND e.empID NOT IN (
          SELECT empID FROM dbo.tblTerminated 
          WHERE termYear = @yearParam AND reason = 'TRANSFER'
      )
)
2

that’s really interesting! i’ve seen similar weirdness with nested function calls like yours. can you check the actual execution plans for both scenarios? also curious - does this performance difference happen consistently or only after certain operations? sometimes clearing the plan cache with DBCC FREEPROCCACHE can help identify if it’s really a plan issue.

3

This happens because of SQL Server’s parameter sniffing during compilation. When you pass literal values directly, the optimizer builds an execution plan based on those specific values and their stats. But inline table functions often struggle with this optimization, especially with nested functions. The variable approach runs faster because it forces SQL Server to create a more generic execution plan. For better performance, consider using OPTION (OPTIMIZE FOR UNKNOWN) or restructuring the function to avoid nested calls. Alternatively, using dynamic SQL in a stored procedure may give you more control over execution plans.

4

yeah, i totally get where ur coming from. I’ve struggled with those nested calls too. sql server can get all mixed up with optimization when using literals. you shld try adding OPTION(RECOMPILE) to force it to create new plans or maybe combine those functions into a single query.