I min sidste artikel beskrev jeg i grove træk, hvordan standard SQL adskiller sig fra T-SQL, og hvem der bør lære hvad. Nu vil jeg gerne fokusere på syntaksforskellene og illustrere disse forskelle med eksempler. Hvis du tror, at T-SQL er en udvidelse, der implementerer alle funktionerne fra standard SQL, så har du ikke ret. Men i SQL Server finder du næsten alle funktionerne fra SQL-standarden. I denne artikel finder du eksempler på nogle af forskellene i syntaksen mellem standard-SQL og Transact-SQL.
#1 Navne på databaseobjekter
I relationelle databasesystemer navngiver vi tabeller, visninger og kolonner, men nogle gange har vi brug for at bruge det samme navn som et nøgleord eller bruge specialtegn. I standard SQL kan du placere denne type navn i anførselstegn (“”), men i T-SQL kan du også placere det i parentes (). Se disse eksempler på navnet på en tabel i T-SQL:
CREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100));CREATE TABLE dbo.test. ( Id INT, Name VARCHAR(100));Kun den første afgrænser (anførselstegnene) for det specielle navn er også en del af SQL-standarden.
Hvad er anderledes i en SELECT-erklæring?
SQL-standarden har ikke en syntaks for en forespørgsel, der returnerer værdier eller værdier, der kommer fra udtryk uden at henvise til nogen kolonner i en tabel, men MS SQL Server tillader denne type udtryk. Hvordan? Du kan bruge en SELECT-anvisning alene med et udtryk eller med andre værdier, der ikke kommer fra kolonner i tabellen. I T-SQL ser det ud som i nedenstående eksempel:
SELECT 12/6 ;I dette udtryk har vi ikke brug for en tabel til at evaluere 12 divideret med 6, og derfor kan FROM-erklæringen og navnet på tabellen udelades.
#3 Begrænsning af poster i et resultatsæt
I SQL-standarden kan du begrænse antallet af poster i resultaterne ved hjælp af den syntaks, der er illustreret nedenfor:
SELECT * FROM tab FETCH FIRST 10 ROWS ONLYT-SQL implementerer denne syntaks på en anden måde. Eksemplet nedenfor viser MS SQL Server-syntaksen:
SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;Som du bemærker, bruges her en ORDER BY-klausul. En anden måde at vælge rækker på, men uden ORDER BY, er ved at bruge TOP-klausulen i T-SQL:
SELECT TOP 10 * FROM tab;#4 Automatisk generering af værdier
SQL-standarden giver dig mulighed for at oprette kolonner med automatisk genererede værdier. Syntaksen til at gøre dette er vist nedenfor:
CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);I T-SQL kan vi også generere værdier automatisk, men på denne måde:
CREATE TABLE tab (id INTEGER IDENTITY);#5 Matematiske funktioner
Flere almindelige matematiske funktioner er en del af SQL-standarden. En af disse matematiske funktioner er CEIL(x), som vi ikke finder i T-SQL. I stedet indeholder T-SQL følgende ikke-standardiserede funktioner: SIGN(x), ROUND(x,) til at afrunde decimalværdien x til det angivne antal decimalpladser, TRUNC(x) til afkortning til et givet antal decimalpladser, LOG(x) til at returnere den naturlige logaritme for en værdi x og RANDOM() til at generere tilfældige tal. Det højeste eller laveste tal i en liste i SQL-standarden returneres af funktionerne MAX(list) og MIN(list), men i Transact-SQL bruger man funktionerne GREATEST(list) og LEAST(list).
T-SQL function ROUND:SELECT ROUND(col) FROM tab;#6 Aggregerede funktioner
Vi finder en anden syntaksforskel med de aggregerede funktioner. Funktionerne COUNT, SUM og AVG tager alle et argument, der er relateret til en tælling. T-SQL tillader brugen af DISTINCT før disse argumentværdier, så rækker kun tælles, hvis værdierne er forskellige fra andre rækker. SQL-standarden tillader ikke brugen af DISTINCT i disse funktioner.
Standard SQL:SELECT COUNT(col) FROM tab;T-SQL:SELECT COUNT(col) FROM tab;SELECT COUNT(DISTINCT col) FROM tab;Men i T-SQL finder vi ikke en funktion til populationskovarians: COVAR_POP(x,y), som er defineret i SQL-standarden.
#7 Hentning af dele af datoer og tidspunkter
De fleste relationelle databasesystemer leverer mange funktioner til at operere på datoer og tidspunkter.
I standard SQL er funktionen EXTRACT(YEAR FROM x) og lignende funktioner til at vælge dele af datoer forskellige fra T-SQL-funktioner som YEAR(x) eller DATEPART(year, x).
Der er også en forskel i at få den aktuelle dato og det aktuelle klokkeslæt. Standard SQL giver dig mulighed for at få den aktuelle dato med funktionen CURRENT_DATE, men i MS SQL Server er der ikke en lignende funktion, så vi er nødt til at bruge funktionen GETDATE som et argument i CAST-funktionen for at konvertere til en DATE-datatatype.
#8 Operere på strings
Anvendelse af funktioner til at operere på strings er også forskellig mellem SQL-standarden og T-SQL. Den største forskel findes ved at fjerne afsluttende og indledende mellemrum fra en streng. I standard-SQL er der funktionen TRIM, men i T-SQL er der flere relaterede funktioner: TRIM (fjernelse af efter- og forreste mellemrum), LTRIM (fjernelse af forreste mellemrum), og RTRIM (fjernelse af efterstregede mellemrum).
En anden meget ofte anvendt strengfunktion er SUBSTRING.
Standard SQL-syntaksen for SUBSTRING-funktionen ser ud som:
SUBSTRING(str FROM start ) Men i T-SQL ser syntaksen for denne funktion ud som:
SUBSTRING(str, start, length)Der er nogle gange grunde til at tilføje værdier, der kommer fra andre kolonner og/eller yderligere strenge. Standard SQL giver mulighed for følgende syntaks til at gøre dette:
Som du kan se, gør denne syntaks brug af operatoren || til at tilføje en streng til en anden.
Men den tilsvarende operatør i T-SQL er plus-tegnet. Se på dette eksempel:
SELECT col1 + col2 FROM tab;I SQL Server har vi også mulighed for at bruge funktionen CONCAT sammenkæder en liste af strenge:
SELECT CONCAT(col1, str1, col2, ...) FROM tab;Vi kan også gentage et tegn flere gange. Standard SQL definerer funktionen REPEAT(str, n) til at gøre dette. Transact-SQL indeholder funktionen REPLICATE. For eksempel:
SELECT REPLICATE(str, x); hvor x angiver, hvor mange gange strengen eller tegnet skal gentages.
#9 Ulighedsoperator
Ved filtrering af poster i en SELECT-anvisning er vi nogle gange nødt til at bruge en ulighedsoperator. Standard SQL definerer <> som denne operatør, mens T-SQL giver mulighed for både standardoperatoren og !=-operatoren:
SELECT col3 FROM tab WHERE col1 != col2;#10 ISNULL-funktion
I T-SQL har vi mulighed for at erstatte NULL-værdier, der kommer fra en kolonne, ved hjælp af ISNULL-funktionen. Dette er en funktion, der er specifik for T-SQL, og som ikke findes i SQL-standarden.
SELECT ISNULL(col1) FROM tab;Hvilke dele af DML-syntaksen er anderledes?
I T-SQL er den grundlæggende syntaks for DELETE-, UPDATE- og INSERT-forespørgsler den samme som i SQL-standarden, men der forekommer forskelle i mere avancerede forespørgsler. Lad os se på dem.
#11 OUTPUT-søgeordet
Søgeordet OUTPUT forekommer i DELETE-, UPDATE- og INSERT-angivelser. Det er ikke defineret i standard SQL.
Ved hjælp af T-SQL kan vi se ekstra oplysninger, der returneres af en forespørgsel. Det returnerer både gamle og nye værdier i UPDATE eller de værdier, der er tilføjet ved hjælp af INSERT eller slettet ved hjælp af DELETE. For at se disse oplysninger skal vi bruge præfikser i INSERT, UPDATE og DELETE.
UPDATE tab SET col='new value' OUTPUT Deleted.col, Inserted.col;Vi ser resultatet af ændrede poster med de tidligere og nye værdier i en opdateret kolonne. SQL-standarden understøtter ikke denne funktion.
#12 Syntaks for INSERT INTO … SELECT
En anden struktur for en INSERT-forespørgsel er INSERT INTO … SELECT. T-SQL giver dig mulighed for at indsætte data fra en anden tabel i en destinationstabel. Se på denne forespørgsel:
INSERT INTO tab SELECT col1,col2,... FROM tab_source;Det er ikke en standardfunktion, men en funktion, der er karakteristisk for SQL Server.
#13 FROM-klausul i DELETE og UPDATE
SQL Server giver udvidet syntaks for UPDATE og DELETE med FROM-klausuler. Du kan bruge DELETE with FROM til at bruge rækker fra en tabel til at fjerne tilsvarende rækker i en anden tabel ved at henvise til en primærnøgle og en fremmednøgle. På samme måde kan du bruge UPDATE with FROM til at opdatere rækker fra en tabel ved at henvise til rækker i en anden tabel ved hjælp af fælles værdier (primærnøgle i den ene tabel og fremmednøgle i den anden, f.eks. det samme bynavn). Her er et eksempel:
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-standarden giver ikke denne syntaks.
#14 INSERT, UPDATE og DELETE med JOIN
Du kan også bruge INSERT, UPDATE og DELETE med JOIN til at oprette forbindelse til en anden tabel. Et eksempel på dette er:
DELETE ItemOrder FROM ItemOrderJOIN Item ON ItemOrder.ItemId=Item.IdWHERE YEAR(Item.DeliveredDate) <= 2017;Denne funktion findes ikke i SQL-standarden.
Summary
Denne artikel dækker ikke alle spørgsmål om syntaksforskelle mellem SQL-standarden og T-SQL, der bruger MS SQL Server-systemet. Denne vejledning er dog med til at påpege nogle grundlæggende funktioner, der kun er karakteristiske for Transact-SQL, og hvilken SQL-standard syntaks der ikke er implementeret af MS SQL Server.