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/
>

Keep together not working as expected in .pdf

I have a report with a table in it, the table's fields are filled with
subreports. I would like the data in the table to be kept together
without additional page breaks. What is happening though is multiple
page breaks when a subreport's data is too long to fit on the rest of
the page.
Any suggestions for keeping the data running continuously? THis works
fine when viewing the report in HTML view, it usually only breaks when
exporting to a .pdf.Hi,
You had any luck solving this issue?
Me too having the same problem :-(
Thanks,
Ranjith
"mlfike" wrote:
> I have a report with a table in it, the table's fields are filled with
> subreports. I would like the data in the table to be kept together
> without additional page breaks. What is happening though is multiple
> page breaks when a subreport's data is too long to fit on the rest of
> the page.
> Any suggestions for keeping the data running continuously? THis works
> fine when viewing the report in HTML view, it usually only breaks when
> exporting to a .pdf.
>|||There is an implicit KeepTogether on subreports that will most likely push
them to another page. I don't know of any workarounds other than avoiding
subreports.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ranjith Prakash" <RanjithPrakash@.discussions.microsoft.com> wrote in
message news:7692FB96-7DF5-4FC0-8B29-34CCAECDF885@.microsoft.com...
> Hi,
> You had any luck solving this issue?
> Me too having the same problem :-(
> Thanks,
> Ranjith
> "mlfike" wrote:
>> I have a report with a table in it, the table's fields are filled with
>> subreports. I would like the data in the table to be kept together
>> without additional page breaks. What is happening though is multiple
>> page breaks when a subreport's data is too long to fit on the rest of
>> the page.
>> Any suggestions for keeping the data running continuously? THis works
>> fine when viewing the report in HTML view, it usually only breaks when
>> exporting to a .pdf.
>>|||How can we achieve this KeepTogether if instead of subreports, there is large
data in one of the rows which doesnt fit into a page ? The scenario is that
we don't want it to push it to the next page with space remaining on the
previous one, instead it should "fit-what is possible" and break the rest
down to the next page.
Thanks,
Satwik.
"Brian Welcker [MSFT]" wrote:
> There is an implicit KeepTogether on subreports that will most likely push
> them to another page. I don't know of any workarounds other than avoiding
> subreports.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ranjith Prakash" <RanjithPrakash@.discussions.microsoft.com> wrote in
> message news:7692FB96-7DF5-4FC0-8B29-34CCAECDF885@.microsoft.com...
> > Hi,
> >
> > You had any luck solving this issue?
> > Me too having the same problem :-(
> >
> > Thanks,
> > Ranjith
> >
> > "mlfike" wrote:
> >
> >> I have a report with a table in it, the table's fields are filled with
> >> subreports. I would like the data in the table to be kept together
> >> without additional page breaks. What is happening though is multiple
> >> page breaks when a subreport's data is too long to fit on the rest of
> >> the page.
> >> Any suggestions for keeping the data running continuously? THis works
> >> fine when viewing the report in HTML view, it usually only breaks when
> >> exporting to a .pdf.
> >>
> >>
>
>|||You would need to merge the subreport contents into the main report.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"satwik" <satwik@.discussions.microsoft.com> wrote in message
news:3E5608B4-4290-43A0-845A-6B70C25027B9@.microsoft.com...
> How can we achieve this KeepTogether if instead of subreports, there is
> large
> data in one of the rows which doesnt fit into a page ? The scenario is
> that
> we don't want it to push it to the next page with space remaining on the
> previous one, instead it should "fit-what is possible" and break the rest
> down to the next page.
> Thanks,
> Satwik.
> "Brian Welcker [MSFT]" wrote:
>> There is an implicit KeepTogether on subreports that will most likely
>> push
>> them to another page. I don't know of any workarounds other than avoiding
>> subreports.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ranjith Prakash" <RanjithPrakash@.discussions.microsoft.com> wrote in
>> message news:7692FB96-7DF5-4FC0-8B29-34CCAECDF885@.microsoft.com...
>> > Hi,
>> >
>> > You had any luck solving this issue?
>> > Me too having the same problem :-(
>> >
>> > Thanks,
>> > Ranjith
>> >
>> > "mlfike" wrote:
>> >
>> >> I have a report with a table in it, the table's fields are filled with
>> >> subreports. I would like the data in the table to be kept together
>> >> without additional page breaks. What is happening though is multiple
>> >> page breaks when a subreport's data is too long to fit on the rest of
>> >> the page.
>> >> Any suggestions for keeping the data running continuously? THis works
>> >> fine when viewing the report in HTML view, it usually only breaks when
>> >> exporting to a .pdf.
>> >>
>> >>
>>