Here's a fun one! :)

Question

Post reply

Here's a fun one! :)

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

DavidT SSCommitted Points: 1727 More actions · Answer 1

In my keenness to post a reply, I neglected to join in the 'debate'

The query uses a cross join

select a, b, c, d, e

from testC ,(select cast(newId() as varchar(50)) as GUID) as B

Order By GUID

It's intersting to note that using ansi complient coding:

select a, b, c, d, e

from testC

cross join (select cast(newId() as varchar(50)) as GUID) as B

Order By GUID

does not produce a random result, presumerabley because it creates a table with one row first before

cross joining.

But cross joining the (old?) style way obviously creates a new GUID for each row joined.

Another thing is that if you don't cast the GUID to a varchar, it will return the "total length to long" error.

Wonder why this makes a difference?

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Interesting approach there David. I would have probably done exactly what sjcsystems suggested with PK or unique value and did a temp table to order and join to. However David's approach has the same effect without that need, so interesting as it even covers if non-uique values exist.

However what would I do if my base query was

SELECT * FROM TestC, TestC As TblA

as this will blow up Davids method right off.

My only point is be carefull with some answers, altough I love the NEWID() method there are factors you must also consider that will lead to various issues.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 3

joshcsmith13

Hall of Fame

Points: 3381

January 17, 2003 at 10:51 am

#446665

WooHoo! We have a flaming folder!

Antares686 SSC Guru Points: 125444 More actions · Answer 4

quote:
In my keenness to post a reply, I neglected to join in the 'debate'
The query uses a cross join
select a, b, c, d, e
from testC ,(select cast(newId() as varchar(50)) as GUID) as B
Order By GUID
It's intersting to note that using ansi complient coding:
select a, b, c, d, e
from testC
cross join (select cast(newId() as varchar(50)) as GUID) as B
Order By GUID
does not produce a random result, presumerabley because it creates a table with one row first before
cross joining.
But cross joining the (old?) style way obviously creates a new GUID for each row joined.
Another thing is that if you don't cast the GUID to a varchar, it will return the "total length to long" error.
Wonder why this makes a difference?

It is curious why, and the execution plan shows something interesting.

Non-ANSI complient

|--Sort(ORDER BY:([Expr1002] ASC))

|--Compute Scalar(DEFINE:([Expr1002]=Convert(newid())))

|--Table Scan(OBJECT:([testDb].[dbo].[testC]))

ANSI Complient

|--Compute Scalar(DEFINE:([Expr1002]=Convert(newid())))

|--Nested Loops(Inner Join)

|--Constant Scan

|--Table Scan(OBJECT:([testDb].[dbo].[testC]))

Right off I notice the execution plan doesn't even containt the Order By. Maybe a bug? I will have to pass this finding along to MS to see if they can shed some light or if they will just call it a "feature" .

Antares686 SSC Guru Points: 125444 More actions · Answer 5

quote:
WooHoo! We have a flaming folder!

Glad I could help you out there.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 6

but seriously, folks, this is great stuff! Thanks for your input!

btw. I'm voting for "Feature".

DavidT SSCommitted Points: 1727 More actions · Answer 7

So Antares686

It sounds like you had an idea up your sleeve as to how to order a full rowed table like:

SELECT * FROM TestC, TestC As TblA

so,what was it, any clues...?

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Nope just throwing things out that could push the Row width larger that 8K which an Order By generates a Temp work table in tempDB in many cases and it falls under the 8k width limit. Just being an antagonist on request and to show be carefull with some concepts as they work under a few conditions but not all. Possibly a subselect using the

select a, b, c, d, e

from testC ,(select cast(newId() as varchar(50)) as GUID) as B

Order By GUID

with a TOP 100 PERCENT and nthen cross join that resultset. But if anyone has a method that can keep it from stumbling over Temp work table I am curious to see what they come up with and what we can do to break it.