SQL Server - Scalar UDFs vs Inline

Scalar UDFs are good if you're working with a handful of rows or a variable.  However, when you throw a lot of rows at them, they generally slow down your queries.Microsoft recommends inlining them whenever possible.  Microsoft Website. Let's see if they are right.

Let's say we have to Proper Case a set of rows, just because where we live, if you don't Proper Case, you're an outcast.

Step 1 - Create table

CREATE TABLE dbo.ProperCasing

(

    IDCol int not null identity primary key,

    StringCol varchar(500)

)

Step 2 - Add a ton of rows

INSERT INTO dbo.ProperCasing

(StringCol)

SELECT ('This object has the name ' + OBJECT_NAME(Object_Id))

FROM sys.Objects

GO 1000

-- Scalar UDF to ProperCase a string that works in 2016, 2017, etc

-- You can use STRING_AGG in 2017+ and use STUFF in 2016

Step 3 - Create Scalar UDF to ProperCase a string

-- You can use STRING_AGG in 2017+ and STUFF in 2016

CREATE FUNCTION dbo.ProperCase

(    

    @vch_Input varchar(1000) 

)

RETURNS varchar(1000)

AS

BEGIN

    DECLARE @vch_Output varchar(1000);

    SELECT @vch_Output = STRING_AGG(UPPER(LEFT(T.Value, 1)) + LOWER(RIGHT(T.Value, LEN(T.Value)-1)), ' ')

    FROM    (SELECT Value FROM String_Split(@vch_Input, ' ')) T

    RETURN (@vch_Output);

END

Step 4 - Query using Scalar UDF

SELECT IDCol, dbo.ProperCase (StringCol) AS ProperCased 

FROM dbo.ProperCasing 

ORDER BY IDCol

Output

1 This Object Has The Name Sysrscols

2 This Object Has The Name Sysrowsets

3 This Object Has The Name Sysclones

4 This Object Has The Name Sysallocunits

... and 1000s more rows

Time taken     = 3 seconds on my laptop

Rows fetched = 109,826

Execution Plan

Step 5 - Query using Inline System Functions

SELECT IDCol, STRING_AGG(UPPER(LEFT(Value, 1)) + LOWER(RIGHT(Value, LEN(Value)-1)), ' ') X

FROM dbo.ProperCasing

CROSS APPLY String_Split(StringCol, ' ')

GROUP BY IDCol

ORDER BY IDCol

Time taken     = < 1 second on my laptop

Rows fetched = 109,826

Execution Plan