SQL SERVER – Deterministic Functions and Nondeterministic Functions

Deterministic functions always returns the same output result all the time it is executed for same input values. i.e. ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions may return different results each time they are executed. i.e. NEWID, RAND, @@CPU_BUSY etc. Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Function
Previous Post
SQL SERVER – 2005 – Forced Parameterization and Simple Parameterization – T-SQL and SSMS
Next Post
SQL SERVER – 2005 – Get Current User – Get Logged In User

Related Posts

6 Comments. Leave new

  • Hello Pinal

    can i have more example of non deterministic function which are taking parameters.

  • jagadeeswararao.chapp
    August 11, 2011 12:07 pm

    i have on doubt?
    can we call a non determenistic funtion in our user defined function..if not why?

  • jagadeeswararao.chapp
    August 11, 2011 12:08 pm

    can we call a non determenistic funtion from our user defined function..if not why?

  • I’m working on loading data into tables using some stored-procedures as a model for doing so. In one stored-procedure is this variable assignment

    SELECT TOP 1 @RowD = RowID FROM NameofTable WHERE ReferenceID = @ReferenceID;

    Glancing over it I did not notice anything of significance. Then I put it into practice in the script that is loading data. When testing I immediately saw it was not returning what I was expecting it to return. Based on what the original SP was doing I was expecting it to return the highest ID. But it wasn’t Then I realized there is no ORDER BY clause. To me this is saying the SELECT statement is non-deterministic. When the tests were run the result was the same value, but that value was neither the lowest or highest value.

    I just though this is a good example of expecting something but not getting because of a missing ORDER BY clause

  • select OBJECTPROPERTY(OBJECT_ID(‘dbo.AddNumbers’), ‘IsDeterministic’); return 0. The AddNumbers function simply adds two numbers. it is deterministic function. then why objectproperty function is returning zero instead of one.


Leave a Reply