Il blog di Nicola

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.

Ecco un esempio di come utilizzare i Parameters all’interno di un oggetto OLEDBCommand.

La query

INSERT INTO TABELLA VALUES ('25/04/1972 10:40:00')

eseguita su un campo di tipo DATETIME si traduce grossolanamente in:

OleDbConnection conn;
OleDbCommand comando;

String connStr;
String sql;

connStr = @"Provider = SQLOLEDB.1;Password=" + password + ";User ID=" + user + ";Initial Catalog=" + database + ";Data Source=" + server;

conn = new OleDbConnection(connStr);
conn.Open();

sql = "INSERT INTO TABELLA VALUES ('25/04/1972 10:40:00')";
comando = new OleDbCommand(sql, conn);

comando.ExecuteNonQuery();

Può capitare che l’ultima istruzione dia errore, per problemi di sintatti del formato data. A seconda delle impostazioni predefinite del server il formato per la data può variare (ad esempio può essere ’1972-04-25 10:40:00).
L’utilizzo dei Parameters ovvia questa problematica. Ecco come diventa il codice:

DateTime dt = new DateTime(1972,4,25,10,40,0);
sql = "INSERT INTO TABELLA VALUES (?)";
comando = new OleDbCommand(sql, conn);
comando.Parameters.AddWithValue("@dataora", dt);

comando.ExecuteNonQuery();

Tutto qua.