È possibile utilizzare SELECT…INTO in SQL Server per creare una nuova tabella da un’origine tabella. SQL Server utilizza gli attributi delle espressioni nell’elenco SELECT per definire la struttura della nuova tabella.

Prima di SQL Server 2005, l’uso di SELECT…INTO nel codice di produzione era un “odore di codice” delle prestazioni perché acquisiva blocchi dello schema sulle tabelle di sistema del database, facendo sì che SQL Server non rispondesse durante l’esecuzione della query. Questo perché è un’istruzione DDL in una transazione implicita, che è inevitabilmente di lunga durata poiché i dati vengono inseriti all’interno della stessa istruzione SQL. Tuttavia, questo comportamento è stato corretto in SQL Server 2005, quando il modello di blocco è cambiato.

SELECT…INTO è diventato popolare perché era un modo più veloce per inserire dati rispetto all’utilizzo di INSERT INTO…SELECT…. Ciò era dovuto principalmente al fatto che l’operazione SELECT…INTO veniva, ove possibile, registrata in blocco. Sebbene INSERT INTO ora possa essere registrato in blocco, potresti comunque vedere questo vantaggio in termini di prestazioni in SQL Server 2012 e 2014, perché SELECT…INTO può essere parallelizzato su queste versioni, mentre il supporto per la parallelizzazione di INSERT INTO è emerso solo in SQL Server 2016. Tuttavia, con SELECT…INTO, hai ancora il compito di definire tutti gli indici e i vincoli richiesti, e così via, nella nuova tabella.

Una raccomandazione a evitare l’uso di SELECT…INTO, per il codice di produzione, è incluso come regola di analisi del codice nel prompt SQL (PE003).

Creazione di tabelle utilizzando l’istruzione SELECT INTO

La funzionalità SELECT…INTO in SQL Server è stata progettata per archiviare o “rendere persistente” un’origine tabella come parte di un processo. Ecco un semplice esempio:

Tuttavia, una sorgente di tabella può essere molte cose diverse da una tabella convenzionale, come una funzione definita dall’utente, un OpenQuery, una OpenDataSource, una OPENXML clausola, una tabella derivata, una tabella unita, una tabella pivot, un’origine dati remota, una variabile di tabella o una funzione variabile. È con queste fonti di tabelle più esotiche che la sintassi SELECT…INTO diventa più utile.

Le persone spesso usano SELECT…INTO con il malinteso che sia un modo rapido per copiare le tabelle, quindi è una sorpresa che nessuno degli indici, vincoli, colonne calcolate o trigger definiti in la tabella di origine viene trasferita nella nuova tabella. Non possono essere specificati neanche nell’istruzione SELECT…INTO. Inoltre non fa nulla per nullability o per preservare le colonne calcolate. Tutte queste attività devono essere eseguite in modo retrospettivo con i dati al loro posto, il che inevitabilmente richiede tempo.

Tuttavia, puoi utilizzare la funzione IDENTITY (tipo di dati, seed, incremento) per impostare un campo identità ed è possibile, quando l’origine è una singola tabella, trasformare una colonna della tabella di destinazione in una colonna identità. È questo fatto che probabilmente porta gli sviluppatori a presumere che trasferirà altri attributi di colonna.

Inoltre, non può creare tabelle partizionate, colonne sparse o qualsiasi altro attributo ereditato da una tabella di origine. Come potrebbe accadere quando i dati potrebbero provenire da una query che coinvolge molti join o da un’origine dati esterna esotica?

Da SQL 2012 SP1 CU10, SELECT…INTO può essere eseguito in parallelo, tuttavia, da SQL Server 2016, l’inserimento parallelo è consentito nell’istruzione INSERT INTO…SELECT convenzionale, con alcune limitazioni, quindi qualsiasi vantaggio in termini di prestazioni dell’utilizzo di SELECT… INTO è ora piuttosto ridotto. Anche il processo INSERT INTO può essere accelerato se può essere registrato in blocco, anziché essere completamente ripristinato, impostando il modello di ripristino per loggarsi in modo semplice o in blocco, inserendo in una tabella vuota o in un heap e impostando il TABLOCK suggerimento per la tabella.

Quanto segue riassume alcune delle limitazioni e limitazioni quando si utilizza SELECT…INTO.

• La proprietà IDENTITY di una colonna viene trasferita, ma non se:
  • L’istruzione SELECT contiene tabelle unite (utilizzando JOIN o UNION), GROUP BY clausola o funzione di aggregazione.Se devi evitare che una proprietà IDENTITY venga trasferita alla nuova tabella, ma hai bisogno dei valori della colonna, vale la pena aggiungere un JOIN all’origine della tabella in una condizione che non è mai vera o un UNION che non fornisce righe.
  • Il IDENTITY la colonna è elencata più di una volta nell ‘SELECT elenco
  • La colonna IDENTITY fa parte di un’espressione
  • La colonna IDENTITY proviene da un’origine dati remota
• Non puoi SELECT…INTO un parametro con valori di tabella o una variabile di tabella come destinazione, anche se puoi selezionarli FROM.
• Anche se la tua fonte è una tabella partizionata, la nuova tabella viene creata nel filegroup predefinito. Tuttavia, in SQL Server 2017, è possibile specificare il filegroup in cui viene creata la tabella di destinazione, tramite la clausola ON.
• È possibile specificare un ORDER BY, ma generalmente viene ignorata. Per questo motivo, l’ordine di IDENTITY_INSERT non è garantito.
• Quando una colonna calcolata è inclusa nel SELECT list, la colonna corrispondente nella nuova tabella non è una colonna calcolata. I valori nella nuova colonna sono i valori calcolati al momento in cui SELECT…INTO è stato eseguito.
• Come con CREATE TABLE, se un’istruzione SELECT…INTO è contenuta all’interno di una transazione esplicita, le righe sottostanti nelle tabelle di sistema interessate sono bloccato esclusivamente fino a quando la transazione non viene esplicitamente impegnata. Nel frattempo, ciò si tradurrà in blocchi su altri processi che utilizzano queste tabelle di sistema.

C’è una certa confusione sui problemi che possono verificarsi con l’uso di SELECT…INTO utilizzando tabelle temporanee. SELECT…INTO si è guadagnato una reputazione in qualche modo ingiusta per questo, ma faceva parte di un problema più generale che coinvolgeva la contesa del latch in tempdb sotto un carico pesante di creazione e cancellazione di piccole tabelle temporanee. Quando SELECT…INTO è stato adottato con entusiasmo, potrebbe aumentare notevolmente questo tipo di attività. Il problema potrebbe essere facilmente risolto in SQL Server 2000 in poi con l’introduzione dell’utilizzo del flag di traccia TF1118, che non è più richiesto da SQL Server 2016 in poi. Per una spiegazione completa, vedere Idee sbagliate su TF 1118.

Riepilogo

In sintesi, SELECT…INTO è un buon modo per creare una tabella- fonte temporaneamente persistente come parte di un processo, se non ti interessano vincoli, indici o colonne speciali. Non è un buon modo per copiare una tabella perché è possibile copiare solo i minimi elementi essenziali dello schema della tabella. Nel corso degli anni, ci sono stati fattori che hanno aumentato o diminuito l’attrazione di SELECT…INTO, ma nel complesso è una buona idea evitare di utilizzarlo quando possibile. Invece, crea una tabella in modo esplicito, con l’intera gamma di funzionalità che la tabella possiede, progettate per garantire che i dati siano coerenti.