Redgate Hub (Svenska)

Tillfälliga tabeller är just det. De används oftast för att tillhandahålla arbetsytan för mellanresultaten vid bearbetning av data inom en batch eller procedur. De används också för att skicka en tabell från en tabellvärderad funktion, för att skicka tabellbaserad data mellan lagrade procedurer eller, mer nyligen i form av tabellvärderade parametrar, för att skicka hela skrivskyddade tabeller från applikationer till SQL Server-rutiner , eller skicka skrivskyddade tillfälliga tabeller som parametrar. När de är färdiga med deras användning kasseras de automatiskt.

Innan vi går för djupt in i tekniken rekommenderar jag att du använder tabellvariabler där det är möjligt. De är enkla och SQL Server gör jobbet åt dig. De tenderar också att orsaka färre problem för ett hårt arbetande OLTP-system. Ibland kan du behöva finjustera dem för att få bra prestanda från dem i sammanfogningar, men jag kommer att förklara det på ett ögonblick, men om du gör mer komplex bearbetning av tillfälliga data eller sannolikt kommer att använda mer än rimligt liten mängder data i dem, då är det troligt att lokala tillfälliga tabeller är ett bättre val.

Tabellvariabler

Tabellvariabler används inom ramen för rutinen eller batchen inom vilken de är definierades och skapades ursprungligen för att möjliggöra tabellvärderade funktioner. De är dock bra för många av de användningsområden som den traditionella tillfälliga tabellen sattes till. De beter sig som andra variabler i deras omfattningsregler. När de är borta från räckvidden, kasseras de. Dessa är mycket lättare att arbeta med och ganska säkra, och de utlöser också färre rekompiler i rutinerna där de används än om du skulle använda tillfälliga tabeller. Tabellvariabler kräver mindre låsningsresurser eftersom de är ”privata” för processen som skapade dem. Transaktionsåterställningar påverkar inte dem eftersom tabellvariabler har begränsat omfång och inte ingår i den ihållande databasen, så de är praktiska för att skapa eller lagra data som borde överleva återställning som loggposter. Nackdelen med tabellvariabler är att de ofta kasseras innan du kan undersöka deras innehåll för felsökning, eller använda dem för att prova olika SQL-uttryck interaktivt.

Om din applikation är konservativ och dina datavolymer tänder dig Jag vill aldrig ha något annat. Men du kan drabbas av problem. En svårighet är att tabellvariabler endast kan refereras till i deras lokala omfång, så du kan inte bearbeta dem med dynamisk SQL som du kan med en tillfällig tabell eller tabellvärderad parameter. Detta beror på att du inte kan hänvisa till en externt definierad tabellvariabel inom dynamisk SQL som du sedan utför via EXEC-satsen eller sp_ExecuteSQL lagrad procedur eftersom den dynamiska SQL körs utanför räckvidden av tabellvariabeln. Du kan naturligtvis skapa och sedan använda tabellvariabeln i den dynamiska SQL eftersom tabellvariabeln skulle vara inom räckvidd. Men när den dynamiska SQL-enheten körs skulle det inte finnas någon tabellvariabel

Det finns också några anomalier att vara medveten om. Du kan till exempel inte ändra tabelldefinitionen efter det ursprungliga DECLARE-uttalandet. I SQL Server 2000 kan en tabellvariabel inte vara destinationen för ett SELECT INTO -uttalande eller ett INSERT EXEC (nu fixat); Du kan inte anropa användardefinierade funktioner från CHECK-begränsningar, DEFAULT-värden och beräknade kolumner i tabellvariabeln. De enda begränsningarna som du har tillåtelse utöver CHECK-begränsningarna är PRIMÄRT NYCKEL, UNIKT NYCKELTYG och NULL / INTE NULL

De svåraste problemen kommer dock med ökande storlek på tabellerna, för innan SQL Server 2016 , du kunde inte deklarera ett index uttryckligen, och indexen som tvingade UNIQUE och PRIMARY KEY-begränsningarna hade inte distributionsindex bibehållna. Nu kan du skapa vissa indextyper inbyggda med tabelldefinitionen, men distributionsstatistik finns fortfarande inte på dem. Frågaoptimeraren förutsätter att det bara finns en rad i tabellen. Du kan inte heller skapa parallella frågeplaner för ett SQL-uttryck som ändrar tabellens innehåll. För att delvis komma runt indexbegränsningen kan du använda begränsningar för att göra samma sak. Det viktigaste är begränsningen för primär nyckel som låter dig införa ett klustrat index, men unika begränsningar är användbara för prestanda. Frågeoptimeraren använder dem gärna om de finns.

Det största problemet med tabellvariabler är att statistik inte upprätthålls i kolumnerna. Detta innebär att frågan optimera måste göra en gissning om storleken och distributionen av data och om det blir fel, kommer du att se dålig prestanda på sammanfogningar: Om detta händer, det är lite du kan göra annat än att återgå till att använda klassiska lokala tillfälliga tabeller. Från och med SQL Server 2019 introducerade Microsoft en ny funktion som heter Table Variable Deferred Compilation som löser problemet. För att lära dig mer, läs den här artikeln från Greg Larsen.

Om du inte använder SQL Server 2019 är en sak du kan försöka lägga till OPTION (RECOMPILE) till uttalandet som innebär att tabellvariabelen går med i andra tabeller. Genom att göra detta kommer SQL Server att kunna upptäcka antalet rader vid återkompilering eftersom raderna redan har fyllts i. Detta löser inte helt problemet eftersom optimeraren fortfarande inte har någon distributionsstatistik och kan, vanligtvis där distributionen är skev, producera en dålig plan. I denna demo minskades kopplingen i tid med tre fjärdedelar genom att helt enkelt lägga till OPTION (RECOMPILE)

Om du nu kan göra det som går in i tabellerna unikt kan du sedan använda en primär nyckelbegränsning för dessa tabeller. Detta gjorde det möjligt för optimeraren att använda ett grupperat indexsök istället för en tabellsökning och exekveringstiden var för snabb för att mäta

Börja med tabellvariabler, men gå tillbaka till att använda lokala temporära tabeller om du stöter på prestandaproblem. Vissa människor är modiga nog för att ge råd när det gäller antalet rader i en tabell, och jag har sett 100 eller 1000 erbjudna som ett maximum; men jag har sett mycket större tabellvariabler fungera perfekt tillfredsställande över tiden, och mycket mindre ger problem. I mindre tabeller är problemet emellertid mindre detekterbart!

Tabellvärderade parametrar

Tabellvärderade parametrar (TVP) är en speciell typ av tabellvariabel som utökar dess användning. När tabellvariabler skickas som parametrar materialiseras tabellen i TempDB-systemdatabasen som en tabellvariabel och skickas som referens, en pekare till tabellen i TempDB.

Tabellvärderade parametrar har använts sedan SQL Server 2008 för att skicka flera rader med data till en Transact-SQL-rutin eller till en sats via sp_ExecuteSQL .. Deras speciella värde för programmeraren är att de kan användas inom TSQL-kod som samt i klientapplikationen, så de är bra för att skicka klienttabeller till servern. Från TSQL kan du deklarera tabellvärderade variabler, infoga data i dem och skicka dessa variabler som tabellvärderade parametrar till lagrade procedurer och funktioner. Deras mer allmänna användbarhet begränsas av det faktum att de endast skickas som skrivskyddade. Du kan inte göra UPDATE, DELETE eller INSERT på ett tabellvärderat parametern i rutinen.

Du måste skapa en användardefinierad tabelltyp och definiera en tabellstruktur för att använda dem. Här är ett enkelt exempel på deras användning i TSQL

Som med tabellvariabler upphör den tabellvärderade parametern att existera när den är utanför räckvidden men typdefinitionen förblir tills den uttryckligen tappas.Liksom tabellvariabler förvärvar de inte lås när data fylls i från en klient och statistik underhålls inte på kolumner med tabellvärderade parametrar. Du kan inte använda en tabellvärderad parameter som mål för ett SELECT INTO eller INSERT EXEC uttalande. Som du förväntar dig kan en tabellvärderad parameter finnas i FROM -satsen i SELECT INTO eller i INSERT EXEC sträng eller lagrad procedur.

TVP löser det vanliga problemet att vilja skicka en lokal variabel till dynamisk SQL som sedan körs av en sp_ExecuteSQL. Det är dåligt dokumenterat av Microsoft, så jag visar dig ett fungerat exempel för att komma igång

Innan vi går vidare för att beskriva de mer traditionella tillfälliga tabellerna och deras användning måste vi gräva i plats där tillfälliga bord hålls. TempDB.

TempDB

Tillfälliga tabeller och tabellvariabler skapas i TempDB-databasen, som egentligen bara är en annan databas med enkel återställning: Med TempDB görs bara tillräcklig ”minimal” loggning för att möjliggöra återställning och andra syror. Den speciella skillnaden för TempDB är att alla objekt som tabeller rensas bort vid start. Eftersom TempDB alltid använder den enkla återställningsmodellen rensas den slutförda transaktionen från loggloggen vid nästa TempDB-kontrollpunkt och endast live-transaktionerna behålls. Allt detta innebär att tillfälliga tabeller beter sig som alla andra bastabeller genom att de loggas och lagras precis som dem. I praktiken kommer tillfälliga tabeller sannolikt att förbli cachade i minnet, men bara om de ofta används: samma som med en bastabell. TempDB driver ett system som kallas tillfällig återanvändning av objekt, vilket cachar en del av de tillfälliga objekten med planen, om det finns tillräckligt med minne. Detta kan redogöra för förklaringen att tillfälliga objekt bara finns i minnet. Sanningen som alltid är ”det beror på …”.

Många andra saker händer i TempDB: Databasmotorn kan använda den för att placera arbetstabeller för DBCC-kontroller, för att skapa eller bygga om index, markörer, till exempel. Mellantabeller i frågor som beskrivs som hashes, sorter och spools materialiseras till exempel i TempDB, tillsammans med de som krävs för flera fysiska operationer vid körning av SQL-uttalanden. Det används också som en version butik för ögonblicksbildsisolering, flera aktiva resultatuppsättningar (MARS), utlösare och online-indexbyggnad.

Eftersom tillfälliga tabeller lagras precis som bastabeller finns det en eller två saker du behöver vara försiktig med. Du måste till exempel ha CREATE TABLE behörighet i TempDB för att skapa en normal tabell. För att spara dig besväret tilldelas detta som standard DBO-rollen (db-ägare), men du kan behöva göra det uttryckligen för användare som inte tilldelas DBO-rollen. Alla användare har behörighet att skapa lokala eller globala tillfälliga tabeller i TempDB eftersom detta tilldelas dem via GUEST användarsäkerhetskontext.

Den klassiska tillfälliga tabellen kommer in två smaker, den globala, eller delbara, tillfälliga tabellen, prefix med ##, och den lokala tillfälliga tabellen, vars namn är prefix med #. De lokala tillfälliga tabellerna är mindre som vanliga tabeller än de globala temporära tabellerna: Du kan inte skapa vyer om dem eller associera utlösare med dem. Det är lite knepigt att räkna ut vilken process, session eller procedur som skapade dem. Vi ger dig lite hjälp med det senare. Viktigast av allt är att de är säkrare än en global tillfällig tabell eftersom endast ägarprocessen kan se den.

En annan konstighet hos den lokala temporära tabellen (och den lokala tillfälliga lagrade proceduren) är att den har ett annat namn i metadata till den du ger den i din rutin eller sats. Om samma rutin körs samtidigt av flera processer måste databasmotorn kunna skilja mellan de identiskt namngivna lokala tillfälliga tabellerna som skapats av de olika processerna. Det gör det genom att lägga till en numerisk sträng till varje lokalt tillfälligt tabellnamn som lämnas vadderat av understrykningstecken. Även om du anger det korta namnet som #MyTempTable, består det som faktiskt lagras i TempDB av det tabellnamn som anges i CREATE TABLE och suffixet. På grund av detta suffix måste lokala tillfälliga tabellnamn innehålla 116 tecken eller mindre.

Om du är intresserad av att se vad som händer kan du se tabellerna i TempDB på samma sätt som med andra tabell. Du kan till och med använda sp_help arbete på tillfälliga tabeller bara om du åberopar dem från TempDB.

eller så kan du hitta dem i systemvyerna i TempDB utan att byta databaser.

Eller informationsschemat

Ännu bättre, du kan ta reda på vilken process och vilken användare som håller fast vid enorma tillfälliga tabeller i TempDB och vägrar att ge upp utrymmet

Du kan inte använda användardefinierade datatyper i temporära tabeller om inte datatyperna finns i TempDB; det vill säga om inte datatyperna har skapats uttryckligen

Användartabeller i TempDB

I normal användning skapar du tillfälliga tabeller eller tabellvariabler utan att tänka för djupt på det. Det är dock intressant att TempDB är där för någon form av sandlådeaktivitet. Du kan skapa vanliga bastabeller, vyer eller vad du än vill. Du kan skapa scheman, lagrade procedurer och så vidare. Det är osannolikt att du vill göra det, men det är verkligen möjligt eftersom TempDB bara är en annan databas. Jag har bara fått starta om min SQL Server-utveckling efter att ha bevisat detta för mig själv genom att installera AdventureWorks på den. Det betyder att det är möjligt att skapa en bastabell i TempDB, en slags ..er … tillfällig permanent tabell. Till skillnad från den globala tillfälliga tabellen måste du göra all din egen hushållning på den: du är ensam. Detsamma gäller rutiner. Fördelen med att göra detta är att all bearbetning som du gör använder TempDB: s enkla återställning så att SQL Server fungerar som mamma vid nästa start, om du misslyckas med att torka upp. Nästa steg är att ha det jag kallar en ”ihållande tillfällig” tabell. I denna tabell är själva data flyktiga när servern startas om, men själva tabellen kvarstår. Förmodligen det vanligaste sättet att skapa en ihållande tillfällig tabell är att återskapa vid start av en global tillfällig tabell. Detta kan göras automatiskt när alla databaser återställs och meddelandet ”Återställning är klar” loggas. Även om detta är en ”global tillfällig” raderas den inte när alla anslutningar som använder den har försvunnit, eftersom processen som kör den försvinner aldrig. Förmodligen är det bättre att skapa den här typen av arbetstabell i databasen som använder den, men om du använder full återställning kommer det tillfälliga arbetet att finnas kvar i loggen. Du kan naturligtvis bara skapa en vanlig tabell i TempDB. Du kan skapa dessa ”beständiga” tabeller vid start genom att definiera en lagrad procedur i master som skapar den globala tillfälliga tabellen

Varför använda denna typ av hybridtabell? Det finns till exempel ett nummer av tekniker för att skicka tabeller mellan procedurer via beständiga tabeller på ett multiprocess-säkert sätt för att göra en serie bearbetning av data. Dessa hänvisas till en processnycklad tabell (se Hur man delar data mellan lagrade procedurer : Processnycklat bord av Erland Sommarskog ). De kommer inledningsvis att höja ögonbrynen på vilken erfaren DBA som helst men de är en effektiv och säker lösning på ett flerårigt problem när de görs ordentligt.

Förutom tillfälliga tabeller finns det också ett antal bordstyper som inte härrör direkt från bastabeller, till exempel ”falska” tabeller och härledda tabeller: några av dessa är så flyktiga att de bäst betraktas som kortvariga snarare än tillfälliga. CTE använder kortvariga tabeller som är inline eller härledda och som inte materialiseras. BOL hänvisar till dem som ”tillfälliga namngivna resultatuppsättningar”. De finns bara inom ramen för uttrycket. I en CTE har de fördelen över härledda tabeller genom att de kan nås mer än en gång.

Lokal tillfällig tabell

Med lokal temporär tabell (namn som börjar med #), vad som händer under huven liknar förvånansvärt tabellvariabler. Som med tabellvariabler är lokala tillfälliga tabeller privata för processen som skapade den. De kan därför inte användas i vyer och du kan inte associera utlösare med dem.

De är trevligare än tabellvariabler om du vill använda SELECT INTO för att skapa dem, men jag är lite försiktig med att använda SELECT INTO i ett system som sannolikt kommer att kräva modifiering, skulle jag hellre skapa mina tillfälliga tabeller uttryckligen, tillsammans med alla begränsningar som behövs.

Du kan inte lätt berätta vilken session eller procedur som har skapat dessa tabeller. Detta beror på att om samma lagrade procedur körs samtidigt av flera processer, måste databasmotorn kunna skilja på samma tabeller som skapats av de olika processerna. Databasmotorn gör detta genom att internt lägga till ett vänster vadderat numeriskt suffix till varje lokalt tillfälligt tabellnamn. Det fullständiga namnet på en tillfällig tabell som lagrad i sys.objects-vyn i TempDB består av tabellnamnet som anges i CREATE TABLE -utdraget och det systemgenererade numeriska suffixet. För att tillåta suffixet måste tabellnamnet som anges för ett lokalt tillfälligt namn vara mindre än 116 tecken.

Du får hushållning med lokala tillfälliga tabeller. de tappas automatiskt när de går utanför räckvidden, såvida de inte uttryckligen tappas genom att använda DROP TABLE. Deras omfattning är mer generös än en tabellvariabel så att du inte har problem med att referera till dem i batcher eller i dynamisk SQL. Lokala tillfälliga tabeller släpps automatiskt i slutet av den aktuella sessionen eller proceduren. Att släppa den i slutet av proceduren som skapade den kan orsaka huvudskrapor: en lokal tillfällig tabell som skapas inom en lagrad procedur eller session tappas när den är klar så att den inte kan refereras till av processen som kallade den lagrade proceduren som skapade tabellen. Det kan dock hänvisas till av alla kapslade lagrade procedurer som utförs av den lagrade proceduren som skapade tabellen. Om det kapslade förfarandet hänvisar till en tillfällig tabell och två tillfälliga tabeller med samma namn finns vid den tiden, vilken tabell är frågan löst mot?

Som en nyfikenhet kan du också skapa lokala tillfälliga lagrade procedurer med samma omfattning och livstid som en lokal tillfällig tabell. Du kan inte göra detsamma för andra rutiner.

Globala tillfälliga tabeller.

Precis som lokala tillfälliga tabeller tappas globala tillfälliga tabeller (de börjar med ##) automatiskt när sessionen som skapade tabellen slutar: Men eftersom globala tabeller inte är privata för den process som skapade den, de måste fortsätta därefter tills den senaste Transact-SQL-satsen som aktivt hänvisade till tabellen vid den tidpunkt då skapande session avslutades har slutförts och lås tappas. Den som har tillgång till TempDB när dessa globala tillfälliga tabeller finns kan direkt fråga, ändra eller släppa dessa tillfälliga objekt.

Du kan associera regler, standardvärden och index till tillfälliga tabeller, men du kan inte skapa vyer på tillfälliga tabeller eller associera utlösare med dem. Du kan använda en användardefinierad datatyp när du skapar en tillfällig tabell endast om datatypen finns i TempDB

Lagrade procedurer kan referera till temporära tabeller som skapas under den aktuella sessionen. Inom en lagrad procedur kan du inte skapa en tillfällig tabell, släppa den och sedan skapa en ny tillfällig tabell med samma namn.

Även om detta fungerar …

… det här fungerar inte t

Du kan, med hjälp av lokala tillfälliga tabeller, oavsiktligt tvinga om kompilering på den lagrade proceduren varje gång den används. Detta är inte bra eftersom det är osannolikt att det lagrade förfarandet fungerar bra. För att undvika rekompilering, undvik att hänvisa till en tillfällig tabell som skapats i ett samtal eller kallas lagrad procedur: Om du inte kan göra det, lägg sedan referensen i en sträng som sedan körs med EXECUTE uttalande eller sp_ExecuteSQL lagrad procedur. Se också till att den tillfälliga tabellen skapas i den lagrade proceduren eller utlösaren innan den refereras och släpps efter dessa referenser.Skapa inte en tillfällig tabell i ett kontrollflödesuttal som IF... ELSE eller WHILE.

Du får skapa globala tillfälliga lagrade procedurer, men jag har ännu inte hittat något för dem. Globala tillfälliga funktioner är inte tillåtna.

Slutsatser

På en delad lekplats, var mycket försiktig med hur du svänger bat. När du läser detta har du insett att mycket aktivitet pågår i TempDB, och du kan orsaka förödelse för hela SQL Server genom att använda långvariga processer som fyller tillfälliga tabeller, oavsett vilken typ de är, med onödiga mängder av data. I själva verket har jag gett dig ledtrådar i den här artikeln hur man verkligen, verkligen kan störa din DBA genom att inte betrakta den värdefulla delade resursen, TempDB. (Förr i tiden före S2005 var SELECT INTO med ett stort bord det stora V-vapnet (Vergeltungswaffe)

Jag är alltid försiktig med att tillhandahålla över- generaliserade råd, men jag föredrar alltid att mina databaser använder tabellvariabler och TVP: er där det är möjligt. De kräver mindre resurser och det är mindre troligt att du håller på dem när du är klar med dem. Jag gillar att använda dem maximalt , med kolumn- och tabellkontroller och begränsningar. Du kan hitta tider när de tar slut på ånga, särskilt när tabellstorlekar blir större. I sådana fall eller om det inte är praktiskt att använda tabellvariabler på grund av deras begränsade omfattning, Jag kommer att använda lokala tillfälliga tabeller. Det krävs en hel del inpressade läppar och huvudskakningar innan jag går med på ett globalt tillfälligt bord eller ihållande tillfälligt bord. De har några giltiga och helt rimliga användningsområden, men de litar på programmerare att göra nödvändig hushållning