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
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
Execute Natively Compiled Scalar User-Defined Function
DECLARE @result BIGINT;
SELECT @result=dbo.NativelyCompiledFunctionExample (0)