T-SQL User-Defined Functions: Power and Pitfalls

Three Types of User-Defined Functions

1. Scalar Functions (Return Single Value)

CREATE FUNCTION dbo.CalculateAge (@BirthDate DATE)
RETURNS INT
AS
BEGIN
  RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;

-- Usage
SELECT UserID, Name, dbo.CalculateAge(BirthDate) AS Age
FROM dbo.Users;

-- Problem: Function called PER ROW!
-- 1 million users = 1 million function calls

2. Inline Table-Valued Functions (iTVF)

CREATE FUNCTION dbo.GetActiveUsers (@MinLoginDays INT = 30)
RETURNS TABLE
AS
RETURN
  SELECT UserID, Name, LastLogin
  FROM dbo.Users
  WHERE LastLogin > DATEADD(DAY, -@MinLoginDays, GETDATE());

-- Optimizer PUSHES WHERE into function (fast!)

3. Multi-Statement Table-Valued Functions (mTVF)

CREATE FUNCTION dbo.GetUserStats (@UserID INT)
RETURNS @UserStats TABLE (
  UserID INT,
  OrderCount INT,
  TotalSpent DECIMAL(10,2)
)
AS
BEGIN
  DECLARE @Count INT = (SELECT COUNT(*) FROM dbo.Orders WHERE UserID = @UserID);
  DECLARE @Total DECIMAL(10,2) = (SELECT SUM(Amount) FROM dbo.Orders WHERE UserID = @UserID);

  INSERT @UserStats VALUES (@UserID, @Count, @Total);
  RETURN;
END;

-- Problem: Optimizer cannot inline
-- Function ALWAYS executed completely
-- Data scanned even if not needed

Performance Pitfalls: The Scalar Function Disaster

Scenario: Scalar Function in WHERE Clause

-- Query without function (FAST):
SELECT UserID FROM dbo.Users
WHERE YEAR(BirthDate) = 1990;
-- Execution: Index on BirthDate used, 10 ms

-- Query with scalar function (SLOW):
CREATE FUNCTION dbo.GetBirthYear (@BirthDate DATE) RETURNS INT
AS BEGIN RETURN YEAR(@BirthDate); END;

SELECT UserID FROM dbo.Users
WHERE dbo.GetBirthYear(BirthDate) = 1990;
-- Execution: NO index used
-- Function called for EVERY row (even if 1 million rows)
// Result: 10,000 ms (1000x slower!)

-- Why?
// - Cannot use index (function result unknown)
// - Must evaluate function row-by-row
// - No optimization opportunity

⚠️ NEVER use scalar functions in WHERE clauses!
Always rewrite as:
WHERE BirthDate >= '1990-01-01' AND BirthDate < '1991-01-01'

Scenario: Scalar Function in SELECT List

-- Slow: Called per row
SELECT UserID, dbo.CalculateAge(BirthDate) AS Age
FROM dbo.Users
WHERE Active = 1;
-- 100,000 rows = 100,000 function calls

-- Better: Use column expression
SELECT UserID, DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age
FROM dbo.Users
WHERE Active = 1;
-- Inline math, no function overhead

Deterministic vs. Non-Deterministic

-- DETERMINISTIC: Same input = Same output (always)
CREATE FUNCTION dbo.CalculateAge (@BirthDate DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;
-- Problem: GETDATE() is NOT deterministic!
// Different days = different ages
// Can't be deterministic with GETDATE()

-- Actually DETERMINISTIC:
CREATE FUNCTION dbo.Add (@A INT, @B INT)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN @A + @B;
END;
-- Same inputs ALWAYS produce same output

-- Why matter?
// Deterministic functions can be indexed
// Can be used in computed columns
// Optimizer can cache/optimize better

-- Non-Deterministic: GETDATE(), RAND(), NEWID(), etc.
// Cannot be indexed
// Cannot be used in computed columns

Schema Binding: Protect Dependencies

-- Without SCHEMABINDING:
CREATE FUNCTION dbo.GetUsername (@UserID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
  RETURN (SELECT Name FROM dbo.Users WHERE UserID = @UserID);
END;

-- Someone deletes dbo.Users table
// Function now broken (invalid)
// No warning when deleting table

-- With SCHEMABINDING:
CREATE FUNCTION dbo.GetUsername (@UserID INT)
RETURNS NVARCHAR(100)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT Name FROM dbo.Users WHERE UserID = @UserID);
END;

-- Try to drop dbo.Users table:
-- ERROR: Cannot drop dbo.Users, referenced by function
// Protection! Table cannot be deleted

Inlining (SQL Server 2019+): The Performance Savior

-- SQL Server 2019+: Scalar function inlining

-- Function:
CREATE FUNCTION dbo.CalculateAge (@BirthDate DATE)
RETURNS INT
WITH INLINE = ON  -- Enable inlining
AS
BEGIN
  RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;

-- Query:
SELECT UserID, dbo.CalculateAge(BirthDate) AS Age
FROM dbo.Users;

-- Optimizer REWRITES as:
SELECT UserID, DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age
FROM dbo.Users;
-- Removed function call overhead!

-- Requirements for inlining:
// - SQL Server 2019+
// - Single RETURN statement
// - No complex logic
// - Not accessing tables (can inline simple calculations)

Inlining improves performance dramatically. Scalar functions that were 10x slower can become as fast as inline expressions with inlining enabled.

When to Use Each Type

Type	Performance	Best For	Avoid For
Scalar	Slow (unless 2019+ inlined)	Simple calculations (2019+)	WHERE clause, SELECT list on large datasets
iTVF	Very Fast (inlined by optimizer)	Reusable queries, filtering	Complex multi-step logic
mTVF	Slow (not inlined)	Complex procedural logic	Performance-critical queries

Common Mistakes

Mistake 1: Scalar Function in WHERE

-- ✗ BAD (100x slower):
WHERE dbo.GetYear(BirthDate) = 1990

-- ✓ GOOD (use range):
WHERE BirthDate >= '1990-01-01' AND BirthDate < '1991-01-01'

Mistake 2: Accessing Tables in Scalar Function

-- ✗ BAD (called per row, queries table per row):
CREATE FUNCTION dbo.GetDepartmentName (@DeptID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
  RETURN (SELECT Name FROM dbo.Departments WHERE DeptID = @DeptID);
END;

SELECT UserID, dbo.GetDepartmentName(DeptID) FROM dbo.Users;
-- 10,000 users = 10,000 queries to Departments table!

-- ✓ GOOD (use JOIN):
SELECT u.UserID, d.Name
FROM dbo.Users u
JOIN dbo.Departments d ON u.DeptID = d.DeptID;

Mistake 3: Forgetting Schema Binding

-- ✗ BAD (table can be deleted, function breaks):
CREATE FUNCTION dbo.GetUsername (@UserID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
  RETURN (SELECT Name FROM dbo.Users WHERE UserID = @UserID);
END;

-- ✓ GOOD (table protected):
CREATE FUNCTION dbo.GetUsername (@UserID INT)
RETURNS NVARCHAR(100)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT Name FROM dbo.Users WHERE UserID = @UserID);
END;

Best Practices

☐ Prefer inline table-valued functions (iTVF) over scalar functions.
☐ Never use scalar functions in WHERE clauses. Rewrite as column expressions.
☐ Always use SCHEMABINDING to protect referenced objects.
☐ Avoid mTVF (multi-statement TVF); use iTVF or stored procedures instead.
☐ For SQL Server 2019+, use INLINE = ON on scalar functions when possible.
☐ Don't access tables inside scalar functions. Use JOINs instead.
☐ Test scalar function performance. If SELECT list shows 10x slowdown, rewrite inline.
☐ Document which objects (tables, columns) functions depend on.
☐ Avoid nesting functions (function calling function calling function).
☐ For deterministic calculations, mark with RETURNS NOT NULL.

The Bottom Line

T-SQL functions are powerful for code reuse. But they come with a steep performance cost if used incorrectly.

The rule: inline table-valued functions (fast, inlined by optimizer), avoid scalar functions (slow, call-per-row), and if you must use scalar functions, only in 2019+ with INLINE = ON enabled.

Most "slow query" problems trace back to a scalar function hidden in a WHERE clause. Before adding a function, ask: "Will this be called per row?" If yes, find another way.