Czym jest T-SQL? Przewodnik dla początkujących po Transact-SQL dla SQL Server

Funkcje w T-SQL

T‑SQL zawiera zarówno funkcje wbudowane, jak i funkcje definiowane przez użytkownika (UDF) służące do przekształcania danych.

Funkcje wbudowane

Funkcje te są dostępne w SQL Server i można ich używać bezpośrednio w zapytaniach.

Funkcja tekstowa

To funkcja przekształcająca typy tekstowe. Na przykład poniższe zapytanie pobiera wszystkie imiona z tabeli Customers i zamienia je na wielkie litery.

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

Funkcja daty

Ta funkcja przekształca wartości daty. Poniższe zapytanie zwraca bieżącą datę i czas systemowy.

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

Funkcja agregująca

Są to funkcje służące do agregacji wartości w tabeli. Na przykład poniższe zapytanie zlicza łączną liczbę wierszy (klientów) w tabeli Customers.

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

Funkcje definiowane przez użytkownika (UDF)

Można też tworzyć własne funkcje, aby wielokrotnie używać logiki w różnych zapytaniach. 

Funkcja skalarna

Funkcja skalarna zwraca pojedynczą wartość i jest przydatna, gdy potrzebna jest pojedyncza wartość wyliczona, np. do formatowania lub obliczeń. W poniższym zapytaniu funkcja przyjmuje imię i nazwisko jako wejście i zwraca je jako jedno pełne imię i nazwisko.

-- 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;

Funkcje zwracające tabele

Funkcje zwracające tabele zwracają tabelę. Na przykład poniższa funkcja zwraca tabelę wszystkich klientów należących do określonego kraju.

-- 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
);

Polecam cały ścieżkę kariery SQL Server Developer, która nie tylko wyposaży Państwa w umiejętności tworzenia, aktualizowania i wykonywania procedur składowanych, ale także pomoże w pracy z funkcjami agregującymi, łączeniem, wstawianiem i usuwaniem tabel oraz wieloma innymi zagadnieniami.

Obsługa błędów w T-SQL

T‑SQL używa bloków TRY...CATCH do obsługi błędów bez zatrzymywania całego skryptu. To ważne, ponieważ zapobiega nieoczekiwanemu wysypywaniu się skryptów i pozwala zwracać znaczące komunikaty do debugowania lub logowania.

Na przykład poniższe zapytanie próbuje wykonać obliczenie powodujące błąd, przechwytuje go i wypisuje czytelną wiadomość zamiast przerywać działanie.

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;

Transakcje w T-SQL

Transakcja zapewnia, że grupa operacji albo powiedzie się w całości, albo w całości się nie powiedzie. Zgodne jest to z zasadą ACID, która gwarantuje spójność bazy danych nawet w razie awarii zasilania w trakcie aktualizacji.

W poniższym przykładzie transakcja przenosi środki z jednego konta na drugie i zapisuje obie zmiany razem.

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

Jeśli coś pójdzie nie tak, można użyć funkcji ROLLBACK, aby anulować transakcję i przywrócić dane do poprzedniego stanu.

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

Transakcje w T‑SQL są ważne, ponieważ zapewniają integralność danych, zwłaszcza w systemach krytycznych, takich jak aplikacje finansowe, gdzie częściowe aktualizacje mogłyby prowadzić do niespójności.

Mogą Państwo wypróbować naszą ścieżkę kariery Transakcje i obsługa błędów w SQL Server, która obejmuje transakcje i obsługę błędów w SQL Server.

Typowe funkcje T-SQL do analizy danych

T‑SQL zawiera narzędzia szczególnie przydatne dla specjalistów danych. Zobaczmy kilka przykładów i ich zastosowanie w analizie danych.

Funkcje okna

Funkcje okna wykonują obliczenia w poprzek wierszy bez grupowania ich do jednego wyniku. Najczęściej służą do rankingów, sum narastających i zaawansowanej analityki.

W poniższym przykładzie zapytanie przypisuje unikalny numer wiersza każdemu klientowi na podstawie jego CustomerID.

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

W zapytaniu:

• ROW_NUMBER(): Przypisuje unikalne numery wierszy

• RANK(): Przypisuje rangi z lukami dla remisów

• OVER(): Definiuje okno (porządkowanie/partycyjność)

Zachęcam do sprawdzenia naszej ściągawki z funkcji okna w SQL do pobrania — to poręczna pomoc dotycząca różnych typów funkcji okna w SQL. Proszę też zajrzeć na kurs SQL Server dla średnio zaawansowanych, aby dowiedzieć się więcej o używaniu T‑SQL w analizie danych, w tym funkcji okna do podsumowań.

Wspólne wyrażenia tablicowe (CTE)

CTE (Common Table Expressions) tworzą tymczasowy zestaw wyników, który można ponownie wykorzystać w zapytaniu. Poniższe zapytanie tworzy tymczasową listę klientów z USA, a następnie pobiera z niej dane.

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

-- Query the CTE
SELECT *
FROM CustomerCTE;

Z kolei rekursywne CTE to szczególny typ CTE, który odwołuje się do samego siebie w swojej definicji, umożliwiając wykonywanie powtarzalnych operacji. Dzięki temu idealnie nadają się do pracy z danymi hierarchicznymi, jak struktury organizacyjne.

Polecam kurs Zapytania hierarchiczne i rekursywne w SQL Server, aby nauczyć się pisać zaawansowane zapytania w SQL Server, w tym z użyciem CTE i rekursywnych CTE, na praktycznych przykładach.

Tabele tymczasowe

Tabele tymczasowe przechowują dane do krótkotrwałego użytku w trakcie sesji. Są przydatne do etapowania danych, upraszczania wieloetapowych transformacji lub poprawy wydajności w złożonych zapytaniach.

Aby pokazać, jak działają tabele tymczasowe w T‑SQL, proszę rozważyć poniższe trzy bloki zapytań. Pierwszy tworzy tabelę tymczasową, następnie wypełnia ją danymi klientów, a potem odczytuje ją w tej samej sesji.

-- 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;

Ważne: tabele tymczasowe mają prefiks # i są automatycznie usuwane po zakończeniu sesji.

T-SQL a inne dialekty SQL

Wszystkie dialekty SQL mają wspólną podstawę, ale każdy system bazodanowy dodaje własną składnię i funkcje. T‑SQL jest zaprojektowany specjalnie dla Microsoft SQL Server, co wpływa na jego działanie i najlepsze zastosowania.

T-SQL a PostgreSQL (PL/pgSQL)

PostgreSQL używa PL/pgSQL do logiki proceduralnej — podobnie jak T‑SQL, lecz z inną składnią i mocnymi stronami. Poniższa tabela podsumowuje te różnice:

Proszę sprawdzić naszą ścieżkę umiejętności SQL for Database Administrators, aby nauczyć się projektować relacyjne bazy danych i strukturyzować zapytania w celu poprawy wydajności.

T-SQL a MySQL

MySQL również obsługuje SQL proceduralny, ale w prostszym i bardziej ograniczonym ujęciu. Poniższa tabela porównuje różnice w składni i funkcjach tych dwóch dialektów.

Rzeczywiste zastosowania T-SQL

T‑SQL jest szeroko stosowany poza prostymi zapytaniami. Oto przykłady, w których był dla mnie użyteczny jako dla specjalisty ds. danych.

Zapytania raportowe

T‑SQL jest powszechnie używany do generowania raportów bezpośrednio z bazy danych. Na przykład można agregować sprzedaż według regionów lub porządkować najlepiej sprzedające się produkty za pomocą funkcji okna.

Procesy ETL (Extract, Transform, Load)

T‑SQL jest używany w potokach danych przez inżynierów danych do przygotowywania i przenoszenia danych. Na przykład można wyciągać dane z tabel etapowania, przekształcać formaty, czyścić niespójności i ładować do docelowych tabel raportowych.

Skrypty czyszczenia danych

Jeśli w bazie są miliony wierszy z niespójnymi formatami adresów, można użyć skryptu T‑SQL z pętlami WHILE lub CTE do identyfikacji duplikatów, uzupełniania braków czy ujednolicania formatów.

Logika zaplecza w aplikacjach

Wiele aplikacji polega na T‑SQL do obsługi logiki biznesowej w bazie danych. Można używać procedur składowanych do operacji CRUD, wykonywać walidacje przed wstawieniem danych lub korzystać z obsługi transakcji przy operacjach krytycznych.

Najczęstsze błędy i dobre praktyki

Choć T‑SQL jest użyteczny w zaawansowanych zapytaniach do baz danych, mogą pojawić się typowe problemy. Oto kilka błędów, na które natrafiłem podczas używania T‑SQL:

• Nadmierne używanie pętli zamiast logiki zbiorowej: Stosowanie pętli, takich jak WHILE, do operacji wiersz‑po‑wierszu może spowalniać wydajność. SQL jest zaprojektowany do pracy na zbiorach danych jednocześnie.

• Słaba świadomość indeksowania: Nieprawidłowe użycie indeksów może spowolnić zapytania, zwłaszcza na dużych tabelach.

• Nieprawidłowe używanie transakcji: Uruchamianie wielu powiązanych operacji bez objęcia ich transakcją może pozostawić dane w niespójnym stanie, jeśli coś się nie powiedzie w połowie.

• Ignorowanie obsługi błędów: Pomijanie bloków TRY...CATCH utrudnia debugowanie i może powodować ciche lub nieprzewidywalne awarie skryptów w produkcji.

Poniżej znajdują się dobre praktyki, które polecam, aby pisać wydajniejsze i bardziej niezawodne zapytania:

• Preferuj zapytania zbiorowe: Używaj JOIN, GROUP BY i funkcji okna zamiast pętli, gdy to możliwe. To zgodne z tym, jak SQL Server efektywnie przetwarza dane.

• Pisz czytelny, łatwy w utrzymaniu kod: Stosuj jasne konwencje nazewnicze, konsekwentnie formatuj zapytania i dodawaj komentarze tam, gdzie to potrzebne. Ułatwia to czytanie kodu, debugowanie i współpracę.

• Testuj procedury: Zawsze testuj procedury składowane i zapytania z różnymi danymi wejściowymi, aby upewnić się, że działają poprawnie i obsługują przypadki brzegowe.

Podsumowanie

T‑SQL rozszerza standardowy SQL o możliwości programistyczne, czyniąc go elastycznym i potężnym narzędziem do automatyzacji, przetwarzania i analizy danych. Dzięki funkcjom takim jak zmienne, procedury składowane i obsługa błędów można budować bardziej dynamiczne i wydajne rozwiązania bazodanowe.

Jako kolejny krok polecam naszą ścieżkę kariery Associate Data Analyst w SQL, aby stać się biegłym analitykiem danych. Nasz kurs Raportowanie w SQL pomoże również opanować budowanie złożonych raportów. Na koniec warto uzyskać naszą Certyfikację SQL Associate, aby zaprezentować biegłość w używaniu SQL do rozwiązywania problemów biznesowych i wyróżnić się na tle innych specjalistów.