What is Natively Compiled Scalar User-Defined Function(Udf)?

dbtut

5 years ago

We are able to create Scalar User-Defined Function in SQL Server 2016 for Memory Optimized tables. For In Memory OLTP and Memory Optimized Tables, I recommend that you read “In Memory OLTP in SQL Server 2014, SQL Server 2016, SQL Server 2017“.

The TSQL codes in the Natively Compiled Scalar User-Defined Function are converted to native code for better performance.

In our previous article, we said that Memory Optimized Tables are 5-30 times better in performance than Disk Based Tables. When we use Memory Optimized Tables together with the Natively Compiled Stored Procedure and the Natively Compiled Scalar User-Defined Function, the difference is even greater. I suggest you test it to see its speed. You can find the necessary code to create natively compiled scalar user-defined function below.

You may also want to read the article “What is Natively Compiled Stored Procedure in SQL Server”

Create Natively Compiled Scalar User-Defined Function

USE [Test]
GO

CREATE FUNCTION [dbo].[NativelyCompiledFunctionExample](@counter int) RETURNS BIGINT WITH NATIVE_COMPILATION, SCHEMABINDING 
AS 
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') 

DECLARE @result BIGINT=0;
WHILE @counter < 50000
BEGIN
SET @result+=@counter;
SET @counter=@counter+1;
END
RETURN @result;
END

USE [Test]

CREATE FUNCTION [dbo].[NativelyCompiledFunctionExample](@counter int) RETURNS BIGINT WITH NATIVE_COMPILATION, SCHEMABINDING

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')

DECLARE @result BIGINT=0;

WHILE @counter < 50000

BEGIN

SET @result+=@counter;

SET @counter=@counter+1;

END

RETURN @result;

END

Execute Natively Compiled Scalar User-Defined Function

DECLARE @result BIGINT;
SELECT @result=dbo.NativelyCompiledFunctionExample (0)
Select @result;

DECLARE @result BIGINT;

SELECT @result=dbo.NativelyCompiledFunctionExample (0)

Select @result;