November 26, 2008 at 10:13 am
Because set order is not guaranteed prior to your ORDER BY, and a RAND() or NEWID() scalar is going to be different with every execution thereof, we appear to end up getting whichever record was chosen that happened to match up with the one execution. I've had 3 consecutive executions end up choosing the same record, but never more than that so far, but I'm not going to spend any more time on it, as I can see no reason to really NEED to NOT have a workaround. Of course, that's just me, so take that with a grain of salt, as it were...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 1, 2008 at 1:21 pm
Here's the official MS answer :
Hi, Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if there are workarounds. See the below KB article for more details: http://support.microsoft.com/kb/287515 The ONLY guaranteed mechanism are the following: 1. Use cursor to loop through the rows in specific order and concatenate the values 2. Use for xml query with ORDER BY to generate the concatenated values 3. Use CLR aggregate (this will not work with ORDER BY clause) -- Umachandar, SQL Programmability Team
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply