Interview Question of the Week #059 – What are the Limitations of User Defined Functions (UDF) ?

Question: What are the limitations of a user-defined function? A T-SQL function returns a scalar value or a table under restrictions that make it usable within a query. It’s not a stored procedure with a different name.

I prefer interviewing with the team that will work with the candidate. This familiar question often comes up, but repeating a memorized list can hide mistakes. Let’s separate genuine restrictions from things a function can do.

What a Normal T-SQL UDF Cannot Do

• Modify permanent database state, such as updating an application table.
• Call a normal stored procedure, execute dynamic SQL, or use temporary tables. Table variables are allowed.
• Return several independent result sets or use output parameters.
• Use TRY/CATCH or RAISERROR to implement procedure-style error handling.
• Change session options with SET NOCOUNT or SET ROWCOUNT. SET for assigning a local variable is allowed.
• Use certain side-effecting built-ins directly, including NEWID, NEWSEQUENTIALID, RAND and TEXTPTR.

It Can Read a Table and Call GETDATE

Two limits you often hear are wrong: a UDF can read permanent tables, and it can call GETDATE. This scratch example demonstrates both, and returns an XML-typed column too:

-- Use a fresh scratch database. Do not overwrite existing objects.
IF OBJECT_ID(N'dbo.InterviewUdfReadDemo') IS NOT NULL
 OR OBJECT_ID(N'dbo.InterviewUdfAllowedDemo') IS NOT NULL
 THROW 50001,'An object already exists; use a fresh scratch database.',1;
CREATE TABLE dbo.InterviewUdfReadDemo(DemoID int NOT NULL);
INSERT dbo.InterviewUdfReadDemo VALUES(1);
GO
CREATE FUNCTION dbo.InterviewUdfAllowedDemo()
RETURNS TABLE
AS RETURN
 SELECT DemoID,GETDATE() AS ObservedAt,CONVERT(xml,'<demo/>') AS Payload
 FROM dbo.InterviewUdfReadDemo;
GO
SELECT DemoID,ObservedAt,Payload FROM dbo.InterviewUdfAllowedDemo();

The result contains DemoID 1, the observed server time and an XML value. Reading a table is different from changing it. GETDATE makes the expression nondeterministic; allowed doesn’t mean eligible for an indexed expression or scalar-UDF inlining.

A function can also return XML. Returning an XML value is different from the restrictions around a FOR XML clause and its context.

Choose the Interface That Fits the Job

I still use functions to express reusable calculations in SELECT statements. A procedure is the right interface for an operation that must modify data, run dynamic SQL, return several result sets or manage procedural error handling.

Both functions and procedures accept up to 2,100 parameters. On SQL Server 2025, 1,025 and 2,100 function parameters were accepted; 2,101 was rejected with error 180. Avoid designing an interface with thousands of arguments anyway.

A UDF is not a procedure in disguise, it is a read-only calculation, so changes belong in a procedure.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.