Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: What is "deterministic"?

    Per 2005 BOL:

    Determinism
    Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same.
    The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function is deterministic. For example, if the function calls other functions that are non-deterministic, or if the function calls extended stored procedures, then the Database Engine marks the function as non-deterministic. For common language runtime (CLR) functions, the Database Engine relies on the author of the function to mark the function as deterministic or not using the SqlFunction custom attribute.


    Now my question. When wouldn't a function return the same result under these circumstances? When wouldn't any query do this for that matter? What would possibly cause different result sets when the same input parameters are supplied?


    TIA, cfr

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For example a function like this (air code):
    Code:
    CREATE FUNCTION dbo.DaysFromToday
     (
     TheDate AS SmallDateTime
     )
     RETURNS Int
    AS
    BEGIN
     RETURN DATEDIFF(D, TheDate, GetDate())
    END
    The return would depend on when you ran it so it is not deterministic.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Posts
    128
    Doh! That was waaayyy too easy.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've always been fond of:
    Code:
    SELECT Rand()
    because by definition it shouldn't be deterministic.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    I've always been fond of:
    Code:
    SELECT Rand()
    because by definition it shouldn't be deterministic.

    -PatP
    I must confess I don't know how SQL Server handles random number generation but I thought the general programming principle was that random number generation is always deterministic. Or is that your point?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I forget what the deal is here but there is something about RAND that is not random.

    What is deterministic?

    1. When I show up to work I am sure to have a headache by the end of the day.

    2. My boss will shuffle my priorities at least twice a day ensuring that nothing is accomplished to my satisfaction.

    3. The tasks I do not have time to complete will be given to my team mates who will come to me for help with them ensuring I will not have time to complete other tasks.

    4. I will go out on work nights even though I tell myself I will never do it again every morning after I go out on work nights.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by BoL
    The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function. If no seed is provided, the system generates its own variable seed numbers. If you call RAND with a seed value, you must use variable seed values to generate random numbers. If you call RAND multiple times with the same seed value, it returns the same generated value. This script returns the same value for the calls to RAND because they all use the same seed value:

    SELECT RAND(159784)
    SELECT RAND(159784)
    SELECT RAND(159784)
    Ok - didn't know about the self-seeding nature of SQL Server rand. The other bit sounds familiar though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •