Redgate Hub (Dansk)

Midlertidige tabeller er netop det. De bruges oftest til at give arbejdsområde til de mellemliggende resultater, når de behandler data inden for en batch eller procedure. De bruges også til at videregive en tabel fra en tabelværdifunktion, til at videregive tabelbaserede data mellem lagrede procedurer eller for nylig i form af tabelværdiansatte parametre til at sende hele skrivebeskyttede tabeller fra applikationer til SQL Server-rutiner , eller videregiv midlertidigt read-only tabeller som parametre. Når de er færdige med deres brug, kasseres de automatisk.

Inden vi går for dybt ind i teknologien, vil jeg råde dig til at bruge tabelvariabler, hvor det er muligt. De er lette, og SQL Server gør arbejdet for dig. De har også en tendens til at forårsage færre problemer for et hårdtarbejdende OLTP-system. Bare lejlighedsvis kan det være nødvendigt at finjustere dem for at få god ydeevne fra dem i sammenføjninger, men jeg forklarer det på et øjeblik. Men hvis du laver mere kompleks behandling af midlertidige data eller sandsynligvis bruger mere end rimelig lille mængder data i dem, så lokale midlertidige tabeller er sandsynligvis et bedre valg.

Tabelvariabler

Tabelvariabler bruges inden for rammerne af den rutine eller det batch, inden for hvilket de er defineret og blev oprindeligt oprettet for at muliggøre tabelværdifunktioner. De er dog gode til mange af de anvendelser, som den traditionelle midlertidige tabel blev sat til. De opfører sig som andre variabler i deres omfangsregler. Når de er uden for anvendelsesområdet, bortskaffes de. Disse er meget lettere at arbejde med og ret sikre, og de udløser også færre rekompiler i de rutiner, hvor de bruges, end hvis du bruger midlertidige tabeller. Tabelvariabler kræver færre låseressourcer, da de er private for den proces, der skabte dem. Transaktionsrullbacks påvirker dem ikke, fordi tabelvariabler har begrænset rækkevidde og ikke er en del af den vedvarende database, så de er nyttige til oprettelse eller lagring af data, der burde overleve tilbagekobling, f.eks. Logposter. Ulempen ved tabelvariabler er, at de ofte bortskaffes, før du kan undersøge deres indhold til debugging eller bruge dem til at afprøve forskellige SQL-udtryk interaktivt.

Hvis din applikation er konservativ, og dine datamængder lyser dig Jeg vil aldrig have noget andet. Du kan dog ramme problemer. Et problem er, at tabelvariabler kun kan henvises til i deres lokale omfang, så du kan ikke behandle dem ved hjælp af dynamisk SQL, som du måske med en midlertidig tabel eller tabelværdiparameter. Dette skyldes, at du ikke kan henvise til en eksternt defineret tabelvariabel inden for dynamisk SQL, som du derefter udfører via EXEC-sætningen eller sp_ExecuteSQL lagret procedure, fordi den dynamiske SQL udføres uden for omfanget af tabelvariablen. Du kan selvfølgelig oprette og derefter bruge tabelvariablen inde i den dynamiske SQL, fordi tabelvariablen ville være i omfang. Men når den dynamiske SQL er kørt, ville der ikke være nogen tabelvariabel

Der er også et par anomalier at være opmærksom på. Du kan for eksempel ikke ændre tabeldefinitionen efter den oprindelige DECLARE-sætning. I SQL Server 2000 kan en tabelvariabel ikke være destinationen for en SELECT INTO -erklæring eller en INSERT EXEC (nu løst); Du kan ikke kalde brugerdefinerede funktioner fra CHECK-begrænsninger, DEFAULT-værdier og beregnede kolonner i tabelvariablen. De eneste begrænsninger, som du har tilladelse ud over CHECK-begrænsninger, er PRIMÆRE NØGLE, UNIK NØGLE og NULL / IKKE NULL

De sværeste problemer kommer dog med stigende størrelse på tabellerne, fordi før SQL Server 2016 , du kunne ikke erklære et indeks eksplicit, og de indekser, der håndhævede UNIQUE og PRIMARY KEY-begrænsningerne, havde ikke distributionsindekser opretholdt. Nu kan du oprette bestemte indekstyper inline med tabeldefinitionen, men distributionsstatistikker opretholdes stadig ikke på dem. Forespørgselsoptimeringsprogrammet antager, at der kun er én række i tabellen. Du kan heller ikke generere parallelle forespørgselsplaner for et SQL-udtryk, der ændrer tabelens indhold. For delvist at omgå indeksbegrænsningen kan du bruge begrænsninger til at gøre det samme. Det vigtigste er den primære nøglebegrænsning, der giver dig mulighed for at indføre et klyngedeks, men unikke begrænsninger er nyttige til ydeevne. Forespørgselsoptimeringsværdien bruger dem med glæde, hvis de er i nærheden.

Det største problem med tabelvariabler er, at statistikker ikke opretholdes i kolonnerne. Dette betyder, at forespørgselsoptimisereren er nødt til at gætte sig til størrelsen og fordelingen af dataene, og hvis det bliver forkert, vil du se dårlig ydeevne på sammenføjninger: Hvis dette sker, er der lidt, du kan gøre andet end at vende tilbage til at bruge klassiske lokale midlertidige tabeller. Fra og med SQL Server 2019 introducerede Microsoft en ny funktion kaldet Table Variable Deferred Compilation, der løser dette problem. Hvis du vil vide mere, skal du læse denne artikel fra Greg Larsen.

Hvis du ikke bruger SQL Server 2019, kan du prøve at tilføje OPTION (RECOMPILE) til udsagnet, der involverer, at tabelvariablen sammenføjes med andre tabeller. Ved at gøre dette vil SQL Server kunne registrere antallet af rækker ved genkompilering, fordi rækkerne allerede er udfyldt. Dette løser ikke helt problemet, da optimeringsapparatet stadig ikke har nogen distributionsstatistik og normalt, hvor distributionen er skæv, kan producere en dårlig plan. I denne demo blev sammenkædningen reduceret med tre kvartaler med tiden ved blot at tilføje OPTION (RECOMPILE)

Hvis du nu kan gøre det, der går ind i tabellerne unikt, kan du derefter bruge en primær nøglebegrænsning på disse tabeller. Dette gjorde det muligt for optimisereren at bruge et klynget indekssøgning i stedet for en tabellscanning, og udførelsestiden var for hurtig til at måle

Start med tabelvariabler, men fald tilbage til at bruge lokale midlertidige tabeller, hvis du rammer ydeevne problemer. Nogle mennesker er dristige nok til at give råd med hensyn til antallet af rækker i en tabel, og jeg har set 100 eller 1000 tilbudt som et maksimum; men jeg har set langt større tabelvariabler fungere perfekt tilfredsstillende over tid, og langt mindre giver problemer. I mindre tabeller er problemerne dog mindre detekterbare!

Parameter med tabelværdi

Parameter med tabelværdi (TVP) er en speciel type tabelvariabel, der udvider brugen. Når tabelvariabler sendes som parametre, materialiseres tabellen i TempDB-systemdatabasen som en tabelvariabel og sendes som reference, en markør til tabellen i TempDB.

Tabelværdiparametre er blevet brugt siden SQL Server 2008 til at sende flere rækker med data til en Transact-SQL-rutine eller til en batch via sp_ExecuteSQL .. Deres særlige værdi for programmøren er, at de kan bruges inden for TSQL-kode som såvel som i klientapplikationen, så de er gode til at sende klienttabeller til serveren. Fra TSQL kan du deklarere tabelværdiansatte variabler, indsætte data i dem og videregive disse variabler som tabelværdiansatte parametre til lagrede procedurer og funktioner. Deres mere generelle anvendelighed er begrænset af, at de kun sendes som skrivebeskyttet. Du kan ikke afgive UPDATE, DELETE eller INSERT udsagn på et bordværdisk parameter i selve rutinen.

Du skal oprette en brugerdefineret tabeltype og definere en tabelstruktur for at bruge dem. Her er et simpelt eksempel på deres anvendelse i TSQL

Som med tabelvariabler ophører den tabelværdiansatte parameter med at eksistere, når den først er uden for anvendelsesområdet, men typedefinitionen forbliver, indtil den udtrykkeligt droppes.Ligesom tabelvariabler erhverver de ikke låse, når dataene udfyldes fra en klient, og statistik opretholdes ikke på kolonner med tabelværdiparametre. Du kan ikke bruge en tabelværdiansat parameter som mål for en SELECT INTO eller INSERT EXEC udsagn. Som du kunne forvente, kan en tabelværdiparameter være i FROM -sætningen til SELECT INTO eller i INSERT EXEC streng eller lagret-procedure.

TVP løser det almindelige problem med at ønske at overføre en lokal variabel til dynamisk SQL, der derefter udføres af en sp_ExecuteSQL. Det er dårligt dokumenteret af Microsoft, så jeg viser dig et udført eksempel for at komme i gang

Før vi går videre til at beskrive de mere traditionelle midlertidige tabeller og deres anvendelse, bliver vi nødt til at dykke ned i sted, hvor der holdes midlertidige borde. TempDB.

TempDB

Midlertidige tabeller og tabelvariabler oprettes i TempDB-databasen, som egentlig bare er en anden database med simpel gendannelse: Med TempDB udføres kun tilstrækkelig minimal logning for at tillade tilbageførsel og andre ACID-smagsstoffer. Den specielle forskel ved TempDB er, at objekter såsom tabeller ryddes ud ved opstart. Da TempDB altid bruger den enkle gendannelsesmodel, ryddes den gennemførte transaktion fra loggen på det næste TempDB-kontrolpunkt, og kun de live transaktioner bevares. Alt dette betyder, at midlertidige tabeller opfører sig som enhver anden form for basistabel, da de er logget og gemt ligesom dem. I praksis forbliver midlertidige tabeller sandsynligvis cachelagrede i hukommelsen, men kun hvis de ofte bruges: det samme som med en basistabel. TempDB driver et system kaldet midlertidig genanvendelse af objekt, som cache en del af de midlertidige objekter med planen, hvis der er tilstrækkelig hukommelse. Dette kan forklare forklaringen om, at midlertidige objekter kun findes i hukommelsen. Sandheden som altid er det afhænger af ….

En masse andre ting foregår i TempDB: Databasemotoren kan bruge den til at placere arbejdstabeller til DBCC-kontroller, til at oprette eller genopbygge indekser, markører, for eksempel. Mellemtabeller i forespørgsler, der er beskrevet som hashes, sorter og spools, materialiseres for eksempel i TempDB sammen med dem, der kræves til flere fysiske operationer ved udførelse af SQL-udsagn. Det bruges også som en versionslager til snapshot-isolering, flere aktive resultatsæt (MARS), udløsere og online-indeks-build.

Fordi midlertidige tabeller er gemt ligesom basistabeller, er der en eller to ting, du skal være forsigtig med. Du skal for eksempel have CREATE TABLE tilladelse i TempDB for at oprette en normal tabel. For at spare dig for problemerne tildeles dette som standard DBO-rollen (db-ejer), men det kan være nødvendigt at du gør det eksplicit for brugere, der ikke er tildelt DBO-rollen. Alle brugere har tilladelse til at oprette lokale eller globale midlertidige tabeller i TempDB, fordi dette tildeles dem via GUEST brugernes sikkerhedssammenhæng.

Den klassiske midlertidige tabel kommer ind to varianter, den globale eller delbare, midlertidige tabel, der er forud for ##, og den lokale midlertidige tabel, hvis navn er forud for #. De lokale midlertidige tabeller ligner mindre normale tabeller end de globale midlertidige tabeller: Du kan ikke oprette synspunkter på dem eller knytte udløsere til dem. Det er lidt vanskeligt at finde ud af, hvilken proces, session eller procedure der skabte dem. Vi giver dig lidt hjælp til det senere. Vigtigst er det, at de er mere sikre end en global midlertidig tabel, da kun ejerprocessen kan se den.

En anden underlighed ved den lokale midlertidige tabel (og den lokale midlertidige lagrede procedure) er, at den har et andet navn i metadataene til den, du giver den i din rutine eller batch. Hvis den samme rutine udføres samtidigt af flere processer, skal databasemotoren være i stand til at skelne mellem de identisk navngivne lokale midlertidige tabeller oprettet af de forskellige processer. Det gør det ved at tilføje en numerisk streng til hvert lokale midlertidige tabelnavn, der er venstrepolstret med understregningstegn. Selvom du angiver det korte navn som #MyTempTable, består det, der faktisk er gemt i TempDB, af det tabelnavn, der er angivet i CREATE TABLE -erklæringen og suffikset. På grund af dette suffiks skal lokale midlertidige tabelnavne være på 116 tegn eller derunder.

Hvis du er interesseret i at se, hvad der foregår, kan du se tabellerne i TempDB på samme måde som med andre bord. Du kan endda kun bruge sp_help til midlertidige tabeller, hvis du påberåber dem fra TempDB.

eller du kan finde dem i systemvisningerne i TempDB uden at skifte databaser.

Eller informationsskemaet

Endnu bedre, du kan Find ud af, hvilken proces og bruger, der holder enorme midlertidige tabeller i TempDB og nægter at opgive pladsen

Du kan ikke bruge brugerdefinerede datatyper i midlertidige tabeller, medmindre datatyperne findes i TempDB; det vil sige, medmindre datatyperne eksplicit er oprettet

Brugertabeller i TempDB

Ved normal brug opretter du midlertidige tabeller eller tabelvariabler uden at tænke for dybt over det. Det er dog interessant, at TempDB er der for enhver form for sandkasseaktivitet. Du kan oprette almindelige basistabeller, visninger eller andet, du ønsker. Du kan oprette skemaer, lagrede procedurer og så videre. Det er usandsynligt, at du vil gøre dette, men det er bestemt muligt, da TempDB bare er en anden database. Jeg har lige været nødt til at genstarte min udvikling af SQL Server efter at have bevist dette for mig selv ved at installere AdventureWorks på det. Det betyder, at det er muligt at oprette en basistabel i TempDB, en slags ..er … midlertidig permanent tabel. I modsætning til den globale midlertidige tabel er du nødt til at gøre al din egen husholdning på den: du er alene. Det samme gælder for rutiner. Fordelen ved at gøre dette er, at enhver behandling, du foretager, bruger TempDBs enkle opsving, så SQL Server fungerer som mor ved næste opstart, hvis du ikke klarer at klatre op: selvom dette kan være meget lang tid. Den næste fase er at have det, jeg kalder en vedvarende midlertidig tabel. I denne tabel er selve dataene flygtige, når serveren genstartes, men selve tabellen fortsætter. Sandsynligvis den mest almindelige måde at oprette en vedvarende midlertidig tabel på er at genskabe ved opstart af en global midlertidig tabel. Dette kan gøres automatisk, når alle databaser gendannes, og meddelelsen “Gendannelse er afsluttet” er logget. Selvom dette er en global midlertidig, slettes den ikke, når alle forbindelser, der bruger den, er forsvundet, fordi processen, der kører den forsvinder aldrig. Det er uden tvivl bedre at oprette denne type arbejdstabel i den database, der bruger den, men hvis du bruger fuld opsving, forbliver det midlertidige arbejde i logfilen. Du kan selvfølgelig bare oprette en almindelig tabel i TempDB. Du kan oprette disse vedvarende tabeller ved opstart ved at definere en gemt procedure i master, der opretter den globale midlertidige tabel

Hvorfor bruge denne type hybrid tabel? Der er for eksempel et tal af teknikker til at videregive tabeller mellem procedurer via vedvarende tabeller på en multiprocess-sikker måde for at udføre en række behandlinger til dataene. Disse henvises til en procesnøgletabel (se Sådan deles data mellem lagrede procedurer : Procesnøglet bord af Erland Sommarskog ). De løfter oprindeligt øjenbrynene på enhver krydret DBA, men de er en effektiv og sikker løsning på et flerårigt problem, når de gøres ordentligt.

Ud over midlertidige tabeller er der også en række bordtyper der ikke er direkte afledt af basistabeller, såsom falske tabeller og afledte tabeller: nogle af disse er så flygtige, at de bedst betragtes som flygtige snarere end midlertidige. CTE bruger kortvarige tabeller, der er inline eller afledt og ikke materialiseres. BOL henviser til dem som midlertidige navngivne resultatsæt. De eksisterer kun inden for omfanget af udtrykket. I en CTE har de fordelen i forhold til afledte tabeller, idet de er tilgængelige mere end én gang.

Lokal midlertidig tabel

Med lokal midlertidig tabel (navne der begynder med #), hvad der foregår under emhætten ligner overraskende tabelvariabler. Som med tabelvariabler er lokale midlertidige tabeller private for den proces, der oprettede den. De kan derfor ikke bruges i visninger, og du kan ikke knytte udløsere til dem.

De er nemmere end tabelvariabler, hvis du kan lide at bruge SELECT INTO til at oprette dem, men jeg er lidt forsigtig med at bruge SELECT INTO i et system, der sandsynligvis kræver ændring, vil jeg meget hellere oprette mine midlertidige tabeller eksplicit sammen med alle de begrænsninger, der er nødvendige.

Du kan ikke let fortælle, hvilken session eller procedure der er oprettet disse tabeller. Dette skyldes, at hvis den samme lagrede procedure udføres samtidigt af flere processer, skal databasemotoren være i stand til at skelne de samme tabeller oprettet af de forskellige processer. Databasemotoren gør dette ved internt at tilføje et venstre-polstret numerisk suffiks til hvert lokale midlertidige tabelnavn. Det fulde navn på en midlertidig tabel, som den er gemt i sys.objects-visningen i TempDB, består af det tabelnavn, der er angivet i CREATE TABLE -udtalelsen og det systemgenererede numeriske suffiks. For at tillade suffikset skal det tabelnavn, der er angivet for et lokalt midlertidigt navn, være mindre end 116 tegn.

Du får husholdning med lokale midlertidige tabeller; de slettes automatisk, når de falder uden for anvendelsesområdet, medmindre de udtrykkeligt droppes ved hjælp af DROP TABLE. Deres omfang er mere generøst end en tabelvariabel, så du ikke har problemer med at henvise til dem inden for batches eller i dynamisk SQL. Lokale midlertidige tabeller slettes automatisk ved afslutningen af den aktuelle session eller procedure. At droppe det i slutningen af proceduren, der oprettede det, kan forårsage ridser i hovedet: en lokal midlertidig tabel, der oprettes inden for en gemt procedure eller session, droppes, når den er færdig, så den ikke kan refereres til ved den proces, der kaldte den lagrede procedure, der oprettede bordet. Det kan dog henvises til ved hjælp af indlejrede lagrede procedurer, der udføres af den lagrede procedure, der oprettede tabellen. Hvis den indlejrede procedure henviser til en midlertidig tabel, og der findes to midlertidige tabeller med samme navn på det tidspunkt, hvilken tabel er forespørgslen løst mod?

Som en nysgerrighed kan du også oprette lokale midlertidige lagrede procedurer med samme omfang og levetid som en lokal midlertidig tabel. Du kan ikke gøre det samme for andre rutiner.

Globale midlertidige tabeller.

Som lokale midlertidige tabeller slettes globale midlertidige tabeller (de begynder med ##) automatisk, når den session, der oprettede tabellen, slutter: Men fordi globale tabeller ikke er private for den proces, der oprettede den, skal de fortsætte derefter, indtil den sidste Transact-SQL-sætning, der aktivt henviste til tabellen på det tidspunkt, hvor oprettelsessessionen sluttede, er færdig med at blive udført, og låsene er droppet. Enhver, der har adgang til TempDB på det tidspunkt, hvor disse globale midlertidige tabeller eksisterer, kan spørge direkte, ændre eller slippe disse midlertidige objekter.

Du kan knytte regler, standardindekser og indekser til midlertidige tabeller, men du kan ikke oprette visninger på midlertidige tabeller eller associerede udløsere med dem. Du kan kun bruge en brugerdefineret datatype, når du opretter en midlertidig tabel, hvis datatypen findes i TempDB

Gemte procedurer kan henvise til midlertidige tabeller, der oprettes under den aktuelle session. Inden for en gemt procedure kan du ikke oprette en midlertidig tabel, droppe den og derefter oprette en ny midlertidig tabel med samme navn.

Selvom dette fungerer….

… dette virker ikke t

Du kan ved hjælp af lokale midlertidige tabeller utilsigtet tvinge genkompilering til den lagrede procedure hver gang den bruges. Dette er ikke godt, fordi den lagrede procedure sandsynligvis ikke fungerer godt. For at undgå rekompilering skal du undgå at henvise til en midlertidig tabel oprettet i en kald eller kaldet lagret procedure: Hvis du ikke kan gøre det, skal du placere referencen i en streng, der derefter udføres ved hjælp af EXECUTE statement eller sp_ExecuteSQL lagret procedure. Sørg også for, at den midlertidige tabel oprettes i den gemte procedure eller udløser, før den henvises til og droppes efter disse referencer.Opret ikke en midlertidig tabel i en kontrol-for-flow-sætning som IF... ELSE eller WHILE.

Du har lov til at oprette globale midlertidige lagrede procedurer, men jeg har endnu ikke fundet en anvendelse til dem. Globale midlertidige funktioner er ikke tilladt.

Konklusioner

På enhver delt legeplads skal du være meget forsigtig med, hvordan du svinger det flagermus. Mens du læser dette, har du indset, at der foregår en masse aktivitet i TempDB, og du kan forårsage kaos i hele SQL Server ved at bruge langvarige processer, der udfylder midlertidige tabeller, uanset hvilken type de er, med unødvendige mængder af data. Faktisk har jeg givet dig spor i denne artikel, hvordan du virkelig, virkelig kan forstyrre din DBA ved hensynsløs brug af den dyrebare delte ressource, TempDB. (I gamle dage før S2005 var brug af SELECT INTO med et stort bord det store V-våben (Vergeltungswaffe)

Jeg er altid forsigtig med at levere over- generaliseret rådgivning, men jeg foretrækker altid, at mine databaser bruger tabelvariabler og TVPer, hvor det er muligt. De kræver mindre ressource, og du er mindre tilbøjelige til at holde fast i dem, når du er færdig med dem. Jeg kan godt lide at bruge dem maksimalt , med kolonne- og tabelkontrol og -begrænsninger. Du kan finde tidspunkter, hvor de løber tør for damp, især når tabelstørrelser bliver større. I tilfælde som dette, eller hvor det ikke er praktisk at bruge tabelvariabler på grund af deres begrænsede omfang, Jeg bruger lokale midlertidige borde. Det tager en masse sammenbøjede læber og rystelser af hoveder, før jeg accepterer et globalt midlertidigt bord eller vedvarende midlertidigt bord. De har et par gyldige og helt rimelige anvendelser, men de sætter deres lid til programmør til at udføre den nødvendige husholdning