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

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

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;

 

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Usare json su SQLServer 2005, 2008, 2012 e 2014 .. è possibile!

Vedere il supporto nativo di JSON su SQLServer 2016 (e anche su Azure SQLDatabase) mi …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

+ sixty eight = seventy

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.