Paramètres table SQL Server: définir une interface par lots

Un appel par ligne de commande multiplie les échanges réseau et complique les échecs. Une chaîne séparée par des virgules déplace le problème vers l'analyse et l'échappement. Un paramètre table, ou TVP, permet d'envoyer un ensemble typé à une procédure en un appel.

Définissez le contrat avant le type: produits uniques, lignes ordonnées avec produits répétés, ou opérations indépendantes? Ce choix détermine clés, validation, corrélation et reprise.

Décrire l'entrée par un type

L'interface de pratique accepte une quantité positive par produit et signale les identifiants inconnus.

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

La clé primaire refuse les ProductId répétés. Si plusieurs lignes d'un même produit sont légitimes, utilisez LineId comme clé et conservez ProductId comme attribut. Ne dédupliquez pas des lignes réelles uniquement pour faciliter l'interface.

CHECK impose une quantité positive et NOT NULL exclut une quantité inconnue. Ces contraintes définissent forme et invariants simples. Elles ne remplacent pas les contrôles sur le catalogue actuel, le statut ou la disponibilité.

READONLY est obligatoire pour le paramètre TVP. La procédure peut lire et joindre les lignes, mais pas modifier le paramètre comme une table temporaire. Pour transformer les données, créez une table de travail distincte et comptez son coût.

L'appel contient volontairement un produit absent.

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

Les produits 1 et 2 sont valides; 99 est signalé. LEFT JOIN conserve l'entrée incorrecte pour la restituer. Un INNER JOIN la ferait disparaître et pourrait présenter un résultat partiel comme une validation complète.

Relier validation et opération réelle

Cette procédure illustre une vérification, pas une transaction de commande. Décidez si une ligne invalide doit rejeter toute la demande ou si une acceptation partielle est autorisée. Chaque résultat doit être corrélé à sa ligne et le statut global rester clair.

Une vérification réussie ne fige pas les produits pour une écriture ultérieure. Prix, disponibilité et droits peuvent changer entre appels. Revérifiez les conditions nécessaires dans la véritable transaction ou utilisez un contrat de version adapté.

Un TVP ne garantit aucun ordre. Ajoutez une séquence explicite si nécessaire et un ORDER BY dans le résultat. Pour les identifiants créés, retournez la LineId source avec la clé cible au lieu de compter sur leur ordre d'apparition.

Dans un client .NET, utilisez un paramètre structuré avec le bon nom de type qualifié par schéma. DataTable ou un flux de lignes peuvent fournir les données. Faites correspondre types, précision, échelle, longueurs et nullabilité. Un nom correct ne suffit pas si les colonnes sont incompatibles.

Mesurer les tailles réellement rencontrées

SQL Server ne maintient pas de statistiques de colonnes sur les TVP. Cinq lignes et cinquante mille peuvent pourtant demander des plans différents. Une clé primaire fournit unicité et accès, mais pas d'histogramme de distribution.

Pour de grandes entrées variables, une copie vers une table temporaire indexée avec statistiques peut aider les jointures. Elle ajoute copie et travail tempdb. Mesurez l'appel complet, pas seulement le SELECT final. Recompiler peut aider certaines différences de cardinalité sans créer les statistiques manquantes.

Fixez une taille maximale et découpez les très grands imports si nécessaire. Les TVP ne sont pas plus rapides que le chargement bulk pour toutes les tailles. Comparez sérialisation, réseau, compilation et exécution ensemble.

Le consommateur a également besoin des droits de procédure et de type, dont REFERENCES lorsque requis. Séparez droits de déploiement et usage normal. Faire évoluer un type table utilisateur demande généralement un déploiement versionné des dépendances et une stratégie pour les anciens clients.

Testez entrée vide, doublons, quantités invalides, produits inconnus, taille maximale et reprise après réponse incertaine. Une interface TVP utile économise les échanges tout en préservant le comportement de chaque ligne et de la demande entière.

Références techniques: Microsoft Learn: Table-valued parameters · Microsoft Learn: CREATE TYPE.