SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deterministic Functions


Deterministic Functions

Author
Message
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3896 Visits: 859
Comments posted to this topic are about the item Deterministic Functions

Jamie
AntonDeski
AntonDeski
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 33
Have to quibble with the answer. Yes, RAND() on its own is non-deterministic, as SS randomly supplies the seed. But, for a single connection, if RAND(seed) has been called then a second RAND() will use the same seed and is therefore deterministic.

My two bob. Smile

Tony..
Iggy-SQL
Iggy-SQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3253 Visits: 440
I agree with Tony. If a seed is already specified, all subsequent call to the function RAND() in the same connection will be deterministic (i.e., same number). So that's why I think the answer should be "depends on definition" BigGrin


Urbis, an urban transformation company
brewmanz
brewmanz
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 406
Ditto Ditto - the answer's quoted text is WRONG WRONG WRONG - or at least only partially correct/ very misleading! Gimme my point back! I happen to understand how this function works very well.
Check ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/363c84d6-b9fa-49ba-9a75-e44f27535ff6.htm "For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers."
So, is a particular RAND() deterministic without using a seed? IT DEPENDS on whether a PREVIOUS RAND() used a seed - so IT DEPENDS on definition of "deterministic"
Ben Leighton
Ben Leighton
SSC Eights!
SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)

Group: General Forum Members
Points: 866 Visits: 359
Am in there with the nay-sayers... For a single connection... RANd() behaves deterministically if following a seeded definition. Give us our points!
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3896 Visits: 859
The question is a quote from the Microsoft documentation. but has a second purpose here. See the article written by two Microsoft researchers. http://msdn.microsoft.com/en-us/library/cc441928.aspx

Based on the fact that RAND is deterministic when used with a seed, they are able to get a speedy sample from huge tables using the NEWID() function in conjunction with the RAND function. If it weren't deterministic, the sample would not work. Attempt it on a table with several million records. The sampling is extremely fast. The algorithm is faster if the keycol1 refers to a clustered ID table.

SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM
(keycol1, NEWID())) as int))
% 100) < 10

Jamie
jims-723592
jims-723592
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 103
No disagreement with specifying a seed causes a deterministic value

How about this?
Set Nocount On
Create Table #Temp
(
Val int
)
Insert Into #Temp values (1)
Insert Into #Temp values (2)
Insert Into #Temp values (3)
Insert Into #Temp values (4)
Insert Into #Temp values (5)
Set Nocount Off
Select Rand() From #Temp

Drop table #Temp

Should this be "Deterministic"?
Can I tell which value will be displayed FIRST? - No
Can I tell which value will be displayed SECOND - Yes, same as first!. To me, this is deterministic.

Output, for those that didn't try:

----------------------
0.293249400409258
0.293249400409258
0.293249400409258
0.293249400409258
0.293249400409258

(5 row(s) affected)

So seems like "Depends on Definition" to me
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3896 Visits: 859
Try it this way:

Create Table #Temp
(
UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(), Val int
)
Insert Into #Temp (val) values(1)
Insert Into #Temp (val) values(2)
Insert Into #Temp (val) values(3)
Insert Into #Temp (val) values(4)
Insert Into #Temp (val) values(5)

Set Nocount Off
SELECT * FROM #Temp
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 100

Drop table #Temp

Jamie
Ben Leighton
Ben Leighton
SSC Eights!
SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)

Group: General Forum Members
Points: 866 Visits: 359
http://en.wikipedia.org/wiki/Random_number_generator

given that there is still considerable debate as to the "True Randomness" of computer generated random number algorithms it would be reasonable to assume that there are definitions of "Deterministic" that vary (ever so slightly) from Microsoft's. To summarise, whether RAND is deterministic or not "depends on the definition" of deterministic that one is using. Thus logic dictates that the third option (depends on the definition) is correct.

The points please!
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2462 Visits: 1383
Technically speaking RAND() is only pseudo-random, thus it is by definition always deterministic.

Now, if the question were "Does the RAND() function create a fixed series of numbers only when a seed parameter is specified" the answer would be yes. However, given the current seed value (the current value within RAND() itself) then RAND() is always deterministic, given that RAND uses an algorithm to calculate the result!

"No" is the truly correct answer, although "depends on definition" obviously is a strong contender. Smile

I think this question ran aground on the reefs of vagueness...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search