- Ctrl + E nebo F5 pro spuštění dotazu
- Ctrl + R – schová/zobrazí okno s výsledky dotazu
- Ctrl + K, Ctrl + C: zakomentuje označené řádky
- Ctrl + K, Ctrl + U: odkomentuje označené řádky
- Ctrl + U: hodí kurzor do výběru databází, tam stačí začít psát název databáze, plus třeba dovybrat šipkama
- Alt + F1 volá proceduru sp_help a jako parametr ji předá označený text
Alt + F1 při označeném názvu tabulky vypíše sloupece tabulky, zda má IDENTITY, indexy, constrainty, cizí klíče. Sloupce se dají snadno vykopírovat a použít do rozepsaného selectu nebo insertu.
Alt + F1 při označeném názvu procedury vypíše parametry procedury - Ctrl + G pro skok na konkrétní řádek textu
- Shift + Alt + šipka nahoru/dolů roztáhne kurzor přes více řádků a umožní psát/mazat na více řádcích najednou
Archiv pro měsíc: Květen 2021
Retry logika na ošetření deadlocku
Nejjednodušším řešením deadlocku je spustit znova transakci, která se stala jeho obětí. Transakce, která se stala obětí deadlocku je neplatná. Nedá se nad ní udělat commit, ani rollback po savepoint. Jedinnou možností je rollback celé transakce. Z toho plyne, že retry logiku můžu nasadit jen v místě, kde mám kontrolu nad zahájením transakce. Celou transakci pak zabalím do cyklu WHILE a v něm se ji v případě deadlocku pokusím několikrát zopakovat. Pokud se mi to nepodaří ani na několikátý pokus, pak chybu vypropaguju ven.
DECLARE @pocetPokusu INT = 1;
WHILE (1=1)
BEGIN
BEGIN TRY
BEGIN TRAN
RAISERROR('pokousim se vykonat transakci',1,1) WITH NOWAIT;
EXECUTE('
CREATE TYPE dbo.instantiDeadlock AS TABLE(
Id INT NOT NULL
)
')
EXEC('
declare @t dbo.instantiDeadlock;')
COMMIT;
BREAK;
END TRY
BEGIN CATCH
ROLLBACK;
IF ERROR_NUMBER() = 1205 AND @pocetPokusu < = 3--jsem obeti deadlocku a jeste jsem si nedal 3 pokusy
BEGIN
RAISERROR('jsem obeti deadlocku',1,1) WITH NOWAIT;
SET @pocetPokusu += 1;
END
ELSE
THROW;
END CATCH
END
IF EXISTS (SELECT 1 FROM sys.table_types tt WHERE tt.name = 'instantiDeadlock')
DROP TYPE instantiDeadlock
pokousim se vykonat transakci Msg 50000, Level 1, State 1 jsem obeti deadlocku Msg 50000, Level 1, State 1 pokousim se vykonat transakci Msg 50000, Level 1, State 1 jsem obeti deadlocku Msg 50000, Level 1, State 1 pokousim se vykonat transakci Msg 50000, Level 1, State 1 jsem obeti deadlocku Msg 50000, Level 1, State 1 pokousim se vykonat transakci Msg 50000, Level 1, State 1 Msg 1205, Level 13, State 55, Line 6 Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Skript pro plnění číselníků
Při vývoji je potřeba často zaverzovat skripty pro naplnění dat. Pokud chceme, aby byly opakovaně spustitelné, tak jsou zpravidla plné IF NOT EXISTS…INSERT INTO konstrukcí.
Já mám raději konstrukci se SELECT * FROM (VALUES()), která mi připadá přehlednější. Ukážu na příkladu.
Nejdříve si založíme dvě provázané číselníkové tabule.
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
Plním to pak pomocí INSERT INTO SELECT, kde za FROM mám konstruktor VALUES, do kterého vypíšu všechna data, co chci založit. Tuhle za běhu vytvořenou tabulku si pojmenuju jako data a její sloupce jako Kod a Nazev. Na tuhle tabuli se pak odkážu za klauzulí SELECT, nebo ve WHERE podmínce.
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 tohle jsou konstrukce, které potkávám častěji a přijde mi to škoda:
IF NOT EXISTS(SELECT * FROM dbo.Kategorie k WHERE k.Kod = 'domacnost')
INSERT INTO dbo.Kategorie
(
Kod,
Nazev,
PlatnostOd,
PlatnostDo
)
VALUES
( N'domacnost',
N'domácnost',
GETDATE(),
'20991231'
)
IF NOT EXISTS(SELECT * FROM dbo.Kategorie k WHERE k.Kod = 'podnikatel')
INSERT INTO dbo.Kategorie
(
Kod,
Nazev,
PlatnostOd,
PlatnostDo
)
VALUES
( N'podnikatel',
N'podnikatel',
GETDATE(),
'20991231'
)
GO
IF NOT EXISTS(
SELECT * FROM dbo.KategorieTyp kt WHERE kt.Kod = N'dum'
)
INSERT INTO dbo.KategorieTyp
(
IdKategorie,
Kod,
Nazev,
PlatnostOd,
PlatnostDo
)
VALUES
( (SELECT k.IdKategorie FROM dbo.Kategorie k WHERE k.Kod = 'domacnost'),
N'dum',
N'dům',
GETDATE(),
'20991231'
)
IF NOT EXISTS(
SELECT * FROM dbo.KategorieTyp kt WHERE kt.Kod = N'byt'
)
INSERT INTO dbo.KategorieTyp
(
IdKategorie,
Kod,
Nazev,
PlatnostOd,
PlatnostDo
)
VALUES
( (SELECT k.IdKategorie FROM dbo.Kategorie k WHERE k.Kod = 'domacnost'),
N'byt',
N'byt',
GETDATE(),
'20991231'
)
GO
Jak vytáhnout z SQL nechutně dlouhý řádek textu skrz management studio
--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) + N'b'
--print zvládne jen 8000 bytů, to je 4000 znaků v NVARCHAR
PRINT @out
GO
--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out VARCHAR(MAX) = '<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST('a' AS NVARCHAR(MAX)),70000) + 'b'
--print zvládne jen 8000 bytů, to je 8000 znaků ve VARCHAR
PRINT @out
GO
--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) + N'b'
--select a Results to grid (Ctrl-D)
--Do SSMS 18.1 včetně se dá z buňky gridu vykopírovat 43680 znaků, přesto, že v nastavení gridu je 65 535
--Od SSMS 18.2 se dá vzít až 2 milony znaků
--Pravá myš/Query Option/Results/Grid
--Nebo Tools/Options/Query Results/SQL Server/Results to Grid
----Retain CR/LF on copy or save - pokud chceme z gridu vykopírovat i konce řádků (Od SSMS 16)
----Non XML data: pro SSMS 18.1 maximálně 65 535, od 18.2 maximálně 2 000 000
SELECT @out
GO
--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) + N'b'
--select a Results to text (Ctrl-T)
--Query Option/Resutls/Text/Maximum number of characters diplayed in each column - maximálně 8192 - tedy málo
SELECT @out
GO
--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) + N'b'
--Results to grid (Ctrl-D)
--funkční i pro starší ssms. Výsledkem je XML sloupec prokliknutelný z management studia
--poradí si s konci řádků
--výsledek je uvozený <? a zakončený ?>
--problém nastane jen pokud text obsahuje ?>
SELECT @out AS [processing-instruction(x)] FOR XML PATH('')
GO
--text obsahující balast připomínající XML, 70 000 písmen 'a' a na konci je 'b', pro kontrolu, že se vypsalo vše
DECLARE @out NVARCHAR(MAX) = N'<?<a>?>'+CHAR(13)+CHAR(10)+REPLICATE(CAST(N'a' AS NVARCHAR(MAX)),70000) + N'b'
--Results to grid (Ctrl-D)
--funkční i pro starší ssms. Výsledkem je XML sloupec prokliknutelný z management studia
--poradí si s konci řádků
--výsledek je uvozený <export><![CDATA[ a zakončený ]]></export>
SELECT 1 as Tag,
0 as Parent,
@out as [export!1!!CDATA]
FOR XML EXPLICIT
Zřetězení sloupce řetězců do jednoho řetězce s oddělovačem
CREATE TABLE #zretezit(slovo NVARCHAR(30))
INSERT INTO #zretezit(slovo)
VALUES
(N'pondělí')
,(N'úterý')
,(N'středa')
DECLARE @zretezeni0 NVARCHAR(MAX) = (
SELECT ','+z.slovo
FROM #zretezit z
FOR XML PATH('')
)
PRINT @zretezeni0
/*
Výsledek, všechno hraje jak má:
,pondělí,úterý,středa
*/
--Horší je, jak tam někdo nacpe html tagy a odřádkování
INSERT INTO #zretezit(slovo)
VALUES
(N'<b>text</b>')
,(N'Lorem'+CHAR(13)+CHAR(10)+N'iposum')
DECLARE @zretezeni1 NVARCHAR(MAX) = (
SELECT ','+z.slovo
FROM #zretezit z
FOR XML PATH('')
)
PRINT @zretezeni1
/*
Vysledkem je:
,pondělí,<b>text</b>,Lorem
iposum
*/
--Pak pomáhá to zkonvertovat na NVARCHAR
DECLARE @zretezeni2 NVARCHAR(MAX) = (
SELECT (
SELECT ','+z.slovo
FROM #zretezit z
FOR XML PATH(''), TYPE
).value('(./text())[1]','NVARCHAR(MAX)')
)
PRINT @zretezeni2
/*
Výsledek:
,pondělí,<b>text</b>,Lorem
iposum
*/
--Ještě se zbavit první čárky a máme tu konstrukci jak víno:
DECLARE @zretezeni3 NVARCHAR(MAX) = (
SELECT STUFF(
(
SELECT ','+z.slovo
FROM #zretezit z
FOR XML PATH(''), TYPE
).value('(./text())[1]','NVARCHAR(MAX)')
,1
,1
,''
)
)
PRINT @zretezeni3
/*
Krása střídá nádheru:
pondělí,úterý,středa,<b>text</b>,Lorem
iposum
*/
--Asi by bylo dobré zmínit, že od SQL 2017 funguje tohle
DECLARE @zretezeni4 NVARCHAR(MAX) = (
SELECT STRING_AGG(z.slovo,',')
FROM #zretezit z
)
PRINT @zretezeni4