ORDER BY nella clausola UNION in SQL Server 2000 e 2005 (o successivi)

ottobre 24, 2009

Con SQL Server 2000, vi siete mai imbattuti nel messaggio di errore numero 104 ?

Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Questo messaggio viene restituito quando si utilizza l’operatore UNION per combinare i risultati di due o più istruzioni SELECT nelle cui dichiarazioni è presente una clausola ORDER BY e le colonne citate nell’ordinamento non sono parte (anche) della SELECT.

L’errore è riproducibile con un semplice esempio che consiste di una tabella clienti dbo.customer e della relativa tabella ordini (testate) dbo.orderheader.

Con i seguenti comandi T-SQL vengono create le strutture di esempio sul tempdb:

use [tempdb];
go

-- create table dbo.customer
create table dbo.customer
(
customerid int identity(1, 1) not null,
modifieddate datetime default (getdate() + datepart(ss, getdate())) not null
primary key(customerid)
);
go

-- create table dbo.orderheader
create table dbo.orderheader
(orderid int identity(1, 1) not null,
orderdate datetime default getdate() not null,
ordernumber as (isnull(N’SO’ + convert([nvarchar](23), [orderid], 0), N’*** error ***’)),
customerid int default 1 not null,
shipname varchar(20) default ’name’,
shipaddress varchar(40) default ‘address’,
shipvia varchar(40) default ‘via’,
shipcity varchar(20) default ‘city’,
shipregion varchar(20) default ‘region’,
shippostalcode varchar(20) default ‘postal code’,
shipcountry varchar(20) default ‘country’,
deliverydate datetime default (getdate() + datepart(ss, getdate())) not null,
deliverynote varchar(40)
primary key(orderid)
);
go

alter table dbo.orderheader
add constraint fk_customer_customerid foreign key (customerid)
references dbo.customer(customerid);
go

Popoliamo le tabelle con alcuni dati di prova:

/* Popoliamo le tabelle con dati di prova */
declare
@i as int,
@customerid int

set @i = 0
while (@i < 1000)
begin
set nocount on

-- insert dbo.customer
insert into dbo.customer default values;

select
    @customerid = (select scope_identity());

-- dbo.orderheader
insert into dbo.orderheader
(
    orderdate,
    customerid,
    shipname,
    shipaddress,
    shipvia,
    shipcity,
    shipregion,
    shippostalcode,
    shipcountry,
    deliverydate,
    deliverynote
)
values
(
    default,
    @customerid,
    default,
    default,
    default,
    default,
    default,
    default,
    default,
    default,
    default
);

set @i = (@i + 1)
set nocount on
end

Per riprodurre l’errore è sufficiente scrivere un’istruzione di SELECT in cui:

E’ presente l’operatore UNION
Sono presenti due o più comandi di SELECT
E’ presente una clausola ORDER BY e le colonne citate nell’ordinamento non sono parte (anche) della SELECT

Il seguente comando verifica i tre requisiti:

select
h.orderid
from
dbo.orderheader h
where
h.customerid = (select top 1
                    c.customerid
                  from
                    dbo.customer c
                  order by
                    c.modifieddate desc)
union all

select
top 10 percent
h1.orderid
from
dbo.orderheader h1

Se eseguiamo la SELECT con SQL Server 2000 riceviamo l’errore che ci aspettiamo:

Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Proviamo ora lo stesso esempio utilizzando però un’istanza SQL Server 2005, il precedente comando di SELECT viene processato correttamente senza errori. Oltre al dataset restituito osserviamo, nella figura seguente, il piano di esecuzione generato per la query:

Figura 1 - Piano di esecuzione con SQL Server 2005

Osserviamo i dettagli dei task Clustered Index Scan e Sort nelle figure seguenti:

Figura 2 - Task: Clustered Index Scan

Con SQL Server 2005 il task di estrazione dati per la tabella dbo.customer (Clustered Index Scan illustrato in figura 2) fornisce implicitamente in output per il successivo Sort task (illustrato in figura 3) sia la colonna customerid, estratta nella SELECT, che la colonna modifieddate, estratta soltanto nella clausola ORDER BY.

Figura 3 - Task: Sort

Con SQL Server 2005 il Sort task esegue l’ordinamento per la colonna modifieddate specificata nella clausola ORDER BY anche se tale colonna non compare nella SELECT, anche in presenza dell’operatore UNION.

Per ovviare al messaggio di errore, con SQL Server 2000 è necessario riscrivere la SELECT in questo modo:

select
h.orderid
from
dbo.orderheader h
where
h.customerid = (select
                    t.customerid
                  from
                    (select top 1
                       c.customerid,
                       c.modifieddate
                     from
                       dbo.customer c
                     order by
                       c.modifieddate desc) as t)
union all

select
top 10 percent
h1.orderid
from
dbo.orderheader h1

Pulizia del tempdb

use [tempdb];
go

if (object_id(‘orderheader’, ‘U’) is not null)
drop table dbo.orderheader;
go

if (object_id(‘customer’, ‘U’) is not null)
drop table dbo.customer;