known, expected or weird behavior ?

Hi all,
Please have a look at a script below:
USE tempdb
DECLARE @.t TABLE (c1 uniqueidentifier, c2 uniqueidentifier)
INSERT @.t (c1, c2)
SELECT y.id, NULL
FROM ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5
) x (id)
CROSS JOIN
( SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID()
) y (id)
UPDATE t
SET c2 = y.c2
FROM @.t t,
( SELECT c1, NEWID()
FROM ( SELECT DISTINCT c1
FROM @.t
) x (c1)
) y (c1, c2)
WHERE t.c1 = y.c1
SELECT * FROM @.T
I would expect c2 column value to be the same accross all records where c1
column value is the same.
But in fact c2 column is unique accross the table. I looked at plan and can
see what it's doing and I can rewrite it in a proper way but question
remains, - why is that? Can comeone explain that behavior?
Thank a lot in advance
AlexThat's the way functions work. They are executed for each row of the final
result.
What are you trying to do? If you need uniqueidentifier values for each
integer value, insert them into a temporary table (i.e. table variable)
before issuing the update.
ML
http://milambda.blogspot.com/|||my colleague came accros this piece of code. She reworked it with use of
temp table, and I could find a workaround with derived table. But why the
function is executed in the final set? The code imlies newid() should be
called inside derived table y, and then derived table y joins the table var?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BBDCFC04-63E4-4408-B153-26A1C5A9EB2B@.microsoft.com...
> That's the way functions work. They are executed for each row of the final
> result.
> What are you trying to do? If you need uniqueidentifier values for each
> integer value, insert them into a temporary table (i.e. table variable)
> before issuing the update.
>
> ML
> --
> http://milambda.blogspot.com/|||We'll know this for sure as soon as we find another system function that
produces as random results as NEWID(). :)
Have you tried using a user-defined function that returns a random result?
ML
http://milambda.blogspot.com/|||Interesting, I used RAND() and float instead of NEWID() and
uniqueidentifier, and in this case RAND() was applied to the final result
set too, but the difference seems to be that RAND() was called only once
since ALL records have the same float value.
No, I didn't used UDF yet, maybe later today when I have time.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
> We'll know this for sure as soon as we find another system function that
> produces as random results as NEWID(). :)
> Have you tried using a user-defined function that returns a random result?
>
> ML
> --
> http://milambda.blogspot.com/|||NEWID is special. It is called for every row in a query. All other function
(the I know of) are
called only once in a query. Hence the difference between RAND and NEWID.
USE northwind
SELECT
NEWID() AS myNEWID
,RAND() AS myRand
,CURRENT_TIMESTAMP AS myTS
FROM "Order Details"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <alex_remove_this_mak@.telus.net> wrote in message news:dB6Ef.153384$6K2.43614@.edtnps
90...
> Interesting, I used RAND() and float instead of NEWID() and uniqueidentifi
er, and in this case
> RAND() was applied to the final result set too, but the difference seems t
o be that RAND() was
> called only once since ALL records have the same float value.
> No, I didn't used UDF yet, maybe later today when I have time.
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
>|||This is the expected behaviour of RAND.
ML
http://milambda.blogspot.com/|||I was wondering whether getdate() is called for each row or for the set...
Would it show on a big enough set?
ML
http://milambda.blogspot.com/|||thanks guys a lot
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D44C180C-8F26-461D-A552-9365B4DA27C9@.microsoft.com...
> This is the expected behaviour of RAND.
>
> ML
> --
> http://milambda.blogspot.com/|||Getdate() is normally only called once.
However, Itzik Ben-Gan came up with a really clever workaround for both
rand() and getdate().
Normally, you can't put getdate() or rand() in a User Defined Function, but
you can put them in a view, and then have your function select from the
view. You can then put your function in the select list, to have the
getdate() or rand() regenerated for each row.
Note that it might not look like getdate() is called for every single row,
because of the precision of the datatype. The function might be called
repeatedly more quickly than the getdate() value changes. But if you have
enough rows, you'll see that they aren't ALL the same, even though there
could be duplication.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:30C35A84-706B-48F3-8C41-69ECB598DA13@.microsoft.com...
>I was wondering whether getdate() is called for each row or for the set...
> Would it show on a big enough set?
>
> ML
> --
> http://milambda.blogspot.com/
>