SQL Tips: SQL Server Management Studion käyttö

Muuttamalla SQL Server Management Studion (SSMS) asetuksia, voi tehostaa ohjelman parissa työskentelyä. Tässä vinkissä käydään läpi muutamia näistä.

******1. Korvaa selauspalkki sisällön esikatselulla**

Monissa tekstieditoreissa löytyy ominaisuus lisätä navigointipalkkiin koodin esikatselu. Management Studioon sen saa päälle asetuksista valikosta (Text Editor -> All Languages -> Scroll Bars).

Valitse sieltä päälle ”Use map mode for vertical scroll bar” -asetus. Oletuksena rullauspalkissa, hiiren kursorin kohdalla, näytetään myös pieni tekstin esikatseluikkuna. Jos kyseinen ominaisuus häiritsee, “Show Preview Tooltip” -kohdasta voi ottaa sen pois käytöstä. Source Overview -asetus muuttaa palkin leveyttä. Selauspalkin ulkonäön lisäksi, asetus muuttaa myös hieman sen toimintaa. Palkin raahaamisen sijaan, dokumentissa voi siirtyä nopeasti haluttuun kohtaan vain klikkaamalla siihen.

******2. Kiinnitetyt välilehdet omalle riville**

Monesti Management Studiossa työskennellessä kyselyitä kertyy useisiin välilehtiin, jolloin niiden hallinnasta tulee hankalaa. Välilehden voi kiinnittää ensimmäiseksi sulkemispainikkeen vieressä olevasta painikkeesta, mutta asetuksista saa muutettua kiinnittämisen vieläkin tehokkaammaksi tavaksi järjestellä välilehtiä.

Asetus löytyy valikosta (Environment -> Tabs and Windows), josta voi ottaa päälle ”Show pinned tabs in a separate row” asetuksen. Nyt kiinnitetyt välilehdet siirtyy omalle riville, eikä ne katoa näkyvistä vaikka välilehtiä kertyisi kymmeniä.

******3. Rivinumerot näkyviin**

Oletuksena Management Studiossa rivinumerot ei ole näkyvillä. Rivinumerot auttaa selkeyttämään pitkän SQL-kyselyn lukemista, ja tietyn kohdan uudelleenlöytämistä kyselyssä. Asetuksen saa päälle valikosta (Text Editor -> All Languages -> General) ja ottamalla käyttöön kohdan ”Line numbers”.

******4. Muuta tilapalkin väriä yhteydestä riippuen**

Useita palvelinyhteyksiä käyttäessä pitää olla tarkkana, mille palvelimelle nykyinen välilehti on yhteydessä. Yhteysasetuksista voi muuttaa alareunassa näkyvän tilapalkin väriä, riippuen siitä mihin palvelimeen välilehti on yhteydessä. Ominaisuutta käyttääksesi, yhteyden muodostusikkunassa valitse ”Options” ja siellä valitse rasti ruutuun ”Use custom color”. Värin voi käydä valitsemassa ”Select” napista.

Tämän jälkeen yhteyden muodostuksessa, jos palvelinnimen valitsee pudotusvalikosta, tulee myös käyttöön aiemmin määritetty tilapalkin väri.

SQL Tips: Split

SQL-kyselyissä tulee ajoittain tarvetta pilkkoa jonkin kentän sisältöä palasiksi. SQL Serverin versiosta 2016 alkaen T-SQL tarjoaa sisäisen funktion pilkkomisen tekemiseen. Lisäksi kannan compatibility level -asetus pitää olla vähintään 130, jotta tuo sisäinen string_split -funktio löytyy ja on käytettävissä.

String_split -funktion käyttö on hyvin suoraviivaista; parametreiksi annetaan pilkottava merkkijono sekä erotinmerkki. Funktio palauttaa yksisarakkeisen taulun, jossa pilkotut merkkijonon alkiot ovat value -nimisessä kentässä.

Helppo käyttöesimerkki on datan suodattaminen. Alla olevassa kyselyssä on Customer -taulusta suodatettu mukaan ne rivit, joiden id -kentän arvo on @test -merkkijonon sisällä.

Entäpä sitten aikaisemmissa Sql Serverin versioissa, joissa sisäistä funktiota ei ole tarjolla? Ainut tapa saada vastaava toiminnallisuus on tehdä oma funktio. Alla on esimerkkitoteutus funktiosta. Mukana on myös muutama lisäominaisuus; OrderNbr ja Trim.

Oman funktion käyttö on hyvin samanlaista kuin sisäisen string_split -funktion käyttö. Alla vastaava haku kuin ensimmäisessä esimerkissä.

Tässä esimerkissä on otettu mukaan myös oman funktion tarjoama OrderNbr -kenttä, josta nähdään monesko alkio on kyseessä.

SQL Tips: SARGable kyselyt

SARGable kyselyt (Search Argument-able queries) ovat kyselyitä, joissa indeksejä voidaan käyttää hyödyksi kun filtteröidään dataa, joko joinilla tai where -lauseella. Pelkkä indeksin lisääminen ei siis välttämättä paranna suorityskykyä, vaan kysely tulee tehdä tavalla, jolla Sql Server osaa hyödyntää indeksejä.

Demo datana toimii vastaava varasto, joka sisältää automallin, satunnaisen vuosiluvun ja luontipäivän:

DROP TABLE IF EXISTS dbo.CarInventory
CREATE TABLE dbo.CarInventory
(
    ID int IDENTITY(1,1),
    Name VARCHAR(100),
    Year int,
    CreateDate DATETIME2 DEFAULT GETDATE()
)
GO

CREATE NONCLUSTERED INDEX IX_CreateDate ON dbo.CarInventory (CreateDate) INCLUDE (Name)
CREATE NONCLUSTERED INDEX IX_Name ON dbo.CarInventory (Name)

Otetaan ensimmäisenä kysely, joka ei pysty hyödyntämään indeksiä halutulla tavalla:

SELECT Name
FROM dbo.CarInventory
WHERE Convert(varchar(8), CreateDate, 112) > '20170101'

Non-sargable execution plan

Execution planista nähdään, että Numbers of Rows Read on 330, joka vastaa koko taulua. Actual Number of Rows on kuitenkin vain 164. Tämä johtuu siitä, että Sql Server joutuu konvertoimaan jokaisen rivin CreateDate sarakkeen, jotta se pystyy filtteröimään datan.

Tähän helppo parannus:

SELECT Name
FROM dbo.CarInventory
WHERE CreateDate > CAST('20170101' as datetime2)

Sargable execution plan

Nyt Sql Serverin ei tarvitse käydä jokaista riviä läpi, vaan konvertointi tehdään vain kerran. Tästä johtuen taulusta luetaan vain tarvittavat 164 riviä.

Sql Server on myös sen verran fiksu, että se osaa tehdä konvertoinnin automaattisesti.

SELECT Name
FROM dbo.CarInventory
WHERE CreateDate > '20170101'

Sargable execution plan - implicit convert

Tulos on sama, mutta Sql Server huolehti konvertoinnin.

Toinen versio:

SELECT Name
FROM dbo.CarInventory
WHERE MONTH(CreateDate) = 6
AND YEAR(CreateDate) = 2017

Myös funktiota kutsuttaessa konvertointi tehdään sarakkeelle, jolloin funktiota kutsutaan taulun jokaisella rivillä.

Tämäkin voidaan korjata:

SELECT Name
FROM dbo.CarInventory
WHERE CreateDate >= '20170601' and  CreateDate < '20170701'

Näillä voi olla todella suuri merkitys suorituskyvyn kannalta, joten kannattaa olla tarkkana kuinka kyselynsä rakentaa. Netistä löytyy hyvin esimerkkejä, kuinka omasta ympäristöstään voi bongata kyselyitä, joissa on mahdollisesti edellä mainittuja ongelmia.

SQL Tips: SQL-kyselyiden datan vertailu

Välillä tulee tarve tehdä vertailua kahden SQL-kyselyn välillä. Silmämääräinen vertailu on hidasta ja epävarmaa. Ja EXCEPT ja INTERSECT lauseiden rakentaminen työlästä. Tähän tarkoitukseen monesti riittää tekstipohjainen rivien vertailu, joka löytyy mm. Notepad++ -ohjelmasta. Tämänkertaisessa vinkissä käydään läpi, kuinka Notepad++:n voi valjastaa datan vertailuun.

Notepad++ ohjelman voi ladata täältä: https://notepad-plus-plus.org/

Itse ohjelman lisäksi tarvitaan ”Compare” lisäosa. Varmista että Notepad++ asennuksessa on ”Plugins Admin” valittuna, tämä helpottaa lisäosien asentamista.

Asennuksen jälkeen avaa Notepad++ ja Plugins Admin ylävalikosta kohdasta: ”Plugins” -> ”Plugins Admin”. Etsi listalta "Compare" lisäosa ja laita ruksi lisäosan kohdalle. Sitten klikkaa ”Install”, niin lisäosa asentuu ohjelmaan.

Sen jälkeen Plugins-valikon alta löytyy Compare-valikko. Kannattaa kyseisestä valikosta laittaa päälle asetus ”Ignore Spaces”, niin vertailu ei ota huomioon eroja välilyöntien määrässä.

Vertailua varten avataan kaksi dokumenttia, jotka voi olla tallennettuja tiedostoja tai uusia tallentamattomia dokumentteja. Ja jotta vertailuun valitut tiedostot ei vaikuttaisi mielipuolisilta, niin kannattaa toinen vertailtava dokumentti siirtää viereiselle näkymälle, joka onnistuu, kun klikkaa välilehteä hiiren oikealla ja valitsee ”Move to other view”.

Sitten kopioi kyselyn tulokset molempiin dokumentteihin ja valitse ylävalikosta: ”Plugins” -> ”Compare” -> ”Compare”. Tai paina näppäinyhdistelmää: Ctrl + Alt + C

Joskus tuloksissa tulee paljon ”siirretyksi” tunnistettuja, sinisiä rivejä, tai vertailu ei muuten näyttänyt tunnistavan muutoksia oikein. Kannattaa silloin molempien dokumenttien rivit ensin järjestellä. Useimmiten muutenkin hyvä tapa tehdä rivien järjestely ennen vertailua. Järjestelyn voi tehdä valikosta: “Edit” -> “Line Operations” -> “Sort Lines Lexicographically Ascending”. Ja operaatio pitää tehdä molemmille verrattaville dokumenteille.

Notepad++ toimii kätevästi rivien vertailuun ja tuloksista näkee selkeästi, missä löytyy eroavaisuuksia. Suurten rivimäärien kanssa vertailun prosessointi voi alkaa kestämään pitkään ja ohjelmalta voi myös muisti loppua kesken, mutta alle 10 000 rivin vertailu onnistuu vielä hyvin.

SQL Tips: Case sensitive SQL:n WHERE-lauseessa

Tässä esimerkissä on yksinkertainen taulu, jossa dataa näin:

Normaalisti esimerkiksi alla oleva kysely palauttaa rivit riippumatta tekstin kirjainkoosta:

Jos halutaan hakea vain tietyllä kirjainkoolla olevat rivit, pitää WHERE-ehdon perään lisätä oikea COLLATE-osuus. Tässä esimerkissä voidaan käyttää COLLATE SQL_Latin1_General_CP1_CS_AS. Tärkeintä on, että COLLATE-osuudesta löytyy _CS, joka tarkoittaa Case Sensitiveä. _AS (Accent Sensitive) tarkoittaa sitä, että a ei ole sama asia, kuin ä.

Kaikki eri COLLATE vaihtoehdot voit tarkistaa kyselyllä:

SELECT name, description 
FROM sys.fn_helpcollations()

Nyt kysely palauttaa vain halutulla kirjainkoolla olevat rivit:

Jos halutaan, että Tekstiarvo-kentässä mahdollisesti olevat indeksoinnit vaikuttaisivat, pitää rivi lisätä vielä ilman COLLATE-osuutta SQL-lauseeseen.

SQL Tips: Harmeja NULL-arvosta

SQL-kyselyissä NULL-arvot aiheuttavat usein harmeja. On tärkeää ymmärtää, että NULL tarkoittaa puuttuvaa arvoa. Se ei tarkoita välilyöntiä eikä nollaa vaan tuntematonta arvoa. Jos mahdollista, pyri muuttamaan NULL-arvot aina joko nollaksi (0) tai tyhjäksi (’’) niin säästyt monelta harmilta.

Totuus on, että tauluista löytyy kuitenkin aina myös NULL-arvoja. Siksi onkin syytä huomioida, ettei NULL käyttäydy samoin kuin muut arvot.

Tässä ensin esimerkkitaulujen tiedot ja esimerkit NULL-arvojen toiminnasta.

Jos laskukaavassa on mukana NULL-arvo palauttaa laskenta aina NULL. AlennettuHinta jää siis laskennassa NULL-arvoksi, koska Alennus on NULL.

NOT IN kyselyssä Fakta-taulun TuoteID on NULL-arvo, joten seuraava lause ei palauta mitään vaikka periaatteessa TuoteID 223 puuttuukin Fakta-taulusta.

NULL ei toimi taulujen liitoksissa (JOIN tai MERGE). Vaikka molempien taulujen avainkentässä olisi NULL, niin se ei yhdistä rivejä ja rivi jää puuttumaan. Esim. jos Fakta2 taulu olisi samanlainen taulu kuin Fakta ja yhdistetään ne TuoteID sekä Yhtio kenttien avulla. Kysely ei palauta yhtään riviä vaikka TuoteID 222 löytyykin molemmista tauluista ja Yhtio molemmissa on NULL.

Merge lauseessa syntyy joka kerta uudet rivit, koska NULL-arvo ei yhdistä avaimia.

Ongelman voi kiertää NULL tarkistuksilla, kuten esim. lisäämällä IS NULL -ehdot tai käyttämällä COALESCE()-funktiota.

Tai

Saatat myös ymmärtää NULL-arvon ja tyhjän arvon eron seuraavan havainnollistavan kuvan avulla:


Kuva: DevRant

SQL Tips: INT-muotoisen kentän jakolaskut

Jakaessa kahta INT-tyyppistä kenttää keskenään tulos on aina myös INT ja vieläpä niin, että tulosta ei pyöristetä, vaan se katkaistaan.

Esim. alla oleva SQL-lause palauttaa vastaukseksi 1.

SELECT 9 / 5

Jos taas lauseen jompikumpi arvo konvertoidaan desimaaliksi esimerkiksi näin

SELECT 9 * 1.0 / 5

tai näin

SELECT CAST(9 as float) / 5

palauttaa SQL vastaukseksi 1,8.

Samoin toimii sisäänrakennetut aggregointifunktiot, esim. AVG.

Esim. Tässä yksinkertainen taulu, jossa int-tyyppisessä kentässä arvoja

Normaali AVG-funktio palauttaa keskiarvoksi 2.

Kun taas konvertoituna palauttaa näin

Desimaalin muuttamiseksi kannattaa valita kyselyyn sopiva suorituskykyisin keino