Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created step before.

But, it is possible to order result set by column that is not listed in select list.

How is it possible, when SELECT clause are executed before ORDER BY and which primary task is to select columns for rendering?

It is a little confusing or I don't get something.

share|improve this question
1  
Yes the normal explanation of logical query processing phases does break down a bit there. Not sure if imagining another project phase after the ORDER would be valid. –  Martin Smith Feb 18 '13 at 15:02
    
For the problem the OP describes, just putting the ORDER BY before the SELECT will do the job. Don't read too much into these kinds of "flowcharts of query processing". They are rarely intended for being 100% accurate and complete. Rather, they are intended for illustrating somewhat globally what goes on as a consequence of an SQL statement being issued. –  Erwin Smout Feb 18 '13 at 15:31
    
Or it wouldn't. A table is explicitly defined by the standard to be an "unordered collection of rows". Make that fit with ORDER BY clauses ... –  Erwin Smout Feb 18 '13 at 15:33

2 Answers 2

up vote 2 down vote accepted

It's not always possible :

If DISTINCT is specified, the expression in the ORDER BY clause have access only to the virtual table returned by the previous step (VT5)

(VT5 is virtual table returned generated by SELECT)

If DISTINCT is not specified expressions in the ORDER BY clause can access both the input and the output virtual tables of the SELECT phase.

Explanation is taken from "Inside SQLServer 2008 T-SQL Querying"; I'm not sure if this particular chapter available online (Step 6: The Presentation ORDER BY Phase, p.16).

Thus,

SELECT distinct field2 FROM table1 order by field1 

gives an error : "Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. "

share|improve this answer
    
Yes, it is not always possible, but what is interesting for me how is ever possible. –  veljasije Feb 19 '13 at 10:10
1  
@veljasije: I think explanation is " ...ORDER BY can access both the input and the output virtual tables created by previous step". Also, docs says that each step of logical processing generates virtual table that is used as the input to the next step. However, it doesn't specify explicitly that such virtual table is the only possible input, and access to virtual tables returned by previous steps must be prohibited... –  a1ex07 Feb 19 '13 at 15:23

What happens in this scenario is the column that is sorted on will now be part of the Output of the scan/seek operation. For instance, take this example:

CREATE TABLE [dbo].[TestTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [some_int] [int] NOT NULL,
    [some_string] [char](128) NOT NULL,
    [some_bigint] [bigint] NOT NULL,
);
go

ALTER TABLE [dbo].[TestTable] 
ADD  CONSTRAINT [PK_TestTable_Id] 
PRIMARY KEY CLUSTERED 
(
    [id] ASC
);
GO

Now take this query (no sort, just retrieving two columns' data):

select
    some_int,
    some_bigint
from TestTable;

enter image description here

We will have an execution plan that, for the clustered index scan, has an output list of some_int and some_bigint. Just as you'd expect. Now let's alter this query and sort on a column, but don't include it in the selected column data:

select
    some_int,
    some_bigint
from TestTable
order by id;

enter image description here

Now we have an output list on the clustered index scan that includes id, some_int, and some_bigint.

share|improve this answer
2  
As far as I understand the question the OP is asking how that fits in with the explanation here (or similar) sql.co.il/books/insidetsql2008/… –  Martin Smith Feb 18 '13 at 15:13
    
@MartinSmith:First Word came to my mind looking to the pdf ,WOW :),thanks for sharing –  Gaurav Soni Feb 18 '13 at 15:18
    
I must be misunderstanding the question then. In order to do the sort, that data will have to be available (I.e. The output of the scan operation). –  Thomas Stringer Feb 18 '13 at 15:22
    
@ThomasStringer - I think you are misunderstanding the question. Using your example query and previous flow chart I think the OP is saying id is not selected and therefore not one of the columns in virtual table VT5 so how is it available to ORDER BY –  Martin Smith Feb 18 '13 at 15:41
    
@MartinSmith Yes, Martin, exactly that is my question, how is possible order result set by column which is not generated in previous step. –  veljasije Feb 19 '13 at 6:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.