Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make SQL Memory Optimized Native Compiled Function Deterministic

Here is a really, really simple function, it comes back deterministic. If I compile it native it's no longer deterministic. How can I make it native compiled and deterministic?

CREATE FUNCTION [hash].[HashDelimiter2]()
RETURNS NCHAR(1)
WITH SCHEMABINDING
AS BEGIN
        RETURN N';'
END
GO

/* This does indeed result in YES */
SELECT IS_DETERMINISTIC
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'HashDelimiter2'



/* But then compile it native and it's no longer deterministic */
CREATE FUNCTION [hash].[HashDelimiter3]()
RETURNS NCHAR(1)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    RETURN N';'
END
GO

/* This results in NO */
SELECT IS_DETERMINISTIC
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'HashDelimiter3'
like image 492
Aaron Stainback Avatar asked Aug 16 '16 22:08

Aaron Stainback


People also ask

How do I create a memory optimized table in SQL Server?

Create a memory-optimized data filegroup and add a container to the filegroup. Create memory-optimized tables and indexes. For more information, see CREATE TABLE (Transact-SQL). Load data into the memory-optimized table and update statistics after loading the data and before creating the compiled stored procedures.

What is a memory optimized table in SQL Server?

What are Memory Optimized Tables? A Memory Optimized Table, starting in SQL Server 2014, is simply a table that has two copies, one in active memory and one durable on disk whether that includes data or just Schema Only, which I will explain later.


1 Answers

only a guess, I do not have 2016 to test it,
but I will try to change:

RETURN N';'

TO

DECLARE @R AS NCHAR(1) = N';'
RETURN @R

I think the problem is that N';' is created as NVARCHAR(1) and then implicit converted to NCHAR(1) so maybe the conversion make it non deterministic.

like image 96
MtwStark Avatar answered Oct 05 '22 03:10

MtwStark