Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 |
Execute Natively Compiled Scalar User-Defined Function
1 2 3 | DECLARE @result BIGINT; SELECT @result=dbo.NativelyCompiledFunctionExample (0) Select @result; |
