Today's question (by Steve Jones - SSC Editor):

The Countrows Result

SOME ROWS = COUNTROWS(RELATEDTABLE(OrderLine))

Think you know the answer? Click here, and find out if you are right.

Inlining Scalar UDFs

Scalar user defined functions (UDFs) can cause performance issues that can often be difficult to troubleshoot. Microsoft introduced a new 2019 feature called T-SQL Scalar UDF Inlining that can improve performance of some of these functions without changing any of your code. Which of the functions can take advantage of this feature? (choose 3)

--Function 1
CREATE OR ALTER FUNCTION dbo.GetRating_1(@CustomerID INT) 
RETURNS VARCHAR(20) 
AS 
BEGIN 
DECLARE @Rating VARCHAR(20);
SELECT @Rating = Rating 
FROM Customer 
WHERE CustomerID = @CustomerID;

RETURN @Rating;
END
GO

CREATE OR ALTER FUNCTION dbo.GetRating_2(@CustomerID INT) 
RETURNS VARCHAR(20) 
AS 
BEGIN 
DECLARE @Rating varchar(20);
IF @CustomerID % 4 = 0 BEGIN 
SET @Rating = 'Diamond';
END
ELSE IF @CustomerID % 4 = 1 BEGIN
SET @Rating = 'Gold';
END
ELSE IF @CustomerID % 4 = 2 BEGIN 
SET @Rating = 'Silver';
END
ELSE IF @CustomerID % 4 = 3 BEGIN 
SET @Rating = 'Bronze';
END

RETURN @Rating;
END

GO

--Function 3
CREATE OR ALTER FUNCTION dbo.GetRating_3(@CustomerID INT) 
RETURNS VARCHAR(20) 
AS 
BEGIN 
DECLARE @Rating varchar(20);
DECLARE @RatingID INT;

SELECT @RatingID = RatingID 
FROM Customer
WHERE CustomerID = @CustomerID;

IF @RatingID = 0 BEGIN 
SET @Rating = 'Diamond';
END
ELSE IF @RatingID = 1 BEGIN
SET @Rating = 'Gold';
END
ELSE IF @RatingID = 2 BEGIN 
SET @Rating = 'Silver';
END
ELSE IF @RatingID = 3 BEGIN 
SET @Rating = 'Bronze';
END

RETURN @Rating;
END


GO

--Function 4
CREATE OR ALTER FUNCTION dbo.GetRating_4(@CustomerID INT)
RETURNS VARCHAR(20)
AS
BEGIN
   DECLARE @Ratings TABLE(RatingID INT, RatingDesc  varchar(20));
   DECLARE @Rating VARCHAR(20);

   INSERT INTO @Ratings(RatingID, RatingDesc)
   SELECT RatingID, RatingDesc
   FROM Rating;

   SELECT @Rating = R.RatingDesc
   FROM Customer  AS C
   JOIN @Ratings  AS R ON C.RatingID = R.RatingID
   WHERE CustomerID = @CustomerID;

   RETURN @Rating;

END

GO

--Function 5
CREATE OR ALTER FUNCTION dbo.GetRating_5(@RatingID INT) 
RETURNS VARCHAR(20) 
AS 
BEGIN 

DECLARE @ID INT = 0;
DECLARE @Rating varchar(20);

WHILE @ID <> @RatingID BEGIN 
SET @ID = @ID + 1;
END;

SELECT @Rating = RatingDesc 
FROM Rating 
WHERE RatingID = @ID;

RETURN @Rating;
END

Answer: Function 1, Function 2, Function 3

Explanation: The functions found in answers a, b, and c take advantage of inlining. It doesn’t work when you have a loop or a table variable in the function. You can also use the is_inlineable column of sys.sql_module DMV to find out.

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, sm.is_inlineable
FROM sys.sql_modules AS sm 
JOIN sys.objects AS o ON sm.object_id = o.object_id 
WHERE o.type_desc = 'SQL_SCALAR_FUNCTION';

Reference:

• https://www.red-gate.com/simple-talk/sql/performance/get-your-scalar-udfs-to-run-faster-without-code-changes/
• https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.