1

Topic: NEWID () does not work in correlating request

In advance I am sorry for a preparatory bed-sheet, at me at once a practical problem which I wanted to solve it at a leisure on one entertaining gambling. It was necessary for me  to steam  start hands on 7 casual cards and to count some statistics.
For guaranteed I selected randomnesses old-kind ORDER BY NewID () and here it appeared that it simply does not work in a correlating subquery and a cross connect .
Under a spoiler the temporary table dataful:
[spoiler]

CREATE TABLE #TheDeck (TheCard varchar (40), CMC int)
INSERT INTO #TheDeck SELECT ' Arid Mesa ' , 0
INSERT INTO #TheDeck SELECT ' Arid Mesa ' , 0
INSERT INTO #TheDeck SELECT ' Arid Mesa ' , 0
INSERT INTO #TheDeck SELECT ' Arid Mesa ' , 0
INSERT INTO #TheDeck SELECT ' Bloodstained Mire ' , 0
INSERT INTO #TheDeck SELECT ' Bloodstained Mire ' , 0
INSERT INTO #TheDeck SELECT ' Bloodstained Mire ' , 0
INSERT INTO #TheDeck SELECT ' Inspiring Vantage ' , 0
INSERT INTO #TheDeck SELECT ' Inspiring Vantage ' , 0
INSERT INTO #TheDeck SELECT ' Inspiring Vantage ' , 0
INSERT INTO #TheDeck SELECT ' Inspiring Vantage ' , 0
INSERT INTO #TheDeck SELECT ' Mountain ' , 0
INSERT INTO #TheDeck SELECT ' Mountain ' , 0
INSERT INTO #TheDeck SELECT ' Sacred Foundry ' , 0
INSERT INTO #TheDeck SELECT ' Sacred Foundry ' , 0
INSERT INTO #TheDeck SELECT ' Wooded Foothills ' , 0
INSERT INTO #TheDeck SELECT ' Wooded Foothills ' , 0
INSERT INTO #TheDeck SELECT ' Wooded Foothills ' , 0
INSERT INTO #TheDeck SELECT ' Wooded Foothills ' , 0
INSERT INTO #TheDeck SELECT ' Eidolon of the Great Revel ' , 2
INSERT INTO #TheDeck SELECT ' Eidolon of the Great Revel ' , 2
INSERT INTO #TheDeck SELECT ' Eidolon of the Great Revel ' , 2
INSERT INTO #TheDeck SELECT ' Eidolon of the Great Revel ' , 2
INSERT INTO #TheDeck SELECT ' Goblin Guide ' , 1
INSERT INTO #TheDeck SELECT ' Goblin Guide ' , 1
INSERT INTO #TheDeck SELECT ' Goblin Guide ' , 1
INSERT INTO #TheDeck SELECT ' Goblin Guide ' , 1
INSERT INTO #TheDeck SELECT ' Grim Lavamancer ' , 1
INSERT INTO #TheDeck SELECT ' Monastery Swiftspear ' , 1
INSERT INTO #TheDeck SELECT ' Monastery Swiftspear ' , 1
INSERT INTO #TheDeck SELECT ' Monastery Swiftspear ' , 1
INSERT INTO #TheDeck SELECT ' Monastery Swiftspear ' , 1
INSERT INTO #TheDeck SELECT ' Boros Charm ' , 2
INSERT INTO #TheDeck SELECT ' Boros Charm ' , 2
INSERT INTO #TheDeck SELECT ' Boros Charm ' , 2
INSERT INTO #TheDeck SELECT ' Boros Charm ' , 2
INSERT INTO #TheDeck SELECT ' Lava Spike ' , 1
INSERT INTO #TheDeck SELECT ' Lava Spike ' , 1
INSERT INTO #TheDeck SELECT ' Lava Spike ' , 1
INSERT INTO #TheDeck SELECT ' Lava Spike ' , 1
INSERT INTO #TheDeck SELECT ' Lightning Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Lightning Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Lightning Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Lightning Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Lightning Helix ' , 2
INSERT INTO #TheDeck SELECT ' Lightning Helix ' , 2
INSERT INTO #TheDeck SELECT ' Lightning Helix ' , 2
INSERT INTO #TheDeck SELECT ' Lightning Helix ' , 2
INSERT INTO #TheDeck SELECT ' Rift Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Rift Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Rift Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Rift Bolt ' , 1
INSERT INTO #TheDeck SELECT ' Searing Blaze ' , 2
INSERT INTO #TheDeck SELECT ' Searing Blaze ' , 2
INSERT INTO #TheDeck SELECT ' Searing Blaze ' , 2
INSERT INTO #TheDeck SELECT ' Searing Blaze ' , 2
INSERT INTO #TheDeck SELECT ' Skullcrack ' , 2
INSERT INTO #TheDeck SELECT ' Skullcrack ' , 2
INSERT INTO #TheDeck SELECT ' Skullcrack ' , 2
INSERT INTO #TheDeck SELECT ' Skullcrack ' , 2

[/spoiler]
Turn  on column LandsCount

SELECT TOP 10 object_id,
LandsCount = (SELECT SUM (F.TheLand) FROM
(
SELECT TOP (7) Z.TheCard, Z.CMC, TheLand = CASE WHEN Z.CMC = 0 THEN 1 ELSE 0 END
FROM (
SELECT TheCard, CMC, Case When CMC = 0 THEN 1 ELSE 0 END TheLand
, SortID = NewID ()/*odinakovo for all lines!!! */
FROM #TheDeck) Z ORDER BY Z.SortID
) F)
FROM sys.all_columns

While at more simple t

SELECT TOP 10 object_id, LAMEID = (SELECT LameID = NEWID ()) from sys.all_objects

All works correctly.

2

Re: NEWID () does not work in correlating request

1. Purely idle interest: and what use in this statistics? Statistics NewID () and statistics of gambling  are not connected anywhere.
2. Whether to interpose cards with NewID () into the table is easier.  the pair of millions is a nonsense from all points of view.
3. To construct clustered index and to select on-order seven pieces.
3. And .

3

Re: NEWID () does not work in correlating request

wrote:

2. Whether to interpose cards with NewID () into the table is easier.  the pair of millions is a nonsense from all points of view.
3. To construct clustered index and to select on-order seven pieces.

Before game the pack carefully mixes up. I do not know in what sequence there are cards. A start hand - seven  cards.
Here we approach to a question

wrote:

1. Purely idle interest: and what use in this statistics?

The pack in the given game consists of different types of cards. The main , important for the player - an amount of cards with type the Earth in a start hand. In the professional environment there are disputes apropos in what ratio it is necessary to put the Earth and other cards in a pack, for maximum efficiency of a start hand: 18-42, 19-41, 20-40.
According to me it is necessary to make a little  running to look how many the earths comes to a start hand depending on the above-stated ratio.

4

Re: NEWID () does not work in correlating request

Cammomile;
It is not absolutely clear that you here name "correlating request"...
Besides here an example

declare @t1 table (id1 int)
declare @t3 table (id3 int)
insert @t1 (id1) values (11), (12), (13)
insert @t3 (id3) values (31), (32), (33)
select
id3;
(
select top (1)
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) as w
from
@t3

in which w different, means and newid () inside the different.
Or I did not understand you?

5

Re: NEWID () does not work in correlating request

wrote:

Cammomile;
It is not absolutely clear that you here name "correlating request"...

(SELECT TOP (7) FROM ORDER BY NewID ()) which should be fulfilled for every line input table of type dbo. Numbers

6

Re: NEWID () does not work in correlating request

Cammomile;
That example, what I above resulted corresponds?

7

Re: NEWID () does not work in correlating request

wrote:

Cammomile;
That example, what I above resulted corresponds?

No
In my case here so

declare @t1 table (id1 int)
declare @t3 table (id3 int)
insert @t1 (id1) values (11), (12), (13)
insert @t3 (id3) values (31), (32), (33)
select
id3;
(
SELECt SUM (p) as THE_P_SUM FROM
(
select top (2)
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) DD
) as w
from
@t3

And, as you can see, W it is identical, though should be different

8

Re: NEWID () does not work in correlating request

Cammomile wrote:

it is passed...
No
In my case here so

declare @t1 table (id1 int)
declare @t3 table (id3 int)
insert @t1 (id1) values (11), (12), (13)
insert @t3 (id3) values (31), (32), (33)
select
id3;
(
SELECt SUM (p) as THE_P_SUM FROM
(
select top (2)
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) DD
) as w
from
@t3

And, as you can see, W it is identical, though there should be different

According to plan how many time the subquery w is fulfilled?
It is independent of exterior request FROM @t3.
Means, it can be fulfilled once? If I so and made was the optimizer!

9

Re: NEWID () does not work in correlating request

If to look at the plan that it is visible that the aggregate and then only  to the exterior table at first becomes
Carry out summation outside

declare @t1 table (id1 int)
declare @t3 table (id3 int)
insert @t1 (id1) values (11), (12), (13)
insert @t3 (id3) values (31), (32), (33)
select
a.id3, sum (a.p) as sp
from
(
select
id3;
w.p
from
@t3
cross apply
(
select top (2)
id,
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) as w
) a
group by
a.id3

10

Re: NEWID () does not work in correlating request

Cammomile wrote:

And, as you can see, W it is identical, though different

It should be would be desirable you, that was identical.
Actually a subquery not  for it is invariant rather t3.
In general, compare:

declare @t1 table (id1 int);
declare @t3 table (id3 int);
insert @t1 (id1) values (11), (12), (13);
insert @t3 (id3) values (31), (32), (33);
set statistics xml on;
select
id3;
(
SELECt SUM (p) as THE_P_SUM FROM
(
select top (2)
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) DD
) as w
from
@t3;
select
id3;
(
SELECt SUM (p) as THE_P_SUM FROM
(
select top (2)
case when t3.id3> 0 and q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) DD
) as w
from
@t3 t3;
set statistics xml off;

11

Re: NEWID () does not work in correlating request

wrote:

It would be desirable you, that was identical.
Actually a subquery not  for it is invariant rather t3.
In general, compare:

Well I initially wrote CROSS CONNECT  in which like as it will be explicitly written that it is applied ON every LINE input pattern.
But your words it is simple in a point! When I explicitly ordered usage of the "input" table, all flied up as it is necessary.

12

Re: NEWID () does not work in correlating request

THOUGH there is a question. Why in such behavior results usage SUM ()?
After all if to write simply

SELECT TOP 10 object_id, LameID = (SELECT LameID = NEWID ()) from sys.all_objects

Where, note, LameID too it is not connected with sys.all_objects the optimizer, nevertheless, considers new  on every line.

13

Re: NEWID () does not work in correlating request

Cammomile wrote:

Well I initially wrote CROSS CONNECT  in which like as it will be explicitly written that it is applied ON every LINE input pattern.

Well so "is applied" it is not equivalent "is fulfilled".

Cammomile wrote:

After all if to write simply

SELECT TOP 10 object_id, LameID = (SELECT LameID = NEWID ()) from sys.all_objects

And here there is no subquery. (SELECT LameID = NEWID ()) it will be transformed in Compute Scalar.
Generally, the result depends on the order of connection of tables. Compare

select
id3, a. THE_P_SUM
from
@t3 cross apply
(
SELECt SUM (p) as THE_P_SUM FROM
(
select top (2)
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) DD
) a
option
(force order);
select
id3, a. THE_P_SUM
from
(
SELECt SUM (p) as THE_P_SUM FROM
(
select top (2)
case when q.id1 = 12 then 1 else 0 end as p
from
(select id1, newid () as id from @t1) q
order by q.id
) DD
) a cross apply
@t3
option
(force order);

Your initial request for this example is written in such a manner that to change the connection order it is possible only making a subquery dependent on the table.