Úvod
Vypočtené sloupce jsou pouze virtuální sloupce, jejichž obsah je výsledkem výrazu. Obvykle se používají k uchování dat na základě ostatních sloupců tabulky. Výraz může obsahovat jiné nevypočítané sloupce tabulky, konstanty, operátory a funkce, ale jako výraz pro vypočtený sloupec nelze zadat dotaz.
Protože se jedná o „virtuální“ sloupce, nejsou uloženy na disku jako zbytek tabulky. Ve skutečnosti se neukládají, ale vypočítávají pokaždé, když se k danému sloupci přistupuje v dotazu. Jak uvidíte, můžete SQL Server donutit, aby sloupec uložil („persistoval“) v tabulce s určitými omezeními.
Nejlepší způsob, jak pochopit, jak vypočítané sloupce fungují, je použít ukázky. Na konci najdete soubor obsahující všechny skripty použité v článku a některé z nich vám ukážeme v textu pro ilustraci výkladu. Pro začátek vytvoříme dvě tabulky: první bude obsahovat informace o fakturách a druhá řádky s detaily těchto faktur. V souboru se skriptem najdete také několik insertů pro vytvoření ukázkových dat.
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
Výpočetní sloupec vytvoříte stejným způsobem jako ostatní sloupce v tabulce, tedy příkazem CREATE TABLE nebo ALTER TABLE. U vypočteného sloupce nahradíme datový typ sloupce výrazem, který bude použit k získání obsahu sloupce. Syntaxe bude následující: název sloupce, za ním klíčové slovo „jako“ a pak výraz. Vytvoříme vypočtený sloupec v tabulce detail_line pro uložení celkové částky linky, kterou vypočítáme vynásobením jednotkové_ceny a množství.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Kontrola vypočteného sloupce
Existuje několik způsobů, jak potvrdit, že sloupec je skutečně vypočtený sloupec. Jedním z nich je použití funkce columnproperty() s uvedením vlastnosti „IsComputed“.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Dalším způsobem, jak získat informace o vypočtených sloupcích, je systémový pohled sys.computed_columns. Toto zobrazení je rozšířením zobrazení sys.columns. To znamená, že sys.computed_columns dědí všechny sloupce z pohledu sys.columns a také přidává další, které jsou specifické pro tento typ sloupce. V průběhu článku se budeme setkávat s některými sloupci tohoto pohledu, protože si ukážeme různé vlastnosti vypočtených sloupců. Prozatím stačí vědět, že tento pohled zobrazuje pouze vypočtené sloupce a má sloupec s názvem is_computed, který říká, zda je sloupec vypočtený, nebo ne. Je zřejmé, že všechny záznamy tohoto zobrazení budou mít v tomto sloupci jedničku.
SELECT name, is_computed FROM sys.computed_columns;
Protože se obsah sloupce počítá pokaždé, když se na něj odkazuje v dotazu, obsah se vždy aktualizuje. Jakákoli změna ve sloupcích, které jsou zahrnuty ve výrazu, se automaticky promítne do hodnoty sloupce. Můžeme se o tom přesvědčit změnou množství v registru v ukázkové tabulce detail_lines a zkontrolovat výsledek.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Krok vpřed
To, co jsme dosud viděli u vypočtených sloupců, je velmi jednoduché, protože jde pouze o provádění výpočtů s jinými sloupci. Vyjádření vypočtených sloupců však může obsahovat funkce, a to jak standardní funkce jazyka T-SQL, tak uživatelsky definované funkce (UDF). Tímto způsobem je možné funkčnost těchto sloupců mnohem více rozšířit.
Podívejme se na ukázku. Do tabulky faktur přidáme vypočtený sloupec, který vypočítá celkovou částku faktury. K tomu musíme získat číslo faktury a dotazem do tabulky detail_lines sečíst celkovou_částku z každého záznamu s daným ID faktury. Nejlepší způsob, jak to provést, je použít funkci, která jako parametr obdrží id faktury a vrátí součet. Poté musíme vytvořit sloupec, který tuto funkci používá.
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
Správnou funkci tohoto sloupce můžeme ověřit přidáním nového záznamu do tabulky detail_lines, takže by se měla změnit celková_účetní částka.
INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity) VALUES (2,'Cup',9.90,1)SELECT id_invoice, customer_name, total_invoice FROM invoices WHERE id_invoice=2
Změna sloupce
Mohou nastat situace, kdy budete muset vypočítaný sloupec upravit. To bohužel není možné. Aby bylo možné tuto změnu provést, je nutné sloupec smazat a znovu jej vytvořit s novým výrazem.
V případě, že vypočtený sloupec používá externí funkci, nebudeme moci tuto funkci měnit. Pokud se o to pokusíme, obdržíme chybové hlášení, že tato funkce je spojena s tabulkou. Pro změnu funkce je nutné sloupec smazat, provést úpravu funkce a nakonec sloupec znovu vytvořit s novou verzí funkce.
Definici sloupce můžeme získat u sloupce „definition“ z pohledu sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Uložení vypočteného sloupce
Jak jsme již zmínili, tyto sloupce jsou „virtuální“, takže nejsou fyzicky uloženy v tabulce. Existuje však možnost vynutit si fyzické uložení výpočtu v tabulce, čemuž se říká „persistence“ sloupce. To může zlepšit výkonnost příkazů SELECT, protože se tak vyhneme nutnosti provádět výpočet sloupce při každém odkazu na něj.
Aby bylo možné sloupec persistovat, musí být navíc výraz použitý k jeho vytvoření „deterministický“. Jak se můžeme dočíst na stránkách společnosti Microsoft, „deterministické funkce vracejí vždy stejný výsledek, kdykoli jsou volány s určitou sadou vstupních hodnot a při stejném stavu databáze“. (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Pokud chceme zjistit, zda SQL Server považuje vyjádření vypočteného sloupce za deterministické, nebo ne, můžeme použít funkci columnproperty() s vlastností „IsDeterministic“.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Pokud je definice sloupce uživatelsky definovaná funkce, můžete také ověřit, zda je tato funkce sama o sobě deterministická, nebo ne. K tomu je třeba použít funkci objectproperty() s vlastností IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Jak je vidět z dotazů, sloupec z prvního příkladu, ve kterém počítáme celkovou cenu detailu, je považován za deterministický. Funkce, která počítá celkovou cenu účtu, je však považována za nedeterministickou. V tabulce tak může být uložen pouze sloupec total_price z tabulky detail_table.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
V zobrazení sys.computed_columns opět vidíte pole is_persisted, které vám ukáže, zda je sloupec v tabulce persistován, nebo ne.
Indexy s vypočtenými sloupci
V indexech je možné použít vypočtené sloupce, musí však splňovat několik požadavků:
- Vlastnictví:
- Determinismus: Všechny funkce, které jsou použity v definici vypočteného sloupce, musí být ve vlastnictví stejného uživatele jako tabulka: Vypočítávaný sloupec musí být deterministický. Pokud sloupec obsahuje CLR výrazy, musí být kromě determinističnosti také persistentní.
- Přesnost: Vyjádření vypočteného sloupce musí být přesné. To znamená, že nemůže být datového typu „float“ nebo „real“. Tento typ dat nelze použít ani v definici. Tuto vlastnost lze ověřit pomocí funkce columnproperty() zadáním vlastnosti IsPrecise.
- Datový typ: Vypočítávaný sloupec nemůže být typu text, ntext nebo obrázek. Také pokud výraz obsahuje datové typy image, ntext, text, varchar (max), nvarchar (max), varbinary (max) nebo xml, lze jej použít pouze v případě, že datový typ vyplývající z výrazu je povolen v indexu.
Kromě těchto úvah musí mít spojení použitá pro vytvoření sloupce a spojení použitá pro vytvoření indexu určitou konfiguraci, aby bylo možné tyto akce provést.
Spojení pro vytvoření vypočteného sloupce musí mít aktivní volbu ANSI_NULLS. To lze ověřit pomocí funkce columnproperty() zadáním vlastnosti IsAnsiNullsOn.
Spojení pro vytvoření indexu je, stejně jako spojení pro provádění vkládání, aktualizace a mazání záznamů, které ovlivňují index, musí mít aktivní volby ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER. Navíc musí být vypnuta volba NUMERIC_ROUNDABORT.
Poslední úvahy
Na závěr si projdeme některé další aspekty, které je nutné znát pro správné použití vypočtených sloupců.
Je zřejmé, že vypočtené sloupce nelze aktualizovat ani zahrnout do seznamu hodnot akce INSERT. Přestože vypočtené sloupce mohou být součástí seznamu výsledků příkazu select, lze je také použít v klauzulích WHERE, ORDER BY nebo ve všech těch, do kterých lze vložit výraz.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Navzdory výše uvedenému nelze vypočtený sloupec použít v definici omezení DEFAULT nebo FOREIGN KEY. Nemůže být ani s definicí omezení NOT NULL.
Na druhou stranu lze vypočtené sloupce použít jako součást omezení PRIMARY KEY nebo UNIQUE. Za tímto účelem by definice vypočteného sloupce měla být deterministickým výrazem.
Závěr
Použití vypočtených sloupců může být v některých situacích velmi užitečné. Je třeba pečlivě prostudovat, kde je použít, protože mají svá omezení, speciálně pro vytváření indexů a jejich persistenci. To je ale jen začátek. Nebojte se zkoušet nové věci a experimentovat s vypočtenými sloupci, abyste našli nové možnosti.