Wat is T-SQL? Een beginnersgids voor Transact-SQL voor SQL Server

Functies in T-SQL

T-SQL bevat zowel ingebouwde functies als door de gebruiker gedefinieerde functies (UDF’s) om data te transformeren.

Ingebouwde functies

Deze functies zijn al beschikbaar in SQL Server en kun je direct in queries gebruiken.

Tekenreeksfunctie

Dit is een functie die tekenreeks-datatypen transformeert. De onderstaande query haalt bijvoorbeeld alle voornamen op uit de tabel Customers en zet ze om naar hoofdletters.

-- Converts text to uppercase
SELECT UPPER(FirstName)  
FROM Customers;

Datumfunctie

Deze functie transformeert datumfuncties. De onderstaande query geeft de huidige systeemtijd en -datum terug.

-- Returns current date and time
SELECT GETDATE();  

Aggregaatfunctie

Dit zijn functies die waarden in een tabel aggregeren. De onderstaande query telt bijvoorbeeld het totale aantal rijen (klanten) in de tabel Customers.

-- Counts rows
SELECT COUNT(*) AS TotalCustomers
FROM Customers;  

Door de gebruiker gedefinieerde functies (UDF’s)

Je kunt ook je eigen functies maken om logica in meerdere queries te hergebruiken. 

Scalair-functie

Een scalair-functie retourneert één waarde en is handig wanneer je één berekende waarde nodig hebt, zoals voor opmaak of berekeningen. In de onderstaande query neemt de functie een voornaam en achternaam als invoer en geeft deze terug als één volledige naam.

-- Create a function that combines the first and last name
CREATE FUNCTION GetFullName 
(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName; -- Concatenates the two values
END;

Tabelwaardige functies

Tabelwaardige functies retourneren een tabel. De onderstaande functie retourneert bijvoorbeeld een tabel met alle klanten die tot het opgegeven land behoren.

-- Create a table-valued function named GetCustomersByCountry
CREATE FUNCTION GetCustomersByCountry (@Country VARCHAR(50))
RETURNS TABLE -- Specifies that the function returns a table
AS
RETURN
(
    -- Select all columns from the Customers table
    SELECT *
    FROM Customers
    WHERE Country = @Country -- Filter rows by the given country
);

Ik raad je aan onze volledige SQL Server Developer-carrièretrack te proberen, die je niet alleen de vaardigheden geeft om stored procedures te maken, bij te werken en uit te voeren, maar je ook helpt met aggregatiefuncties, joinen, invoegen en verwijderen van tabellen, en nog veel meer.

Foutafhandeling in T-SQL

T-SQL gebruikt TRY...CATCH-blokken om fouten af te handelen zonder het hele script te stoppen. Dit is belangrijk omdat het voorkomt dat scripts onverwacht crashen en je in staat stelt betekenisvolle berichten terug te geven voor debugging of logging.

In het onderstaande voorbeeld probeert de query een berekening uit te voeren die een fout veroorzaakt, vangt die op en print een leesbare foutmelding in plaats van te crashen.

BEGIN TRY
    -- Attempt to run this code
    SELECT 1 / 0;  -- This causes a divide-by-zero error
END TRY
BEGIN CATCH
    -- Runs if an error occurs in the TRY block
    PRINT 'An error occurred: ' + ERROR_MESSAGE(); -- Displays the error message
END CATCH;

Transacties in T-SQL

Een transactie zorgt ervoor dat een groep bewerkingen ofwel allemaal slaagt of allemaal faalt. Dit volgt het ACID-principe, dat garandeert dat je database consistent blijft, zelfs als de stroom uitvalt midden in een update.

In het onderstaande voorbeeld boekt de transactie geld over van de ene rekening naar de andere en slaat beide wijzigingen samen op.

BEGIN TRANSACTION; -- Start the transaction

-- Deduct money from Account 1
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Add money to Account 2
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT; -- Save all changes permanently

Als er iets misgaat, kun je de functie ROLLBACK gebruiken om de transactie te annuleren en de data in de vorige staat te herstellen.

-- Undo all changes made in the current transaction
ROLLBACK; 

Transacties in T-SQL zijn belangrijk omdat ze dataintegriteit waarborgen, vooral in kritieke systemen zoals financiële applicaties, waar gedeeltelijke updates tot inconsistenties kunnen leiden.

Je kunt onze Transactions and Error Handling in SQL Server-carrièretrack proberen, die transacties en foutafhandeling in SQL Server behandelt.

Veelgebruikte T-SQL-functies voor data-analyse

T-SQL bevat tools die vooral nuttig zijn voor dataprofessionals. Laten we enkele voorbeelden bekijken en hoe je ze toepast in data-analyse.

Vensterfuncties

Vensterfuncties voeren berekeningen uit over rijen zonder ze te groeperen tot één resultaat. Ze worden vooral gebruikt voor ranking, voortschrijdende totalen en geavanceerde analyses.

In het onderstaande voorbeeld kent de query een uniek rijnummer toe aan elke klant op basis van hun CustomerID.

-- Assigns row numbers to customers
SELECT 
    FirstName,
    ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum -- Assigns row numbers
FROM Customers;

In de query:

• ROW_NUMBER(): Kent unieke rijnummers toe

• RANK(): Kent rangen toe met gaten bij gelijke waarden

• OVER(): Definieert het venster (ordening/partitionering)

Ik raad je aan onze SQL Window Functions Cheat Sheet te bekijken, die je kunt downloaden, met een handig naslagwerk van de verschillende soorten vensterfuncties in SQL. Bekijk ook onze cursus Intermediate SQL Server om meer te leren over het gebruik van T-SQL in data-analyse, inclusief vensterfuncties om data samen te vatten.

Common table expressions (CTE’s)

CTE’s (Common Table Expressions) maken een tijdelijk resultaat dat je binnen een query kunt hergebruiken. De onderstaande query maakt een tijdelijke lijst van Amerikaanse klanten en haalt er vervolgens data uit op.

-- Define a CTE
WITH CustomerCTE AS (
    SELECT FirstName, Country
    FROM Customers
    WHERE Country = 'USA' -- Filter only USA customers
)

-- Query the CTE
SELECT *
FROM CustomerCTE;

Recursieve CTE’s daarentegen zijn een speciaal type CTE dat naar zichzelf verwijst binnen de definitie, waardoor de query herhaalde operaties kan uitvoeren. Dit maakt ze ideaal voor hiërarchische data, zoals organigrammen.

Ik raad je aan onze cursus Hierarchical and Recursive Queries in SQL Server te volgen om te leren hoe je geavanceerde queries in SQL Server schrijft, inclusief CTE’s en recursieve CTE’s, met praktische voorbeelden.

Tijdelijke tabellen

Tijdelijke tabellen slaan data op voor kortstondig gebruik tijdens een sessie. Ze zijn handig voor staging van data, het vereenvoudigen van meerstapstransformaties of het verbeteren van prestaties in complexe queries.

Om te laten zien hoe tijdelijke tabellen in T-SQL werken, bekijk de volgende drie queryblokken. Het eerste maakt een tijdelijke tabel, vult die vervolgens met klantdata en bevraagt hem daarna binnen dezelfde sessie.

-- Create a temporary table
CREATE TABLE #TempCustomers (
    FirstName VARCHAR(50),
    Country VARCHAR(50)
);

-- Insert data into the temporary table
INSERT INTO #TempCustomers
SELECT FirstName, Country
FROM Customers;

-- Retrieve data from the temporary table
SELECT * FROM #TempCustomers;

Belangrijk: tijdelijke tabellen hebben het voorvoegsel # en worden automatisch verwijderd wanneer de sessie eindigt.

T-SQL vs. andere SQL-dialecten

Alle SQL-dialecten delen dezelfde basis, maar elk databasesysteem voegt eigen syntaxis en functies toe. T-SQL is specifiek ontworpen voor Microsoft SQL Server, wat invloed heeft op hoe het werkt en waar het het best tot zijn recht komt.

T-SQL vs. PostgreSQL (PL/pgSQL)

PostgreSQL gebruikt PL/pgSQL voor procedurele logica, vergelijkbaar met T-SQL maar met andere syntaxis en sterke punten. De onderstaande tabel vat deze verschillen samen:

Bekijk onze skill track SQL for Database Administrators om te leren hoe je relationele databases ontwerpt en je queries structureert om prestaties te verbeteren.

T-SQL vs. MySQL

MySQL ondersteunt ook procedurele SQL, maar met een eenvoudigere en beperktere aanpak. De onderstaande tabel vergelijkt de syntaxis- en functieverorden tussen deze twee dialecten.

Praktijkcases voor T-SQL

T-SQL wordt op grote schaal gebruikt, verder dan simpelweg data opvragen. Hieronder staan voorbeelden waar ik het als dataprofessional nuttig vond.

Rapportagequery’s

T-SQL wordt vaak gebruikt om rechtstreeks vanuit de database rapporten te genereren. Je kunt bijvoorbeeld verkoop per regio aggregeren of topproducten rangschikken met vensterfuncties.

ETL-processen (Extract, Transform, Load)

T-SQL wordt in datapijplijnen gebruikt door data engineers om data voor te bereiden en te verplaatsen. Je kunt bijvoorbeeld data uit stagingtabellen extraheren, formaten transformeren, inconsistenties opschonen en in definitieve rapportagetabellen laden.

Opschoningsscripts voor data

Als je database miljoenen rijen met inconsistente adresnotaties heeft, kun je een T-SQL-script met WHILE-lussen of CTE’s gebruiken om duplicaten te identificeren, ontbrekende waarden op te vullen of formaten te standaardiseren.

Backendlogica in applicaties

Veel applicaties vertrouwen op T-SQL om businesslogica in de database af te handelen. Je kunt stored procedures gebruiken voor CRUD-bewerkingen, validaties uitvoeren vóór het invoegen van data of transactieafhandeling gebruiken voor kritieke operaties.

Veelgemaakte fouten en best practices

Hoewel T-SQL nuttig is voor geavanceerde databasequery’s, kom je waarschijnlijk een paar veelvoorkomende problemen tegen. Hieronder staan enkele veelgemaakte fouten die ik ben tegengekomen bij het gebruik van T‑SQL:

• Lussen overmatig gebruiken in plaats van set-gebaseerde logica: Het gebruik van lussen, zoals WHILE, voor rij-voor-rij-bewerkingen kan de prestaties vertragen. SQL is ontworpen om met datasets in één keer te werken.

• Onvoldoende inzicht in indexering: Indexen niet goed gebruiken kan queries langzaam maken, vooral op grote tabellen.

• Transacties onjuist gebruiken: Meerdere gerelateerde bewerkingen uitvoeren zonder ze in een transactie te verpakken kan je data in een inconsistente staat achterlaten als er halverwege iets misgaat.

• Foutafhandeling negeren: Als je TRY...CATCH-blokken overslaat, wordt debuggen lastiger en kunnen scripts in productie stilzwijgend of onvoorspelbaar falen.

Hieronder de best practices die ik aanraad om efficiëntere en betrouwbaardere queries te schrijven:

• Geef de voorkeur aan set-gebaseerde queries: Gebruik waar mogelijk JOIN, GROUP BY en vensterfuncties in plaats van lussen. Dit sluit aan bij hoe SQL Server data efficiënt verwerkt.

• Schrijf leesbare, onderhoudbare code: Gebruik duidelijke naamgevingsconventies, formatteer queries consistent en voeg waar nodig commentaar toe. Dit maakt je code leesbaar, makkelijker te debuggen en om aan samen te werken.

• Test procedures: Test stored procedures en queries altijd met verschillende inputs om te verzekeren dat ze correct werken en randgevallen afhandelen.

Conclusie

T-SQL breidt standaard SQL uit met programmeermogelijkheden en maakt het tot een flexibele en krachtige tool voor automatisering, dataverwerking en analyse. Met functies zoals variabelen, stored procedures en foutafhandeling kun je dynamischere en efficiëntere databaseoplossingen bouwen.

Als volgende stap raad ik je aan onze Associate Data Analyst in SQL-carrièretrack te proberen om een bekwame data-analist te worden. Onze cursus Reporting in SQL helpt je ook om bedreven te worden in het bouwen van complexe rapporten. Tot slot kun je onze SQL Associate Certification behalen om je vaardigheid in het gebruik van SQL om zakelijke problemen op te lossen te tonen en je te onderscheiden van andere professionals.