Home > Articles > Data > SQL Server

  • Print
  • + Share This
From the author of

Function Determinism

All functions that exist in SQL Server are either deterministic or nondeterministic. The determinism of a function is defined by the data that is returned by the function. The following outlines the determinism of a function:

  • A function is considered deterministic if it always returns the same result set when it's called with the same set of input values.

  • A function is considered nondeterministic if it doesn't return the same result set when it's called with the same set of input values.

This might sound somewhat complicated, but it really isn't. Take, for example, the DATEDIFF and GETDATE functions. DATEDIFF is deterministic because it will always return the same data every time it's run with the same input parameters. GETDATE is nondeterministic because it will never return the same date every time it's run. Look at the code in Listing 7. This code repeats the DATEDIFF and GETDATE function 10 times, waiting 1 second in between each execution.

Listing 7  Function Determinism

SET NOCOUNT ON

DECLARE    @intCounter    INT
DECLARE    @vchGETDATE    VARCHAR(32)
DECLARE    @intDATEDIFF   INT

SELECT    @intCounter = 0

WHILE (@intCounter <= 10)
BEGIN
    SELECT   @vchGETDATE = CONVERT(VARCHAR(32), GETDATE(), 109)
    SELECT   @intDATEDIFF = DATEDIFF(dd, '1/1/2000', '1/2/2000')
    PRINT    '--------------------------'
    PRINT    @vchGETDATE
    PRINT    @intDATEDIFF
    SELECT   @intCounter = @intCounter + 1
    WAITFOR DELAY '00:00:01'

END

Previous versions of SQL Server didn't care about the determinism of the functions that were used. SQL Server 2000 has introduced functionality that depends on the determinism of the functions contained in them. Nondeterministic functions can't be used in two specific types of SQL statements:

  • You can't create an index on a computed column if the expression contained in the computed column references a nondeterministic function.

  • You can't create a clustered index on a view if the view references any nondeterministic functions.

User-Defined Function Determinism

When you create a user-defined function, SQL Server records the determinism. The determinism of a user-defined function is determined in how you create the function. A user-defined function is considered deterministic if all the following criteria is met:

  • The function is schema-bound to all database objects that it references.

  • Any functions called by the user-defined function are deterministic. This includes all user-defined and system functions.

  • The function doesn't reference any database objects that are outside its scope. That means the function can't reference any outside tables, variables, or cursors.

When you create a function, SQL Server applies all these criteria to the function to determine its determinism. If a function doesn't pass any one of these checks, the function is marked as nondeterministic. These checks can sometimes produce functions marked as nondeterministic even when you expect them to be marked as deterministic.

Determinism of System Functions

Because you can't modify the functions that are installed with SQL Server, you can't do anything to change the determinism of these functions. You must be aware of the determinism of functions so that you can control the determinism of any functions that you create.

The following system functions are deterministic:

    All string functions

    ISNULL

    All aggregate functions

    ISNUMERIC

    ABS

    LOG

    ACOS

    LOG10

    ASIN

    MONTH

    ATAN

    NULLIF

    ATN2

    PARSENAME

    CEILING

    PATINDEX

    COALESCE

    POWER

    COS

    RADIANS

    COT

    ROUND

    DATALENGTH

    SIGN

    DATEADD

    SIN

    DATEDIFF

    SQUARE

    DAY

    SQRT

    DEGREES

    TAN

    EXP

    YEAR

    FLOOR

Several functions can be either nondeterministic or deterministic, depending on the data types that they are being used with. You can use these functions in indexes on computed columns and indexed views when they are used in a deterministic way. These functions are as follows:

  • CAST is considered deterministic unless it's used with the datetime, smalldatetime, or sql_variant data types.

  • CONVERT is considered deterministic unless it's used with the datetime, smalldatetime, or sql_variant data types. The function can be considered deterministic when used with the datetime and smalldatetime data types if you also use a style parameter.

  • CHECKSUM is deterministic in all cases except the CHECKSUM(*) operation.

  • ISDATE is deterministic if a style parameter is specified and it's not the 0, 100, 9, or 109 style.

  • RAND is deterministic if you specify a seed value.

Most system functions are always nondeterministic, no matter how they are used. The following functions are always nondeterministic:

    All configuration functions

    STATS_DATE

    All cursor functions

    SYSTEM_USER

    All metadata functions

    TEXTPTR

    All statistical functions

    TEXTVALID

    All security functions

    USER_NAME

    @@ERROR

    GETANSINULL

    @@IDENTITY

    GETDATE

    @@ROWCOUNT

    HOST_ID

    @@TRANCOUNT

    HOST_NAME

    APP_NAME

    IDENT_INCR

    CURRENT_TIMESTAMP

    IDENT_SEED

    CURRENT_USER

    IDENTITY

    DATENAME

    NEWID

    FORMATMESSAGE

    PERMISSIONS

    SESSION_USER

These lists will help you figure out the determinism of system functions so that you can establish the determinism of any functions you are creating.

  • + Share This
  • 🔖 Save To Your Account