I must admit, I have done it very often, encapsulate business logic in a function and use it in a select as this example of Erik Darling shows (this is from his Plan Cache Liars series, using sp_BlitzCache).
As a dinosaur dba you have to look a new scary things like AI, Claude, string_agg, inlining logic.
Why? To make it faster, so we save resources for others. This is a nice journey with all ingredients.
USE StackOverflow2013;
GO
EXEC dbo.DropIndexes;
DBCC FREEPROCCACHE;
CREATE OR ALTER FUNCTION dbo.Fake_String_Agg (@UserId INT)
RETURNS NVARCHAR(4000)
WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING
AS
BEGIN
DECLARE @WickedBadIdeaDude NVARCHAR(4000)
SELECT @WickedBadIdeaDude = STUFF((SELECT N', ' + b2.Name
FROM dbo.Badges AS b2
WHERE b2.UserId = @UserId
GROUP BY b2.Name
FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(4000)'), 1, 2, N'')
RETURN @WickedBadIdeaDude
END
GO
CREATE OR ALTER PROC dbo.pcl_ScalarFunction
AS
BEGIN
SELECT TOP (100)
u.DisplayName
, dbo.Fake_String_Agg(u.Id) AS FakeString_Agg
FROM dbo.Users AS u
WHERE u.Reputation > 100000;
END;
EXEC dbo.pcl_ScalarFunction;
If you execute dbo.pcl_ScalarFunction and look and capture the actual execution plan.
This tool can be found on github ( erikdarlingdata/PerformanceStudio: Free, open-source SQL Server execution plan analyzer — cross-platform GUI + CLI with 30 analysis rules, missing index detection, SSMS extension. Built-in MCP server for AI-assisted plan review ) .. so they all start looking at the waitstats: we need more cpu’s, more memory and if we have the company credit card.. faster io. (Or hire Erik or Ronald) and go to the market?!
Just keep it.. and ask Claude the standard question for all queries you have ever written…
Claude do your thing.. and with the correct conclusion.
.. and he did. Now try to do it inline.
CREATE OR ALTER PROC dbo.pcl_ScalarFunctionInline
AS
BEGIN
SELECT TOP (100)
u.DisplayName,
STRING_AGG(CAST(b.Name AS NVARCHAR(MAX)), N', ') WITHIN GROUP (ORDER BY b.Name) FakeString
FROM dbo.Users AS u
LEFT JOIN dbo.Badges AS b
ON b.UserId = u.Id
WHERE u.Reputation > 100000
GROUP BY u.DisplayName, u.Id;
END;
GO
exec dbo.pcl_ScalarFunctionInline
And we get a nice parallel plan.
And in SSMS only one missing index hint, but we know better..
And Claude’s new “conclionastew..” or whatever he mumbles..
In Dutch “Hiep hiep hoera.. ” or for our Norwegian friends “row” (“ro“) repeated after multiple drumbeats.
CREATE NONCLUSTERED INDEX IX_Users_Reputation
ON dbo.Users (Reputation) INCLUDE (DisplayName);
CREATE NONCLUSTERED INDEX IX_Badges_UserId
ON dbo.Badges (UserId) INCLUDE (Name);
Ok, next bottleneck will be.. the sort.
And make the index.
CREATE NONCLUSTERED INDEX IX_Badges_UserId_Name
ON dbo.Badges (UserId, Name);
So I rest my case and change my prompt…
We go back to the shop and sell the cpu’s, memory and … the fast io.