Scalar Function Blocking

Erik Darling notes that scalar functions can cause multi-table blocking:

Someone had tried to be clever. Looking at the code running, if you’ve been practicing SQL Server for a while, usually means one thing.

A Scalar Valued Function was running!

In this case, here’s what it looked like:

1 2 3 4 5 6 7 8 9 10 11 12

CREATE OR ALTER FUNCTION dbo.BadIdea ( @uid INT ) RETURNS BIGINT WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING AS     BEGIN         DECLARE @BCount BIGINT;         SELECT   @BCount = COUNT_BIG(*)         FROM     dbo.Badges AS b         WHERE    b.UserId = @uid         GROUP BY b.UserId;         RETURN @BCount;     END;

Someone had added that function as a computed column to the Users table:

Spoilers:  this was a bad idea.