Johdanto
Lasketut sarakkeet ovat vain virtuaalisia sarakkeita, joiden sisältö on lausekkeen tulos. Yleensä niitä käytetään pitämään tietoja, jotka perustuvat taulukon muihin sarakkeisiin. Lauseke voi sisältää taulukon muita kuin laskettuja sarakkeita, vakioita, operaattoreita ja funktioita, mutta lasketun sarakkeen lausekkeeksi ei voi määrittää kyselyä.
Koska ne ovat ”virtuaalisia” sarakkeita, niitä ei tallenneta levylle kuten taulukon muita sarakkeita. Itse asiassa niitä ei tallenneta vaan lasketaan aina, kun saraketta käytetään kyselyssä. Kuten tulet näkemään, voit pakottaa SQL Serverin tallentamaan (”persistoimaan”) sarakkeen taulukkoon tietyin rajoituksin.
Paras tapa ymmärtää, miten laskennalliset sarakkeet toimivat, on käyttää esimerkkejä. Lopussa on tiedosto, joka sisältää kaikki artikkelissa käytetyt skriptit, ja näytämme joitakin niistä tekstissä selitysten havainnollistamiseksi. Aluksi luomme kaksi taulukkoa: ensimmäinen sisältää laskujen tiedot ja toinen näiden laskujen yksityiskohtaiset rivit. Skriptitiedostosta löytyy myös joitakin lisäyksiä esimerkkitietojen luomiseksi.
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));
Laskennallisen sarakkeen luominen tapahtuu samalla tavalla kuin muidenkin taulukon sarakkeiden luominen CREATE TABLE- tai ALTER TABLE -lausekkeella. Lasketun sarakkeen osalta sarakkeen tietotyyppi korvataan lausekkeella, jota käytetään sarakkeen sisällön saamiseksi. Syntaksi on sarakkeen nimi, jota seuraa avainsana ”as” ja sitten lauseke. Luodaan detail_line-tauluun laskennallinen sarake, johon tallennetaan rivin kokonaissumma, jonka laskemme kertomalla unit_price ja quantity.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Laskennallisen sarakkeen tarkistaminen
On useita tapoja varmistaa, että sarake on todella laskennallinen sarake. Yksi niistä on käyttää funktiota columnproperty() määrittelemällä ominaisuus ”IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Toinen tapa saada tietoa laskennallisista sarakkeista on järjestelmänäkymän sys.computed_columns kautta. Tämä näkymä on sys.columns-näkymän laajennus. Tämä tarkoittaa sitä, että sys.computed_columns perii kaikki sarakkeet sys.columns-näkymästä ja lisää myös muita, jotka ovat erityisiä tälle saraketyypille. Koko artikkelin ajan näemme joitakin sarakkeita tässä näkymässä, koska näemme laskettujen sarakkeiden eri ominaisuuksia. Toistaiseksi riittää, että tämä näkymä näyttää vain lasketut sarakkeet, ja siinä on sarake nimeltä is_computed, joka kertoo, onko sarake laskettu vai ei. On selvää, että kaikissa tämän näkymän tietueissa on ykkönen tässä sarakkeessa.
SELECT name, is_computed FROM sys.computed_columns;
Koska sarakkeen sisältö lasketaan aina, kun sarakkeeseen viitataan kyselyssä, sisältö päivittyy aina. Kaikki muutokset lausekkeeseen sisältyvissä sarakkeissa heijastuvat automaattisesti sarakkeen arvoon. Voimme nähdä tämän muuttamalla määrän rekisterissä esimerkin detail_lines-taulukossa ja tarkistamalla tuloksen.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Askel eteenpäin
Se, mitä olemme tähän mennessä nähneet laskettujen sarakkeiden kanssa, on hyvin perustavaa laatua, koska siihen kuuluu vain laskutoimitusten suorittaminen muiden sarakkeiden kanssa. Laskettujen sarakkeiden lauseke voi kuitenkin sisältää funktioita, sekä T-SQL-standardifunktioita että käyttäjän määrittelemiä funktioita (UDF). Näin näiden sarakkeiden toiminnallisuutta on mahdollista laajentaa paljon pidemmälle.
Katsotaanpa tästä esimerkki. Lisäämme laskujen taulukkoon laskutetun sarakkeen, joka laskee laskun kokonaissumman. Tätä varten meidän on saatava laskun numero ja kysyttävä yksityiskohtaiset_rivit-taulusta summaamaan kokonaissumma jokaisesta tietueesta, jolla on kyseinen laskun tunnus. Paras tapa tehdä tämä on käyttää funktiota, joka saa parametrina laskun tunnuksen ja palauttaa summan. Tämän jälkeen meidän on luotava sarake, joka käyttää tätä funktiota.
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)
Voimme tarkistaa, että tämä sarake toimii oikein lisäämällä uuden tietueen tauluun detail_lines, jolloin summan total_bill pitäisi muuttua.
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
Sarakkeen muuttaminen
Voi olla tilanteita, joissa joudutaan muuttamaan laskettua saraketta. Valitettavasti tämä ei ole mahdollista. Muutoksen tekeminen edellyttää sarakkeen poistamista ja sen luomista uudelleen uudella lausekkeella.
Jos laskettu sarake käyttää ulkoista funktiota, emme saa muuttaa tätä funktiota. Jos yritämme, saamme virheilmoituksen, joka osoittaa, että tämä funktio on linkitetty taulukkoon. Funktiota muuttaaksemme on poistettava sarake, suoritettava funktion muutos ja lopuksi luotava sarake uudelleen funktion uudella versiolla.
Saamme sarakkeen määritelmän sarakkeesta ”määritelmä” näkymästä sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Laskennallisen sarakkeen tallentaminen
Kuten aiemmin mainitsimme, nämä sarakkeet ovat ”virtuaalisia”, joten niitä ei tallenneta fyysisesti taulukkoon. On kuitenkin mahdollista pakottaa laskenta fyysisesti tallennettavaksi taulukkoon, mitä kutsutaan sarakkeen ”pysyväksi” tallentamiseksi. Tämä voi parantaa suorituskykyä SELECT-lausekkeilla, koska näin vältetään sarakkeen laskennan suorittaminen joka kerta, kun siihen viitataan.
Lisäksi sarakkeen pysyvyyden varmistamiseksi sarakkeen luomiseen käytetyn lausekkeen on oltava ”deterministinen”. Kuten näemme Microsoftin verkkosivuilta, ”deterministiset funktiot palauttavat aina saman tuloksen aina, kun niitä kutsutaan tietyllä joukolla syöttöarvoja ja kun tietokannan tila on sama”. (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Jos haluamme tietää, pitääkö SQL Server lasketun sarakkeen lauseketta deterministisenä vai ei, voimme käyttää columnproperty()-funktiota ”IsDeterministic”-ominaisuudella.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Jos sarakkeen määritelmä on käyttäjän määrittelemä funktio, voit myös tarkistaa, onko kyseinen funktio itsessään deterministinen vai ei. Tätä varten on käytettävä objectproperty()-funktiota IsDeterministic-ominaisuudella.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Kuten kyselyistä näkyy, ensimmäisen esimerkin saraketta, jossa lasketaan yksityiskohdan kokonaishinta, pidetään deterministisenä. Funktiota, joka laskee laskun kokonaishinnan, pidetään kuitenkin epädeterministisenä. Näin taulukkoon voidaan tallentaa vain yksityiskohta_taulukon sarake total_price.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Jälleen sys.computed_columns-näkymässä näkyy kenttä is_persisted, joka kertoo, onko sarake taulukossa persistentti vai ei.
Indeksit laskennallisilla sarakkeilla
Laskennallisia sarakkeita on mahdollista käyttää indekseissä, joskin niiden on täytettävä useita vaatimuksia:
- Omistus: Kaikkien lasketun sarakkeen määrittelyssä käytettyjen funktioiden on oltava saman käyttäjän omistuksessa kuin taulukon.
- Determinismi: Lasketun sarakkeen on oltava deterministinen. Lisäksi, jos sarake sisältää CLR-lausekkeita, sen lisäksi, että sarake on deterministinen, sen on oltava persistentti.
- Tarkkuus: Lasketun sarakkeen lausekkeen on oltava tarkka. Tämä tarkoittaa, että se ei voi olla ”float”- tai ”real”-tietotyyppiä. Määrittelyssä ei myöskään voi käyttää tällaista tietotyyppiä. Tämä ominaisuus voidaan tarkistaa columnproperty()-funktiolla määrittämällä IsPrecise-ominaisuus.
- Tietotyyppi: Laskettu sarake ei voi olla tyyppiä text, ntext tai image. Myös jos lauseke sisältää image-, ntext-, text-, varchar (max), nvarchar (max), varbinary (max) tai xml-tietotyyppejä, sitä voidaan käyttää vain, jos lausekkeen tuloksena syntyvä tietotyyppi on sallittu indeksissä.
Näiden seikkojen lisäksi sarakkeen luomiseen käytetyillä yhteyksillä ja indeksin luomiseen käytetyllä yhteydellä on oltava tietyt määritykset, jotta nämä toiminnot voidaan suorittaa.
Laskennallisen sarakkeen luomiseen käytetyllä yhteydellä on oltava ANSI_NULLS-optio käytössä. Tämä voidaan tarkistaa columnproperty()-funktiolla määrittämällä IsAnsiNullsOn-ominaisuus.
Yhteyden, jolla luodaan indeksi, sekä yhteyksien, joilla suoritetaan indeksiin vaikuttavien tietueiden lisäys-, päivitys- ja poistotoimenpiteet, on oltava ANSI_NULLS-, ANSI_PADDING-, ANSI_WARNINGS-, ARITHABORT-, CONCAT_NULL_YIELDS_NULL-, QUOTED_IDENTIFIER- ja CONCAT_NULL_YIELDS_NULL- sekä QUOTED_IDENTIFIER-ominaisuudet aktiivisia. Lisäksi NUMERIC_ROUNDABORT-vaihtoehdon on oltava pois käytöstä.
Viimeiset huomiot
Lopuksi käymme läpi joitakin lisänäkökohtia, jotka on tarpeen tietää laskettujen sarakkeiden oikeaa käyttöä varten.
Laskettuja sarakkeita ei tietenkään voi päivittää eikä sisällyttää INSERT-toiminnon arvoluetteloon. Vaikka lasketut sarakkeet voivat olla osa select-lauseen tulosluetteloa, niitä voidaan käyttää myös lausekkeissa WHERE, ORDER BY tai kaikissa niissä, joihin voidaan laittaa lauseke.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Edellä mainitusta huolimatta laskettua saraketta ei voi käyttää DEFAULT- tai FOREIGN KEY-rajoituksen määrittelyssä. Ei myöskään NOT NULL -rajoitusmäärittelyssä.
Toisaalta laskettuja sarakkeita voidaan käyttää osana PRIMARY KEY- tai UNIQUE-rajoituksia. Tätä varten lasketun sarakkeen määritelmän tulisi olla deterministinen lauseke.
Johtopäätös
Laskettujen sarakkeiden käyttö voi olla erittäin hyödyllistä joissakin tilanteissa. On tutkittava tarkkaan, missä niitä käytetään, koska niihin liittyy rajoituksia, erityisesti indeksin luomiseen ja niiden pysyvyyteen. Tämä on vasta alkua. Kokeile rohkeasti uusia asioita ja kokeile laskennallisia sarakkeita uusien mahdollisuuksien löytämiseksi.