SQL Server-tietokanta ja klassinen DataOps – Ohjelmoitavuus

Jani Savolainen

Tämä blogi jatkaa blogisarjaani SQL Server-tietokantojen klassiseen DataOpsiin liittyen. Tässä blogissa keskitytään ohjelmoitavuuden (uudelleen-) luomiseen.

Clean install-skriptimoduulit

Tänään läpikäydään seuraavat skriptikokonaisuudet:

  • Drop programmability
  • Table types
  • Create programmability

Huom! Skriptit on ajettava juuri tässä järjestyksessä.

Drop programmability -skripti

Drop programmability-skriptin vaiheet ovat:

  • Pudotetaan valikoidut käyttäjän määrittelemät proseduurit (usp_)
  • Pudotetaan valikoidut käyttäjän määrittelemät funktiot (ufn_)

Käyttäjän määrittelemät proseduurit on hyvä etuliitteistää ”usp_” -alkuisiksi, jotta ne erottuvat systeemin stored proceduresta (sp_). Ohessa skripti, joka automatisoi kaikkien käyttäjän määrittelemien proseduurien poistolausekkeet. Huomaathan, että kyseinen esimerkkiskripti ei itse poista proseduureja, vaan ainoastaan listaa poistolausekkeet. Jää sitten installerin tehtäväksi suorittaa varsinainen poisto-operaatio. Voit halutessasi muuttaa koodia siten, että proseduurien poisto suoritetaan while-slimukassa esimerkiksi exec -komennolla tai sp_execute_sql -systeemiproseduuria käyttäen. Proseduurit löytyvät sys.objects -näkymästä ja niiden tyyppi on ’P’, eli proseduuri.

Käyttäjän määrittelemät funktiot on vastaavasti hyvä etuliitteistää ”ufn_” -alkuisiksi, jotta ne erottuvat systeemifnktioista (fn_). Ohessa vastaavasti skripti, joka automatisoi kaikkien käyttäjän määrittelemien funktioiden poiston luontilauseet. Muutoin samat huomiot kuin proseduurien suhteen.

Funktiot löytyvät sys.objects -näkymästä ja niiden tyyppi on jokin seuraavista:

  • TF = SQL table-valued-function (TVF)
  • FN = SQL Scalar function
  • IF = SQL inline table-valued function (TVF)

Tämän lisäksi on vielä joukko hupomioitavia CLR-funktioita:

  • AF = Aggregate function
  • FS = Assembly scalar-function
  • FT = Assembly table-valued function

Table types -skripti ja TVP:t

Taulutyypit ovat erittäin käteviä monessa tilanteessa. Jos ne eivät ole sinulle ennestään tuttuja, pieni esittely lienee paikallaan: Niin kutsutut TVP:t (”Table-Valued Parameters”) määritetään käyttämällä käyttäjän määrittämiä taulutyyppejä. TVP:iden avulla voit välittää useita tietueita T-SQL-lauseeseen tai rutiiniin, kuten proseduuriin tai funktioon luomatta väliaikaista taulukkoa tai useita parametreja. Käytännössä nämä ovat siis eräänlaisia parametrilistoja. Suorituskykyiseksi tämän tekniikan tekee se, että T-SQL välittää TVP:t rutiineihin viittaamalla, jotta syöttötiedoista ei tehdä erillistä kopiota.

TVP on aina rajattu proseduuriin, funktioon tai dynaamiseen Transact-SQL-tekstiin, aivan kuten muutkin parametrit. Vastaavasti TVP:llä on vaikutusalue kuten millä tahansa muulla paikallisella muuttujalla, joka luodaan käyttämällä DECLARE-lausetta. Voit määritellä TVP:t dynaamisissa T-SQL-lausekkeissa ja välittää nämä muuttujat TVP:nä proseduureihin ja funktioihin.

TVP:t tarjoavat enemmän fleksiä ja joissakin tapauksissa paremman suorituskyvyn kuin temporary tablet tai muut parametriluettelon välittämistavat. Taulukkoarvoiset parametrit tarjoavatkin tietokantakoodarille seuraavia hyötyjä:

  • Ne eivät ota lukkoja alkuperäisessä datan populoinnissa clientilta
  • Ne tarjoavat yksinkertaisemman ohjelmointimallin
  • Voit toteuttaa monimutkaistakin ohjelmalogiikkaa yhdessä rutiinissa
  • Vähennät server roundtrippejä
  • Voit käsitellä eri kardinaliteetin taulurakenteita
  • Rakenteet ovat vahvasti tyypitettyjä
  • Voit antaa clientin valita sort orderin ja uniikit avaimet
  • TVP:n välimuistikäsittely vastaa proseduureissa temppitauluja

Taulutyypin poisto onnistuu seuraavanlaisesti:

Taulutyyppiä luotaessa sen olemassaolemattomuus varmistetaan sys.types -näkymän kautta esimerkiksi seuraavanlaisesti:

Create programmability -skripti

Tässä skriptissä (uudelleen-) luodaan käyttäjän määrittelemät proseduurit ja funktiot kääntäisessä kutsumajärjestyksessä:

  1. Ensin esitellään sellaiset proseduurit ja funktiot, jotka eivät ole riipuvaisia toisista proseduureista ja funktioista.
  2. Sitten esitellään sellaiset proseduurit ja funktiot, joita kutsutaan muista proseduureista ja funktioista siten, että järjestys on kutsumajärjestyksen suhteen käänteinen: Jos A kutsuu B:tä joka kutsuu C:tä, C esitellään ensin, sitten B, lopuksi A.
  3. Viimeisimpänä esitellään näinollen sellaiset proseduurit ja funktiot, jotka ovat eniten riippuvaisia muista proseduureista ja funktioista.

Loppusanat

Jälleen kerran voimme todeta, että systeemiolioiden kautta operointi helpottaa merkittävästi rutiinien ohjelmoitavuutta. Tämän lisäksi opimme, että taulutyypit ovat erinomainen lisäkonsti temppitaulujen sekä CTE:iden (Common Table Expressions) lisäksi silloin, kun ohjelmoitavuus kompleksisoituu rutiinien, kuten käyttäjän määritetlemien proseduurien välillä ja välitettävän tiedon määrä on suuri.

Ovatko DBA:si ylityöllistettyjä? Meiltä saat juuri sopivan kaistan black belt -tason DBA-osaamista tukemaan liiketoimintaasi. Pidetään pyörät pyörimässä!

Jani K. Savolainen

jani.savolainen@dbproservices.fi

0440353637

VP & Chairman

DB Pro Services Oy

Sinua saattaa kiinnostaa myös:
Master Data ja Master Datan hallinta osana Business Intelligence arkkitehtuuria
SQL-tietokanta – historia, nykytila ja tulevaisuus: historia
Power BI pro ja eri lisensiointimallit
Power BI-raportointi – tasapaino tekniikan ja muotoilun välillä 

Katso myös

Ota yhteyttä