Redgate Hub (Čeština)

Dočasné tabulky jsou právě takové. Používají se nejčastěji k poskytnutí pracovního prostoru pro mezivýsledky při zpracování dat v dávce nebo proceduře. Používají se také k předání tabulky z funkce s hodnotou tabulky, k předání dat založených na tabulce mezi uloženými procedurami nebo, nověji ve formě parametrů s hodnotou tabulky, k odeslání celých tabulek jen pro čtení z aplikací na rutiny serveru SQL Server , nebo předat dočasné parametry jen pro čtení jako parametry. Jakmile je jejich použití dokončeno, automaticky se zahodí.

Než se do této technologie dostaneme příliš hluboko, doporučujeme, abyste pokud možno používali proměnné tabulky. Jsou snadné a SQL Server pracuje za vás. Mají také tendenci způsobovat méně problémů pracovitému systému OLTP. Jen příležitostně je možná budete muset doladit, abyste od nich získali dobrý výkon v připojeních, ale vysvětlím to za okamžik, pokud však provádíte složitější zpracování dočasných dat nebo pravděpodobně použijete více než rozumně malé množství dat v nich, pak místní dočasné tabulky pravděpodobně budou lepší volbou.

Proměnné tabulky

Proměnné tabulky se používají v rámci rutiny nebo dávky, ve které jsou definované a byly původně vytvořeny, aby umožnily funkce s tabulkovou hodnotou. Jsou však dobré pro mnoho použití, k nimž byla přidána tradiční dočasná tabulka. Ve svých pravidlech pro stanovení rozsahu se chovají jako jiné proměnné. Jakmile jsou mimo rozsah, jsou zlikvidovány. S nimi je mnohem snazší pracovat a jsou docela bezpečné a také spouštějí méně rekompilací v rutinách, kde se používají, než kdybyste používali dočasné tabulky. Proměnné tabulky vyžadují méně zamykacích prostředků, protože jsou „soukromé“ pro proces, který je vytvořil. Vrácení transakcí je neovlivní, protože proměnné tabulky mají omezený rozsah a nejsou součástí trvalé databáze, takže jsou užitečné pro vytváření nebo ukládání dat, která by měla přežít vrácení zpět, například položky protokolu. Nevýhodou tabulkových proměnných je, že se často zlikvidují, než budete moci prozkoumat jejich obsah pro ladění nebo je použít k interaktivnímu vyzkoušení různých výrazů SQL.

Pokud je vaše aplikace konzervativní a vaše objemy dat vás osvětlí „Nikdy nebudu chtít nic jiného. Můžete však narazit na problémy. Jednou z potíží je, že na proměnné tabulky lze odkazovat pouze v jejich lokálním rozsahu, takže je nemůžete zpracovat pomocí dynamického jazyka SQL, jako byste to mohli udělat s dočasným parametrem tabulky nebo tabulky. Důvodem je, že nemůžete odkazovat na externě definovanou proměnnou tabulky v rámci dynamického SQL, kterou poté provedete pomocí příkazu EXEC nebo sp_ExecuteSQL uložené procedury, protože dynamický SQL se provádí mimo rozsah proměnné tabulky. Samozřejmě můžete vytvořit a poté použít proměnnou tabulky uvnitř dynamického SQL, protože proměnná tabulky by byla v rozsahu. Jakmile je však spuštěn dynamický SQL, nebude existovat žádná proměnná tabulky.

Je třeba si také uvědomit několik anomálií. Po počátečním prohlášení DECLARE nemůžete například změnit definici tabulky. Na serveru SQL Server 2000 nemůže být proměnná tabulky cílem příkazu SELECT INTO nebo INSERT EXEC (nyní opraveno); Uživatelsky definované funkce nelze volat z omezení CHECK, hodnot DEFAULT a vypočítaných sloupců v proměnné tabulky. Jedinými omezeními, která jsou povolena nad omezeními CHECK, jsou PRIMARY KEY, UNIQUE KEY a NULL / NOT NULL.

Nejsložitější problémy však přicházejí s rostoucí velikostí tabulek, protože před SQL Server 2016 , nemohli jste explicitně deklarovat index a indexy, které vynucovaly omezení UNIQUE a PRIMARY KEY, neměly na nich udržované distribuční indexy. Nyní můžete vytvořit určité typy indexů v souladu s definicí tabulky, ale statistiky distribuce na nich stále nejsou udržovány. Optimalizátor dotazů předpokládá, že v tabulce je pouze jeden řádek. Rovněž nemůžete vygenerovat plány paralelních dotazů pro výraz SQL, který upravuje obsah tabulky. Chcete-li částečně obejít omezení indexu, můžete použít omezení k provedení stejné věci. Nejdůležitější je omezení primárního klíče, které vám umožňuje uložit seskupený index, ale jedinečná omezení jsou užitečná pro výkon. Optimalizátor dotazů je šťastně použije, pokud jsou k dispozici.

Největším problémem s proměnnými tabulky je, že se ve sloupcích neudržují statistiky. To znamená, že optimalizátor dotazů musí odhadnout velikost a distribuci dat, a pokud se to pokazí, uvidíte špatný výkon při připojení: Pokud k tomu dojde, je jen málo, co můžete udělat další než se vrátit k používání klasických místních dočasných tabulek. Počínaje serverem SQL Server 2019 společnost Microsoft představila novou funkci nazvanou Odložená kompilace proměnné tabulky, která tento problém řeší. Chcete-li se dozvědět více, přečtěte si tento článek od Grega Larsena.

Pokud nepoužíváte SQL Server 2019, můžete zkusit jednu věc – přidat do příkazu, který spojuje proměnnou tabulky s jinými tabulkami, OPTION (RECOMPILE). Tímto způsobem bude SQL Server schopen detekovat počet řádků při překompilaci, protože řádky již budou vyplněny. To problém zcela nevyřeší, protože optimalizátor stále nemá žádné statistiky distribuce a může, obvykle tam, kde je distribuce vychýlená, vytvořit špatný plán. V této ukázce bylo spojení časově zredukováno o tři čtvrtiny jednoduše přidáním MOŽNOSTI (RECOMPILACE)

Nyní, když můžete udělat to, co jde do tabulek, jedinečné, můžete použít omezení primárního klíče na tyto tabulky. To umožnilo optimalizátoru použít místo skenování tabulky hledání seskupeného indexu a doba provádění byla příliš rychlá na to, aby bylo možné měřit

Začněte s proměnnými tabulky, ale pokud narazíte na problémy s výkonem, přejděte zpět k používání místních dočasných tabulek. Někteří lidé jsou dost smělí na to, aby poradili, pokud jde o počet řádků v tabulce, a viděl jsem maximálně 100 nebo 1000; ale viděl jsem, že mnohem větší proměnné tabulky fungují v průběhu času naprosto uspokojivě a mnohem menší způsobují potíže. U menších tabulek je však problém méně zjistitelný!

Parametry s hodnotami v tabulce

Parametr s hodnotami v tabulce (TVP) je speciální typ proměnné tabulky, který rozšiřuje jeho použití. Když jsou proměnné tabulky předávány jako parametry, tabulka se zhmotní v systémové databázi TempDB jako proměnná tabulky a předá se odkazem, ukazatel na tabulku v TempDB.

Parametry s hodnotou tabulky se používají od SQL Server 2008 k odeslání několika řádků dat do rutiny Transact-SQL nebo do dávky prostřednictvím sp_ExecuteSQL .. Jejich zvláštní hodnota pro programátora je, že je lze použít v kódu TSQL jako stejně jako v klientské aplikaci, takže jsou dobré pro odesílání klientských tabulek na server. Z TSQL můžete deklarovat proměnné s hodnotou tabulky, vložit do nich data a předat tyto proměnné jako parametry s hodnotou tabulky uloženým procedurám a funkcím. Jejich obecnější užitečnost je omezena skutečností, že jsou předávány pouze pro čtení. U tabulky s hodnotou nemůžete dělat UPDATE, DELETE nebo INSERT parametr v těle rutiny.

Musíte vytvořit typ tabulky definovaný uživatelem a definovat strukturu tabulky, abyste je mohli použít. Zde je jednoduchý příklad jejich použití v TSQL

Stejně jako u proměnných tabulky, parametr s hodnotou tabulky přestane existovat, jakmile bude mimo rozsah, ale definice typu zůstane, dokud nebude výslovně zrušena.Stejně jako proměnné tabulky nezískávají zámky, když se data vyplňují od klienta, a statistiky se neudržují ve sloupcích parametrů s hodnotou tabulky. Jako cíl příkazu SELECT INTO nebo INSERT EXEC nemůžete použít parametr s hodnotou tabulky. Jak byste očekávali, parametr s hodnotou tabulky může být v klauzuli FROM SELECT INTO nebo v INSERT EXEC řetězec nebo uložená procedura.

TVP řeší běžný problém chtít předat lokální proměnnou do dynamického SQL, která je poté provedena sp_ExecuteSQL. Je to špatně zdokumentováno společností Microsoft, takže vám ukážu fungující příklad, jak začít

Než přejdeme k popisu tradičnějších dočasných tabulek a jejich použití, budeme se muset ponořit do místo, kde se konají dočasné stoly. TempDB.

TempDB

Dočasné tabulky a proměnné tabulky se vytvářejí v databázi TempDB, což je ve skutečnosti jen další databáze s jednoduchým zotavením: U TempDB se provádí pouze dostatečné „minimální“ protokolování umožnit vrácení zpět a další výhody ACID. Zvláštní rozdíl TempDB spočívá v tom, že všechny objekty, jako jsou tabulky, jsou při spuštění vymazány. Protože TempDB vždy používá jednoduchý model obnovy, jsou dokončené transakce vymazány z protokolu protokolu na dalším kontrolním bodu TempDB a jsou zachovány pouze živé transakce. To vše znamená, že dočasné tabulky se chovají jako jakýkoli jiný druh základní tabulky, protože jsou protokolovány a ukládány stejně jako oni. V praxi dočasné tabulky pravděpodobně zůstanou v mezipaměti v paměti, ale pouze v případě, že se často používají: stejné jako u základní tabulky. TempDB provozuje systém zvaný dočasné použití objektů, který bude ukládat do mezipaměti část dočasných objektů s plánem, pokud je dostatek paměti. To může odpovídat legendě, že dočasné objekty existují pouze v paměti. Pravda jako vždy je „záleží…“.

V TempDB se odehrává spousta dalších věcí: Databázový stroj jej může použít k umisťování pracovních tabulek pro kontroly DBCC, k vytváření nebo opětovnému vytváření indexů, kurzorů například. Mezilehlé tabulky v dotazech popsaných jako „hashe“, „sorts“ a „spools“ se zhmotňují například v TempDB spolu s těmi, které jsou vyžadovány pro několik „fyzických“ operací při provádění příkazů SQL. Používá se také jako úložiště verzí pro izolaci snímků, více sad aktivních výsledků (MARS), spouštěčů a vytváření online indexů.

Protože dočasné tabulky jsou uloženy stejně jako základní tabulky, existuje jedna nebo dvě věcí, na které si musíte dávat pozor. Chcete-li vytvořit normální tabulku, musíte mít například oprávnění CREATE TABLE v TempDB. Abychom vám ušetřili potíže, je toto ve výchozím nastavení přiřazeno roli DBO (vlastník databáze), ale možná to budete muset udělat výslovně pro uživatele, kterým role DBO není přiřazena. Všichni uživatelé mají oprávnění k vytváření místních nebo globálních dočasných tabulek v TempDB, protože jim je přiřazeno prostřednictvím GUEST kontextu zabezpečení uživatele.

Klasická dočasná tabulka je součástí dvě příchutě, globální nebo sdílená dočasná tabulka s předponou ## a místní dočasná tabulka, jejíž název má předponu #. Místní dočasné tabulky jsou méně podobné normálním tabulkám než globální dočasné tabulky: nemůže na ně vytvářet pohledy nebo k nim přidružovat spouštěče. Je trochu složité zjistit, který proces, relace nebo postup je vytvořil. S tím vám později trochu pomůžeme. Nejdůležitější je, že jsou bezpečnější než globální dočasná tabulka, protože ji vidí pouze vlastnický proces.

Další zvláštností místní dočasné tabulky (a místní dočasné uložené procedury) je, že má jiný název v metadatech k těm, která jim dáte ve své rutině nebo dávce. Pokud je stejná rutina spuštěna současně několika procesy, musí být databázový stroj schopen rozlišovat mezi stejnými názvy lokálních dočasných tabulek vytvořených různými procesy. Dělá to přidáním číselného řetězce ke každému místnímu dočasnému názvu tabulky, který je vlevo doplněn znaky podtržítka. Ačkoli zadáte krátký název, například #MyTempTable, to, co je ve skutečnosti uloženo v TempDB, se skládá z názvu tabulky uvedeného ve výrazu CREATE TABLE a přípona. Z důvodu této přípony musí mít místní názvy dočasných tabulek maximálně 116 znaků.

Pokud máte zájem vidět, o co jde, můžete tabulky v TempDB prohlížet stejným způsobem jako ostatní. stůl. Můžete dokonce použít sp_help práci na dočasných tabulkách, pouze pokud je vyvoláte z TempDB.

nebo je můžete najít v systémových pohledech TempDB bez přepínání databází.

Nebo informační schéma

Ještě lépe, můžete zjistit, jaký proces a uživatel se drží obrovských dočasných tabulek v TempDB a odmítá se vzdát prostoru

V dočasných tabulkách nemůžete použít uživatelem definované datové typy, pokud v TempDB tyto datové typy neexistují; to znamená, pokud nebyly datové typy výslovně vytvořeny.

Uživatelské tabulky v TempDB

Při běžném používání vytvoříte dočasné tabulky nebo proměnné tabulky, aniž byste o tom příliš hluboce přemýšleli. Je však zajímavé, že TempDB je tu pro jakýkoli druh sandboxové aktivity. Můžete vytvořit běžné základní tabulky, pohledy nebo cokoli jiného, co chcete. Můžete vytvářet schémata, uložené procedury atd. Je nepravděpodobné, že byste to chtěli udělat, ale je to jistě možné, protože TempDB je jen další databáze. Právě jsem musel restartovat svůj vývojový SQL Server poté, co jsem si to dokázal instalací AdventureWorks. To znamená, že je možné vytvořit základní tabulku v TempDB, jakousi … er … dočasnou trvalou tabulku. Na rozdíl od globálního dočasného stolu byste na něm museli provádět všechny své vlastní úklidové služby: jste sami. Totéž platí o rutinách. Výhodou toho je, že jakékoli zpracování, které provádíte, používá jednoduchou obnovu TempDB, takže pokud se vám nepodaří vyčistit, SQL Server bude při příštím spuštění fungovat jako matka: i když to může být velmi dlouhá doba. Další fází je mít tabulku, kterou nazývám „trvalý dočasný“. V této tabulce jsou data sama o sobě volatilní, když se server restartuje, ale samotná tabulka přetrvává. Pravděpodobně nejběžnějším způsobem, jak vytvořit trvalou dočasnou tabulku, je znovu vytvořit při spuštění globální dočasnou tabulku. To lze provést automaticky, když jsou obnoveny všechny databáze a je zaznamenána zpráva „Obnova je dokončena.“ I když se jedná o „globální dočasný“, nebude odstraněn, když zmizí všechna připojení, která jej používají, protože proces, který jej spouští nikdy nezmizí. Je pravděpodobné, že je lepší vytvořit tento druh pracovní tabulky v databázi, která ji používá, i když, pokud používáte úplné zotavení, dočasná práce zůstane v protokolu. Samozřejmě můžete vytvořit běžnou tabulka v TempDB. Tyto „trvalé“ tabulky můžete vytvořit při spuštění tak, že definujete uloženou proceduru v hlavní složce, která vytvoří globální dočasnou tabulku.

Proč používat tento druh hybridní tabulky? Existuje například řada technik pro předávání tabulek mezi procedurami prostřednictvím „perzistentních“ tabulek bezpečným způsobem s více procesy, aby bylo možné provést řadu zpracování dat. Jedná se o tabulky s procesním klíčem (viz „Jak sdílet data mezi uloženými procedurami“ : Tabulka s procesním klíčem od Erlanda Sommarskoga ). Zpočátku zvednou obočí jakéhokoli ostříleného DBA, ale jsou účinným a bezpečným řešením trvalého problému, pokud jsou provedeny správně.

Kromě dočasných stolů existuje také řada typů stolů které nejsou přímo odvozeny ze základních tabulek, jako jsou „falešné“ tabulky a odvozené tabulky: některé z nich jsou tak pomíjivé, že je nejlepší je považovat spíše za pomíjivé než dočasné. CTE používá pomíjivé tabulky, které jsou „vložené“ nebo „odvozené“ a nejsou zhmotněny. BOL je označuje jako „dočasně pojmenované sady výsledků“. Existují pouze v rámci rozsahu výrazu. V CTE mají oproti odvozeným tabulkám výhodu v tom, že k nim lze přistupovat vícekrát.

Místní dočasná tabulka

S místní dočasnou tabulkou (názvy začínající znakem #), to, co se děje pod kapotou, je překvapivě podobné tabulkovým proměnným. Stejně jako u proměnných tabulek jsou místní dočasné tabulky soukromé pro proces, který je vytvořil. Nelze je tedy použít v pohledech a nelze s nimi spojit spouštěče.

Jsou šikovnější než proměnné tabulky, pokud je chcete vytvořit pomocí SELECT INTO, ale při používání SELECT INTO v systému, který pravděpodobně bude vyžadovat úpravy, bych raději vytvořil dočasné tabulky výslovně spolu se všemi omezeními, která jsou zapotřebí.

Nelze snadno určit, která relace nebo procedura byla vytvořena tyto tabulky. Důvodem je, že pokud je stejná uložená procedura spuštěna současně několika procesy, Database Engine musí být schopen rozlišit stejné tabulky vytvořené různými procesy. Databázový stroj to dělá tak, že interně připojí levou polstrovanou číselnou příponu ke každému názvu místní dočasné tabulky. Celé jméno dočasné tabulky uložené v zobrazení sys.objects v TempDB je tvořeno názvem tabulky zadaným v příkazu CREATE TABLE a číselnou příponou generovanou systémem. Chcete-li povolit příponu, název tabulky zadaný pro místní dočasný název musí mít méně než 116 znaků.

S místními dočasnými tabulkami získáte úklid; automaticky se zruší, když vyjdou z rozsahu, pokud to není výslovně zrušeno pomocí DROP TABLE. Jejich rozsah je velkorysější než proměnná tabulky, takže nemáte problémy s jejich odkazováním v dávkách nebo v dynamickém SQL. Místní dočasné tabulky se automaticky zruší na konci aktuální relace nebo procedury. Puštění na konci procedury, která ji vytvořila, může způsobit poškrábání hlavy: místní dočasná tabulka, která je vytvořena v uložené proceduře nebo relaci, je po dokončení zrušena, takže na ni nemůže odkazovat proces, který zavolal uloženou proceduru, která vytvořil tabulku. Může na ni však odkazovat libovolné vnořené uložené procedury prováděné uloženou procedurou, která vytvořila tabulku. Pokud vnořená procedura odkazuje na dočasnou tabulku a v té době existují dvě dočasné tabulky se stejným názvem, u které tabulky je dotaz vyřešen?

Jako kuriozitu můžete také vytvořit místní dočasné uložené procedury pomocí stejný rozsah a životnost jako místní dočasná tabulka. To samé pro jiné rutiny udělat nemůžete.

Globální dočasné tabulky.

Stejně jako místní dočasné tabulky, i globální dočasné tabulky (začínají znakem ##) se po ukončení relace, která tabulku vytvořila, automaticky zruší: Protože však globální tabulky nejsou soukromé pro proces, který jej vytvořil, musí poté přetrvávat, dokud se nespustí poslední příkaz Transact-SQL, který aktivně odkazoval na tabulku v době, kdy skončila relace vytváření, a zámky jsou zrušeny. Kdokoli, kdo má přístup k TempDB v době, kdy existují tyto globální dočasné tabulky, může tyto dočasné objekty přímo dotazovat, upravovat nebo rušit.

K dočasným tabulkám můžete přidružit pravidla, výchozí hodnoty a indexy, ale nemůžete vytvářet pohledy na dočasných stolech nebo k nim přiřadit spouštěče. Uživatelsky definovaný datový typ můžete použít při vytváření dočasné tabulky, pouze pokud datový typ existuje v TempDB.

Uložené procedury mohou odkazovat na dočasné tabulky, které jsou vytvořeny během aktuální relace. V rámci uložené procedury nemůžete vytvořit dočasnou tabulku, zrušit ji a poté vytvořit novou dočasnou tabulku se stejným názvem.

Ačkoli to funguje ….

… to není t

Pomocí místních dočasných tabulek můžete nechtěně vynutit rekompilaci uložené procedury při každém použití. To není dobré, protože je nepravděpodobné, že uložená procedura bude fungovat dobře. Abyste se vyhnuli rekompilaci, nepoužívejte odkaz na dočasnou tabulku vytvořenou ve volající nebo volané uložené proceduře: Pokud to nemůžete udělat, vložte odkaz do řetězce, který se poté provede pomocí EXECUTE nebo sp_ExecuteSQL uložená procedura. Také se ujistěte, že je dočasná tabulka vytvořena v uložené proceduře nebo aktivačním procesu, než na ni bude odkazováno a po těchto odkazech zahozeno.Nevytvářejte dočasnou tabulku v příkazu kontroly toku, například IF... ELSE nebo WHILE.

Máte povoleno vytvářet globální dočasné uložené procedury, ale zatím jsem pro ně nenašel využití. Globální dočasné funkce nejsou povoleny.

Závěry

Na jakémkoli sdíleném hřišti buďte velmi opatrní, jak touto pálkou houpáte. Během čtení jste si uvědomili, že v TempDB probíhá spousta aktivit a že můžete způsobit zmatek celému serveru SQL pomocí dlouhotrvajících procesů, které vyplňují dočasné tabulky, ať jsou jakéhokoli typu, zbytečným množstvím data. Ve skutečnosti jsem vám v tomto článku poskytl vodítka, jak skutečně, opravdu rozrušit vaši DBA neuváženým používáním tohoto vzácného sdíleného zdroje, TempDB. (Za starých časů před S2005 bylo použití SELECT INTO s obrovským stolem skvělou V-zbraní (Vergeltungswaffe)

Vždy se obávám, že zobecněná rada, ale vždy dávám přednost tomu, aby moje databáze používaly proměnné tabulky a TVP, kdykoli je to možné, vyžadují méně zdrojů a je méně pravděpodobné, že se jich budete držet, až s nimi skončíte. , s kontrolami a omezeními sloupců a tabulek. Můžete se setkat s okamžiky, kdy jim dojde pára, zvláště když se velikost tabulky zvětší. V takových případech nebo tam, kde není praktické používat proměnné tabulky kvůli jejich omezenému rozsahu, pak Použiji místní dočasné tabulky. Než budu souhlasit s globálním dočasným stolem nebo přetrvávajícím dočasným stolem, je potřeba hodně našpulených rtů a potřásání hlav. Mají několik platných a naprosto rozumných použití, ale spoléhají na programátor, aby provedl nezbytné úklidové práce