IDENTITY, transakce a návrh datového modelu

Při návrhu datového modelu musíme myslet i na správnou velikost datového typu pro primární klíč. Standardem je datový typ INT, ale pokud máme dimenzi s několika prvky, rádi zvolíme TINYINT, je-li dimenze cizím klíčem ve faktové tabulce o velkém množství řádků. Úspora úložiště a vliv na výkon je poté jednoznačně pozitivní.

Pokud v našem primární klíči využijeme i IDENTITY, je třeba si dát velký pozor, pokud náš ETL proces pracuje v transakci a není úplně neobvyklé, že dojde k rollbacku transakce. Potom můžeme velice lehce narazit na situaci, kdy se snažíme vložit do naší dimenzionální tabulky nové řádky, ale tabulka nám říká, že hodnota primárního klíče, kterou chceme vložit, přesahuje délku datového typu, ačkoliv po inspekcí tabulky samotné vidíme, že je v ní mnohem méně záznamů, než je maximální velikost datového typu, nebo je dokonce prázdná.

Vše si ukážeme na jednoduchém příkladu:

Vytvoříme si tabulku dbo.SampleTable, která má primární klíč ID datového typu TINYINT a nastaveno IDENTITY(1,1). Maximální hodnota, kterou můžeme do TINYINT uložit je 255, tedy naše tabulka může obsahovat maximálně 256 řádků (včetně 0 by to bylo 256).

CREATE TABLE [dbo].[SampleTable] (
	[ID] TINYINT NOT NULL IDENTITY(1,1),
	[Name] VARCHAR(10)
)
GO

Nyní budeme do tabulky vkládat řádky, ale vždy provedeme rollback dané transakce. K simulaci využijeme volání GO 255, což je funkce SQL Server Management Studia, nikoliv T-SQL syntax.

BEGIN TRAN

	INSERT INTO [dbo].[SampleTable] ([Name]) 
		VALUES ('Test')

ROLLBACK
GO 255

Pokud se nyní podíváme, jaká data naše tabulka obsahuje, je přesně podle očekávání prázdná:

SELECT * FROM [dbo].[SampleTable] [st]
GO

Nyní pojďme do tabulky vložit další řádek:

INSERT INTO [dbo].[SampleTable] ([Name]) 
	VALUES ('Test')
GO

A zde již přichází poněkud nečekaně chyba. Řádek se nepodařilo vložit, protože další hodnota, kterou vygenerovalo IDENTITY je 256, což přesahuje velikost datového typu TINYINT. To si můžeme ověřit i takto:

SELECT IDENT_CURRENT('dbo.SampleTable')
GO

Vidíme, že poslední přiřazená hodnota je 255 a další následující bude opravdu 256.

Řešení celé situace je na první pohled jednoduché:

DBCC CHECKIDENT('dbo.SampleTable',RESEED, 0)
GO

Provedli jsme RESEED tabulky a nastavili sekvenci opět od 0. Zkusme nyní vložit další řádek:

INSERT INTO [dbo].[SampleTable] ([Name]) 
	VALUES ('Test')
GO

SELECT * FROM [dbo].[SampleTable] [st]
GO

Vidíme, že vše prošlo bez problémů a můžeme vesele vkládat ještě dalších 254 řádků.

To, co v naše scénáři vypadá triviálně, nás ovšem v reálném provozu může řádně potrápit. Pokud se nám objeví chybová hláška zobrazená výše, stojíme před následujícími otázkami:

• Na jaké tabulce se chyba vyskytla? V chybové zprávě tuto informaci nenajdeme a pokud naše ETL transakce vkládá do desítek dimenzionálních tabulek, bude hledání bez dobrého logování celého procesu opravu zdlouhavé.
• Pokud již identifikujeme tabulku, budeme čekat, že bude obsahovat 255 řádků a my se snažíme vložit další řádek a budeme proto přemýšlet o změně velikosti datového typu. Po zobrazení dat v tabulce ale může přijít velké překvapení: tabulka obsahuje méně než 255 řádků nebo je dokonce prázdná.
• V naše případě jsme pracovali v prázdnou tabulkou, ale v reálné scénáři může tabulka obsahovat třeba 20 řádků s různě rozloženými hodnotami primárního klíče, třeba 14, 78, 128, kde mezery v sekvenci vznikly právě každým rollbackem ETL transakce. V tomto případě budeme hledat nějakou rozumnou hodnotu, ke které provedeme RESEED, opravdu těžko a nezbude nám, než pracně provést update všech hodnot primárního klíče na hodnoty začínající od 1. Jak velký to může být úkol v případě rozsáhlé faktové tabulky netřeba zmiňovat.
• Pokud skutečně vyčerpáme 255 hodnot a budeme potřebovat zvětšit datový typ, je třeba změnu provést i ve faktového tabulce, což může být při miliardách záznamů opravdu zdlouhavý a komplikovaný úkol.

Při návrhu datového modelu a ETL procesu tedy mysleme vždy na následující:

• Je uvažovaný datový typ skutečně dostačující pro budoucnost? Nebude jeho pozdější změna tak komplikovaná, že momentální volba nejmenšího možného datového typu nestojí a tím získaná úspora místa a lehké zvýšení výkonu nám za to nestojí?
• V rámci ETL procesu musíme myslet při rollbacku i na chování IDENTITY() a zvolit RESSED při rollbacku transakce, případně uvažovat o použití sekvencí.