10

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.

3
  • 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. Feb 18, 2013 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. Feb 18, 2013 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 ... Feb 18, 2013 at 15:33

2 Answers 2

9

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 is 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.

2
  • 1
    Yes, it is not always possible, but what is interesting for me how is ever possible.
    – veljasije
    Feb 19, 2013 at 10:10
  • 2
    @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, 2013 at 15:23
3

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.

5
  • 3
    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/… Feb 18, 2013 at 15:13
  • @MartinSmith:First Word came to my mind looking to the pdf ,WOW :),thanks for sharing Feb 18, 2013 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). Feb 18, 2013 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 Feb 18, 2013 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, 2013 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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