Il blog di Nicola

Occorre fare attenzione alla clausola NOT IN, quando l’insieme dei dati in cui viene eseguito il controllo è una select a sua volta. Se la SELECT interna contiene valori null non viene restituito nessun record.
Qualche query su due tabelle di esempio:

CREATE TABLE TABELLA1 (id INT, descr VARCHAR(11))
INSERT INTO tabella1 VALUES (1, 'pippo')
INSERT INTO tabella1 VALUES (1, 'pluto')
INSERT INTO tabella1 VALUES (1, 'paperino')

CREATE TABLE TABELLA2 (id INT, descr VARCHAR(11))
INSERT INTO tabella2 VALUES (2, 'pippo')
INSERT INTO tabella2 VALUES (2, 'pluto')

Se voglio prendere tutti i record di TABELLA1 che non hanno una descrizione in TABELLA2 posso scrivere così:

SELECT *
  FROM tabella1 t1
 WHERE t1.descr NOT IN (SELECT t2.descr FROM tabella2 t2)

E ottengo il risultato:

che è coerente con la query. Adesso inserisco una riga con valore null nel campo DESCR in TABELLA2;

INSERT INTO tabella2 VALUES (2,NULL)

Ora la stessa query restituisce un recordset vuoto, sbagliando.

Per continuare ad usare il NOT IN occorre inserire la condizione di IS NOT NULL alla select interna:

SELECT *
  FROM tabella1 t1
 WHERE t1.descr NOT IN (SELECT t2.descr FROM tabella2 t2 WHERE t2.descr IS NOT NULL)

Ora il recordset è di nuovo coerente con la query.

Una alternativa al NOT IN è il JOIN diretto fra le tabelle:

SELECT t1.* 
  FROM tabella1 t1 LEFT JOIN tabella2 t2 ON (t1.descr = t2.descr)
 WHERE t2.descr IS NULL

oppure utilizzando la clausola EXCEPT:

SELECT descr FROM tabella1 EXCEPT SELECT descr FROM tabella2

Il problema, ad onore del vero, non è solo di SQLServer, ma è condiviso anche da altri DBMS.

Mi sono imbattuto in questa problematica: utilizzare una SELECT TOP all’interno di un ciclo, in cui il numero di record da utilizzare è assegnato ad una variable.
Questo è l’esempio che non funziona:

DECLARE @num INT
 
DECLARE c_curs CURSOR FOR
   [...]
OPEN c_curs
 
FETCH NEXT FROM c_curs
INTO @num
 
WHILE @@FETCH_STATUS = 0
BEGIN
   [...]
   SELECT TOP @num [...] ORDER BY NEWID()
 
   FETCH NEXT FROM c_curs
   INTO @anno, @num
END
 
CLOSE c_curs
DEALLOCATE c_curs

Ho risolto utilizzando ROWCOUNT che è invece parametrico:

SET ROWCOUNT @num
SELECT [...] ORDER BY NEWID()
SET ROWCOUNT 0
 
FETCH NEXT FROM c_curs
INTO @num

E’ importante ricordarsi di ripristinare il valore di ROWCOUNT a 0.

Curiosando in rete ho trovato questo articolo che spiega meglio il problema, e offre più di una soluzione: http://sqlserver2000.databases.aspfaq.com/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

Si può fare utilizzando SELECT TOP 1 … ORDER BY NEWID(), da una tabella contenente i caratteri disponibili. Ecco l’esempio:

WITH char_table AS (
	SELECT 'a' AS a
    UNION
	SELECT 'b' AS a
        ...
        /* inserire qui l'elenco dei caratteri che si 
            vogliono prendere in considerazione */
        ...
	UNION
	SELECT 'z' AS a
)
SELECT
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid()) +
(SELECT top 1 a FROM char_table ORDER BY newid())

SQL Server utilizza due operatori fisici per gestire le aggregazioni (GROUP BY): lo Stream aggregate e l’Hash aggregate. Alla base di questi due operatori vi sono due diversi algoritmi, con caratteristiche diverse, che SQL Server sceglie a seconda della situazione.

Un esempio:

DROP TABLE ORDINI
CREATE TABLE ORDINI (IDPRODOTTO INT, QUANTITA INT)
GO
 
INSERT INTO ORDINI VALUES (1, 1)
GO 50
INSERT INTO ORDINI VALUES (2, 1)
GO 30
INSERT INTO ORDINI VALUES (3, 1)
GO 60

La query di aggregazione:

SELECT IDPRODOTTO, COUNT(IDPRODOTTO) AS [ORDINATO]
  FROM ORDINI
 GROUP BY IDPRODOTTO

Viene risolta in questo modo attraverso l’operatore Stream:

Ora inseriamo un pò di record e rifacciamo la stessa query:

INSERT INTO ORDINI VALUES (1, 1)
GO 5000
INSERT INTO ORDINI VALUES (2, 1)
GO 3000
INSERT INTO ORDINI VALUES (3, 1)
GO 6000
 
SELECT IDPRODOTTO, COUNT(IDPRODOTTO) AS [ORDINATO]
  FROM ORDINI
 GROUP BY IDPRODOTTO

Il piano di esecuzione questa volta prevere l’operatore Hash Match, scelta dovuta alla dimensione maggiore della tabella:

I due algoritmi alla base degli operatori sono:

• Algoritmo hash

  Per ogni riga in input
  {
     Calcola il valore hash basato sulle colonne incluse nella clausola group by
     cerca il valore trovato nella tabella hash
     if non trovato
        inserisce una nuova riga nella tabella hash</code>
     else
        aggiorna la riga trovato con il nuovo valore</code>
     }
     restituisce tutte le righe presenti nella tabella hash

• Algoritmo Stream


  pulisce i totali correnti
  pulisce il valore attuale delle colonne di group by
  per ogni riga in input
  {
     se la riga attuale non corrisponde con il valore attuale delle colonne del group by
     {
        restituisce i totali corrente
        pulisce i totali correnti
        imposta il valore attuale delle colonne di group by con la riga corrente
     }
     update del totale di aggregazione
  }

Oggi ho guadagnato una medaglia per aver risolto questo problema:

Data questa tabella:

columnn1 	Column2
a 		2010-04-15 00:00:00.000
a 		2010-04-17 00:00:00.000
b 		2010-04-17 00:00:00.000
b 		2010-04-15 00:00:00.000
b 		2010-04-20 00:00:00.000
c 		2010-04-19 00:00:00.000
d 		2010-04-12 00:00:00.000
d 		2010-04-13 00:00:00.000

come si fa ad estrarre, per ogni codice, quello la cui data è più vicina alla data corrente? Ovvero ad avere il risultato:

a 		2010-04-15 00:00:00.000
b 		2010-04-15 00:00:00.000
c 		2010-04-19 00:00:00.000
d 		2010-04-13 00:00:00.000

La soluzione è:

  SELECT column1, column2
    FROM prova
   WHERE
    (SELECT COUNT(*)
       FROM prova AS p
      WHERE p.column1 = prova.column1
        AND  abs(datediff(DAY, p.column2, getdate())) <  abs(datediff(DAY, prova.column2, getdate()))
    ) = 0

Il problema è simile al solito dilemma: dato un group by, come faccio ad estrarre solo i record che hanno valore massimo (o che hanno massima occorrenza o altre condizioni)?
Il risultato è un self-join che in maniera generica può essere scritto così:


select [campi]

from [tabella] t1

where

( select [funzione scalare per la scelta dei campi]

from [tabella] as t2

where t1.key = t2.key

and [condizione di confronto fra due campi (anche calcolati) della tabella]

) [condizione sulla funzione scalare definita]


Il classico esempio: di ogni gruppo vogliamo solo il record di valore massimo

select key, valore

from [tabella] t1

where

( select count(*)

from [tabella] as t2

where t1.key = t2.key

and t2.valore > t1.valore -- prendo il maggiore

) = 0


Ovvero, prendo il record della prima tabella che non ha record con valore superiore sulla seconda tabella.
Se volessimo prendere il valore minino basterebbe invertire la condizione di confronto fra i due valori:


select key, valore

from [tabella] t1

where

( select count(*)

from [tabella] as t2

where t1.key = t2.key

and t2.valore < t1.valore -- prendo il minore

) = 0


Se vogliamo prendere i 2 valori più alti di ogni gruppo, basta cambiare la condizione finale:


select key, valore

from [tabella] t1

where

( select count(*)

from [tabella] as t2

where t1.key = t2.key

and t2.valore minore t1.valore

) < 2 -- numero di record restituiti per la condizione