Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Deterministic Functions Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, July 26, 2008 7:29 PM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 4, 2016 6:32 AM Points: 2,938, Visits: 830
 Comments posted to this topic are about the item Deterministic Functions Jamie
Post #541464
 Posted Sunday, July 27, 2008 4:17 PM
 SSC Veteran Group: General Forum Members Last Login: Sunday, July 14, 2013 12:34 AM Points: 215, 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. :)Tony..
Post #541559
 Posted Sunday, July 27, 2008 9:41 PM
 SSCrazy Group: General Forum Members Last Login: Wednesday, February 4, 2015 2:43 AM Points: 2,953, 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" :D Urbis, an urban transformation company
Post #541594
 Posted Monday, July 28, 2008 1:37 AM
 SSC-Addicted Group: General Forum Members Last Login: Monday, December 22, 2014 8:09 PM Points: 489, 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"
Post #541642
 Posted Monday, July 28, 2008 3:00 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Tuesday, July 5, 2011 8:18 AM Points: 582, 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!
Post #541670
 Posted Monday, July 28, 2008 5:06 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 4, 2016 6:32 AM Points: 2,938, Visits: 830
 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.aspxBased 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
Post #541765
 Posted Monday, July 28, 2008 5:27 AM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, May 22, 2014 5:50 AM Points: 90, Visits: 103
 No disagreement with specifying a seed causes a deterministic valueHow about this?Set Nocount OnCreate 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 OffSelect Rand() From #TempDrop table #TempShould this be "Deterministic"?Can I tell which value will be displayed FIRST? - NoCan 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.2932494004092580.2932494004092580.2932494004092580.2932494004092580.293249400409258(5 row(s) affected)So seems like "Depends on Definition" to me
Post #541779
 Posted Monday, July 28, 2008 5:43 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 4, 2016 6:32 AM Points: 2,938, Visits: 830
 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) < 100Drop table #Temp Jamie
Post #541787
 Posted Monday, July 28, 2008 5:58 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Tuesday, July 5, 2011 8:18 AM Points: 582, Visits: 359
 http://en.wikipedia.org/wiki/Random_number_generatorgiven 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!
Post #541797
 Posted Monday, July 28, 2008 6:41 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, November 30, 2016 2:37 PM Points: 534, Visits: 1,102
 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. :)I think this question ran aground on the reefs of vagueness...
Post #541836

 Permissions