Čtení JSON formátu v SQL serveru

Pro JSON není v SQL serveru spciální datový typ. Používá se NVARCHAR(MAX), JSON formát můžu vynutit constraintem pomocí funkce ISJSON.


Jako přiklad si udělám tempovou tabulku s constraintem

CREATE TABLE #json(
	JSONData NVARCHAR(MAX) NOT NULL
		CONSTRAINT check_JSONData_is_json CHECK (ISJSON(JSONData) = 1)
)
GO



Pokusím se vložit něco, co není JSON

INSERT INTO #json(JSONData) VALUES('nesmysl')
GO
--The INSERT statement conflicted with the CHECK constraint "check_JSONData_is_json". The conflict occurred in database "tempdb", table "dbo.#json_______________________________________________________________________________________________________________000000000067", column 'JSONData'.



Založím testovací data

INSERT INTO #json(JSONData) VALUES(
N'
[
	{
		"jmeno": "Kamil",
		"prijmeni": "Skočdopole",
		"konicky": [
			"šachy",
			"sport",
			"vážná hudba"
		],
		"adresy": [
			{
				"typ": "bydliště",
				"mesto": "Frenštát pod Radhoštěm",
				"cp": "10"
			},
			{
				"typ": "doručovací",
				"mesto": "Ostrava",
				"cp": "50"
			}
		],
		"hodne pije": true,
		"telesna_teplota": 36.9,
		"zbrojni_pas":null
	},
	{
		"jmeno": "Uršula",
		"prijmeni": "Králová",
		"konicky": [
			"pletení",
			"ultramaraton"
		],
		"adresy": [
			{
				"typ": "bydliště",
				"mesto": "Frenštát pod Radhoštěm",
				"cp": "10"
			}
		]
	}
]
'
)

SELECT * FROM #json



Otestování, zda máme validní JSON

SELECT ISJSON((SELECT * FROM #json))--vrací 1



JSON_QUERY vrací JSON objekt, nebo pole

SELECT JSON_QUERY((SELECT * FROM #json))
SELECT JSON_QUERY((SELECT * FROM #json), '$[0].adresy')
SELECT JSON_QUERY((SELECT * FROM #json), '$[1].adresy')
SELECT JSON_QUERY((SELECT * FROM #json), '$[1].konicky')



Striktní vs. laxní mód

By deafault je laxní

--NULL, protoze jmeno je hodnota a ne objekt
SELECT JSON_QUERY((SELECT * FROM #json), '$[0].jmeno')

--NULL, protoze jmeno je hodnota a ne objekt
SELECT JSON_QUERY((SELECT * FROM #json), 'lax $[0].jmeno')

--Object or array cannot be found in the specified JSON path.
SELECT JSON_QUERY((SELECT * FROM #json), 'strict $[0].jmeno')



JSON_VALUE vrací hodnotu NVARCHAR(4000)

SELECT JSON_VALUE((SELECT * FROM #json), '$[0].jmeno')--Kamil



Pokud je v názvu atributu mezera, tak ho musím mít v uvozovkách

--JSON path is not properly formatted. Unexpected character ' ' is found at position 10.
SELECT JSON_VALUE((SELECT * FROM #json), '$[0].hodne pije')

--true
SELECT JSON_VALUE((SELECT * FROM #json), '$[0]."hodne pije"')



Neexistující atribut je problém rozeznat od atributu s NULL hodnotou

--NULL
SELECT JSON_VALUE((SELECT * FROM #json), '$[0].neexistujici')

--Property cannot be found on the specified JSON path.
SELECT JSON_VALUE((SELECT * FROM #json), 'strict $[0].neexistujici')



Atribut s NULL hodnotou

--NULL
SELECT JSON_VALUE((SELECT * FROM #json), '$[0].zbrojni_pas')

--NULL
SELECT JSON_VALUE((SELECT * FROM #json), 'strict $[0].zbrojni_pas')

OPENJSON

Další možností čtení JSONu je s použitím funkce OPENJSON. Umožňuje přečíst více než 4000 znaků a vracet data v tabulkové podobě.

Syntaxe je:

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]
SELECT *
FROM OPENJSON((SELECT * FROM #json))

Výsledkem je tabulka se sloupci key, value, type
openjson1

key
název atributu, nebo pořadové číslo v poli
value
hodnota
type
0 NULL Rozumná cesta jak rozlišit hodnotu NULL od neexistujícího atributu
1 string
2 číslo
3 boolean
4 pole
5 objekt

Dá se k tomu doplnit cesta

SELECT *
FROM OPENJSON((SELECT * FROM #json),'$[0]')

openjson2

SELECT *
FROM OPENJSON((SELECT * FROM #json),'$[0].konicky')

openjson3

Doplněním WITH kluzule uděláme z JSON tabulku, kde budou atributy ve sloupcích


Syntaxe:

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50),
	prijmeni		NVARCHAR(50),
	[hodne pije]	BIT,
	nesmysl			NVARCHAR(50),
	zbrojni_pas		BIT
)

openjson4

Můžeme doplnit column_path a přejmenovat si na vlastní colName

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50)	'$.jmeno',
	prijmeni		NVARCHAR(50)	'$.prijmeni',
	[ochlasta]		BIT				'$."hodne pije"',
	nesmysl			NVARCHAR(50)	'$.nesmysl',
	zbrojni_pas		BIT				'$.zbrojni_pas',
	prvniKonicek	NVARCHAR(250)	'$.konicky[0]',
	mesto			NVARCHAR(250)	'$.adresy[0].mesto'
)

openjson5
Pokud chceme vrátit JSON, nebo pole, tak musíme použít AS JSON, jinak dostaneme NULL

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50)	'$.jmeno',
	prijmeni		NVARCHAR(50)	'$.prijmeni',
	[ochlasta]		BIT				'$."hodne pije"',
	nesmysl			NVARCHAR(50)	'$.nesmysl',
	zbrojni_pas		BIT				'$.zbrojni_pas',
	adresy1			NVARCHAR(MAX)	'$.adresy',				--pokud je tam objekt, nebo pole, 
															--tak nevrátí nic v lax modu. strict by spadnul
	adresy2			NVARCHAR(MAX)	'$.adresy' AS JSON		--je potřeba použít AS JSON
)

openjson6

Vrácený JSON z první funkce můžeme dál rozebrat pomocí APPLY OPENSJON

SELECT *
FROM OPENJSON((SELECT * FROM #json)) WITH(
	jmeno			NVARCHAR(50)	'$.jmeno',
	prijmeni		NVARCHAR(50)	'$.prijmeni',
	[ochlasta]		BIT				'$."hodne pije"',
	nesmysl			NVARCHAR(50)	'$.nesmysl',
	zbrojni_pas		BIT				'$.zbrojni_pas',
	adresy			NVARCHAR(MAX)	'$.adresy' AS JSON
)pole
OUTER APPLY OPENJSON(pole.adresy) WITH(
	typ			NVARCHAR(50)	,
	mesto		NVARCHAR(50)	,
	cp			NVARCHAR(50)
)

openjson7

Tvoření JSONu v SQL – FOR JSON

Pár příkladů jak vygenerovat JSON v SQL serveru.

Nejdřív tabulky a data, které se pak budeme snažit vysypat ve formátu JSON.

IF OBJECT_ID('dbo.Kategorie') IS NULL
CREATE TABLE dbo.Kategorie(
    IdKategorie INT NOT NULL IDENTITY CONSTRAINT pk_Kategorie PRIMARY KEY CLUSTERED,
    Kod NVARCHAR(50) NOT NULL CONSTRAINT uq_Kategorie_Kod UNIQUE,
    Nazev NVARCHAR(250) NOT NULL,
    PlatnostOd DATETIME NOT NULL,
    PlatnostDo DATETIME NOT NULL
)
GO
 
IF OBJECT_ID('dbo.KategorieTyp') IS NULL
CREATE TABLE dbo.KategorieTyp(
    IdKategorieTyp INT NOT NULL IDENTITY CONSTRAINT pk_KategorieTyp PRIMARY KEY CLUSTERED,
    IdKategorie INT NOT NULL CONSTRAINT fk_KategorieTyp_Kategorie FOREIGN KEY (IdKategorie) REFERENCES dbo.Kategorie(IdKategorie),
    Kod NVARCHAR(50) NOT NULL CONSTRAINT uq_KategorieTyp_Kod UNIQUE,
    Nazev NVARCHAR(250) NOT NULL,
    PlatnostOd DATETIME NOT NULL,
    PlatnostDo DATETIME NOT NULL
)
GO


INSERT INTO dbo.Kategorie
(
    Kod,
    Nazev,
    PlatnostOd,
    PlatnostDo
)
SELECT
     data.Kod
    ,data.Nazev
    ,GETDATE()
    ,'20991231'
FROM (VALUES
    (N'domacnost'   ,N'domácnost'   ),
    (N'podnikatel'  ,N'podnikatel'  )
)data(Kod           ,Nazev)
WHERE NOT EXISTS(
    SELECT * 
    FROM  dbo.Kategorie k
    WHERE k.Kod = data.Kod
)
GO
 
INSERT INTO dbo.KategorieTyp
(
    IdKategorie,
    Kod,
    Nazev,
    PlatnostOd,
    PlatnostDo
)
SELECT
     k.IdKategorie
    ,data.Kod
    ,data.Nazev
    ,GETDATE()
    ,'20991231'
FROM (VALUES
    (N'domacnost'   ,N'dum'         ,N'dům'         ),
    (N'domacnost'   ,N'byt'         ,N'byt'         ),
    (N'domacnost'   ,N'chata'       ,N'chata'       ),
    (N'podnikatel'  ,N'garaz'       ,N'garáž'       ),
    (N'podnikatel'  ,N'provozovna'  ,N'provozovna'  ),
    (N'podnikatel'  ,N'kancelar'    ,N'kacelář'     )
)data(KategorieKod  ,Kod            ,Nazev)
JOIN dbo.Kategorie k ON data.KategorieKod = k.Kod
WHERE NOT EXISTS(
    SELECT * 
    FROM  dbo.KategorieTyp kt
    WHERE kt.Kod = data.Kod
)
GO

A teď už příklady

--Ručně s použitím FOR JSON PATH
SELECT k.IdKategorie,
       k.Kod,
       k.Nazev,
	   (
		SELECT 
			   kt.IdKategorieTyp,
               kt.Kod,
               kt.Nazev,
               kt.PlatnostOd,
               kt.PlatnostDo
		FROM dbo.KategorieTyp kt
		WHERE kt.IdKategorie = k.IdKategorie
		FOR JSON PATH
	   )typ
FROM dbo.Kategorie k
FOR JSON PATH
--automaticky s použitím FOR JSON AUTO
SELECT k.IdKategorie,
       k.Kod,
       k.Nazev,
       k.PlatnostOd,
       k.PlatnostDo,
       typ.IdKategorieTyp,
       typ.Kod,
       typ.Nazev,
       typ.PlatnostOd,
       typ.PlatnostDo
FROM dbo.Kategorie k
JOIN dbo.KategorieTyp typ ON typ.IdKategorie = k.IdKategorie
FOR JSON AUTO

Výsledek je stejný v obou případech.

[
	{
		"IdKategorie": 1,
		"Kod": "domacnost",
		"Nazev": "domácnost",
		"PlatnostOd": "2022-01-29T14:47:43.493",
		"PlatnostDo": "2099-12-31T00:00:00",
		"typ": [
			{
				"IdKategorieTyp": 1,
				"Kod": "dum",
				"Nazev": "dům",
				"PlatnostOd": "2022-01-29T14:47:43.540",
				"PlatnostDo": "2099-12-31T00:00:00"
			},
			{
				"IdKategorieTyp": 2,
				"Kod": "byt",
				"Nazev": "byt",
				"PlatnostOd": "2022-01-29T14:47:43.540",
				"PlatnostDo": "2099-12-31T00:00:00"
			},
			{
				"IdKategorieTyp": 3,
				"Kod": "chata",
				"Nazev": "chata",
				"PlatnostOd": "2022-01-29T14:47:43.540",
				"PlatnostDo": "2099-12-31T00:00:00"
			}
		]
	},
	{
		"IdKategorie": 2,
		"Kod": "podnikatel",
		"Nazev": "podnikatel",
		"PlatnostOd": "2022-01-29T14:47:43.493",
		"PlatnostDo": "2099-12-31T00:00:00",
		"typ": [
			{
				"IdKategorieTyp": 4,
				"Kod": "garaz",
				"Nazev": "garáž",
				"PlatnostOd": "2022-01-29T14:47:43.540",
				"PlatnostDo": "2099-12-31T00:00:00"
			},
			{
				"IdKategorieTyp": 5,
				"Kod": "provozovna",
				"Nazev": "provozovna",
				"PlatnostOd": "2022-01-29T14:47:43.540",
				"PlatnostDo": "2099-12-31T00:00:00"
			},
			{
				"IdKategorieTyp": 6,
				"Kod": "kancelar",
				"Nazev": "kacelář",
				"PlatnostOd": "2022-01-29T14:47:43.540",
				"PlatnostDo": "2099-12-31T00:00:00"
			}
		]
	}
]

Pokud se chceme zbavit pole a vrátit jen jeden objekt, pak přidáme WITHOUT_ARRAY_WRAPPER.

SELECT TOP 1 *
FROM dbo.Kategorie k
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER



Zanořený jeden objekt dělá problémy.

SELECT k.IdKategorie,
       k.Kod,
       k.Nazev,
	   (
		SELECT TOP 1
			   kt.IdKategorieTyp,
               kt.Kod,
               kt.Nazev,
               kt.PlatnostOd,
               kt.PlatnostDo
		FROM dbo.KategorieTyp kt
		WHERE kt.IdKategorie = k.IdKategorie
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	   )Typ
FROM dbo.Kategorie k
FOR JSON PATH

Výsledek subselectu se projeví jako escapovaný řetězec, což není zpravidla to, čeho chceme dosáhnout.

[
	{
		"IdKategorie": 1,
		"Kod": "domacnost",
		"Nazev": "domácnost",
		"Typ": "{\"IdKategorieTyp\":1,\"Kod\":\"dum\",\"Nazev\":\"dům\",\"PlatnostOd\":\"2022-01-29T14:47:43.540\",\"PlatnostDo\":\"2099-12-31T00:00:00\"}"
	},
	{
		"IdKategorie": 2,
		"Kod": "podnikatel",
		"Nazev": "podnikatel",
		"Typ": "{\"IdKategorieTyp\":4,\"Kod\":\"garaz\",\"Nazev\":\"garáž\",\"PlatnostOd\":\"2022-01-29T14:47:43.540\",\"PlatnostDo\":\"2099-12-31T00:00:00\"}"
	}
]

Pomůže finta s funkcí JSON_QUERY.

SELECT k.IdKategorie,
       k.Kod,
       k.Nazev,
	   (SELECT JSON_QUERY((
			SELECT TOP 1
				   kt.IdKategorieTyp,
				   kt.Kod,
				   kt.Nazev,
				   kt.PlatnostOd,
				   kt.PlatnostDo
			FROM dbo.KategorieTyp kt
			WHERE kt.IdKategorie = k.IdKategorie
			FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
	   )))Typ
FROM dbo.Kategorie k
FOR JSON PATH

Výsledek už pak vypadá dle očekávání.

[
	{
		"IdKategorie": 1,
		"Kod": "domacnost",
		"Nazev": "domácnost",
		"Typ": {
			"IdKategorieTyp": 1,
			"Kod": "dum",
			"Nazev": "dům",
			"PlatnostOd": "2022-01-29T14:47:43.540",
			"PlatnostDo": "2099-12-31T00:00:00"
		}
	},
	{
		"IdKategorie": 2,
		"Kod": "podnikatel",
		"Nazev": "podnikatel",
		"Typ": {
			"IdKategorieTyp": 4,
			"Kod": "garaz",
			"Nazev": "garáž",
			"PlatnostOd": "2022-01-29T14:47:43.540",
			"PlatnostDo": "2099-12-31T00:00:00"
		}
	}
]

Escapování JSONu

SELECT STRING_ESCAPE('"Nějaká důmyslná data \ / 
druhý řádek"','json')
\"Nějaká důmyslná data \\ \/ \r\ndruhý řádek\"

Pole hodnot je potřeba slepit ručně

--SQL2017 a vyšší
SELECT '['+STRING_AGG('"' + STRING_ESCAPE(kt.Kod,'json') + '"', ',')+']'
FROM dbo.KategorieTyp kt


--SQL2016
SELECT '['+STUFF((
	SELECT 
			',"' + STRING_ESCAPE(kt.Kod,'json') + '"'
	FROM dbo.KategorieTyp kt
	FOR XML PATH('')
),1,1,'')+']'
[
	"byt",
	"dum",
	"garaz",
	"chata",
	"kancelar",
	"provozovna"
]

Pole jako atribut

--zapracuju jako atribut JSONu pomocí funkce JSON_QUERY
SELECT k.IdKategorie,
       k.Kod,
       k.Nazev,
       JSON_QUERY((
            SELECT '['+STUFF((
				SELECT 
						',"' + STRING_ESCAPE(kt.Kod,'json') + '"'
				FROM dbo.KategorieTyp kt
				WHERE kt.IdKategorie = k.IdKategorie
				FOR XML PATH('')
			),1,1,'')+']'
       ))Typy
FROM dbo.Kategorie k
FOR JSON PATH
[
	{
		"IdKategorie": 1,
		"Kod": "domacnost",
		"Nazev": "domácnost",
		"Typy": [
			"dum",
			"byt",
			"chata"
		]
	},
	{
		"IdKategorie": 2,
		"Kod": "podnikatel",
		"Nazev": "podnikatel",
		"Typy": [
			"garaz",
			"provozovna",
			"kancelar"
		]
	}
]

NULL hodnoty

SELECT *
FROM (
	VALUES
	('Kamil'	, 'Novák'),
	('Jarmila'	, NULL)
)a	(jmeno		, prijmeni)
FOR JSON PATH
[
	{
		"jmeno": "Kamil",
		"prijmeni": "Novák"
	},
	{
		"jmeno": "Jarmila"
	}
]
SELECT *
FROM (
	VALUES
	('Kamil'	, 'Novák'),
	('Jarmila'	, NULL)
)a	(jmeno		, prijmeni)
FOR JSON PATH, INCLUDE_NULL_VALUES
[
	{
		"jmeno": "Kamil",
		"prijmeni": "Novák"
	},
	{
		"jmeno": "Jarmila",
		"prijmeni": null
	}
]

Vyhledání procedur obsahujících nějaké slovo

Dneska tu mám rychlovku. Jak najít objekty (procedury, funkce, triggery), které obsahují nějaké slovo (zpravidla nějaký název tabulky). Já na to používám tohle:

SELECT DISTINCT OBJECT_NAME(id)
FROM  sys.syscomments s
WHERE s.text LIKE '%tabul%'

A jak to spustit nad více databázemi najednou? Třeba pomocí registrovaných serverů. Tam si vytvoříme Server Group (1Produkce), pod ní přidáme servery i s konkrétní databází (AssemblyTest, skoleni). A pak nad server group pravá myš a New Query. V management studiu se otevře okno, jehož obsah se pak spustí proti všem serverům/databázím ze skupiny.
vyhledani

Zakládání ID u tabulek bez IDENTITY

Máme historickou tabulku, která má umělé id, ale nemá na něm IDENTITY. Nechceme procházet očistcem a převádět ji na tabulku s IDENTITY, ale chceme zakládat ID tak, aby jsme se vyhnuli chybě „Violation of PRIMARY KEY constraint“ při souběhu ze dvou stran.

Ta historická tabulka může vypadat třeba takto:

CREATE TABLE dbo.tabulka(
	id_tabulka INT CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED,
	data VARCHAR(4000)
)

A špatný přistup, jak zakládat záznamy třeba takto:

BEGIN TRAN

DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t)

--jen pro zvýraznění problému
WAITFOR DELAY '0:0:10'

INSERT INTO dbo.tabulka
(
    id_tabulka,
    data
)
VALUES
(   @id,
    'data'
)
COMMIT

Toto když spustíme ve dvou oknech, tak nám to ve druhém upadne na „Violation of PRIMARY KEY constraint“, protože se pokusíme založit podruhé stejný primární klíč.

Možnosti řešení jsou dvě. Pokud to máme v transakci, tak můžeme použít UPDLOCK. Ten nám uvalí U zámek na poslední záznam v tabulce a druhá transakce ho tak nebude moct přečíst a zůstane čekat do doby, než první transakce doběhne do konce.

BEGIN TRAN

DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t WITH (UPDLOCK))

--jen pro zvýraznění problému
WAITFOR DELAY '0:0:10'

INSERT INTO dbo.tabulka
(
    id_tabulka,
    data
)
VALUES
(   @id,
    'data'
)
COMMIT

Druhou možností je, že v případě chyby zkusíme to maximální id vypočíst znova. Z opatrnosti tomu dáme jen 5 pokusů a pak toho případně necháme a chybu vypropagujem.

DECLARE @counter INT = 0;
DECLARE @max_counter INT = 5;
DECLARE @id INT = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t WITH (UPDLOCK))
WHILE @counter < @max_counter
BEGIN
  BEGIN TRY
    SET @id = (SELECT ISNULL(MAX(t.id_tabulka),0)+1 FROM dbo.tabulka t)

    --jen pro zvýraznění problému
    WAITFOR DELAY '0:0:10'

    INSERT INTO dbo.tabulka
    (
      id_tabulka,
      data
    )
    VALUES
    (	
      @id,
      'data'
    )

    SET @counter = @max_counter; --vypadnout z cyklu
  END TRY
  BEGIN CATCH
    SET @counter += 1;
    IF ERROR_NUMBER() <> 2627 --Violation of PRIMARY KEY constraint
      OR @counter = @max_counter
        THROW;
  END CATCH;
END;

První řešení mi přijde čistší, ale zase tam druhá transakce musí čekat na dokončení první.

Jak na UPSERT

Často potřebujeme podle určitého klíče zaktualizovat hodnotu. A pokud klíč neexistuje, tak ho s hodnotou vložit do tabulky. Tedy UPDATE nebo INSERT…UPSERT. A často nás na první dobrou napadne úplně špatné řešení.

CREATE TABLE dbo.tabulka(
	id INT IDENTITY(1,1) NOT NULL CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED, 
	dalsiKlic VARCHAR(50) NOT NULL CONSTRAINT uq_tabulka_dalsiKlic UNIQUE, 
	data VARCHAR(50) NULL
)
GO

CREATE OR ALTER PROCEDURE dbo.p_velky_spatny_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	IF EXISTS(
		SELECT * 
		FROM  dbo.tabulka t
		WHERE t.dalsiKlic = @dalsiKlic
	)
		UPDATE dbo.tabulka
		SET data = @data
		WHERE dalsiKlic = @dalsiKlic
	ELSE
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   @dalsiKlic,
			@data
			)
END
GO

Tedy najdi záznam a pokud jsi našel, tak ho najdi znova a zaktualizuj. No a pokud jsi ho nenašel, tak ho založ. Problém je, že pokud záznam existuje, tak ho hledám dvakrát a plýtvám tak zdroji. Druhý problém je, že ta operace není atomická a v READ COMMITED isolation levelu se může stát, že dvě transakce najednou ověří, že záznam s daným klíčem neexistuje a obě se ho pak pokusí založit. V lepším případě pak skončíme s chybou Violation of UNIQUE KEY constraint…, v horším založíme duplicitu (pokud nám chybí constraint). Zabalit to do transakce nic neřeší.

Zabalit to do transakce a zvýšit isolation level na serializable řešením je:

CREATE OR ALTER PROCEDURE dbo.p_velky_spatny_serializable_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	SET TRAN ISOLATION LEVEL SERIALIZABLE
	BEGIN TRAN
	IF EXISTS(
		SELECT * 
		FROM  dbo.tabulka t
		WHERE t.dalsiKlic = @dalsiKlic
	)
		UPDATE dbo.tabulka
		SET data = @data
		WHERE dalsiKlic = @dalsiKlic
	ELSE
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   	@dalsiKlic,
			@data
		)
	COMMIT
END
GO

Ale tím vyměníme problém se zakládáním duplicit, za problém s deadlocky. U neexistující položky provede select v existu zamčení RangeS-S zámkem, čímž si pojistí, aby daná hodnota nebyla vložena v jiné transakci. Problém je, že RangeS-S je kompatiblní s RangeS-S. Tedy, pokud se dvě transakce sejdou a obě provedou EXISTS podmínku, tak si obě můžou hodit na stejný rozsah záznamů RangeS-S zámek. Následně při insertu se pokusí použít X zámek, čímž dojde k deadlocku.

Jak je to správně?
Správně to je v transakci, v serializable a bez zbytečného selectu.

CREATE OR ALTER PROCEDURE dbo.p_dobry_UPSERT
	@dalsiKlic VARCHAR(50)
	,@data VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;

	BEGIN TRAN
	UPDATE dbo.tabulka WITH (SERIALIZABLE)
	SET data = @data
	WHERE dalsiKlic = @dalsiKlic

	IF @@ROWCOUNT = 0
		INSERT INTO dbo.tabulka
		(
			dalsiKlic,
			data
		)
		VALUES
		(   	@dalsiKlic,
		    	@data
		)
	COMMIT
END
GO

Pokud záznam existuje, tak se provede update. Pokud neexistuje, tak po updatu zůstane na unikátním indexu RangeS-U zámek, který zabezpečí, že si jiný proces neuvalí stejný zámek a zároveň, že jiný proces nevloží hodnotu klíče, o které jsme se právě přesvědčili, že neexistuje. Tedy žádné deadlocky a žádné Violation of UNIQUE KEY constraint.

UPDATE WITH (UPDLOCK)

Ve zděděném kódu občas potkávám UPDATE tabulka WITH (UPDLOCK) SET… a nikdo mi k tomu nechtěl říct jestli to je k něčemu dobré. Teorie praví, že pro vyhledání updatovaného záznamu se použije U zámek a updatovaný záznam pak dostane X zámek, který se drží do konce transakce. Hint WITH (UPDLOCK) by měl zaručit držení U zámku do konce transakce. Pojďme si to zkusit v praxi.

Zkoušet to budeme na 3 tabulkách. Za prvé na haldě bez primárního klíče. Za druhé na haldě s neklastrovaným primárním klíčem. A za třetí na tabulce s klastrovaným primáním klíčem, kterou ještě okořeníme druhým indexovaným klíčem.

CREATE TABLE dbo.halda(
	id INT NOT NULL, 
	data VARCHAR(500) NULL
)

CREATE TABLE dbo.haldaPK(
	id INT NOT NULL CONSTRAINT pk_haldaPK PRIMARY KEY NONCLUSTERED, 
	data VARCHAR(500) NULL
)

CREATE TABLE dbo.tabulka(
	id INT NOT NULL CONSTRAINT pk_tabulka PRIMARY KEY CLUSTERED, 
	dalsiKlic VARCHAR(50) NOT NULL CONSTRAINT uq_tabulka_dalsiKlic UNIQUE, 
	data VARCHAR(50) NULL
)
GO

Naplníme nějakými testovacími daty

INSERT INTO dbo.halda(id, data)
VALUES(1,'a'),(2,'b'),(3,'c')

INSERT INTO dbo.haldaPK(id, data)
VALUES(1,'a'),(2,'b'),(3,'c')

INSERT INTO dbo.tabulka(id,dalsiKlic,data)
VALUES(1,'a','dataA'),(2,'b','dataB'),(3,'c','dataC')
GO

A jdeme zkoušet updaty. Nejdříve na haldě.

BEGIN TRAN
UPDATE dbo.halda WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1845581613  0      RID  1:40441:2                        X        GRANT
61     32     1845581613  0      PAG  1:40441                          IX       GRANT
61     32     1845581613  0      TAB                                   IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

Hmm, tak na první pokus žádný U zámek.

Zkusíme haldu s primárním klíčem.

BEGIN TRAN
UPDATE dbo.haldaPK WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1861581670  2      KEY  (98ec012aa510)                   U        GRANT
61     32     1861581670  0      RID  1:8089:2                         X        GRANT
61     32     1861581670  0      TAB                                   IX       GRANT
61     32     1861581670  0      PAG  1:8089                           IX       GRANT
61     32     1861581670  2      PAG  1:16177                          IU       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

Tady už se něco událo. Vidíme X zámek na záznamu na haldě a U zámek na záznamu indexu primárního klíče.

Teď zkusme update tabulky s klastrovaným primárním klíčem, kde budeme vyhledávat právě podle toho primárního klíče.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE id = 3
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1909581841  1      KEY  (98ec012aa510)                   X        GRANT
61     32     1909581841  0      TAB                                   IX       GRANT
61     32     1909581841  1      PAG  1:24265                          IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
*/

A tentokrát zase žádný U zámek.

Naposledy zkusíme hledat v klastrované tabulce podle jiného klíče než primárního.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE dalsiKlic = 'c'
EXEC sp_lock @spid1 = @@SPID
ROLLBACK
/*
spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
61     32     0           0      DB                                    S        GRANT
61     32     1909581841  1      KEY  (98ec012aa510)                   X        GRANT
61     32     1909581841  0      TAB                                   IX       GRANT
61     32     1909581841  2      KEY  (f037bcc414ff)                   U        GRANT
61     32     1909581841  1      PAG  1:24265                          IX       GRANT
61     1      1467152272  0      TAB                                   IS       GRANT
61     32767  -571204656  0      TAB                                   Sch-S    GRANT
61     32     1909581841  2      PAG  1:32353                          IU       GRANT
*/

Zafungovalo to. Updatovaný záznam v klastrovaném indexu je zamčený X zámkem a záznam v unikátním indexu je zamčený U zámkem.

Závěr
Závěrem zatím budiž to, že při použití WITH (UPDLOCK) se U zámek drží do konce transakce na záznamu indexu, podle kterého se vyhledávalo, pokud je tento rozdílný od podkladové struktury, která drží data (halda, klastrovaný index).

Využití
Hodně jsem se snažil najít nějaký pěkný příklad, k čemu je to dobré. Ale našel jsem jen jeden dost pochybný. Pokud mám nějaký kód, který v jedné transakci dvakrát updatuje stejný řádek a navíc je tento kód vykonáván paralelně ve dvou vláknech nad stejným záznamem. V takovém případě může dojít k deadlocku, kterému se dá zabránit právě tím UPDATE WITH (UPDLOCK).

Příkladem budiž:

BEGIN TRAN
UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'

--nejaka dalsi prace
WAITFOR DELAY '0:0:10'


UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'
COMMIT

Tohle když spustím ve dvou oknech SSMS, tak mi to v jednom okně upadne na deadlock.

Mechanismus je takový, že se provede první update v prvním okně a na záznamu v klastrovaném indexu je pak držen X zámek. První update ve druhém okně pak provede jen vyhledání záznamu k updatu v unikátním indexu, který si označí U zámkem. Následně se pokusí označit U zámkem i záznam v klastrovaném indexu, což nebude kompatibilní s již uvaleným X zámkem, takže zůstane čekat na jeho uvolnění. Transakce v prvním okně mezitím dojde k druhému updatu. Pokusí se vyhledat záznam pro update v unikátním klíči a uvalit na něj U zámek, což nebude kompatibilní s již uvaleným U zámkem z druhé transakce. Dostaneme se tedy do situace, kdy první transakce čeká na druhou a druhá na první. Deadlock. SQL server jednu vybere a sestřelí.

Předejít tomu jde přidáním WITH (UPDLOCK) na první update.

BEGIN TRAN
UPDATE dbo.tabulka WITH (UPDLOCK) SET data = 'x' WHERE dalsiKlic = 'c'

--nejaka dalsi prace
WAITFOR DELAY '0:0:10'


UPDATE dbo.tabulka SET data = 'x' WHERE dalsiKlic = 'c'
COMMIT

V takovém případě zůstane po prvním updatu v prvním okně držen X zámek na záznamu v klastrovaném indexu a U zámek na záznamu v unikátním indexu. Transakci v druhém okně se už nepovede přidat U zámek na záznam v unikátním indexu a zůstane tedy čekat. Zároveň už nebude nijak omezovat transakci z prvního okna v úspěšném dokončení.

Jak vyskriptovat data z tabulky

Pomocí wizardu

Pro jednorázové akce je nejjednodušší si to vyklikat ve wizardu. V Object Exploreru nad databází pravá myš a Tasks/Generate Scripts… Next, abychom se dostali na záložku Choose Objects, kde vybereme konkrétní tabulky, pro které potřebuje vyskriptovat data.
generate scripts1

Na další záložce vybereme, kde chceme skript uložit, třeba Open in new query window. A pak klikneme na tlačítko Advanced, kde vybereme jestli chceme skriptovat data, strukturu, nebo obojí. Já volím Data only. By default tam je Schema only a data bychom tak nedostali.

generate scripts2

Dvakrát Next a Finish a v novém okně máme insert skripty.

SET IDENTITY_INSERT [dbo].[Kategorie] ON 
GO
INSERT [dbo].[Kategorie] ([IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (1, N'domacnost', N'domácnost', CAST(N'2021-09-30T08:53:06.287' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Kategorie] ([IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (2, N'podnikatel', N'podnikatel', CAST(N'2021-09-30T08:53:06.287' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Kategorie] OFF
GO
SET IDENTITY_INSERT [dbo].[KategorieTyp] ON 
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (1, 1, N'dum', N'dům', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (2, 1, N'byt', N'byt', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (3, 1, N'chata', N'chata', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (4, 2, N'garaz', N'garáž', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (5, 2, N'provozovna', N'provozovna', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (6, 2, N'kancelar', N'kacelář', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[KategorieTyp] OFF
GO

sp_generate_inserts

Pokud potřebujeme pravidelně přenášet data z databáze do databáze, tak se nám hodí nějaká procedura, která nám insert skript vytvoří. K dostání je třeba tady:https://github.com/lambacck/generate_inserts.

Pak v management studiu přepneme Results to text (Ctrl+T) a spustíme:

EXEC dbo.sp_generate_inserts @table_name = 'Kategorie'

Výsledkem pak je skript:

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
SET IDENTITY_INSERT [dbo].[Kategorie] ON
GO
 
 
PRINT 'Inserting values into [Kategorie]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Kategorie] ([IdKategorie],[Kod],[Nazev],[PlatnostOd],[PlatnostDo])VALUES(1,'domacnost','domácnost','Sep 30 2021  8:53:06:287AM','Dec 31 2099 12:00:00:000AM')
INSERT INTO [Kategorie] ([IdKategorie],[Kod],[Nazev],[PlatnostOd],[PlatnostDo])VALUES(2,'podnikatel','podnikatel','Sep 30 2021  8:53:06:287AM','Dec 31 2099 12:00:00:000AM')

PRINT 'Done'
 
 
SET IDENTITY_INSERT [dbo].[Kategorie] OFF
GO
SET NOCOUNT OFF
MocPeknyDeadlock

Kde najít info k proběhlému deadlocku

Kde najít info k proběhlému deadlocku? Od SQL 2012 v by default zapnuté extended event session system_health. Možná je to už od 2008, ale tam to má jiný formát a neznám nikoho, kdo by to tam chtěl hledat :) V sytem_health se loguje více věcí než deadlocky, má omezenou velikost a funguje tak, že nově logované události přepisují ty nejstarší. Na vytíženém serveru tam nemusí být historie ani za celý den.

Lepší je si založit vlastní session jen na deadlocky. Třeba takto:

CREATE EVENT SESSION [deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'deadlocks.xel',max_file_size=(20),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

A teď fotoseríál jak z Bravíčka, jak se k těm informacím doklikat.

V Object Exploreru je to Management/Extended Events/Sessions/system_health a poklikat event_file
Deadlock_system_health

Já pak dávám Grouping, abych ten zmatek trochu zpřehlednil
Deadlock_system_health2

Podle názvu události
Deadlock_system_health3

Pak už je k nalezení deadlock, pokud je nějaký zalogovaný. Z deadlock grafu je vidět akorát o kterou jde databázi a o které tabulky se tam soupeřilo.
Deadlock_system_health4

Všechny informace jsou pak v XML, které dostanem na panelu Details po poklikání na XML.
Deadlock_system_health5

V XML jsou pak stacky volání a konkrétní dotazy, které se deadlocku zúčastnily a taky zdroje o které tam šlo. Zdroje tam jsou uvedené v podobě waitresource. Pokud je potřeba zjistit o který konkrétní záznam v tabulce šlo, tak tady Kendra Little popisuje jak ten waitresource rozkódovat.

Jak psát dynamické SQL

V minulém článku jsem popsal jak nepsat dynamické SQL. Dneska ukážu jak to udělat správně. Rovnou začnu přepsanou procedurou z minulého článku.

CREATE OR ALTER PROCEDURE p_CiselnikDejNazevDleKodu
    @Schema SYSNAME = 'dbo',
    @Tabulka SYSNAME,
    @Kod NVARCHAR(50),
	@Debug BIT = 0
AS
BEGIN
    DECLARE @prikaz NVARCHAR(MAX)
    SET @prikaz = N'
SELECT TOP (1) nazev 
FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Tabulka) + N' 
WHERE kod = @Kod';

	IF @Debug = 1
		PRINT(@prikaz);

    EXEC sys.sp_executesql
		@stmt = @prikaz,
		@param = N'@Kod NVARCHAR(50)',
		@Kod = @Kod
END
GO

Na vstupu procedury mi chybělo schéma, které jsem tady doplnil s výchozí hodnotou dbo. Schéma a název tabulky musím do příkazu zřetězit. Obojí ošetřuji funkcí QUTENAME, která názvy obalí hranatými závorkami a zdvojí případný výskyt pravé hranaté závorky a tím zabrání SQL injekci. Funkce QUTENAME by se měla používat na všechny systémové názvy, to je databáze, tabulky, sloupce, atd.. Co kdyby nějaký filuta pojmenoval tabulku [;DROP TABLE Students--], nebo tak nějak.
Abych ošetřil i parametr @Kod, tak místo zřetězení jeho hodnoty ho zadávám jako parametr @Kod rovnou do řetězce příkazu. Volání EXEC(@prikaz) jsem nahradil voláním EXEC sys.sp_executesql. Tomu předávám samotný příkaz, pak seznam parametrů a jejich datových typů, které jsou v příkazu použity a nakonec parametrům předám hodnotu. Příkaz PRINT jsem podmínil parametrem @Debug, aby se neprintovalo vždy a zbytečně.

Ještě kouknem, jak to vypadá v plan cache.

DBCC FREEPROCCACHE
 
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'E'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'CH'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'D'
 
SELECT
    p.usecounts, 
    p.cacheobjtype, 
    p.objtype, 
    p.size_in_bytes,
    t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE p.cacheobjtype = 'Compiled Plan'
AND t.text NOT LIKE '%dm_exec_cached_plans%';
GO

JakPsatDynamickeSQL

V plan cache mám tentokrát dva prepared plány a každý z toho dvakrát použitý. Plán se tedy pro každou tabulku sestaví jednou a pak se používá opakovaně i pro různé hodnoty parametru @Kod. Minule se mi pro každou hodnotu kódu sestavoval nový adhoc plan.

Jak nepsat dynamické SQL

Mějme nějaké číselníkové tabulky s daty:

CREATE TABLE dbo.Obor(
	IdObor INT IDENTITY NOT NULL 
		CONSTRAINT pk_Obor PRIMARY KEY CLUSTERED,
	Kod NVARCHAR(50) NOT NULL,
	Nazev NVARCHAR(250) NOT NULL
)
GO

INSERT INTO dbo.Obor(Kod, Nazev)
VALUES
(   N'P', N'Plyn'),
(   N'E', N'Elektřina')
GO

CREATE TABLE dbo.TypObjektu(
	IdTypObjektu INT IDENTITY NOT NULL 
		CONSTRAINT pk_TypObjektu PRIMARY KEY CLUSTERED,
	Kod NVARCHAR(50) NOT NULL,
	Nazev NVARCHAR(250) NOT NULL
)
GO

INSERT INTO dbo.TypObjektu(Kod, Nazev)
VALUES
(   N'CH', N'Chata'),
(   N'D', N'Dům')
GO

A mějme výborný nápad na proceduru, která vrátí název ke kódu číselníku a libovolné tabulce.

CREATE OR ALTER PROCEDURE p_CiselnikDejNazevDleKodu
	@Tabulka SYSNAME,
	@Kod NVARCHAR(50)
AS
BEGIN
	DECLARE @prikaz NVARCHAR(MAX)
	SET @prikaz = N'
SELECT TOP (1) nazev 
FROM ' + @Tabulka + N' 
WHERE kod = ''' + @Kod + N'''';
	PRINT(@prikaz);
	EXEC(@prikaz);
END
GO

Vypadá to, že to funguje parádně.

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
/*
Vrátí:
nazev
-----
Plyn
*/

Ale pokud vyliji plan cache, parkát to spustím a kouknu co je v keši:

DBCC FREEPROCCACHE

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'E'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'CH'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'D'

SELECT 
	p.usecounts, 
	p.cacheobjtype, 
	p.objtype, 
	p.size_in_bytes,
	t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE p.cacheobjtype = 'Compiled Plan'
AND t.text NOT LIKE '%dm_exec_cached_plans%';
GO

JakNepsatDynamickeSQL

Teď už to nevypadá tak dobře. Každý select spálil CPU na výpočet plánu, každý text selectu a jeho plán je v plan cache a zabíra tam zbytečně místo. Nemusí jít jen o číselníky. Dneska jsem viděl tisíce položek v keši, které se lišily jen v id, které bylo zřetězené do dotazu.
Samotný dotaz jede rychle, ale pálí se zbytečně CPU na výpočet plánu a zabírá se paměť. Tím jde dolů výkon celého serveru.

A co když je některý parametr procedury vstupem od uživatele?

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'nevim',  @Kod = N'P'
/*
SELECT TOP (1) nazev 
FROM nevim 
WHERE kod = 'P'
Msg 208, Level 16, State 1, Line 60
Invalid object name 'nevim'.
*/

Pokud se neošetřené výjimky vracejí až k uživateli, tak díky printu dostane celý text, co je v komentáři výše. Prostě včetně struktury toho pochybného SELECT TOP (1)…

No a pokud bude uživatel důvtipnější, tak si vrátí strukturu celé DB.

EXEC p_CiselnikDejNazevDleKodu 
    @Tabulka = N'Obor',  
    @Kod = N''' UNION SELECT name FROM sys.tables--'
/*
nazev
------------
a
Obor
tabule
tabule2
TypObjektu
*/

Prostě Little Bobby Tables.

Pokračování článku o tom, jak by to mělo vypadat.