Utiliser les paramètres table (Moteur de base de données)

  • Article

S'applique à : SQL Server Azure SQL Database Not supported. Azure Synapse Analytics Not supported. Analytics Platform System (PDW)

Les paramètres table sont déclarés en utilisant des types de table définis par l'utilisateur. Vous pouvez utiliser des paramètres à valeur de table pour envoyer plusieurs lignes de données à une instruction Transact-SQL ou à une routine, telle qu'une procédure stockée ou une fonction, sans créer de table temporaire ou de nombreux paramètres.

Les paramètres évalués par table sont semblables aux tableaux de paramètres dans OLE DB et ODBC, mais offrent plus de flexibilité et une intégration plus étroite avec Transact-SQL. Ils ont également l'avantage de pouvoir participer aux opérations basées sur des ensembles.

Transact-SQL transmet les paramètres de la table aux routines par référence pour éviter de faire une copie des données d'entrée. Vous pouvez créer et exécuter des routines Transact-SQL avec des paramètres de type table, et les appeler à partir de code Transact-SQL, de clients gérés et natifs dans tout langage géré.

Dans cette rubrique :

Avantages

Restrictions

Paramètres table et opérations BULK INSERT

Exemple

Avantages

Un paramètre évalué par une table est lié à la procédure stockée, à la fonction ou au texte dynamique Transact-SQL, exactement comme les autres paramètres. De même, une variable de type de table a une portée semblable à toute autre variable locale créée à l'aide d'une instruction DECLARE. Vous pouvez déclarer des variables à valeur de table dans des instructions dynamiques Transact-SQL et transmettre ces variables comme paramètres à valeur de table à des procédures et fonctions stockées.

Les paramètres table offrent davantage de souplesse et dans certains cas de meilleures performances que les tables temporaires ou autres méthodes de passage d'une liste de paramètres. Les paramètres table offrent les avantages suivants :

  • Pas d'acquisition de verrous pour le remplissage initial de données à partir d'un client.
  • Fournissent un modèle de programmation simple.
  • Vous permettent d'inclure une logique métier complexe dans une routine unique.
  • Réduisent les boucles au serveur.
  • Peuvent avoir une structure de table de cardinalité différente.
  • Sont fortement typées.
  • Permettent au client de spécifier un ordre de tri et des clés uniques.
  • Sont mis en cache comme une table temporaire en cas de utilisation dans une procédure stockée. À partir de SQL Server 2012 (11.x), les paramètres de valeur de table sont également mis en cache pour les requêtes paramétrées.

Autorisations

Pour créer une instance d'un type de table défini par l'utilisateur ou appeler une procédure stockée avec un paramètre Table, l'utilisateur doit disposer des autorisations EXECUTE et REFERENCES sur le type, ou sur le schéma ou encore la base de données contenant le type.

Restrictions

Les paramètres table ont les restrictions suivantes :

  • Le serveur SQL ne maintient pas de statistiques sur les colonnes des paramètres évalués par la table.
  • Les paramètres évalués par table doivent être passés comme paramètres d'entrée READONLY aux routines Transact-SQL. Vous ne pouvez pas effectuer d'opérations DML telles que UPDATE, DELETE ou INSERT sur un paramètre table dans le corps d'une routine.
  • Vous ne pouvez pas utiliser de paramètre table comme cible d'une instruction SELECT INTO ou INSERT EXEC. Un paramètre table peut être dans la clause FROM de SELECT INTO ou dans la chaîne ou procédure stockée INSERT EXEC.

Paramètres table et opérations BULK INSERT

L'utilisation de paramètres table est comparable à d'autres méthodes d'utilisation de variables basées sur des ensembles ; toutefois, l'utilisation de paramètres table peut souvent s'avérer plus rapide pour les grands ensembles de données. Comparé aux opérations en bloc, qui ont un coût de démarrage supérieur, les paramètres table sont particulièrement adaptés à l'insertion de moins de 1000 lignes.

Les paramètres table réutilisés tirent parti de la mise en cache de table temporaire. Cette mise en cache de table autorise une meilleure extensibilité que les opérations BULK INSERT équivalentes. En utilisant de petites opérations d'insertion de ligne, vous pouvez retirer un petit avantage en matière de performances en utilisant des listes de paramètres ou des instructions groupées au lieu d'opérations BULK INSERT ou de paramètres table. Toutefois, ces méthodes sont moins commodes à programmer et les performances diminuent rapidement à mesure que le nombre de lignes augmente.

Les paramètres table procurent des performances supérieures ou égales à une implémentation de tableau de paramètres équivalente.

Exemple

L'exemple suivant utilise Transact-SQL et vous montre comment créer un type de paramètre à valeur de table, déclarer une variable pour le référencer, remplir la liste des paramètres, puis transmettre les valeurs à une procédure stockée dans la base de données AdventureWorks.

/* Create a table type. */
CREATE TYPE LocationTableType 
   AS TABLE
      ( LocationName VARCHAR(50)
      , CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
   @TVP LocationTableType READONLY
      AS
      SET NOCOUNT ON
      INSERT INTO AdventureWorks2022.Production.Location
         (
            Name
            , CostRate
            , Availability
            , ModifiedDate
         )
      SELECT *, 0, GETDATE()
      FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT Name, 0.00
   FROM AdventureWorks2022.Person.StateProvince;
  
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Voir aussi