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 considereddeterministic 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 andGETDATE 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 andGETDATE 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.

from http://www.informit.com/articles/article.aspx?p=21329&seqNum=6

posted @ 2014-04-22 01:18  princessd8251  阅读(169)  评论(0)  编辑  收藏  举报