x

How does the Query Engine know how to order via newid()

While we all know how to display a result in random order via newid() function, I wonder how the query engine interprets a GUID(result of newid), since ORDER BY statement expects a column list (or position)?

NOTE: And if the GUID somehow magically is converted to column position, this assumption is invalid for a table with single column, since it will return results in random order.

Thanks

more ▼

asked Nov 19, 2009 at 09:41 AM in Default

bonskijr gravatar image

bonskijr
204 5 5 7

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As you can see in the text from books online (bold text), order by expression can include an expression. NEWID() that is nondetermanistic is executed for every row returned by your select and then order by is applied on your set of data,

order_by_expression
Specifies a column on which to sort. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function. A sort column can include an expression, but when the database is in SQL Server (90) compatibility mode, the expression cannot resolve to a constant. Column names and aliases can be qualified by the table or view name. In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.

more ▼

answered Nov 19, 2009 at 09:56 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

Thanks Hakan, however if we're to follow the non-deterministic route the following will still show the result in order.

select top 10 singleColumn from singleColumnTable order by GETUTCDATE() -- this is undeterministic function
Nov 19, 2009 at 10:14 AM bonskijr
Yes you are right, GETUTCDATE() is also non-deterministic but if you try the following statement you will see that GETUTCDATE() will be the same for every row: select top 10 GETUTCDATE(), singleColumn from singleColumnTable order by GETUTCDATE()
Nov 19, 2009 at 10:24 AM Håkan Winther

You're absolutely right.. tried other nondeterministic functions and combo (checksum(getdate()) * rnd(), etc..) yields same result save for NEWID()

thanks!!

Nov 19, 2009 at 10:30 AM bonskijr
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x371

asked: Nov 19, 2009 at 09:41 AM

Seen: 1720 times

Last Updated: Nov 19, 2009 at 09:41 AM