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
  }

Date due tabelle TABELLA_SORGENTE e TABELLA_DESTINAZIONE, per copiare tabella sorgente su tabella destinazione ci sono anche queste due soluzioni:

• Utilizzo dell’accoppiata DROP TABLE + SELECT INTO: assolutamente da EVITARE se avete a cuore la vostra tabella di destinazione.
• Utilizzo dell’accoppiata DELETE + INSERT: sicuramente il metodo da usare.

Il motivi per non usare l’accoppiata DROP TABLE + SELECT INTO sta nel fatto che tutti i CONSTRAINT ed i vincoli strutturale presenti sulla tabella che viene eliminata non vengono ovviamente ricreati dalla SELECT INTO ma vengono persi. Quella che viene creata è una tabella ad-hoc per i dati che deve contenere, nulla di più.

Un esempio concreto:

Creiamo una tabella sorgente e mettiamoci dei dati dentro:

CREATE TABLE TABELLA_SORGENTE (ID INT, DESCRIZIONE VARCHAR(20) NOT NULL DEFAULT 'PIPPO')

INSERT INTO TABELLA_SORGENTE VALUES (1,'PAPERINO');
INSERT INTO TABELLA_SORGENTE VALUES (2,'PLUTO');


Creiamo la tabella di destinazione, speculare alla tabella sorgente:

CREATE TABLE TABELLA_DESTINAZIONE (ID INT, DESCRIZIONE VARCHAR(20) NOT NULL DEFAULT 'PIPPO')

Utilizzando DELETE + INSERT abbiamo:

DELETE FROM TABELLA_DESTINAZIONE
INSERT INTO TABELLA_DESTINAZIONE SELECT * FROM TABELLA_SORGENTE

A questo punto la query

INSERT INTO TABELLA_DESTINAZIONE (ID) VALUES (3)

va a buon fine, e la tabella si presenta con questi record

1 PAPERINO
2 PLUTO
3 PIPPO


Utilizzando invece DROP TABLE + SELECT INTO abbiamo:

DROP TABLE TABELLA_DESTINAZIONE
SELECT * INTO TABELLA_DESTINAZIONE FROM TABELLA_SORGENTE

Sembrerebbe tutto uguale, ma il risultato della query

INSERT INTO TABELLA_DESTINAZIONE (ID) VALUES (3)

è il seguente:

Cannot insert the value NULL into column 'DESCRIZIONE', table 'NICOLA.dbo.TABELLA_DESTINAZIONE'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Questo perchè la condizione DEFAULT ‘PIPPO’ della dichiarazione iniziale della tabella

CREATE TABLE TABELLA_DESTINAZIONE (ID INT, DESCRIZIONE VARCHAR(20) NOT NULL DEFAULT 'PIPPO')

ora non è più presente.
Quindi non fate mai DROP TABLE + SELECT INTO su tabelle cui volete bene.