Hrátky s Table Value Constructorem

Table Value Constructor (TVC) je v SQL Serveru sice již od verze 2008, ale vzhledem k tomu, jak moc si s ním můžeme vyhrát, nebude na škodu si projít pár zajímavých příkladů jeho použití. Jen pro zopakování si připomeňme, že TVC je set jednoho nebo více řádkových výrazů, pomocí kterých můžeme vytvořit virtuální tabulku v jazyce T-SQL a s touto virtuální tabulkou syntakticky dále pracovat jako by se jednalo o běžnou tabulku. Můžeme ji použít v příkazu INSERT, na straně <source table> v MERGE a samozřejmě ve FROM příkazu SELECT.

Jako první si ukážeme použití TVC s příkazem INSERT, abychom si naplnili naši testovací tabulku:

CREATE TABLE dbo.SampleTable
(
    Id INT NOT NULL,
    Val VARCHAR(20) NULL DEFAULT ('X')
)
GO

INSERT INTO dbo.SampleTable
    VALUES (1, 'A'), (2, 'B'), (3, 'C')
GO

SELECT * FROM dbo.SampleTable
GO

Vidíme, že v rámci jednoho DML příkazu jsem vytvořili tři virtuální řádky, které se nakonec vložili do cílové tabulky.

Kromě explicitního zadání hodnot můžeme využít i DEFAULT pro vložení výchozí hodnoty do sloupce, případně NULL, není-li hodnota známa:

INSERT INTO dbo.SampleTable
    VALUES (4, 'A'), (5, DEFAULT), (6, NULL)
GO

Že pomocí TVC vytváří skutečně “virtuální tabulku” v rámci daného SQL příkazu snad nejlépe uvidíme, použijeme-li TVC ve FROM jako by se jednalo i jakoukoliv jinou tabulku:

SELECT * 
FROM (VALUES (1, 'A'), 
             (2, 'B'), 
             (3, 'C')
      ) a (Id, Val)
GO

A nebo ještě lépe, můžeme si pro větší názornost několik TVC mezi sebou propojit:

SELECT * 
FROM (VALUES (1, 'A'), 
             (2, 'B'), 
             (3, 'C')
      ) a (Id, Val)
    INNER JOIN (VALUES (1, 'A'), 
                       (2, 'B'), 
                       (3, 'C')
                ) b (Id, Val) ON a.Id = b.Id
GO

Opravdovou lahůdkou je potom možnost tvořit jednotlivé hodnoty řádku pomocí klasického příkazu SELECT:

SELECT * 
FROM (VALUES (1, 'A'), 
             (2, (SELECT 'B')),
             (3, (SELECT TOP(1) LEFT(NAME, 20) FROM sys.objects WHERE NAME LIKE '%%'))
     ) a (Id, Val)
GO

TVC je také možné libovolně vzájemně zanořovat:

SELECT * 
FROM (VALUES (1, 
              (SELECT Val FROM (VALUES ('A')) b (Val))
              )
      ) a (Id, Val)
GO

Stejně tak můžeme TVC dotazovat jako klasikou tabulku v různých kombinacích SELECT příkazu:

SELECT 
    (SELECT TOP(1) Id FROM (VALUES (1, 'A'), (2, 'B'), (3, 'C')) a (Id, Val) ORDER BY Val DESC) Id
FROM (SELECT 1 a) a
GO

Co ovšem již udělat nemůže je, pokusit se použít TVC s jinými DML příkazy než INSERT, tedy s UPDATE nebo DELETE, případně na <target table> straně příkazu MERGE:

UPDATE a
	SET Id = 5
--SELECT * 
FROM (VALUES (1, 'A'), 
             (2, 'B'), 
             (3, 'C')
      ) a (Id, Val)
GO

To vše samozřejmě platí pouze tehdy, pokoušíme-li se měnit data vytvořená pomocí TVC. Využít TVC následujícím způsobem možné je, neboť zde je součástí SELECT příkazu:

UPDATE st
	SET Val = 'UPD_' + CAST(a.Id AS VARCHAR(10))
--SELECT *
FROM dbo.SampleTable st
	INNER JOIN (VALUES(1), (2)) a (Id) on st.ID = a.ID
GO

Pokud jsem hovořili o použití TVC s příkazem MERGE na místě <source table>, vypadá náš příklad takto:

MERGE INTO dbo.SampleTable trg
USING 
	(VALUES
		(3, 'MRG'),
		(4, 'NEW')
	) AS src (Id, Val)
	ON trg.Id = src.Id
WHEN NOT MATCHED THEN
	INSERT (Id, Val)
	VALUES (src.Id, src.Val)
WHEN MATCHED THEN UPDATE 
	SET trg.Val = src.Val;
GO

Na co nesmíme zapomenout při využít TVC ve FROM jsou pravidla pro pojmenování sloupců u odvozených tabulek: První příkaz skončí s chybou, protože jsem zapomněli pojmenovat sloupce. Jak to napravit ukazuje druhý příkaz:

SELECT * FROM (VALUES (1, 'A')) a
GO

SELECT * FROM (VALUES (1, 'A')) a (Id, Val)
GO

Pozor je třeba dát i na to, že DEFAULT můžeme použít pouze tehdy, vkládáme-li data do tabulky v rámci INSERT příkazu, nikoliv v pouhém SELECTu:

SELECT * FROM (VALUES (1, DEFAULT)) a (Id, Val)
GO