Vi kan bruke SELECT…INTO i SQL Server for å lage en ny tabell fra en tabellkilde. SQL Server bruker attributtene til uttrykkene i SELECT -listen for å definere strukturen til den nye tabellen.

Før SQL Server 2005, bruk av SELECT…INTO i produksjonskode var en ytelse ‘kodelukt’ fordi den fikk skemalåser på systemtabeller i databasen, noe som førte til at SQL Server ikke reagerte mens spørringen ble utført. Dette er fordi det er en DDL-setning i en implisitt transaksjon, som uunngåelig varer lenge siden dataene settes inn i den samme SQL-setningen. Denne oppførselen ble imidlertid løst i SQL Server 2005, da låsemodellen endret seg.

SELECT…INTO ble populær fordi det var en raskere måte å sette inn data på enn å bruke INSERT INTO…SELECT…. Dette skyldtes hovedsakelig at SELECT…INTO -operasjonen, der det var mulig, var masselogget. Selv om INSERT INTO nå kan være masselogg, kan du fremdeles se denne ytelsesfordelen i SQL Server 2012 og 2014, fordi SELECT…INTO kan parallelliseres på disse versjonene, mens støtte for parallellisering av INSERT INTO bare dukket opp i SQL Server 2016. Med SELECT…INTO har du imidlertid fortsatt oppgaven med å definere alle nødvendige indekser og begrensninger og så videre på den nye tabellen.

En anbefaling til unngå bruk av SELECT…INTO, for produksjonskode, er inkludert som en kodeanalyseregel i SQL Prompt (PE003).

Opprette tabeller ved hjelp av SELECT INTO-setningen

Funksjonen SELECT…INTO i SQL Server ble designet for å lagre eller ‘vedvare’ en tabellkilde som en del av en prosess. Her er et enkelt eksempel:

En tabellkilde kan imidlertid være mange andre ting enn en konvensjonell tabell, for eksempel en brukerdefinert funksjon, en OpenQuery, en OpenDataSource, en OPENXML ledd, en avledet tabell, en sammenføyet tabell, en svingbar tabell, en ekstern datakilde, en tabellvariabel eller en variabel funksjon. Det er med disse mer eksotiske tabellkildene at SELECT…INTO syntaksen blir mer nyttig.

Folk bruker ofte SELECT…INTO med den misforståelsen at det er en rask måte å kopiere tabeller på, og det er derfor en overraskelse at ingen av indeksene, begrensningene, de beregnede kolonnene eller utløserne som er definert i kildetabellen overføres til den nye tabellen. De kan ikke spesifiseres i SELECT…INTO -uttalelsen. Det gjør heller ingenting med hensyn til ugyldighet eller å bevare beregnede kolonner. Alle disse oppgavene må gjøres med tilbakevirkende kraft med dataene på plass, noe som uunngåelig tar tid.

Du kan imidlertid bruke funksjonen IDENTITY (datatype, seed, inkrement) for å sette opp et identitetsfelt, og det er mulig, når kilden er en enkelt tabell, å lage en kolonne av destinasjonstabellen til en identitetskolonne. Det er dette som sannsynligvis får utviklere til å anta at det vil overføre andre kolonneattributter.

Videre kan det heller ikke opprette partisjonerte tabeller, sparsomme kolonner eller andre attributter som er arvet fra en kildetabell. Hvordan kan det være når dataene kommer fra et spørsmål som involverer mange tilknytninger, eller fra en eksotisk ekstern datakilde?

Siden SQL 2012 SP1 CU10, kan SELECT…INTO utføres parallelt, men siden SQL Server 2016 har Parallel Insert blitt tillatt på den konvensjonelle INSERT INTO…SELECT uttalelsen, med visse begrensninger, så enhver ytelsesfordel ved å bruke SELECT… INTO er nå ganske redusert. INSERT INTO prosessen kan også øke hastigheten hvis den kan masselogges, i stedet for å bli fullstendig gjenopprettet, ved å sette gjenopprettingsmodellen til enkel eller masselogg, sette inn i en tom tabell eller en haug, og sette TABLOCK hint for tabellen.

Det følgende oppsummerer noen av begrensningene og begrensninger når du bruker SELECT…INTO.

• IDENTITY -egenskapen til en kolonne overføres, men ikke hvis:
  • Uttrykket SELECT inneholder sammenføyde tabeller (bruker enten JOIN eller UNION), GROUP BY ledd, eller samlet funksjon.Hvis du trenger å unngå at en IDENTITY -egenskap overføres til den nye tabellen, men trenger kolonnens verdier, er det verdt å legge til en JOIN til tabellkilden under en tilstand som aldri er sant, eller en UNION som ikke gir noen rader.
  • IDENTITY -kolonnen er oppført mer enn én gang i SELECT -listen
  • IDENTITY -kolonnen er en del av et uttrykk
  • IDENTITY -kolonnen er fra en ekstern datakilde
• Du kan ikke SELECT…INTO enten en tabellverdig parameter eller en tabellvariabel som destinasjon, selv om du kan velge FROM dem.
• Selv om kilden din er en partisjonert tabell, den nye tabellen opprettes i standard filgruppe. I SQL Server 2017 er det imidlertid mulig å spesifisere filgruppen som måltabellen er opprettet i, via ON -klausulen.
• Du kan spesifisere en ORDER BY klausul, men den ignoreres generelt. På grunn av dette er ikke rekkefølgen på IDENTITY_INSERT garantert.
• Når en beregnet kolonne er inkludert i SELECT liste, er den tilsvarende kolonnen i den nye tabellen ikke en beregnet kolonne. Verdiene i den nye kolonnen er verdiene som ble beregnet på det tidspunktet SELECT…INTO ble utført.
• Som med en CREATE TABLE uttalelse, hvis en SELECT…INTO setning er inneholdt i en eksplisitt transaksjon, er den / de underliggende radene i de berørte systemtabellene utelukkende låst til transaksjonen er eksplisitt begått. I mellomtiden vil dette resultere i blokkeringer på andre prosesser som bruker disse systemtabellene.

Det er en viss forvirring om problemer som kan skje ved bruk av SELECT…INTO ved hjelp av midlertidige tabeller. SELECT…INTO har fått et noe urettferdig rykte for dette, men det var en del av et mer generelt problem som involverte låsekonflikt i tempdb under tung belastning av små temp tabell opprettelse og sletting. Når SELECT…INTO ble adoptert med entusiasme, kunne det øke denne typen aktiviteter. Problemet kan lett løses i SQL Server 2000 og utover med innføring av bruk av sporingsflagg TF1118, som ikke lenger er nødvendig fra SQL Server 2016 og utover. For en fullstendig forklaring, se Misforståelser rundt TF 1118.

Sammendrag

Oppsummert er SELECT…INTO en god måte å lage en tabell- kilde midlertidig vedvarende som en del av en prosess, hvis du ikke bryr deg om begrensninger, indekser eller spesielle kolonner. Det er ikke en god måte å kopiere en tabell på, fordi bare det viktigste av tabellskjemaet kan kopieres. Gjennom årene har det vært faktorer som har økt eller redusert tiltrekningen til SELECT…INTO, men generelt er det lurt å unngå å bruke det når det er mulig. I stedet lager du en tabell eksplisitt, med hele spekteret av funksjoner som tabellen har som er utformet for å sikre at data er konsistente.