SQL Server 2019 Scalar User-Defined Functions

Jiayi Yang SQL Server • UDF 2019年3月7日

USE [JSSUG_DEV]
GO

--INLINEできるFunctionを確認する
SELECT *
FROM sys.sql_modules

--INLINE化有効の確認
SELECT *
FROM sys.database_scoped_configurations

--UDF INLINE CONFIGUARTION
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON
GO

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF
GO

--CREATE FUNCTION(計算のみ)
CREATE FUNCTION dbo.Fn_DiscountPrice(@price MONEY, @discount FLOAT) 
RETURNS FLOAT AS
BEGIN
	RETURN @price * (1 - @discount / 100);
END

--UDF検証(GROUP BYはINLINE化されないが、SQL2019では並列処理を行ってくれる)
SET STATISTICS IO, TIME ON
SELECT Storerkey,[Day],SKU,SUM([dbo].Fn_DiscountPrice(Price,Discount)) AS DiscountPrice
FROM ITEMS
GROUP BY Storerkey,[Day],SKU
--OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))
--OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')) --SQL Server 2017
--OPTION (MAXDOP 1)
SET STATISTICS IO, TIME OFF

--CREATE FUNCTION(計算のみ)
CREATE FUNCTION dbo.Fn_GetOrderCode(@sku VARCHAR(7)) 
RETURNS VARCHAR(4) AS
BEGIN
	DECLARE @OrderCode VARCHAR(4) = ''

	SELECT TOP 1 @OrderCode = [ALTSKU]
	FROM [ITEMSKU]
	WHERE [SKU] = @sku
	AND [ALTSKU] != ''
	ORDER BY [ALTSKU]

	RETURN @OrderCode
END

--UDF検証(ほかのテーブルからデータを取り出す)
SET STATISTICS IO, TIME ON
SELECT [dbo].[Fn_GetOrderCode](SKU)
FROM [ITEMS]
--OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))
SET STATISTICS IO, TIME OFF

DROP FUNCTION dbo.Fn_DiscountPrice
DROP FUNCTION dbo.Fn_GetOrderCode

USE [JSSUG_DEV]

--INLINEできるFunctionを確認する

SELECT *

FROM sys.sql_modules

--INLINE化有効の確認

SELECT *

FROM sys.database_scoped_configurations

--UDF INLINE CONFIGUARTION

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF

--CREATE FUNCTION(計算のみ)

CREATE FUNCTION dbo.Fn_DiscountPrice(@price MONEY, @discount FLOAT)

RETURNS FLOAT AS

BEGIN

RETURN @price * (1 - @discount / 100);

END

--UDF検証(GROUP BYはINLINE化されないが、SQL2019では並列処理を行ってくれる)

SET STATISTICS IO, TIME ON

SELECT Storerkey,[Day],SKU,SUM([dbo].Fn_DiscountPrice(Price,Discount)) AS DiscountPrice

FROM ITEMS

GROUP BY Storerkey,[Day],SKU

--OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))

--OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')) --SQL Server 2017

--OPTION (MAXDOP 1)

SET STATISTICS IO, TIME OFF

--CREATE FUNCTION(計算のみ)

CREATE FUNCTION dbo.Fn_GetOrderCode(@sku VARCHAR(7))

RETURNS VARCHAR(4) AS

BEGIN

DECLARE @OrderCode VARCHAR(4) = ''

SELECT TOP 1 @OrderCode = [ALTSKU]

FROM [ITEMSKU]

WHERE [SKU] = @sku

AND [ALTSKU] != ''

ORDER BY [ALTSKU]

RETURN @OrderCode

END

--UDF検証(ほかのテーブルからデータを取り出す)

SET STATISTICS IO, TIME ON

SELECT [dbo].[Fn_GetOrderCode](SKU)

FROM [ITEMS]

--OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))

SET STATISTICS IO, TIME OFF

DROP FUNCTION dbo.Fn_DiscountPrice

DROP FUNCTION dbo.Fn_GetOrderCode