Deterministic Functions in SQL

Deterministic functions always return the same result whenever they are called with a specific set of input values provided the database state remains unchanged. Non-deterministic functions may return different results each time they are called with a specific set of input values even if the database state remains unchanged.

In another word, we can predict the output easily for deterministic function where as result from non-deterministic functions is not predictable.

There are few functions that are generally deterministic in nature, however behaves different under certain circumstances. So it is very important to know the behavior. The best example is RAND being deterministic  only when the SEED value is specified or CAST being non-deterministic when used with datetime, smalldatetime, or sql_variant.

Per BOL all of the aggregate and string built-in functions are deterministic and you cannot influence the deterministic behavior of the build in functions. Its important (and interesting) that the GETDATE() is classified as a non-deterministic function. A function being deterministic or non-deterministic decides the capability to have a primary key or a clustered index on computed columns using these functions. 

We could look at the IS_DETERMINISTIC column in INFORMATION_SCHEMA to see if a UDF is deterministic or not. Sometimes we could make it deterministic minor hints. 

CREATE FUNCTION ReturnNum(@num AS BIGINT)
RETURNS DECIMAL
AS
BEGIN
  DECLARE  @val INT
  SET @val=@num/10
  RETURN @val
END
GO
SELECT ROUTINE_NAME,IS_DETERMINISTIC FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ReturnNum'

You will notice that that function is marked as non-deterministic. Lets try to create a table with persisted computed column using this function.

CREATE TABLE dbo.Test
(
  ID int NOT NULL,
  NewID AS dbo.ReturnNum(ID) PERSISTED
)

It will throw an error because the function is not deterministic. 

Msg 4936, Level 16, State 1, Line 1
Computed column 'NewID' in table 'Test' cannot be persisted because the column is non-deterministic.

 You can fix this error easily by using SCHEMABINDING option while creating the function. 

CREATE FUNCTION ReturnNum(@num AS BIGINT)
RETURNS DECIMAL
WITH SCHEMABINDING
AS
BEGIN
  DECLARE  @val INT
  SET @val=@num/10
  RETURN @val
END
GO
CREATE TABLE dbo.Test
(
  ID int NOT NULL,
  NewID AS dbo.ReturnNum(ID) PERSISTED
)
GO
SELECT ROUTINE_NAME,IS_DETERMINISTIC FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ReturnNum'

Here is the list of deterministic function(as listed in BOL)  

ABS

DATEDIFF

POWER

ACOS

DAY

RADIANS

ASIN

DEGREES

ROUND

ATAN

EXP

SIGN

ATN2

FLOOR

SIN

CEILING

ISNULL

SQUARE

COALESCE

ISNUMERIC

SQRT

COS

LOG

TAN

COT

LOG10

YEAR

DATALENGTH

MONTH

 

DATEADD

NULLIF

 

Nondeterministic functions

@@CONNECTIONS

@@TOTAL_WRITE

@@CPU_BUSY

CURRENT_TIMESTAMP

@@DBTS

GETDATE

@@IDLE

GETUTCDATE

@@IO_BUSY

GET_TRANSMISSION_STATUS

@@MAX_CONNECTIONS

MIN_ACTIVE_ROWVERSION

@@PACK_RECEIVED

NEWID

@@PACK_SENT

NEWSEQUENTIALID

@@PACKET_ERRORS

NEXT VALUE FOR

@@TIMETICKS

PARSENAME

@@TOTAL_ERRORS

RAND

@@TOTAL_READ

TEXTPTR

Few exception

Function

Comments

CAST

Deterministic unless used with datetimesmalldatetime, or sql_variant.

CONVERT

Deterministic unless one of these conditions exists:

  • Source type is sql_variant.
  • Target type is sql_variant and its source type is nondeterministic.
  • Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

CHECKSUM

Deterministic, with the exception of CHECKSUM(*).

ISDATE

Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.

RAND

RAND is deterministic only when a seed parameter is specified.

Reference:
http://technet.microsoft.com/en-us/library/ms178091.aspx

1 thought on “Deterministic Functions in SQL”

Leave a Reply

Your email address will not be published. Required fields are marked *