Основы T-SQL для разработчиков, пришедших с других баз

Если вы знаете SQL по PostgreSQL или MySQL, вы уже знаете большую часть T-SQL. SELECT, соединения, GROUP BY, оконные функции и подзапросы работают так, как вы ожидаете. Разработчиков подводит короткий список различий: нет LIMIT (используйте TOP или OFFSET ... FETCH), нет RETURNING (используйте OUTPUT), нет boolean (используйте bit), нет ON CONFLICT (аккуратно используйте MERGE или обновление с последующей вставкой), + склеивает строки и делает весь результат NULL, если NULL хоть одна часть, а читающие при уровне изоляции по умолчанию могут блокировать пишущих. Это руководство проходит по этим различиям с рабочими примерами для SQL Server 2022, чтобы первая неделя на SQL Server ушла на ваше приложение, а не на сообщения об ошибках.

Ограничение и постраничный вывод результатов#

LIMIT нет. Его заменяют два варианта - TOP и OFFSET ... FETCH:

-- The ten newest ordersSELECT TOP (10) OrderId, CustomerId, CreatedAtFROM dbo.OrdersORDER BY CreatedAt DESC;-- Page 3 at 20 rows per pageSELECT OrderId, CustomerId, CreatedAtFROM dbo.OrdersORDER BY CreatedAt DESC, OrderId DESCOFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

TOP принимает в скобках переменную или параметр, а TOP (10) WITH TIES включает дополнительные строки, совпадающие с десятой по столбцам ORDER BY. TOP без ORDER BY допустим и возвращает те строки, которые быстрее всего найти, а это редко то, что вы имели в виду.

OFFSET ... FETCH требует ORDER BY, и порядок должен быть уникальным - добавьте первичный ключ для разрешения равенства, как выше, иначе строки с одинаковой меткой времени могут оказаться на двух страницах или ни на одной. Большие смещения здесь так же дороги, как в любой базе, потому что SQL Server читает и отбрасывает каждую пропущенную строку. Для глубокого листания используйте пагинацию по ключу: запоминайте значения сортировки последней строки и запрашивайте то, что идёт после них:

SELECT TOP (20) OrderId, CustomerId, CreatedAtFROM dbo.OrdersWHERE CreatedAt < @lastCreatedAt   OR (CreatedAt = @lastCreatedAt AND OrderId < @lastOrderId)ORDER BY CreatedAt DESC, OrderId DESC;

С индексом на (CreatedAt, OrderId) каждая страница стоит одинаково, как бы глубоко она ни находилась. Почему важен порядок в индексе, объясняет статья индексы и планы выполнения SQL Server.

Столбцы identity и получение нового идентификатора#

Аналог AUTO_INCREMENT и SERIAL - свойство IDENTITY:

CREATE TABLE dbo.Customers (    CustomerId  int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,    Email       nvarchar(320) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,    IsActive    bit NOT NULL CONSTRAINT DF_Customers_IsActive DEFAULT (1),    CreatedAt   datetime2(3) NOT NULL CONSTRAINT DF_Customers_CreatedAt DEFAULT (SYSUTCDATETIME()));

Вместо RETURNING в T-SQL есть OUTPUT, который возвращает столбцы вставленных, обновлённых или удалённых строк:

INSERT INTO dbo.Customers (Email)OUTPUT INSERTED.CustomerId, INSERTED.CreatedAtVALUES (N'ana@example.com');

Он работает и с UPDATE (где есть и DELETED со старыми значениями, и INSERTED с новыми), и с DELETE, и с MERGE. Одно ограничение: если у таблицы есть включённый триггер, OUTPUT должен писать INTO табличную переменную, а не возвращать строки напрямую.

Более старый способ получить идентификатор - SCOPE_IDENTITY(), который возвращает последнее значение identity, сгенерированное в текущей области. Избегайте @@IDENTITY, который возвращает последнее значение, сгенерированное где угодно в сеансе, - в том числе внутри триггера, вставившего строку в таблицу аудита, - и IDENT_CURRENT('dbo.Customers'), который возвращает последнее значение для таблицы по всем сеансам и неверен при конкурентной работе.

В значениях identity бывают пропуски, и это нормально. Откаченная вставка расходует значение, а SQL Server кэширует значения identity блоками, так что неожиданный перезапуск может заставить последовательность прыгнуть - до 1000 для int. Если пропуски действительно важны (а так почти никогда не должно быть; обычный пример - номера счетов), генерируйте эти номера сами внутри транзакции, а не полагайтесь на identity. Чтобы вставить явные значения в столбец identity при миграции данных, оберните вставку в SET IDENTITY_INSERT dbo.Customers ON; и OFF.

Для значений, общих для нескольких таблиц или нужных до вставки, объект SEQUENCE работает как в PostgreSQL: CREATE SEQUENCE dbo.OrderNumbers START WITH 1000;, затем NEXT VALUE FOR dbo.OrderNumbers.

Upsert: MERGE и более безопасная альтернатива#

В T-SQL нет ON CONFLICT или ON DUPLICATE KEY UPDATE. Есть MERGE:

MERGE dbo.PageHits WITH (HOLDLOCK) AS targetUSING (SELECT @pageId AS PageId) AS source    ON target.PageId = source.PageIdWHEN MATCHED THEN    UPDATE SET Hits = target.Hits + 1WHEN NOT MATCHED THEN    INSERT (PageId, Hits) VALUES (source.PageId, 1);

Две вещи в MERGE не обсуждаются. Он должен заканчиваться точкой с запятой, иначе будет синтаксическая ошибка. И без HOLDLOCK (сериализуемой блокировки цели) два сеанса могут оба увидеть «not matched» для одного ключа и оба попытаться вставить, так что под нагрузкой один упадёт с ошибкой дублирующегося ключа. Кроме того, за годы у MERGE набрался длинный список ошибок, в основном в сочетании с триггерами, фильтрованными индексами и индексированными представлениями. Многие опытные разработчики SQL Server избегают его для однострочных upsert и пишут явную форму:

SET XACT_ABORT ON;BEGIN TRANSACTION;UPDATE dbo.PageHits WITH (UPDLOCK, SERIALIZABLE)SET Hits = Hits + 1WHERE PageId = @pageId;IF @@ROWCOUNT = 0    INSERT dbo.PageHits (PageId, Hits) VALUES (@pageId, 1);COMMIT TRANSACTION;

Подсказки UPDLOCK, SERIALIZABLE блокируют диапазон ключей, даже если строки ещё нет, так что второй сеанс ждёт, а не соревнуется. Для массовых upsert множества строк из промежуточной таблицы MERGE разумен и намного короче; протестируйте его и оставьте HOLDLOCK.

Транзакции и обработка ошибок#

Как и в других движках, каждое выражение выполняется в собственной транзакции, если вы не открыли её сами. Отличается обработка ошибок. По умолчанию многие ошибки времени выполнения в T-SQL прерывают только текущее выражение, а не транзакцию: пакет продолжает выполняться, и последующий COMMIT фиксирует половину работы. SET XACT_ABORT ON заставляет любую ошибку времени выполнения откатывать всю транзакцию, и это должно быть первой строкой каждой процедуры или скрипта, открывающих транзакцию:

CREATE OR ALTER PROCEDURE dbo.TransferCredit    @fromId int, @toId int, @amount decimal(12,2)ASBEGIN    SET NOCOUNT ON;    SET XACT_ABORT ON;    BEGIN TRY        BEGIN TRANSACTION;        UPDATE dbo.Accounts SET Balance = Balance - @amount WHERE AccountId = @fromId;        UPDATE dbo.Accounts SET Balance = Balance + @amount WHERE AccountId = @toId;        COMMIT TRANSACTION;    END TRY    BEGIN CATCH        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;        THROW;    END CATCH;END;

THROW без аргументов повторно выбрасывает исходную ошибку с её номером и сообщением, так что приложение видит, что на самом деле пошло не так. SET NOCOUNT ON подавляет сообщения «rows affected», которые некоторые драйверы иначе принимают за наборы результатов.

Изоляция - второй сюрприз для разработчиков с PostgreSQL. Уровень по умолчанию в SQL Server, READ COMMITTED, использует блокировки, так что долгое обновление блокирует читающих те же строки, пока не зафиксируется. Включение read committed snapshot заставляет читающих видеть последнюю зафиксированную версию, как это происходит в PostgreSQL:

ALTER DATABASE [appdb] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Он хранит версии строк в tempdb, что стоит немного ввода-вывода и места, а для большинства веб-приложений убирает целый класс блокировок. Не хватайтесь вместо этого за WITH (NOLOCK): он читает незафиксированные данные и при разбиении страниц может вернуть строки дважды или пропустить их.

Типы, которые работают иначе#

Предпочитайте datetime2 старому datetime, который округляет с шагом около 3 миллисекунд и имеет меньший диапазон. Избегайте money, у которого неожиданное округление при делении; используйте decimal. И учтите, что rowversion, он же timestamp, - автоматически меняющееся двоичное значение для оптимистичной конкуренции: полезно, но ко времени отношения не имеет.

Что касается текста, nvarchar хранит Unicode, а varchar - нет, если у столбца нет collation UTF-8, и строковым литералам нужен префикс N, чтобы оставаться Unicode. Это как следует разбирает статья collation и Unicode в SQL Server, потому что это самый частый источник испорченного текста.

Строки, NULL и даты#

Конкатенация строк выполняется через +, и NULL + 'anything' - это NULL. CONCAT считает NULL пустой строкой, а CONCAT_WS добавляет разделитель и пропускает NULL:

SELECT FirstName + ' ' + LastName              AS may_be_null,       CONCAT(FirstName, ' ', LastName)         AS never_null,       CONCAT_WS(', ', Street, City, Postcode)  AS addressFROM dbo.Customers;

Аналоги распространённых функций из других движков:

Некоторые из них появились только в SQL Server 2022, а некоторые, например GENERATE_SERIES, требуют уровня совместимости базы 160. База, восстановленная со старого сервера, сохраняет старый уровень, пока вы его не повысите; когда это делать, разбирает статья перенос базы на хостинг SQL Server.

ISNULL и COALESCE различаются так, что это может укусить: ISNULL возвращает тип первого аргумента, поэтому ISNULL(@shortVarchar, 'a much longer default') обрезает значение по умолчанию. COALESCE следует обычному приоритету типов. А DATEDIFF считает пересечённые границы, а не прошедшее время: DATEDIFF(year, '2025-12-31', '2026-01-01') равно 1.

GETDATE() возвращает местное время сервера как datetime. На сервере, которым вы не управляете, это может быть не ваш часовой пояс. Храните UTC через SYSUTCDATETIME() и преобразуйте для отображения, через AT TIME ZONE, если это обязательно делать в SQL.

JSON, CTE и мышление множествами#

В SQL Server 2022 нет нативного типа столбца для JSON; JSON живёт в nvarchar(max), и с ним работают через функции. Это ограничивает меньше, чем кажется. Ограничение CHECK не пускает некорректные документы, JSON_VALUE читает скалярное значение, JSON_QUERY - объект или массив, а OPENJSON превращает документ в строки, которые можно соединять:

CREATE TABLE dbo.Events (    EventId  bigint IDENTITY PRIMARY KEY,    Payload  nvarchar(max) NOT NULL CONSTRAINT CK_Events_Json CHECK (ISJSON(Payload) = 1),    UserId   AS CAST(JSON_VALUE(Payload, '$.userId') AS int)   -- computed column);CREATE INDEX IX_Events_UserId ON dbo.Events (UserId);SELECT e.EventId, t.[value] AS TagFROM dbo.Events AS eCROSS APPLY OPENJSON(e.Payload, '$.tags') AS tWHERE e.UserId = 42;

Вычисляемый столбец - это приём, который делает запросы к JSON быстрыми: SQL Server не умеет индексировать содержимое документа напрямую, но может проиндексировать вычисляемый столбец, извлекающий значение, и оптимизатор использует этот индекс для запросов, фильтрующих по тому же выражению. В обратную сторону FOR JSON PATH в конце SELECT возвращает результат в виде документа JSON, а псевдонимы столбцов с точками, например [customer.email], дают вложенные объекты. В SQL Server 2022 также появились JSON_OBJECT и JSON_ARRAY для построения документов прямо в запросе и JSON_PATH_EXISTS для проверки наличия пути.

Обобщённые табличные выражения работают как в других движках, с двумя деталями. Выражение перед WITH должно заканчиваться точкой с запятой, поэтому T-SQL часто пишут как ;WITH. А рекурсивные CTE - они пишутся без ключевого слова RECURSIVE, просто ссылкой на самих себя - по умолчанию останавливаются с ошибкой после 100 уровней; добавьте OPTION (MAXRECURSION 1000) во внешний запрос, чтобы пойти глубже, или 0, чтобы снять ограничение, если вы уверены, что рекурсия закончится.

Более общая привычка, которую стоит принести в T-SQL, - мышление множествами. Процедурный код с курсорами и циклами WHILE, обрабатывающий по одной строке за раз, - классическая проблема производительности SQL Server, потому что каждая итерация - отдельное выражение со своими накладными расходами и блокировками. UPDATE с соединением или INSERT ... SELECT выполняют ту же работу за один проход. Циклы - правильный инструмент ровно в одном распространённом случае: когда огромное изменение намеренно разбивают на пакеты, чтобы оно не держало блокировки и не заполняло журнал транзакций, как описывает статья модели восстановления SQL Server и рост журнала.

Идентификаторы, пакеты и привычки DDL#

Идентификаторы, совпадающие с ключевыми словами или содержащие пробелы, заключаются в квадратные скобки: [Order], [User Name]. Двойные кавычки тоже работают, когда включён QUOTED_IDENTIFIER, а он включён у всех современных драйверов; обратные апострофы не работают вообще. Объекты живут в схемах, по умолчанию в dbo, и хорошая практика - всегда писать схему, dbo.Orders: это избавляет от шага разрешения имени и сюрпризов с кэшем планов.

Скрипты делятся на пакеты через GO - клиентский разделитель в sqlcmd и SSMS, а не T-SQL. CREATE PROCEDURE, CREATE VIEW и CREATE FUNCTION должны быть первым выражением в пакете, а локальные переменные не переживают GO. Как запускать такие скрипты из конвейера, разбирает статья sqlcmd и bcp.

Точка с запятой необязательна в большинстве выражений, но в нескольких местах обязательна: MERGE должен ею заканчиваться, а выражение перед обобщённым табличным выражением WITH или перед THROW должно быть завершено. Если ставить точку с запятой после каждого выражения, вопрос отпадает целиком.

Для повторяемого DDL:

CREATE OR ALTER VIEW dbo.ActiveCustomers ASSELECT CustomerId, Email FROM dbo.Customers WHERE IsActive = 1;GODROP TABLE IF EXISTS dbo.ImportStaging;IF OBJECT_ID(N'dbo.AuditLog', N'U') IS NULL    CREATE TABLE dbo.AuditLog (AuditId bigint IDENTITY PRIMARY KEY, Message nvarchar(4000));

CREATE OR ALTER работает для представлений, процедур, функций и триггеров, но не для таблиц, а CREATE TABLE IF NOT EXISTS не существует, отсюда проверка через OBJECT_ID. Столбец добавляется так: ALTER TABLE dbo.Orders ADD Notes nvarchar(500) NULL;, без ключевого слова COLUMN. Временные таблицы начинаются с # и исчезают по окончании сеанса; ## создаёт глобальную, видимую всем сеансам, а это редко то, что нужно.

Параметры и динамический SQL#

Всё сказанное выше предполагает параметры, и правило то же, что и везде: никогда не собирайте SQL конкатенацией пользовательского ввода. Когда динамический SQL внутри T-SQL действительно нужен - скажем, столбец сортировки выбирается во время выполнения, - используйте sp_executesql с параметрами для значений и QUOTENAME для идентификаторов:

DECLARE @sql nvarchar(max) =    N'SELECT TOP (@n) OrderId, CreatedAt FROM dbo.Orders ORDER BY '    + QUOTENAME(@sortColumn) + N' DESC;';EXEC sp_executesql @sql, N'@n int', @n = @pageSize;

QUOTENAME заключает имя в скобки и экранирует любую закрывающую скобку внутри него, так что злонамеренное имя столбца не сможет вырваться наружу. Дополнительно сверяйте его со списком разрешённых столбцов. Параметризованные вызовы sp_executesql к тому же позволяют SQL Server повторно использовать один план для всех значений, чего склеенный SQL не позволяет. Как ограничить возможности логина приложения на случай, если что-то всё же проскочит, разбирает статья логины, пользователи и роли SQL Server.

FAQ#

Как ограничить число строк в SQL Server без LIMIT?

Используйте SELECT TOP (n) ... ORDER BY ... для первых n строк или ORDER BY ... OFFSET x ROWS FETCH NEXT n ROWS ONLY для постраничного вывода. OFFSET требует ORDER BY, который должен включать уникальный столбец, чтобы страницы были стабильными.

Что в SQL Server заменяет RETURNING?

Конструкция OUTPUT. INSERT ... OUTPUT INSERTED.Id VALUES (...) возвращает новый identity, и это работает также с UPDATE, DELETE и MERGE, где DELETED даёт старые значения. В таблице с триггерами выводите результат в табличную переменную.

Есть ли в SQL Server логический тип?

Нет. Используйте bit, который хранит 0, 1 или NULL, и сравнивайте через = 1 или = 0. Большинство ORM и драйверов автоматически сопоставляют свой логический тип с bit.

Почему мои чтения блокируются, пока другой сеанс обновляет данные?

Уровень изоляции по умолчанию использует разделяемые блокировки, которые ждут, пока пишущие зафиксируют изменения. Включите для базы READ_COMMITTED_SNAPSHOT, чтобы читающие видели последнюю зафиксированную версию. Именно этого ожидает большинство разработчиков, пришедших с PostgreSQL.

Безопасно ли использовать MERGE для upsert?

Он работает, но для безопасности при конкурентной работе ему нужен HOLDLOCK, завершающая точка с запятой и тестирование, если задействованы триггеры или фильтрованные индексы. Для однострочных upsert UPDATE с UPDLOCK, SERIALIZABLE и последующим условным INSERT проще и предсказуемее.