14 Standard SQL:n ja Transact-SQL:n välisiä eroja - DZone Database

Viimeisessä artikkelissani kuvasin karkeasti, miten standard SQL eroaa T-SQL:stä ja kenen pitäisi oppia kumpi. Nyt haluan keskittyä syntaksieroihin ja havainnollistaa näitä eroja esimerkeillä. Jos luulet, että T-SQL on laajennus, joka toteuttaa kaikki ominaisuudet standardista SQL:stä, et ole oikeassa. SQL Serveristä löydät kuitenkin lähes kaikki SQL-standardin ominaisuudet. Tässä artikkelissa on esimerkkejä joistakin standardin SQL:n ja Transact-SQL:n välisistä syntaktisista eroista.

#1 Tietokantaobjektien nimet

Relaatiotietokantajärjestelmissä nimetään taulukoita, näkymiä ja sarakkeita, mutta toisinaan joudutaan käyttämään samaa nimeä avainsanana tai erikoismerkkejä. Tavallisessa SQL:ssä tällainen nimi voidaan sijoittaa lainausmerkkeihin (””), mutta T-SQL:ssä se voidaan sijoittaa myös sulkuihin (). Katso näitä esimerkkejä taulukon nimestä T-SQL:ssä:

CREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100));CREATE TABLE dbo.test. ( Id INT, Name VARCHAR(100));

Vain erikoisnimen ensimmäinen erotusmerkki (lainausmerkit) kuuluu myös SQL-standardiin.

Mitä erilaista SELECT-lausekkeessa on?

SQL-standardissa ei ole syntaksia kyselylle, jossa palautetaan arvoja tai lausekkeista tulevia arvoja viittaamatta mihinkään taulukon sarakkeisiin, mutta MS SQL Server sallii kuitenkin tämäntyyppiset lausekkeet. Miten? Voit käyttää SELECT-lauseketta pelkästään lausekkeen kanssa tai muiden arvojen kanssa, jotka eivät tule taulukon sarakkeista. T-SQL:ssä se näyttää alla olevan esimerkin kaltaiselta:

SELECT 12/6 ;

Tässä lausekkeessa emme tarvitse taulukkoa 12 jaettuna 6:lla, joten FROM-lause ja taulukon nimi voidaan jättää pois.

#3 Tietueiden rajoittaminen tulosjoukossa

SQL-standardissa voit rajoittaa tietueiden määrää tuloksissa käyttämällä alla esitettyä syntaksia:

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY

T-SQL toteuttaa tämän syntaksin eri tavalla. Alla olevassa esimerkissä näkyy MS SQL Serverin syntaksi:

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

Kuten huomaat, tässä käytetään ORDER BY -lauseketta. Toinen tapa valita rivejä, mutta ilman ORDER BY:tä, on käyttää T-SQL:n TOP-lauseketta:

SELECT TOP 10 * FROM tab;

#4 Automaattisesti generoitavat arvot

SQL-standardin avulla voit luoda sarakkeita, joilla on automaattisesti generoituja arvoja. Syntaksi tähän on esitetty alla:

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

T-SQL:ssä voimme myös luoda arvoja automaattisesti, mutta tällä tavalla:

CREATE TABLE tab (id INTEGER IDENTITY);

#5 Matemaattiset funktiot

Monet yleiset matemaattiset funktiot ovat osa SQL-standardia. Yksi näistä matemaattisista funktioista on CEIL(x), jota emme löydä T-SQL:stä. Sen sijaan T-SQL tarjoaa seuraavat ei-standardinmukaiset funktiot: SIGN(x), ROUND(x,), jolla pyöristetään desimaaliarvo x desimaalien lukumäärään, TRUNC(x), jolla typistetään annettuun desimaalien lukumäärään, LOG(x), jolla palautetaan luonnollinen logaritmi arvolle x, ja RANDOM(), jolla luodaan satunnaislukuja. SQL-standardissa listan suurin tai pienin luku palautetaan MAX(list) ja MIN(list) -funktioilla, mutta Transact-SQL:ssä käytetään GREATEST(list) ja LEAST(list) -funktioita.

T-SQL function ROUND:SELECT ROUND(col) FROM tab;

#6 Aggregaattifunktiot

Löydämme toisenkin syntaktisen eron aggregaattifunktioista. Funktiot COUNT, SUM ja AVG ottavat kaikki laskentaan liittyvän argumentin. T-SQL sallii DISTINCT:n käytön näiden argumenttiarvojen edessä, jotta rivit lasketaan vain, jos arvot eroavat muista riveistä. SQL-standardi ei salli DISTINCT:n käyttöä näissä funktioissa.

Standard SQL:SELECT COUNT(col) FROM tab;T-SQL:SELECT COUNT(col) FROM tab;SELECT COUNT(DISTINCT col) FROM tab;

Mutta T-SQL:stä ei löydy populaatiokovarianssifunktiota: COVAR_POP(x,y), joka on määritelty SQL-standardissa.

#7 Päivämäärien ja kellonaikojen osien hakeminen

Useimmat relaatiotietokantajärjestelmät toimittavat monia funktioita päivämäärien ja kellonaikojen käsittelyyn.

SQL-standardissa EXTRACT(YEAR FROM x) -funktio ja vastaavat funktiot päivämäärien osien valitsemiseksi eroavat T-SQL-funktioista, kuten YEAR(x) tai DATEPART(year, x).

Eroa on myös nykyisen päivämäärän ja kellonajan saamisessa. Standard SQL mahdollistaa nykyisen päivämäärän saamisen CURRENT_DATE-funktiolla, mutta MS SQL Serverissä ei ole vastaavaa funktiota, joten joudumme käyttämään GETDATE-funktiota argumenttina CAST-funktiossa muuntaaksemme DATE-tietotyypiksi.

#8 Operointi merkkijonojen kanssa

Funktioiden käyttäminen merkkijonojen kanssa operoimiseen eroaa niin ikään SQL-standardin ja T-SQL:n välillä. Suurin ero on merkkijonon perässä ja edessä olevien välilyöntien poistamisessa. SQL-standardissa on TRIM-funktio, mutta T-SQL:ssä on useita siihen liittyviä funktioita: TRIM (peräkkäisten ja etummaisten välilyöntien poistaminen), LTRIM (etummaisten välilyöntien poistaminen) ja RTRIM (peräkkäisten välilyöntien poistaminen).

Toinen hyvin usein käytetty merkkijonofunktio on SUBSTRING.

SUBSTRING-funktion vakiomuotoinen SQL-syntaksi näyttää seuraavalta:

SUBSTRING(str FROM start )

Mutta T-SQL:ssä tämän funktion syntaksi näyttää seuraavalta:

SUBSTRING(str, start, length)

Joskus on syitä lisätä arvoja, jotka ovat peräisin muista sarakkeista ja/tai lisäjonoista. Standard SQL mahdollistaa tähän seuraavan syntaksin:

Kuten näet, tässä syntaksissa käytetään ||-operaattoria yhden merkkijonon lisäämiseksi toiseen.

Mutta vastaava operaattori T-SQL:ssä on plus-merkki. Katso tätä esimerkkiä:

SELECT col1 + col2 FROM tab;

SQL Serverissä meillä on myös mahdollisuus käyttää CONCAT-funktiota, joka yhdistää merkkijonojen luettelon:

SELECT CONCAT(col1, str1, col2, ...) FROM tab;

Voidaan myös toistaa yksi merkki useita kertoja. Standard SQL määrittelee tätä varten funktion REPEAT(str, n). Transact-SQL tarjoaa REPLICATE-funktion. Esimerkiksi:

SELECT REPLICATE(str, x);

jossa x ilmoittaa, kuinka monta kertaa merkkijono tai merkki toistetaan.

#9 Epäyhtäläisyysoperaattori

Tietueiden suodatuksessa SELECT-lauseessa joudumme joskus käyttämään epäyhtäläisyysoperaattoria. Standard SQL määrittelee <> tämän operaattorin, kun taas T-SQL sallii sekä standardioperaattorin että !=-operaattorin:

SELECT col3 FROM tab WHERE col1 != col2;

#10 ISNULL-funktio

T-SQL:ssä meillä on mahdollisuus korvata sarakkeesta tulevat NULL-arvot ISNULL-funktion avulla. Tämä on T-SQL-kohtainen funktio, jota ei ole SQL-standardissa.

SELECT ISNULL(col1) FROM tab;

Missä DML-syntaksin osissa on eroja?

T-SQL:ssä DELETE-, UPDATE- ja INSERT-kyselyjen perussyntaksi on sama kuin SQL-standardissa, mutta erot ilmenevät kehittyneemmissä kyselyissä. Tarkastellaan niitä.

#11 OUTPUT-avainsana

OUTPUT-avainsana esiintyy DELETE-, UPDATE- ja INSERT-lauseissa. Sitä ei ole määritelty tavallisessa SQL:ssä.

T-SQL:n avulla voimme nähdä kyselyn palauttamaa lisätietoa. Se palauttaa sekä vanhat että uudet arvot UPDATE:ssa tai INSERT:lla lisätyt tai DELETE:lla poistetut arvot. Nähdäksemme nämä tiedot meidän on käytettävä etuliitteitä INSERT-, UPDATE- ja DELETE-kohdissa.

UPDATE tab SET col='new value' OUTPUT Deleted.col, Inserted.col;

Näemme tietueiden vaihtamisen tuloksena edellisen ja uuden arvon päivitetyssä sarakkeessa. SQL-standardi ei tue tätä ominaisuutta.

#12 Syntaksi INSERT INTO … SELECT

Toinen INSERT-kyselyn rakenne on INSERT INTO … SELECT. T-SQL:n avulla voit lisätä tietoja toisesta taulukosta kohdetaulukkoon. Katso tätä kyselyä:

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

Se ei ole vakio-ominaisuus, vaan SQL Serverille ominainen ominaisuus.

#13 FROM-lauseke DELETE- ja UPDATE-lausekkeissa

SQL Server tarjoaa laajennetun syntaksin UPDATE- ja DELETE-lausekkeille FROM-lausekkeilla. DELETE with FROM -lausekkeen avulla voit käyttää yhden taulun rivejä poistamaan vastaavat rivit toisesta taulusta viittaamalla ensisijaiseen avaimeen ja ulkoiseen avaimeen. Vastaavasti voit käyttää UPDATE with FROM -lauseketta päivittää yhden taulukon rivejä viittaamalla toisen taulukon riveihin käyttämällä yhteisiä arvoja (ensisijainen avain yhdessä taulukossa ja vieras avain toisessa, esim. sama kaupungin nimi). Tässä on esimerkki:

DELETE FROM BookFROM AuthorWHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE BookSET Book.Price=Book.Price*0.2FROM AuthorWHERE Book.AuthorId=Author.Id AND Author.Id=12;

SQL-standardi ei tarjoa tätä syntaksia.

#14 INSERT, UPDATE, and DELETE With JOIN

Voit käyttää INSERT, UPDATE ja DELETE -tekniikoita myös JOINin avulla, kun haluat muodostaa yhteyden toiseen tauluun. Esimerkki tästä on:

DELETE ItemOrder FROM ItemOrderJOIN Item ON ItemOrder.ItemId=Item.IdWHERE YEAR(Item.DeliveredDate) <= 2017;

Tämä ominaisuus ei ole SQL-standardissa.

Yhteenveto

Tämä artikkeli ei kata kaikkia SQL-standardin ja MS SQL Server -järjestelmää käyttävän T-SQL:n välisiin syntaksieroihin liittyviä asioita. Tämä opas auttaa kuitenkin osoittamaan joitakin perusominaisuuksia, jotka ovat ominaisia vain Transact-SQL:lle, ja mitä SQL-standardin syntaksia MS SQL Server ei toteuta.