If you use this script, you can follow along with the results.
The sys.dm_exec_function_stats DMV is only available in SQL Server 2016+, so if you’re on an earlier version than that, you should focus your energies on upgrading.
I’m sure you’ve just been busy for the last five years.
/*Context is everything*/ USE master; GO /*Piece of crap function*/ CREATE OR ALTER FUNCTION dbo.useless_function(@dbid int) RETURNS sysname AS BEGIN DECLARE /*This is to ruin scalar UDF inlining*/ @gd datetime = GETDATE(); RETURN DB_NAME(@dbid); END; GO /*Just in the select list*/ SELECT database_name = dbo.useless_function(d.database_id), total_databases = (SELECT c = COUNT_BIG(*) FROM sys.databases AS d2) FROM sys.databases AS d; GO /*Executions here should match the count above*/ SELECT object_name = OBJECT_NAME(defs.object_id), defs.execution_count FROM sys.dm_exec_function_stats AS defs WHERE defs.object_id = OBJECT_ID('dbo.useless_function'); /*Reset the counters*/ DBCC FREEPROCCACHE; GO /*Now in the where clause*/ SELECT mf.name, total_database_files = (SELECT c = COUNT_BIG(*) FROM sys.master_files AS mf2) FROM sys.master_files AS mf WHERE mf.name = dbo.useless_function(mf.database_id) GO /*Executions here should match the count above*/ SELECT object_name = OBJECT_NAME(defs.object_id), defs.execution_count FROM sys.dm_exec_function_stats AS defs WHERE defs.object_id = OBJECT_ID('dbo.useless_function');
On my server, I have 9 total databases and 42 total database files.
The results, therefore, look precisely and consistently like so:
For the select list, T-SQL scalar UDFs will execute once per row projected by the query, e.g. the final resulting row count, under… Every circumstance I’ve ever seen.
In SQL server. Of course.
As a couple easy-to-digest examples. Let’s say you execute a query that returns 100 rows:
- Your T-SQL scalar UDF is referenced once in the select list, so it’ll run 100 times
- Your T-SQL scalar UDF is referenced twice in the select list, so it’ll run 200 times
For T-SQL scalar UDFs in other parts of a query, like:
- Where Clause
- Join Clause
They will execute for as many rows need to be filtered when these parts of the query are executed, for as many individual references to the function as there are in the query.
The results here may vary, depending on if there are any other predicates involved that may filter out other rows.
As a couple easy-to-digest examples:
- If you use a T-SQL scalar UDF as a predicate on a million row table, it’ll execute a million times to produce a result and apply the predicate
- If you do the same thing as above, but there’s another part of the where clause that filters out 500k rows, the function will only execute 500k times
All sorts of other things might change this, like if the other predicate(s) can be pushed to when the data is accessed, and if there are multiple invocations of the function.
You can see an edge case where that’s not true in this post:
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Software Vendor Mistakes With SQL Server: Using Date Functions On Columns Or Local Variables
- Software Vendor Mistakes With SQL Server: Using Functions In Join Or Where Clauses
- Software Vendor Mistakes With SQL Server: Writing Functions That Already Exist
- Software Vendor Mistakes With SQL Server: Not Using Inline Table Valued Functions
One thought on “Proving That SQL Server T-SQL Scalar UDFs Execute Once Per Row”
Comments are closed.