Help - Select with newid()

While trying to get a list from joined tables that return 2 GUIDs, I ran
into an interesting gotcha. I'm trying to get 1 guid that's unique for all
the rows returned, and 1 guid that is unique only for each of the child
rows. I _thought_ that could be accomplished using a derived table.
To illustrate, here's a query of the pubs db.
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join ( select title_id, title, newid() as TitleGUID from titles ) T on
sales.title_id = T.title_id
I would hope this would return something like:
StoreA 10 TitleA 100
StoreB 11 TitleA 100
StoreB 12 TitleB 101
StoreC 13 TitleA 100
StoreC 14 TitleC 102
etc...
Instead, both of the GUIDs are unique for each row. In looking at the
execution plan, it appears that the NewId() functions are being resolved at
the parent query.
Is there any way to have the NewId() function within the derived table stay
static? Or, any other ideas on how to generate the results would be
appreciated.
- RickThis works exactly as how it should work. The newid() as TitleStoreGUID
applies to each row in the resultset generated by the join. The newid() as
TitleGUID applies to each row in the resultset generated by titles. So, by
design each call to newid() should generate an unique id. I would be very
troubled if they generate a same id.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"RickT" <rick@.npspamplease.kqrs.com> wrote in message
news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> While trying to get a list from joined tables that return 2 GUIDs, I ran
> into an interesting gotcha. I'm trying to get 1 guid that's unique for
all
> the rows returned, and 1 guid that is unique only for each of the child
> rows. I _thought_ that could be accomplished using a derived table.
> To illustrate, here's a query of the pubs db.
> select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join ( select title_id, title, newid() as TitleGUID from titles ) T on
> sales.title_id = T.title_id
> I would hope this would return something like:
> StoreA 10 TitleA 100
> StoreB 11 TitleA 100
> StoreB 12 TitleB 101
> StoreC 13 TitleA 100
> StoreC 14 TitleC 102
> etc...
> Instead, both of the GUIDs are unique for each row. In looking at the
> execution plan, it appears that the NewId() functions are being resolved
at
> the parent query.
> Is there any way to have the NewId() function within the derived table
stay
> static? Or, any other ideas on how to generate the results would be
> appreciated.
> - Rick
>|||Well, oj, it may work exactly as how it should. But I'm not sure this is
precisely documented or widely known.
There is no question about the uniqueness of TitleStoreGUID. The question is
why TitleGUID is unique even for the same title.
If you look at the query plan, it definitely supports your explanation
because newid() as TutleGUID is indeed evaluated at the outer most layer in
the final resultset. Therefore, we see the uniqueness of its value for each
row, given the nature of newid().
However, I'm struggling to find where in the BOL or in the SQL specs does it
say that we shouldn't expect the subquery to be evaluated first and that the
subsequent evaluation of the query uses the cached resultset. In other
words, why can't it be evaluated as if the derived table is a real tmp table
or a table variable as something similar to the following:
declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
uniqueidentifier)
insert into @.tmp
select title_id, title, newid() as TitleGUID from titles
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join @.tmp T on
sales.title_id = T.title_id
order by title
Maybe this type of subquery is always considered a correlated subquery, and
therefore all this confusion would go away :-)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"oj" <nospam_ojngo@.home.com> wrote in message
news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> This works exactly as how it should work. The newid() as TitleStoreGUID
> applies to each row in the resultset generated by the join. The newid() as
> TitleGUID applies to each row in the resultset generated by titles. So, by
> design each call to newid() should generate an unique id. I would be very
> troubled if they generate a same id.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > While trying to get a list from joined tables that return 2 GUIDs, I ran
> > into an interesting gotcha. I'm trying to get 1 guid that's unique for
> all
> > the rows returned, and 1 guid that is unique only for each of the child
> > rows. I _thought_ that could be accomplished using a derived table.
> >
> > To illustrate, here's a query of the pubs db.
> > select stores.stor_name, newid() as TitleStoreGUID, T.title,
T.TitleGUID
> > from stores
> > join sales on stores.stor_id = sales.stor_id
> > join ( select title_id, title, newid() as TitleGUID from titles ) T
on
> > sales.title_id = T.title_id
> >
> > I would hope this would return something like:
> > StoreA 10 TitleA 100
> > StoreB 11 TitleA 100
> > StoreB 12 TitleB 101
> > StoreC 13 TitleA 100
> > StoreC 14 TitleC 102
> > etc...
> >
> > Instead, both of the GUIDs are unique for each row. In looking at the
> > execution plan, it appears that the NewId() functions are being resolved
> at
> > the parent query.
> >
> > Is there any way to have the NewId() function within the derived table
> stay
> > static? Or, any other ideas on how to generate the results would be
> > appreciated.
> >
> > - Rick
> >
> >
>|||<< Maybe this type of subquery is always considered a correlated subquery,
and therefore all this confusion would go away :-) >>
I've always worked under the belief that what made a _correlated_ subquery
was a reference to an outer query object from within the inner query, thus
causing the behavior of re-evaluation of the inner query for each row of the
outer query.
The #tmp table does work. I could toss the statements:
select title_id, title, newid() as TitleGUID into #Tmp from titles
select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
from stores
join sales on stores.stor_id = sales.stor_id
join #Tmp T on sales.title_id = T.title_id
into a stored procedure. I will if I have to, though I'm hoping the greater
minds in this group might be able to help me with a better way.
TIA,
- Rick
BTW - OJ, your utilities look awesome!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:ujiw6xViDHA.2516@.TK2MSFTNGP09.phx.gbl...
> Well, oj, it may work exactly as how it should. But I'm not sure this is
> precisely documented or widely known.
> There is no question about the uniqueness of TitleStoreGUID. The question
is
> why TitleGUID is unique even for the same title.
> If you look at the query plan, it definitely supports your explanation
> because newid() as TutleGUID is indeed evaluated at the outer most layer
in
> the final resultset. Therefore, we see the uniqueness of its value for
each
> row, given the nature of newid().
> However, I'm struggling to find where in the BOL or in the SQL specs does
it
> say that we shouldn't expect the subquery to be evaluated first and that
the
> subsequent evaluation of the query uses the cached resultset. In other
> words, why can't it be evaluated as if the derived table is a real tmp
table
> or a table variable as something similar to the following:
> declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
> uniqueidentifier)
> insert into @.tmp
> select title_id, title, newid() as TitleGUID from titles
> select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join @.tmp T on
> sales.title_id = T.title_id
> order by title
> Maybe this type of subquery is always considered a correlated subquery,
and
> therefore all this confusion would go away :-)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> > This works exactly as how it should work. The newid() as TitleStoreGUID
> > applies to each row in the resultset generated by the join. The newid()
as
> > TitleGUID applies to each row in the resultset generated by titles. So,
by
> > design each call to newid() should generate an unique id. I would be
very
> > troubled if they generate a same id.
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> > news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > > While trying to get a list from joined tables that return 2 GUIDs, I
ran
> > > into an interesting gotcha. I'm trying to get 1 guid that's unique
for
> > all
> > > the rows returned, and 1 guid that is unique only for each of the
child
> > > rows. I _thought_ that could be accomplished using a derived table.
> > >
> > > To illustrate, here's a query of the pubs db.
> > > select stores.stor_name, newid() as TitleStoreGUID, T.title,
> T.TitleGUID
> > > from stores
> > > join sales on stores.stor_id = sales.stor_id
> > > join ( select title_id, title, newid() as TitleGUID from titles )
T
> on
> > > sales.title_id = T.title_id
> > >
> > > I would hope this would return something like:
> > > StoreA 10 TitleA 100
> > > StoreB 11 TitleA 100
> > > StoreB 12 TitleB 101
> > > StoreC 13 TitleA 100
> > > StoreC 14 TitleC 102
> > > etc...
> > >
> > > Instead, both of the GUIDs are unique for each row. In looking at the
> > > execution plan, it appears that the NewId() functions are being
resolved
> > at
> > > the parent query.
> > >
> > > Is there any way to have the NewId() function within the derived table
> > stay
> > > static? Or, any other ideas on how to generate the results would be
> > > appreciated.
> > >
> > > - Rick
> > >
> > >
> >
> >
>|||RickT;
My understanding is the same as yours.
I'm deliberately cross posting this to the sqlserver.programming group,
hoping that Joe Celko has time to straighten us out :-)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"RickT" <rick@.npspamplease.kqrs.com> wrote in message
news:u1SmlAWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> << Maybe this type of subquery is always considered a correlated subquery,
> and therefore all this confusion would go away :-) >>
> I've always worked under the belief that what made a _correlated_ subquery
> was a reference to an outer query object from within the inner query, thus
> causing the behavior of re-evaluation of the inner query for each row of
the
> outer query.
> The #tmp table does work. I could toss the statements:
> select title_id, title, newid() as TitleGUID into #Tmp from titles
> select stores.stor_name, newid() as TitleStoreGUID, T.title,
T.TitleGUID
> from stores
> join sales on stores.stor_id = sales.stor_id
> join #Tmp T on sales.title_id = T.title_id
> into a stored procedure. I will if I have to, though I'm hoping the
greater
> minds in this group might be able to help me with a better way.
> TIA,
> - Rick
> BTW - OJ, your utilities look awesome!
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:ujiw6xViDHA.2516@.TK2MSFTNGP09.phx.gbl...
> > Well, oj, it may work exactly as how it should. But I'm not sure this is
> > precisely documented or widely known.
> >
> > There is no question about the uniqueness of TitleStoreGUID. The
question
> is
> > why TitleGUID is unique even for the same title.
> >
> > If you look at the query plan, it definitely supports your explanation
> > because newid() as TutleGUID is indeed evaluated at the outer most layer
> in
> > the final resultset. Therefore, we see the uniqueness of its value for
> each
> > row, given the nature of newid().
> >
> > However, I'm struggling to find where in the BOL or in the SQL specs
does
> it
> > say that we shouldn't expect the subquery to be evaluated first and that
> the
> > subsequent evaluation of the query uses the cached resultset. In other
> > words, why can't it be evaluated as if the derived table is a real tmp
> table
> > or a table variable as something similar to the following:
> >
> > declare @.tmp table(title_id varchar(6), title varchar(80), TitleGUID
> > uniqueidentifier)
> > insert into @.tmp
> > select title_id, title, newid() as TitleGUID from titles
> >
> > select stores.stor_name, newid() as TitleStoreGUID, T.title, T.TitleGUID
> > from stores
> > join sales on stores.stor_id = sales.stor_id
> > join @.tmp T on
> > sales.title_id = T.title_id
> > order by title
> >
> > Maybe this type of subquery is always considered a correlated subquery,
> and
> > therefore all this confusion would go away :-)
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "oj" <nospam_ojngo@.home.com> wrote in message
> > news:OwXAFvUiDHA.272@.tk2msftngp13.phx.gbl...
> > > This works exactly as how it should work. The newid() as
TitleStoreGUID
> > > applies to each row in the resultset generated by the join. The
newid()
> as
> > > TitleGUID applies to each row in the resultset generated by titles.
So,
> by
> > > design each call to newid() should generate an unique id. I would be
> very
> > > troubled if they generate a same id.
> > >
> > > --
> > > -oj
> > > RAC v2.2 & QALite!
> > > http://www.rac4sql.net
> > >
> > >
> > > "RickT" <rick@.npspamplease.kqrs.com> wrote in message
> > > news:OP8GwqSiDHA.2504@.TK2MSFTNGP09.phx.gbl...
> > > > While trying to get a list from joined tables that return 2 GUIDs, I
> ran
> > > > into an interesting gotcha. I'm trying to get 1 guid that's unique
> for
> > > all
> > > > the rows returned, and 1 guid that is unique only for each of the
> child
> > > > rows. I _thought_ that could be accomplished using a derived table.
> > > >
> > > > To illustrate, here's a query of the pubs db.
> > > > select stores.stor_name, newid() as TitleStoreGUID, T.title,
> > T.TitleGUID
> > > > from stores
> > > > join sales on stores.stor_id = sales.stor_id
> > > > join ( select title_id, title, newid() as TitleGUID from
titles )
> T
> > on
> > > > sales.title_id = T.title_id
> > > >
> > > > I would hope this would return something like:
> > > > StoreA 10 TitleA 100
> > > > StoreB 11 TitleA 100
> > > > StoreB 12 TitleB 101
> > > > StoreC 13 TitleA 100
> > > > StoreC 14 TitleC 102
> > > > etc...
> > > >
> > > > Instead, both of the GUIDs are unique for each row. In looking at
the
> > > > execution plan, it appears that the NewId() functions are being
> resolved
> > > at
> > > > the parent query.
> > > >
> > > > Is there any way to have the NewId() function within the derived
table
> > > stay
> > > > static? Or, any other ideas on how to generate the results would be
> > > > appreciated.
> > > >
> > > > - Rick
> > > >
> > > >
> > >
> > >
> >
> >
>