A számított oszlopok használata

Bevezetés

A számított oszlopok csak virtuális oszlopok, amelyek tartalma egy kifejezés eredménye. Általában a táblázat többi oszlopán alapuló adatok tárolására szolgálnak. A kifejezés tartalmazhatja a táblázat más, nem számított oszlopait, konstansokat, operátorokat és függvényeket, de egy számított oszlophoz nem adható meg kifejezésként lekérdezés.

Mivel ezek “virtuális” oszlopok, nem tárolódnak a lemezen, mint a táblázat többi része. Valójában nem tárolódnak, hanem kiszámításra kerülnek minden alkalommal, amikor az oszlopot egy lekérdezésben elérjük. Mint látni fogjuk, bizonyos korlátozásokkal rákényszeríthetjük az SQL Servert, hogy az oszlopot a táblázatban tárolja (“tartósítsa”).

A számított oszlopok működését legjobban a minták segítségével érthetjük meg. A végén talál egy fájlt, amely a cikkben használt összes szkriptet tartalmazza, és a szövegben mutatunk belőlük néhányat a magyarázatok illusztrálására. Kezdetnek két táblázatot hozunk létre: az elsőben a számlák adatait tároljuk, a másikban pedig az említett számlák részletező sorait. A szkriptfájlban talál néhány beillesztést is a mintaadatok létrehozásához.

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));

A számított oszlop létrehozásának módja ugyanaz, mint ahogyan más oszlopokat is létrehoznánk egy táblázatban, CREATE TABLE vagy ALTER TABLE utasítással. Egy számított oszlop esetében az oszlop adattípusát azzal a kifejezéssel helyettesítjük, amelyet az oszlop tartalmának kinyerésére fogunk használni. A szintaxis a következő: az oszlop neve, majd az “as” kulcsszó, végül a kifejezés. Hozzunk létre egy számított oszlopot a detail_line táblában a sor végösszegének tárolására, amelyet az unit_price és a quantity szorzatával számolunk ki.

ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;

Számított oszlop ellenőrzése

Többféleképpen is meggyőződhetünk arról, hogy egy oszlop valóban számított oszlop. Az egyik a columnproperty() függvény használata az “IsComputed” tulajdonság megadásával.

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')

Egy másik módja a számított oszlopokra vonatkozó információk megszerzésének a sys.computed_columns rendszernézet segítségével. Ez a nézet a sys.columns nézet kiterjesztése. Ez azt jelenti, hogy a sys.computed_columns a sys.columns nézet összes oszlopát örökli, és további, az adott oszloptípusra jellemző oszlopokat is hozzáad. A cikk során látni fogunk néhány oszlopot ebben a nézetben, mivel a számított oszlopok különböző jellemzőit látjuk. Egyelőre elég annyit tudni, hogy ez a nézet csak a számított oszlopokat mutatja, és rendelkezik egy is_computed nevű oszloppal, amely megmondja, hogy az oszlop számított-e vagy sem. Nyilvánvaló, hogy ennek a nézetnek az összes rekordja egyes lesz ebben az oszlopban.

SELECT name, is_computed FROM sys.computed_columns;

Mivel az oszlop tartalma minden alkalommal kiszámításra kerül, amikor az oszlopra egy lekérdezésben hivatkozunk, a tartalma mindig frissül. A kifejezésben szereplő oszlopok bármilyen változása automatikusan megjelenik az oszlop értékében. Erről meggyőződhetünk, ha megváltoztatjuk a mennyiséget a minta részletes_vonalak táblázatának egyik nyilvántartásában, és ellenőrizzük az eredményt.

UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'

Egy lépés előre

Amit eddig a számított oszlopokkal kapcsolatban láttunk, az nagyon egyszerű, mivel csak más oszlopokkal való számítások elvégzéséről van szó. A számított oszlopok kifejezése azonban tartalmazhat függvényeket, mind a T-SQL szabványos függvényeket, mind a felhasználó által definiált függvényeket (UDF). Így ezeknek az oszlopoknak a funkcionalitása sokkal tovább bővíthető.

Lássunk erre egy példát. Hozzáadunk a számlák táblához egy számított oszlopot, amely kiszámítja a számla teljes összegét. Ehhez meg kell szereznünk a számla számát, és le kell kérdeznünk a detail_lines táblát, hogy összegezzük a teljes_összeget minden olyan rekordból, amely az adott számlaazonosítóval rendelkezik. A legjobb megoldás erre egy olyan függvény használata, amely paraméterként megkapja a számla azonosítóját, és visszaadja az összeget. Ezután létre kell hoznunk az oszlopot, amely ezt a függvényt használja.

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)

Ellenőrizhetjük, hogy ez az oszlop helyesen működik-e, ha új rekordot adunk hozzá a detail_lines táblához, így a total_billnek változnia kell.

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

Az oszlop módosítása

Előfordulhat olyan helyzet, hogy módosítani kell egy számított oszlopot. Ez sajnos nem lehetséges. A módosításhoz törölni kell az oszlopot, és az új kifejezéssel újra létrehozni.

Abban az esetben, ha a számított oszlop külső függvényt használ, akkor ezt a függvényt nem módosíthatjuk. Ha megpróbáljuk, hibaüzenetet kapunk arra vonatkozóan, hogy ez a függvény a táblához kapcsolódik. A függvény módosításához törölni kell az oszlopot, el kell végezni a függvény módosítását, végül újra létre kell hozni az oszlopot a függvény új verziójával.

Az oszlop definícióját a sys.computed_columns nézetben a “definition” oszlopnál kaphatjuk meg.

SEECT name, definition FROM sys.computed_columns

Számított oszlop tárolása

Mint már említettük, ezek az oszlopok “virtuálisak”, tehát fizikailag nem tárolódnak a táblázatban. Van azonban lehetőség arra, hogy kikényszerítsük a számítás fizikai tárolását a táblában, ezt nevezzük az oszlop “tartósításának”. Ez javíthatja a SELECT utasítások teljesítményét, mivel így elkerülhető, hogy minden egyes hivatkozáskor el kelljen végezni az oszlop számítását.

Ezenkívül az oszlop tartósításához az oszlop létrehozásához használt kifejezésnek “determinisztikusnak” kell lennie. Ahogy a Microsoft honlapján olvashatjuk, “a determinisztikus függvények mindig ugyanazt az eredményt adják vissza minden alkalommal, amikor egy adott bemeneti értékkészlettel és az adatbázis azonos állapotával hívják meg őket”. (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Ha tudni akarjuk, hogy az SQL Server egy számított oszlop kifejezését determinisztikusnak tekinti-e vagy sem, akkor a columnproperty() függvényt használhatjuk az “IsDeterministic” tulajdonsággal.

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')

Ha az oszlop definíciója egy felhasználó által definiált függvény, akkor azt is ellenőrizhetjük, hogy maga a függvény determinisztikus-e vagy sem. Ehhez az objectproperty() függvényt kell használnunk az IsDeterministic tulajdonsággal.

SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')

Mint a lekérdezésekből látható, az első példa oszlopát, amelyben a részlet teljes árát számoljuk ki, determinisztikusnak tekintjük. A számla teljes árát kiszámító függvényt azonban nemdeterminisztikusnak tekintjük. Így a detail_table táblában csak a total_price oszlopot lehet tárolni.

ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;

Ismét a sys.computed_columns nézetben láthatjuk az is_persisted mezőt, amely jelzi, hogy az oszlop perzisztens vagy nem perzisztens a táblában.

Indexek számított oszlopokkal

Lehetőség van számított oszlopok használatára indexekben, bár azoknak több követelménynek is meg kell felelniük:

• Tulajdonjog:
• Determinizmus: A számított oszlop definíciójában használt összes függvénynek ugyanazon felhasználó tulajdonában kell lennie, mint a táblának.
• Determinizmus: A számított oszlopnak determinisztikusnak kell lennie. Továbbá, ha az oszlop CLR-kifejezéseket tartalmaz, akkor a determinisztikusság mellett az oszlopnak perzisztensnek is kell lennie.
• Pontosság: A számított oszlop kifejezésének pontosnak kell lennie. Ez azt jelenti, hogy nem lehet “float” vagy “real” adattípusú. A definícióban sem használhat ilyen típusú adatokat. Ez a tulajdonság a columnproperty() függvénnyel ellenőrizhető az IsPrecise tulajdonság megadásával.
• Adattípus: A számított oszlop nem lehet text, ntext vagy image típusú. Továbbá, ha a kifejezés image, ntext, text, varchar (max), nvarchar (max), varbinary (max) vagy xml adattípusokat tartalmaz, csak akkor használható, ha a kifejezésből eredő adattípus indexben megengedett.

Ezeken a megfontolásokon kívül az oszlop létrehozásához használt kapcsolatoknak és az index létrehozásához használt kapcsolatoknak bizonyos konfigurációkkal kell rendelkezniük ahhoz, hogy ezeket a műveleteket végre lehessen hajtani.

A számított oszlop létrehozásához használt kapcsolatnak aktívnak kell lennie az ANSI_NULLS opciónak. Ez a columnproperty() függvénnyel ellenőrizhető az IsAnsiNullsOn tulajdonság megadásával.

Az index létrehozására szolgáló kapcsolatnak, valamint az indexet befolyásoló rekordok beszúrását, frissítését és törlését végző kapcsolatoknak az ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER opciónak aktívnak kell lennie. Ezenkívül a NUMERIC_ROUNDABORT opciót ki kell kapcsolni.

Utolsó szempontok

Végezetül áttekintünk néhány további szempontot, amelyeket a számított oszlopok helyes használatához szükséges tudni.

Nyilvánvaló, hogy a számított oszlopok nem frissíthetők, és nem szerepelhetnek egy INSERT művelet értéklistájában. Bár a számított oszlopok a select utasítás eredménylistájának részét képezhetik, de a WHERE, ORDER BY vagy mindazokban a záradékokban is használhatók, amelyekben egy kifejezés szerepelhet.

SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount

A fentiek ellenére a számított oszlop nem használható DEFAULT vagy FOREIGN KEY megkötés definíciójában. NOT NULL megkötésdefinícióval sem lehet.

Másrészt a kiszámított oszlopok PRIMARY KEY vagy UNIQUE megkötések részeként is használhatók. Ehhez a számított oszlop definíciójának determinisztikus kifejezésnek kell lennie.

Következtetés

A számított oszlopok használata bizonyos helyzetekben nagyon hasznos lehet. Alaposan meg kell vizsgálni, hogy hol használjuk őket, mert korlátozásokkal rendelkeznek, különösen az index létrehozására és a perzisztenciájukra vonatkozóan. Ez még csak a kezdet. Nyugodtan próbáljon ki új dolgokat, és kísérletezzen a számított oszlopokkal, hogy új lehetőségeket találjon.