Redgate Hub (Norsk)

Midlertidige tabeller er nettopp det. De brukes oftest til å gi arbeidsområdet for mellomresultatene når de behandler data i en batch eller prosedyre. De brukes også til å sende en tabell fra en tabellverdi-funksjon, for å overføre tabellbaserte data mellom lagrede prosedyrer eller, nylig i form av tabellverdige parametere, for å sende hele skrivebeskyttede tabeller fra applikasjoner til SQL Server-rutiner , eller send midlertidige skrivebeskyttede tabeller som parametere. Når de er ferdige med bruken, kastes de automatisk.

Før vi kommer for dypt inn i teknologien, vil jeg råde deg til å bruke tabellvariabler der det er mulig. De er enkle, og SQL Server gjør jobben for deg. De har også en tendens til å forårsake færre problemer for et hardt arbeidende OLTP-system. Bare noen ganger kan det hende du må finjustere dem for å få god ytelse fra dem, men jeg forklarer det om et øyeblikk. Hvis du gjør mer kompleks behandling av midlertidige data eller sannsynligvis vil bruke mer enn rimelig liten mengder data i dem, da er det sannsynlig at lokale midlertidige tabeller vil være et bedre valg.

Tabellvariabler

Tabellvariabler brukes innenfor omfanget av rutinen eller batchen de er innenfor definert, og ble opprinnelig opprettet for å muliggjøre tabellverdige funksjoner. Imidlertid er de gode for mange av bruksområdene som det tradisjonelle midlertidige bordet ble satt til. De oppfører seg som andre variabler i sine omfangsregler. Når de er utenfor omfanget, blir de kastet. Disse er mye lettere å jobbe med, og ganske sikre, og de utløser også færre rekompiler i rutinene der de brukes enn om du skulle bruke midlertidige tabeller. Tabellvariabler krever mindre låsingsressurser ettersom de er private for prosessen som skapte dem. Transaksjonens tilbakefall påvirker ikke dem fordi tabellvariabler har begrenset omfang og ikke er en del av den vedvarende databasen, så de er nyttige for å opprette eller lagre data som burde overleve tilbakeslag som loggoppføringer. Ulempen med tabellvariabler er at de ofte blir kastet før du kan undersøke innholdet deres for feilsøking, eller bruke dem til å prøve forskjellige SQL-uttrykk interaktivt.

Hvis applikasjonen din er konservativ og datavolumene dine lyser deg Jeg vil aldri ha noe annet. Imidlertid kan du treffe problemer. En vanskelighet er at tabellvariabler bare kan refereres til i deres lokale omfang, så du kan ikke behandle dem ved hjelp av dynamisk SQL som du kan med en midlertidig tabell eller tabellverdi-parameter. Dette er fordi du ikke kan henvise til en eksternt definert tabellvariabel i dynamisk SQL som du deretter utfører via EXEC-setningen eller sp_ExecuteSQL lagret prosedyre fordi den dynamiske SQL kjøres utenfor omfanget av tabellvariabelen. Du kan selvfølgelig opprette og deretter bruke tabellvariabelen i den dynamiske SQL fordi tabellvariabelen ville være i omfang. Når den dynamiske SQL-en er kjørt, vil det imidlertid ikke være noen tabellvariabel

Det er også noen få avvik å være klar over. Du kan for eksempel ikke endre tabelldefinisjonen etter den første DECLARE-setningen. I SQL Server 2000 kan en tabellvariabel ikke være målet for en SELECT INTO -erklæring eller en INSERT EXEC (nå løst); Du kan ikke ringe brukerdefinerte funksjoner fra CHECK-begrensninger, STANDARD-verdier og beregnede kolonner i tabellvariabelen. De eneste begrensningene du har tillatelse utover CHECK-begrensningene, er PRIMÆRE NØKKEL, UNIK Nøkkel og NULL / IKKE NULL

De vanskeligste problemene kommer imidlertid med økende størrelse på tabellene, fordi før SQL Server 2016 , du kunne ikke erklære en indeks eksplisitt, og indeksene som håndhevet UNIQUE og PRIMARY Nøkkelbegrensningene hadde ikke distribusjonsindekser opprettholdt. Nå kan du opprette visse indeksetyper integrert med tabelldefinisjonen, men distribusjonsstatistikk opprettholdes fortsatt ikke på dem. Spørringsoptimereren antar at det bare er én rad i tabellen. Du kan heller ikke generere parallelle spørringsplaner for et SQL-uttrykk som endrer innholdet på tabellen. For å delvis komme deg rundt indeksbegrensningen, kan du bruke begrensninger for å gjøre det samme. Det viktigste er den primære nøkkelbegrensningen som lar deg innføre en gruppert indeks, men unike begrensninger er nyttige for ytelse. Spørringsoptimereren vil gjerne bruke dem hvis de er i nærheten.

Det største problemet med tabellvariabler er at statistikken ikke opprettholdes i kolonnene. Dette betyr at spørringsoptimalisereren må gjette størrelsen og distribusjonen av dataene, og hvis det blir feil, vil du se dårlig ytelse på sammenføyninger: Hvis dette skjer, er det lite du kan gjøre annet enn å gå tilbake til å bruke klassiske lokale midlertidige tabeller. Fra og med SQL Server 2019 introduserte Microsoft en ny funksjon kalt Table Variable Deferred Compilation som løser dette problemet. For å lære mer, les denne artikkelen fra Greg Larsen.

Hvis du ikke bruker SQL Server 2019, er en ting du kan prøve å legge til OPTION (RECOMPILE) i setningen som innebærer at tabellvariabelen blir sammen med andre tabeller. Ved å gjøre dette vil SQL Server kunne oppdage antall rader ved rekompilering fordi radene allerede har blitt befolket. Dette løser ikke helt problemet siden optimalisereren fremdeles ikke har noen distribusjonsstatistikk og kan, vanligvis der distribusjonen er skjev, produsere en dårlig plan. I denne demoen ble sammenføyningen redusert med tre kvartaler i tid ved å legge til OPTION (RECOMPILE)

Nå hvis du kan gjøre det som går inn i tabellene unikt, kan du bruke en primær nøkkelbegrensning på disse bord. Dette tillot optimalisereren å bruke en gruppert indekssøk i stedet for en tabellskanning, og utførelsestiden var for rask til å måle

Start med tabellvariabler, men gå tilbake til å bruke lokale midlertidige tabeller hvis du treffer ytelsesproblemer. Noen mennesker er dristige nok til å gi råd når det gjelder antall rader i en tabell, og jeg har sett 100 eller 1000 tilbudt som et maksimum; men jeg har sett langt større tabellvariabler yte perfekt tilfredsstillende over tid, og langt mindre gir problemer. Imidlertid, i mindre tabeller, er problemene mindre oppdagelige!

Parameter for tabellverdier

Parameter for tabellverdier (TVP) er en spesiell type tabellvariabel som utvider bruken. Når tabellvariabler sendes som parametere, materialiseres tabellen i TempDB-systemdatabasen som en tabellvariabel og sendes som referanse, en peker til tabellen i TempDB.

Tabellverdiene har blitt brukt siden SQL Server 2008 for å sende flere rader med data til en Transact-SQL-rutine eller til en batch via sp_ExecuteSQL .. Deres spesielle verdi for programmereren er at de kan brukes innen TSQL-kode som så vel som i klientapplikasjonen, så de er gode for å sende klienttabeller til serveren. Fra TSQL kan du erklære variabler som er verdsatt av tabeller, sette inn data i dem og sende disse variablene som tabellverdiene til lagrede prosedyrer og funksjoner. Den mer generelle nytten er begrenset av det faktum at de bare sendes som skrivebeskyttet. Du kan ikke utføre UPDATE, DELETE eller INSERT på en tabellverdi parameter i kroppen til en rutine.

Du må opprette en brukerdefinert tabelltype og definere en tabellstruktur for å bruke dem. Her er et enkelt eksempel på deres bruk i TSQL

Som med tabellvariabler, slutter tabellverdiene å eksistere når den er utenfor omfanget, men typedefinisjonen forblir til den eksplisitt blir droppet.I likhet med tabellvariabler får de ikke låser når dataene fylles ut fra en klient, og statistikk opprettholdes ikke på kolonner med parametere som er verdsatt av tabeller. Du kan ikke bruke en parameter med tabellverdi som mål for en SELECT INTO eller INSERT EXEC -uttalelse. Som du forventer, kan en tabellverdig parameter være i FROM -satsen til SELECT INTO eller i INSERT EXEC streng eller lagret prosedyre.

TVP løser det vanlige problemet med å overføre en lokal variabel til dynamisk SQL som deretter kjøres av en sp_ExecuteSQL. Det er dårlig dokumentert av Microsoft, så jeg viser deg et utarbeidet eksempel for å komme i gang

Før vi går videre til å beskrive de mer tradisjonelle midlertidige tabellene og deres bruk, må vi fordype oss i sted der det holdes midlertidige bord. TempDB.

TempDB

Midlertidige tabeller og tabellvariabler opprettes i TempDB-databasen, som egentlig bare er en annen database med enkel gjenoppretting: Med TempDB gjøres bare tilstrekkelig minimal logging for å tillate tilbakeføring og andre syresykdommer. Den spesielle forskjellen med TempDB er at objekter som tabeller blir ryddet ut ved oppstart. Fordi TempDB alltid bruker den enkle gjenopprettingsmodellen, blir den fullførte transaksjonen slettet fra loggloggen på neste TempDB-sjekkpunkt, og bare live-transaksjonene beholdes. Alt dette betyr at midlertidige tabeller oppfører seg som alle andre slags basetabeller ved at de er logget og lagret akkurat som dem. I praksis vil midlertidige tabeller sannsynligvis forbli bufret i minnet, men bare hvis de ofte brukes: det samme som med en grunntabell. TempDB driver et system kalt midlertidig gjenbruk av objekter, som vil cache en del av de midlertidige objektene med planen, hvis det er tilstrekkelig minne. Dette kan forklare forklaringen om at midlertidige objekter bare eksisterer i minnet. Sannheten som alltid er «det kommer an på …».

Mange andre ting skjer i TempDB: Databasmotoren kan bruke den til å plassere arbeidstabeller for DBCC-sjekker, for å lage eller gjenoppbygge indekser, markører, for eksempel. Mellomliggende tabeller i spørsmål beskrevet som hashes, sorterer og spools blir materialisert i TempDB, for eksempel sammen med de som kreves for flere fysiske operasjoner i å utføre SQL-setninger. Den brukes også som en versjonsbutikk for øyeblikksbildeisolering, flere aktive resultatsett (MARS), utløsere og online-indeksbygging.

Fordi midlertidige tabeller lagres akkurat som basistabeller, er det en eller to ting du må være forsiktig med. Du må for eksempel ha CREATE TABLE tillatelse i TempDB for å kunne lage en normal tabell. For å spare deg for problemer er dette tilordnet DBO-rollen (db-eier) som standard, men du må kanskje gjøre det eksplisitt for brukere som ikke er tildelt DBO-rollen. Alle brukere har tillatelse til å opprette lokale eller globale midlertidige tabeller i TempDB fordi dette er tildelt dem via GUEST brukernes sikkerhetskontekst.

Den klassiske midlertidige tabellen kommer inn to smaker, den globale, eller delbare, midlertidige tabellen, foran «##», og den lokale midlertidige tabellen, hvis navn er prefikset med «#. De lokale midlertidige tabellene ligner mindre på normale tabeller enn de globale midlertidige tabellene: Du kan ikke lage synspunkter på dem, eller knytte utløsere til dem. Det er litt vanskelig å finne ut hvilken prosess, økt eller prosedyre som skapte dem. Vi vil gi deg litt hjelp med det senere. Viktigst av alt er at de er sikrere enn en global midlertidig tabell, ettersom bare eierprosessen kan se den.

En annen raritet i den lokale midlertidige tabellen (og den lokale midlertidige lagrede prosedyren) er at den har et annet navn i metadataene til den du gir den i rutinen eller batchen. Hvis den samme rutinen utføres samtidig av flere prosesser, må databasemotoren være i stand til å skille mellom identisk navngitte lokale midlertidige tabeller opprettet av de forskjellige prosessene. Det gjør dette ved å legge til en numerisk streng til hvert lokale midlertidige tabellnavn som er venstrepolstret med understrekingstegn. Selv om du spesifiserer det korte navnet som #MyTempTable, består det som faktisk er lagret i TempDB av tabellnavnet som er angitt i CREATE TABLE -uttalelsen og suffikset. På grunn av dette suffikset må lokale midlertidige tabellnavn være på 116 tegn eller mindre.

Hvis du er interessert i å se hva som skjer, kan du se tabellene i TempDB på samme måte som med andre bord. Du kan til og med bruke sp_help arbeid på midlertidige tabeller bare hvis du påkaller dem fra TempDB.

eller du kan finne dem i systemvisningene til TempDB uten å skifte databaser.

Eller informasjonsskjemaet

Enda bedre, du kan finn ut hvilken prosess, og bruker, som holder på enorme midlertidige tabeller i TempDB og nekter å gi opp plassen

Du kan ikke bruke brukerdefinerte datatyper i midlertidige tabeller med mindre datatypene finnes i TempDB; det vil si med mindre datatypene eksplisitt er opprettet

Brukertabeller i TempDB

Ved normal bruk vil du opprette midlertidige tabeller eller tabellvariabler uten å tenke for dypt på det. Det er imidlertid interessant at TempDB er der for alle slags sandkasseaktiviteter. Du kan lage vanlige basetabeller, visninger eller noe annet du vil ha. Du kan lage skjemaer, lagrede prosedyrer og så videre. Det er lite sannsynlig at du vil gjøre dette, men det er absolutt mulig siden TempDB bare er en annen database. Jeg har nettopp måtte starte SQL Server-utviklingen min på nytt etter å ha bevist dette for meg selv ved å installere AdventureWorks på den. Dette betyr at det er mulig å lage en basistabell i TempDB, en slags ..er … midlertidig permanent tabell. I motsetning til det globale midlertidige bordet, må du gjøre hele din egen rengjøring på den: du er alene. Det samme gjelder rutiner. Fordelen med å gjøre dette er at enhver behandling du bruker, bruker TempDBs enkle gjenoppretting, slik at hvis du ikke klarer å tørke opp, fungerer SQL Server som mor ved neste oppstart: selv om dette kan være veldig lenge. Den neste fasen er å ha det jeg kaller en vedvarende midlertidig tabell. I denne tabellen er selve dataene ustabile når serveren starter på nytt, men selve tabellen vedvarer. Sannsynligvis den vanligste måten å lage en vedvarende midlertidig tabell på er å gjenskape en global midlertidig tabell ved oppstart. Dette kan gjøres automatisk når alle databaser gjenopprettes og meldingen «Gjenoppretting er fullført» logges. Selv om dette er en «global midlertidig», blir den ikke slettet når alle tilkoblinger som bruker den, har forsvunnet, fordi prosessen som kjører den forsvinner aldri. Uten tvil er det bedre å lage denne typen arbeidstabell i databasen som bruker den, men hvis du bruker full gjenoppretting, vil det midlertidige arbeidet forbli i loggen. Du kan selvfølgelig bare lage en vanlig Du kan opprette disse vedvarende tabellene ved oppstart ved å definere en lagret prosedyre i master som oppretter den globale midlertidige tabellen.

Hvorfor bruke denne typen hybrid tabell? Det er for eksempel et tall av teknikker for å overføre tabeller mellom prosedyrer via vedvarende tabeller på en flerprosess-sikker måte, for å gjøre en serie behandlinger til dataene. Disse henvises til en prosessnøkkel tabeller (se Hvordan dele data mellom lagrede prosedyrer : Prosessnøkkelbord av Erland Sommarskog ). De vil i første omgang løfte øyenbrynene til en hvilken som helst erfaren DBA, men de er en effektiv og sikker løsning på et flerårig problem når de er ferdig skikkelig.

I tillegg til midlertidige tabeller er det også en rekke bordtyper som ikke er direkte avledet fra basistabeller, for eksempel falske tabeller og avledede tabeller: noen av disse er så flyktige at de best tenkes å være kortvarige i stedet for midlertidige. CTE bruker kortvarige tabeller som er inline eller avledet og som ikke blir materialisert. BOL refererer til dem som ‘midlertidige navngitte resultatsett’. De eksisterer bare innenfor uttrykkets omfang. I en CTE har de fordelen over avledede tabeller ved at de kan nås mer enn en gang.

Lokal midlertidig tabell

Med lokal midlertidig tabell (navn som begynner med #), det som foregår under panseret er overraskende lik tabellvariabler. Som med tabellvariabler, er lokale midlertidige tabeller private for prosessen som skapte den. De kan derfor ikke brukes i visninger, og du kan ikke knytte utløsere til dem.

De er bedre enn tabellvariabler hvis du liker å bruke SELECT INTO for å lage dem, men jeg er litt skeptisk til å bruke SELECT INTO i et system som sannsynligvis vil kreve endring, vil jeg heller lage mine midlertidige tabeller eksplisitt, sammen med alle begrensningene som er nødvendige.

Du kan ikke enkelt fortelle hvilken økt eller prosedyre som har opprettet disse bordene. Dette skyldes at hvis den samme lagrede prosedyren utføres samtidig av flere prosesser, må databasemotoren kunne skille de samme tabellene som er opprettet av de forskjellige prosessene. Database Engine gjør dette ved å internt legge til et venstrepolstret numerisk suffiks til hvert lokale midlertidige tabellnavn. Det fulle navnet på en midlertidig tabell som lagret i sys.objects-visningen i TempDB består av tabellnavnet spesifisert i CREATE TABLE -uttalelsen og det systemgenererte numeriske suffikset. For å tillate suffikset må tabellnavnet som er spesifisert for et lokalt midlertidig navn, være mindre enn 116 tegn.

Du får rengjøring med lokale midlertidige tabeller; de slettes automatisk når de går utenfor omfanget, med mindre de eksplisitt blir droppet ved å bruke DROP TABLE. Omfanget deres er mer sjenerøst enn en tabellvariabel, slik at du ikke har problemer med å referere dem i grupper eller i dynamisk SQL. Lokale midlertidige tabeller slippes automatisk på slutten av den gjeldende økten eller prosedyren. Å slippe den på slutten av prosedyren som opprettet den, kan forårsake hodeskrape: en lokal midlertidig tabell som opprettes i en lagret prosedyre eller økt, slippes når den er ferdig, slik at den ikke kan refereres til av prosessen som kalte den lagrede prosedyren som opprettet bordet. Det kan imidlertid refereres til ved hjelp av nestede lagrede prosedyrer utført av den lagrede prosedyren som opprettet tabellen. Hvis den nestede prosedyren refererer til en midlertidig tabell og to midlertidige tabeller med samme navn eksisterer på den tiden, hvilken tabell er spørringen løst mot?

Som en kuriositet kan du også opprette lokale midlertidige lagrede prosedyrer med samme omfang og levetid som et lokalt midlertidig bord. Du kan ikke gjøre det samme for andre rutiner.

Globale midlertidige tabeller.

Som lokale midlertidige tabeller, blir globale midlertidige tabeller (de begynner med ##) automatisk sluppet når økten som opprettet tabellen avsluttes: Imidlertid fordi globale tabeller ikke er private for prosessen som opprettet den, må de vedvare deretter til den siste Transact-SQL-setningen som aktivt refererte til tabellen på det tidspunktet da opprettingsøkten ble avsluttet, ble utført og låsene slettes. Alle som har tilgang til TempDB på det tidspunktet disse globale midlertidige tabellene eksisterer, kan spørre, endre eller slippe disse midlertidige objektene direkte.

Du kan knytte regler, standardinnstillinger og indekser til midlertidige tabeller, men du kan ikke opprette visninger på midlertidige tabeller eller knytte utløsere til dem. Du kan bare bruke en brukerdefinert datatype når du oppretter en midlertidig tabell hvis datatypen eksisterer i TempDB

Lagrede prosedyrer kan referere til midlertidige tabeller som er opprettet under den aktuelle økten. I en lagret prosedyre kan du ikke opprette en midlertidig tabell, slippe den og deretter opprette en ny midlertidig tabell med samme navn.

Selv om dette fungerer….

… dette virker ikke t

Du kan ved å bruke lokale midlertidige tabeller utilsiktet tvinge rekompilering til den lagrede prosedyren hver gang den brukes. Dette er ikke bra fordi den lagrede prosedyren neppe vil fungere bra. For å unngå rekompilering, unngå å henvise til en midlertidig tabell opprettet i en samtale eller kalt lagret prosedyre: Hvis du ikke kan gjøre det, legg referansen til en streng som deretter kjøres ved hjelp av EXECUTE statement eller sp_ExecuteSQL lagret prosedyre. Sørg også for at den midlertidige tabellen er opprettet i den lagrede prosedyren eller utløseren før det henvises til og slippes etter disse referansene.Ikke opprett en midlertidig tabell i en kontroll-for-strøm-setning som IF... ELSE eller WHILE.

Du har lov til å lage globale midlertidige lagrede prosedyrer, men jeg har ennå ikke funnet en bruk for dem. Globale midlertidige funksjoner er ikke tillatt.

Konklusjoner

På en hvilken som helst delt lekeplass må du være veldig forsiktig med hvordan du svinger den flaggermusen. Mens du leser dette, har du innsett at mye aktivitet fortsetter i TempDB, og du kan forårsake kaos for hele SQL Server ved å bruke langvarige prosesser som fyller midlertidige tabeller, uansett hvilken type de er, med unødvendige mengder data. Faktisk har jeg gitt deg ledetråder i denne artikkelen hvordan du virkelig, virkelig, kan forstyrre DBA-en din ved hensynsløs bruk av den dyrebare delte ressursen, TempDB. (I gamle dager før S2005 var bruk av SELECT INTO med et stort bord det store V-våpenet (Vergeltungswaffe)

Jeg er alltid skeptisk til å tilby over- generaliserte råd, men jeg foretrekker alltid at databasene mine bruker tabellvariabler og TVP-er der det er mulig. De krever mindre ressurser, og det er mindre sannsynlig at du holder på dem når du er ferdig med dem. Jeg liker å bruke dem maksimalt , med kolonne- og tabellkontroller og begrensninger. Du kan finne tidspunkter når de går tom for damp, spesielt når bordstørrelser blir større. I tilfeller som dette, eller der det ikke er praktisk å bruke tabellvariabler på grunn av deres begrensede omfang, Jeg bruker lokale midlertidige tabeller. Det tar mange sammenbøyde lepper og hoderystelser før jeg godtar et globalt midlertidig bord eller vedvarende midlertidig bord. De har noen gyldige og helt rimelige bruksområder, men de stoler på programmerer for å utføre nødvendig rengjøring