Табличные параметры SQL Server как пакетный интерфейс

Отдельный вызов базы для каждой позиции заказа тратит сетевые обращения и усложняет обработку отказа. Строка через запятую переносит проблему в разбор и экранирование. Табличный параметр, или TVP, передаёт типизированный набор процедуре за один вызов.

Сначала определите контракт: уникальные продукты, позиции с разрешёнными повторами либо независимые операции. От этого зависят ключ, проверки, сопоставление результата и смысл повторного запроса.

Тип описывает входные данные

Учебный интерфейс принимает положительное количество на продукт и явно показывает неизвестные идентификаторы.

-- Disposable practice database. Send GO-separated batches separately.
CREATE TYPE dbo.RequestLinesDemo AS TABLE (
    ProductId int NOT NULL PRIMARY KEY,
    Quantity int NOT NULL CHECK (Quantity > 0)
);
GO
CREATE TABLE dbo.ProductsTvpDemo(ProductId int PRIMARY KEY, UnitPrice decimal(12,2));
INSERT dbo.ProductsTvpDemo VALUES (1,10.00),(2,15.00);
GO
CREATE PROCEDURE dbo.ValidateLinesDemo @Lines dbo.RequestLinesDemo READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT l.ProductId, l.Quantity, p.UnitPrice,
           CONVERT(bit, CASE WHEN p.ProductId IS NULL THEN 0 ELSE 1 END) AS IsValid
    FROM @Lines AS l
    LEFT JOIN dbo.ProductsTvpDemo AS p ON p.ProductId = l.ProductId
    ORDER BY l.ProductId;
END;
GO

Первичный ключ запрещает повтор ProductId. Если одинаковые товары являются самостоятельными позициями, используйте LineId как ключ. Нельзя объединять настоящие строки заказа только ради удобной обработки множеств.

CHECK требует положительное количество, а NOT NULL запрещает неизвестное значение. Ограничения типа проверяют форму и простые инварианты, но не заменяют проверку текущего каталога, состояния продукта или доступности.

READONLY обязателен для TVP. Процедура читает и соединяет входные строки, но не изменяет параметр как временную таблицу. Для преобразований нужна отдельная рабочая структура с собственной стоимостью.

Пример вызова содержит несуществующий продукт.

DECLARE @Input dbo.RequestLinesDemo;
INSERT @Input VALUES (1,2),(2,3),(99,1);
EXEC dbo.ValidateLinesDemo @Lines = @Input;

Продукты 1 и 2 допустимы, 99 отмечен ошибкой. LEFT JOIN сохраняет неверный вход для отчёта. INNER JOIN скрыл бы его и мог превратить частичный результат в видимость полной успешной проверки.

Согласуем проверку с операцией

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

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

У TVP нет гарантированного порядка. Передавайте отдельную последовательность при необходимости и явно сортируйте выдачу. Новые идентификаторы возвращайте вместе с исходной LineId, а не сопоставляйте по случайному порядку строк.

В .NET задавайте структурированный параметр с правильным schema-qualified именем типа. Вход может предоставлять DataTable или поток строк. Согласуйте типы, precision, scale, длину и допустимость NULL. Совпадение имени параметра не компенсирует неверную структуру.

Измеряем реальные размеры пакетов

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

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

Ограничьте размер запроса и рассмотрите контролируемые пакеты для огромной загрузки. TVP не обязательно быстрее bulk load при любом объёме. Включайте сериализацию, сеть, компиляцию и выполнение в общее сравнение.

Вызывающей стороне нужны права на процедуру и тип, включая REFERENCES там, где это требуется. Разделяйте развёртывание и обычное использование. Изменение пользовательского табличного типа обычно требует согласованной версии зависимых объектов и совместимости старых клиентов.

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

Техническая документация: Microsoft Learn: Table-valued parameters · Microsoft Learn: CREATE TYPE.