Cross apply and newid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross apply and newid

Author

Topic

virzak
Starting Member

4 Posts

Posted - 2008-04-28 : 16:37:45

Hi,

Why am I getting a different numbers of distinct ids in those queries?


USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';

select locations.*, T.c.value('.','nvarchar(max)') from 
(
	select newid() as Id
    from Production.ProductModel
    where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);

select mytable.*    , T.c.value('.','nvarchar(max)') from 
(
	select newid() as Id
    union
    select newid()
) as mytable cross apply @myXml.nodes('(/lol)') T(c);

Thanks,

Victor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-29 : 00:12:57

Since you are using newid() function it generates a new unique identifier each time so you get different set of unique ids. May i know what you are trying to achieve here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 04:34:34

I get same number of records for both queries (4 records).
First SELECT gives me 4 different GUIDs and second query gives me 2 different GUIDs.

First query

Id					(No column name)
62D85DA8-F68A-4E09-A367-108C642BEF34	omg
B7BE729B-5759-403A-9F93-A6B2279C9C04	omg
578BCDBA-B254-47E8-88FA-6380BAC99CBC	rofl
397A8FFD-A3BC-4951-B92A-141B39AFB726	rofl

Second query

Id					(No column name)
8D4BD4D8-AB92-4E0D-9539-762325679280	omg
8D4BD4D8-AB92-4E0D-9539-762325679280	rofl
D0AE37DE-320B-46CA-A817-8E195335D67A	omg
D0AE37DE-320B-46CA-A817-8E195335D67A	rofl

E 12°55'05.25"
N 56°04'39.16"

virzak
Starting Member

4 Posts

Posted - 2008-04-29 : 07:49:04

Peso, my question is *why* there are 4 and 2 distinct ids in first and second query respectively. If you try running the inner queries, there are only 2 rows in both cases. How come after a cross apply we see different behavior?

visakh16, the key word in my previous post is *distinct*.
What I'm ultimately trying to achieve here is more complicated:
select Instructions from Production.ProductModel where ProductModelID = 7
Production.ProductModel has different instructions.
If you inspect the XML you'll notice that there are multiple location nodes which contain step nodes.
I want to get a location per row and concatinate the steps for that location.

So I would like to have the following results:
Id Location Steps
-----------------------------------------------------------------
7 Work Center 10 Insert..., Attach..., Using..., Insert...
7 Work Center 20 ...
7 Work Center 30 ...
7 Work Center 45 ...
7 Work Center 50 ...
7 Work Center 60 ...
10 Work Center 10 ...
10 Work Center 20 ...
...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 07:54:46

I think this is by behaviour.

For first query, you are combining 4 records.
For second query, you are combining 2 records.

E 12°55'05.25"
N 56°04'39.16"

virzak
Starting Member

4 Posts

Posted - 2008-04-29 : 08:30:55

I don't agree.
In both cases I generate newid() only for 2 records.
Then I combine it with 2 other records that don't have IDs.
I don't see why I should be getting 4 distinct IDs.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 08:40:20

No, for first query you are creating 4 GUIDs due to CROSS APPLY.
When running second query, there is no real relationship between the data.

Having that said we can investigate the execution plans.
And yes, they are completely different!

Green are same, red are different

First query


  |--Compute Scalar(DEFINE:([Expr1003]=newid(), [Expr1020]=[Expr1020]))
       |--Nested Loops(Inner Join)
            |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id]))
            |    |--Filter(WHERE:(STARTUP EXPR([@myXml] IS NOT NULL)))
            |    |    |--Table-valued function
            |    |--Stream Aggregate(DEFINE:([Expr1020]=MIN(CASE WHEN [@myXml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1017])>=(128) THEN CONVERT_IMPLICIT(nvarchar(max),[Expr1018],0) ELSE CONVERT_IMPLICIT(nvarchar(max),[Expr1017],0) END END)))
            |         |--UDX((XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1011], XML Reader.[id]))
            |              |--Compute Scalar(DEFINE:([Expr1011]=0x58))
            |                   |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader.[id], [Expr1023]))
            |                        |--Compute Scalar(DEFINE:([Expr1023]=getdescendantlimit(XML Reader.[id])))
            |                        |    |--Table-valued function
            |                        |--Table-valued function
            |--Row Count Spool
                 |--Clustered Index Seek(OBJECT:([AdventureWorks].[Production].[ProductModel].[PK_ProductModel_ProductModelID]), SEEK:([AdventureWorks].[Production].[ProductModel].[ProductModelID]=(7) OR [AdventureWorks].[Production].[ProductModel].[ProductModelID]=(8)) ORDERED FORWARD)

Second query


  |--Compute Scalar(DEFINE:([Expr1019]=[Expr1019]))
       |--Nested Loops(Inner Join)
            |--Merge Join(Union)
            |    |--Compute Scalar(DEFINE:([Expr1000]=newid()))
            |    |    |--Constant Scan
            |    |--Compute Scalar(DEFINE:([Expr1001]=newid()))
            |         |--Constant Scan
            |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id]))
                 |--Filter(WHERE:(STARTUP EXPR([@myXml] IS NOT NULL)))
                 |    |--Table-valued function
                 |--Stream Aggregate(DEFINE:([Expr1019]=MIN(CASE WHEN [@myXml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1016])>=(128) THEN CONVERT_IMPLICIT(nvarchar(max),[Expr1017],0) ELSE CONVERT_IMPLICIT(nvarchar(max),[Expr1016],0) END END)))
                      |--UDX((XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1010], XML Reader.[id]))
                           |--Compute Scalar(DEFINE:([Expr1010]=0x58))
                                |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader.[id], [Expr1022]))
                                     |--Compute Scalar(DEFINE:([Expr1022]=getdescendantlimit(XML Reader.[id])))
                                     |    |--Table-valued function
                                     |--Table-valued function

virzak
Starting Member

4 Posts

Posted - 2008-04-29 : 09:13:56

I would expect those results to be correct if I ran the following query:

Declare @myXml as xml
set @myXml = '<lol>omg</lol><lol>rofl</lol>';

select newid() as Id, T.c.value('.','nvarchar(max)') from
(
select 1 as one
from Production.ProductModel
where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);

newid() is selected for every row...
But I can't see any relationship between the first and second table in any query. My second table is static and uses nothing from the first table.

It seems to me that the implementation/optimization is incorrect.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 09:32:55

Then you better hurry and open a case at PSS.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources