We kunnen SELECT…INTO in SQL Server gebruiken om een nieuwe tabel te maken op basis van een tabelbron. SQL Server gebruikt de attributen van de expressies in de SELECT lijst om de structuur van de nieuwe tabel te definiëren.

Vóór SQL Server 2005, gebruik van SELECT…INTO in productiecode was een prestatiecode-geur omdat het schemasloten op systeemtabellen van de database heeft verworven, waardoor SQL Server niet reageerde terwijl de query werd uitgevoerd. Dit komt omdat het een DDL-instructie is in een impliciete transactie, die onvermijdelijk lang loopt omdat de gegevens in dezelfde SQL-instructie worden ingevoegd. Dit gedrag is echter opgelost in SQL Server 2005, toen het vergrendelingsmodel veranderde.

SELECT…INTO werd populair omdat het een snellere manier was om gegevens in te voegen dan met INSERT INTO…SELECT…. Dit kwam voornamelijk doordat de SELECT…INTO -bewerking, waar mogelijk, in bulk werd gelogd. Hoewel INSERT INTO nu bulksgewijs kan worden geregistreerd, kunt u dit prestatievoordeel nog steeds zien in SQL Server 2012 en 2014, omdat SELECT…INTO kan op deze versies worden geparallelliseerd, terwijl ondersteuning voor parallellisatie van INSERT INTO alleen in SQL Server naar voren kwam 2016. Met SELECT…INTO heb je echter nog steeds de taak om alle vereiste indexen en beperkingen, enzovoort, in de nieuwe tabel te definiëren.

Een aanbeveling voor vermijd het gebruik van SELECT…INTO, voor productiecode, is opgenomen als een codeanalyseregel in SQL Prompt (PE003).

Tabellen maken met de SELECT INTO-instructie

De functie SELECT…INTO in SQL Server is ontworpen om een tabelbron op te slaan of te ‘behouden’ als onderdeel van een proces. Hier is een eenvoudig voorbeeld:

Een tabelbron kan echter veel andere dingen zijn dan een conventionele tabel, zoals een door de gebruiker gedefinieerde functie, een OpenQuery, een OpenDataSource, een OPENXML -clausule, een afgeleide tabel, een samengevoegde tabel, een draaitabel, een externe gegevensbron, een tabelvariabele of een variabele functie. Met deze meer exotische tabelbronnen wordt de SELECT…INTO syntaxis nuttiger.

Mensen gebruiken SELECT…INTO vaak met het misverstand dat dit een snelle manier is om tabellen te kopiëren, en daarom is het een verrassing dat geen van de indexen, beperkingen, berekende kolommen of triggers gedefinieerd in de brontabel wordt overgebracht naar de nieuwe tabel. Ze kunnen ook niet worden opgegeven in de instructie SELECT…INTO. Het doet ook niets aan de nullabiliteit of het behouden van berekende kolommen. Al deze taken moeten met terugwerkende kracht worden uitgevoerd met de gegevens aanwezig, wat onvermijdelijk tijd kost.

U kunt echter de functie IDENTITY (datatype, seed, increment) om een identiteitsveld in te stellen, en het is mogelijk om, wanneer de bron een enkele tabel is, een kolom van de doeltabel om te zetten in een identiteitskolom. Het is dit feit dat ontwikkelaars er waarschijnlijk toe brengt aan te nemen dat het andere kolomattributen zal overdragen.

Bovendien kan het ook geen gepartitioneerde tabellen, beperkte kolommen of enig ander attribuut maken dat is overgenomen van een brontabel. Hoe kan het als de gegevens afkomstig kunnen zijn van een query met veel joins, of van een exotische externe gegevensbron?

Sinds SQL 2012 SP1 CU10 kan SELECT…INTO parallel worden uitgevoerd, maar sinds SQL Server 2016 is Parallel Insert toegestaan op de conventionele INSERT INTO…SELECT -instructie, met bepaalde beperkingen, dus elk prestatievoordeel van het gebruik van SELECT… INTO is nu nogal verminderd. Het INSERT INTO -proces kan ook worden versneld als het in bulk kan worden gelogd in plaats van volledig te worden hersteld, door het herstelmodel in te stellen naar eenvoudig of bulksgewijs gelogd, invoegen in een lege tabel of een heap, en de TABLOCK hint voor de tabel instellen.

Het volgende vat enkele van de beperkingen samen en beperkingen bij het gebruik van SELECT…INTO.

• De IDENTITY eigenschap van een kolom wordt overgedragen, maar niet als:
  • De instructie SELECT bevat samengevoegde tabellen (met JOIN of UNION), GROUP BY clausule of geaggregeerde functie.Als u wilt voorkomen dat een IDENTITY -eigenschap wordt overgedragen naar de nieuwe tabel, maar de waarden van de kolom nodig heeft, is het de moeite waard om een JOIN naar uw tabelbron op een voorwaarde die nooit waar is, of een UNION die geen rijen bevat.
  • De IDENTITY kolom wordt meer dan één keer vermeld in de SELECT lijst
  • De IDENTITY kolom maakt deel uit van een expressie
  • De IDENTITY -kolom is afkomstig van een externe gegevensbron
• U kunt SELECT…INTO ofwel een parameter met tabelwaarde of een tabelvariabele als bestemming, hoewel je FROM ze kunt selecteren.
• Zelfs als uw bron is een gepartitioneerde tabel, de nieuwe tabel wordt gemaakt in de standaard bestandsgroep. In SQL Server 2017 is het echter mogelijk om de bestandsgroep te specificeren waarin de bestemmingstabel is gemaakt, via de ON -clausule.
• U kunt een ORDER BY -clausule, maar deze wordt over het algemeen genegeerd. Hierdoor is de volgorde van IDENTITY_INSERT niet gegarandeerd.
• Wanneer een berekende kolom is opgenomen in de SELECT lijst, de overeenkomstige kolom in de nieuwe tabel is geen berekende kolom. De waarden in de nieuwe kolom zijn de waarden die zijn berekend op het moment dat SELECT…INTO werd uitgevoerd.
• Net als bij een CREATE TABLE -instructie, als een SELECT…INTO -instructie zich in een expliciete transactie bevindt, zijn de onderliggende rij (en) in de betrokken systeemtabellen exclusief vergrendeld totdat de transactie expliciet is vastgelegd. In de tussentijd zal dit resulteren in blokkades op andere processen die deze systeemtabellen gebruiken.

Er is enige verwarring over problemen die kunnen optreden bij het gebruik van SELECT…INTO met behulp van tijdelijke tabellen. SELECT…INTO heeft hiervoor een ietwat oneerlijke reputatie opgebouwd, maar het maakte deel uit van een algemener probleem met betrekking tot latch-conflicten in tempdb onder zware belasting van het maken en verwijderen van kleine tijdelijke tabellen. Wanneer SELECT…INTO met enthousiasme werd aangenomen, zou dit dit soort activiteit aanzienlijk kunnen vergroten. Het probleem zou gemakkelijk kunnen worden verholpen in SQL Server 2000 en later met de introductie van het gebruik van traceringsvlag TF1118, die niet langer nodig is vanaf SQL Server 2016. Voor een volledige uitleg, zie Misvattingen rond TF 1118.

Samenvatting

Samenvattend is SELECT…INTO een goede manier om een tabel te maken. source tijdelijk persistent als onderdeel van een proces, als u zich niet druk maakt om beperkingen, indexen of speciale kolommen. Het is geen goede manier om een tabel te kopiëren, omdat alleen de meest essentiële onderdelen van het tabelschema kunnen worden gekopieerd. Door de jaren heen zijn er factoren geweest die de aantrekkingskracht van SELECT…INTO hebben vergroot of verkleind, maar over het algemeen is het een goed idee om het waar mogelijk te vermijden. Maak in plaats daarvan expliciet een tabel, met het volledige scala aan functies waarover de tabel beschikt en die zijn ontworpen om ervoor te zorgen dat de gegevens consistent zijn.