Что такое T-SQL? Руководство для начинающих по Transact-SQL для SQL Server

Функции в T-SQL

T‑SQL включает как встроенные функции, так и определяемые пользователем функции (UDF), используемые для преобразования данных.

Встроенные функции

Эти функции уже доступны в SQL Server и могут использоваться непосредственно в запросах.

Строковая функция

Функция, преобразующая строковые типы данных. Например, запрос ниже выбирает имена из таблицы Customers и превращает их в верхний регистр.

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

Функция даты

Эта функция работает с датами. Запрос ниже возвращает текущие системные дату и время.

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

Агрегатная функция

Функции для агрегирования значений в таблице. Например, запрос ниже подсчитывает общее число строк (клиентов) в таблице Customers.

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

Определяемые пользователем функции (UDF)

Вы также можете создавать собственные функции и переиспользовать логику в нескольких запросах. 

Скалярная функция

Скалярная функция возвращает одно значение и полезна, когда нужен единичный вычисленный результат, например форматирование или вычисления. В запросе ниже функция принимает имя и фамилию как входные параметры и возвращает их в виде полного имени.

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

Табличные функции

Табличные функции возвращают таблицу. Например, функция ниже возвращает таблицу всех клиентов из указанной страны.

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

Рекомендую пройти полный карьерный трек SQL Server Developer, который не только научит создавать, обновлять и выполнять хранимые процедуры, но и поможет освоить агрегатные функции, объединение, вставку и удаление таблиц и многое другое.

Обработка ошибок в T-SQL

T‑SQL использует блоки TRY...CATCH для обработки ошибок без остановки всего скрипта. Это важно, поскольку предотвращает неожиданные сбои и позволяет возвращать понятные сообщения для отладки или логирования.

Например, запрос ниже пытается выполнить вычисление, вызывающее ошибку, перехватывает её и выводит читаемое сообщение вместо аварийного завершения.

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;

Транзакции в T-SQL

Транзакция гарантирует, что группа операций либо выполнится вся, либо не выполнится вовсе. Это соответствует принципам ACID, которые обеспечивают согласованность базы данных даже при сбоях, например отключении питания посреди обновления.

В примере ниже транзакция переводит деньги с одного счёта на другой и сохраняет оба изменения вместе.

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

Если что-то пошло не так, вы можете использовать функцию ROLLBACK, чтобы отменить транзакцию и вернуть данные в прежнее состояние.

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

Транзакции в T‑SQL важны, поскольку обеспечивают целостность данных, особенно в критичных системах, например финансовых приложениях, где частичные обновления могут привести к несогласованности.

Вы можете пройти наш карьерный трек Transactions and Error Handling in SQL Server, который охватывает транзакции и обработку ошибок в SQL Server.

Распространённые возможности T-SQL для анализа данных

В T‑SQL есть инструменты, особенно полезные для специалистов по данным. Рассмотрим примеры и то, как применять их в анализе данных.

Оконные функции

Оконные функции выполняют вычисления по строкам без объединения их в один результат. Чаще всего применяются для ранжирования, накопительных итогов и продвинутой аналитики.

В примере ниже запрос присваивает каждой записи клиента уникальный номер строки на основе CustomerID.

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

В запросе:

• ROW_NUMBER(): присваивает уникальные номера строк

• RANK(): присваивает рейтинг с пропусками при равенствах

• OVER(): определяет окно (упорядочивание/разбиение)

Рекомендую ознакомиться с нашей шпаргалкой по оконным функциям SQL, которую можно скачать: это удобный справочник по различным видам оконных функций в SQL. Также посмотрите наш курс Intermediate SQL Server, чтобы узнать больше об использовании T‑SQL в анализе данных, включая оконные функции для суммирования.

Общие табличные выражения (CTE)

CTE (Common Table Expressions) создают временный набор результатов, который можно переиспользовать в рамках запроса. Запрос ниже формирует временный список клиентов из США, а затем извлекает из него данные.

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

-- Query the CTE
SELECT *
FROM CustomerCTE;

Рекурсивные CTE, в свою очередь, — это особый тип CTE, который ссылается на себя в определении, позволяя выполнять повторяющиеся операции. Они идеально подходят для работы с иерархическими данными, например организационными структурами.

Рекомендую пройти курс Hierarchical and Recursive Queries in SQL Server, чтобы научиться писать продвинутые запросы в SQL Server, включая CTE и рекурсивные CTE, на практических примерах.

Временные таблицы

Временные таблицы хранят данные для краткосрочного использования в рамках сессии. Они полезны для промежуточной загрузки данных, упрощения многошаговых преобразований или повышения производительности сложных запросов.

Чтобы продемонстрировать работу временных таблиц в T‑SQL, рассмотрим три блока запросов. В первом создаётся временная таблица, затем в неё загружаются данные о клиентах, после чего выполняется выборка в пределах той же сессии.

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

Важно: имена временных таблиц начинаются с #, и они автоматически удаляются при завершении сессии.

T-SQL и другие диалекты SQL

Все диалекты SQL имеют общую основу, но каждая СУБД добавляет собственный синтаксис и возможности. T‑SQL разработан специально для Microsoft SQL Server, что влияет на его работу и области наилучшего применения.

T-SQL и PostgreSQL (PL/pgSQL)

PostgreSQL использует PL/pgSQL для процедурной логики — схожий с T‑SQL подход, но с иным синтаксисом и сильными сторонами. Ниже таблица с кратким сравнением:

Ознакомьтесь с нашим треком навыков SQL for Database Administrators, чтобы научиться проектировать реляционные базы и оптимизировать запросы для повышения производительности.

T-SQL и MySQL

MySQL также поддерживает процедурный SQL, но в более простом и ограниченном варианте. Таблица ниже сравнивает синтаксис и возможности этих двух диалектов.

Практические сценарии использования T-SQL

T‑SQL широко применяется не только для простых выборок. Ниже примеры, где он был для меня особенно полезен как для специалиста по данным.

Запросы для отчётности

T‑SQL часто используют для формирования отчётов напрямую из базы. Например, можно агрегировать продажи по регионам или ранжировать наиболее успешные товары с помощью оконных функций.

ETL‑процессы (Extract, Transform, Load)

Инженеры данных используют T‑SQL в конвейерах данных для подготовки и перемещения данных. Например, можно извлечь данные из промежуточных таблиц, преобразовать форматы, очистить несоответствия и загрузить их в итоговые отчётные таблицы.

Скрипты очистки данных

Если в вашей базе миллионы строк с разнородными форматами адресов, можно написать скрипт T‑SQL с циклами WHILE или CTE для поиска дублей, заполнения пропусков или стандартизации форматов.

Бэкенд‑логика в приложениях

Многие приложения полагаются на T‑SQL для реализации бизнес‑логики внутри базы данных. Можно использовать хранимые процедуры для операций CRUD, выполнять проверки перед вставкой данных или обрабатывать транзакции для критичных операций.

Типичные ошибки и лучшие практики

Хотя T‑SQL полезен для продвинутых запросов к базам, вы можете столкнуться с распространёнными проблемами. Ниже — типичные ошибки, с которыми я встречался при использовании T‑SQL:

• Чрезмерное использование циклов вместо наборной логики: операции построчно при помощи WHILE могут замедлить производительность. SQL рассчитан на работу с наборами данных сразу.

• Недооценка индексов: неправильное использование индексов может сделать запросы медленными, особенно на больших таблицах.

• Неправильное использование транзакций: выполнение нескольких связанных операций без обёртки в транзакцию может оставить данные в несогласованном состоянии при сбое посередине.

• Игнорирование обработки ошибок: отсутствие блоков TRY...CATCH усложняет отладку и может привести к тихим сбоям или непредсказуемой работе скриптов в продакшене.

Ниже — лучшие практики, которые я рекомендую для написания более эффективных и надёжных запросов:

• Предпочитайте наборные запросы: используйте JOIN, GROUP BY и оконные функции вместо циклов, когда это возможно. Это соответствует тому, как SQL Server оптимально обрабатывает данные.

• Пишите читаемый и поддерживаемый код: используйте понятные соглашения об именовании, единый стиль форматирования и добавляйте комментарии при необходимости. Так код будет понятнее, его проще отлаживать и в нём легче сотрудничать.

• Тестируйте процедуры: всегда проверяйте хранимые процедуры и запросы с разными входными данными, чтобы убедиться в корректной работе и обработке крайних случаев.

Заключение

T‑SQL расширяет стандартный SQL за счёт программных возможностей, делая его гибким и мощным инструментом для автоматизации, обработки и анализа данных. Благодаря таким функциям, как переменные, хранимые процедуры и обработка ошибок, вы можете строить более динамичные и эффективные решения на базе данных.

В качестве следующего шага рекомендую пройти карьерный трек Associate Data Analyst по SQL, чтобы стать уверенным аналитиком данных. Наш курс Reporting in SQL также поможет вам овладеть построением сложных отчётов. И, наконец, получите нашу SQL Associate Certification, чтобы подтвердить мастерство в применении SQL для решения бизнес‑задач и выгодно выделиться среди профессионалов.