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'
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 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With