Scripted Content
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.…
Category: Functions
Software Vendor Mistakes With SQL Server: Using Date Functions On Columns Or Local Variables
Reusable Logic
In most programming languages, it’s quite sensible to create a variable or parameter, use some predefined logic to assign it a value, and then keep reusing it to prevent having to execute the same code over and over …
Software Vendor Mistakes With SQL Server: Using Functions In Join Or Where Clauses
Easy Thinking
To start with, let’s classify functions into two varietals:
- Ones built-in to SQL Server
- Ones that developers write
Of those, built-in functions are pretty much harmless when they’re in the select list. Classifying things a bit further for …
Software Vendor Mistakes With SQL Server: String Splitting With Functions
Advent
Since SQL Server 2016, at least for databases in a similarly current compatibility level, the STRING_SPLIT function has been available. There were some problems with it initially that have recently been solved, too, like a lack of an …
Software Vendor Mistakes With SQL Server: Writing Functions That Already Exist
Reinventing The Sequel
In my time consulting, I’ve seen dbo-prefixed functions that have internal-function names on a number of occasions, acting like a wrapper to the existing functions that come with SQL Server.
While those are fairly ridiculous, and easy …
Software Vendor Mistakes With SQL Server: Not Using Inline Table Valued Functions
Swiss Army
In the last couple posts, I’ve talked about the problems caused by two types of T-SQL functions: Scalar User Defined Functions, and Multi-Statement Table Valued Functions.
I’ve also hinted around about a third type of function, called an …
Software Vendor Mistakes With SQL Server: Multi Statement Table Valued Functions
Available Units
In T-SQL, there are two kinds of “table valued functions”. One of them returns a select as a derived table (Inline Table Valued Function), and the other returns a @table variable as a result (Multi-Statement Table Valued Functions).…
Software Vendor Mistakes With SQL Server: Scalar User Defined Functions
The Very Worst Idea
Scalar UDFs answered a very good question: How do I ruin query performance so that two generations of consultants can make a living off of SQL Server?
In the videos below, which are part of my …
Be Careful Where You Call Inline Table Valued Functions In SQL Server Queries
Keep’em Comin!
While helping someone tame a bunch of rather unfortunate scalar valued functions, we eventually hit a point where they were able to do some of the rewrites themselves. During testing, they ran into a situation where performance got …
A Bug With Recursive UDFs When Inlined In SQL Server 2019
Enough Already
I see people do things like this fairly often with UDFs. I don’t know why. It’s almost like they read a list of best practices and decided the opposite was better.
This is a quite simplified function, but …