Paramètres table (Moteur de base de données)

Création et utilisation de paramètres table dans Transact-SQL

Les paramètres table ont deux principaux composants : un type SQL Server et un paramètre qui fait référence à ce type. Pour créer et utiliser des paramètres table, procédez comme suit :

1. Créez un type de table et définissez la structure de la table.

   Pour plus d'informations sur la création d'un type SQL Server, consultez Types de tables définis par l'utilisateur. Pour plus d'informations sur la définition d’une structure de table, consultez CREATE TABLE (Transact-SQL).

2. Déclarez une routine qui a un paramètre du type table. Pour plus d'informations sur les routines SQL Server, consultez CREATE PROCEDURE (Transact-SQL) et CREATE FUNCTION (Transact-SQL).

3. Déclarez une variable du type table et faites référence au type de table. Pour plus d'informations sur la déclaration de variables, consultez DECLARE @local_variable (Transact-SQL).

4. Remplissez la variable de table à l'aide d'une instruction INSERT. Pour plus d'informations sur l'insertion de données, consultez Ajout de lignes à l'aide d'INSERT et SELECT.

5. Après avoir créé et rempli la variable de table, vous pouvez passer la variable à une routine.

   Une fois la routine hors de portée, le paramètre table n'est plus disponible. La définition de type reste jusqu'à ce qu'elle soit supprimée.

Pour utiliser un paramètre table dans le SQL Server Native Client, consultez Paramètres table (SQL Server Native Client).

Pour utiliser un paramètre table dans ADO.NET, consultez la documentation ADO.NET.

Avantages

Restrictions

Portée

Sécurité

Affichages catalogue

Comparaison des paramètres table et des 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 évolutivité 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.

La table suivante indique quelle technologie utiliser selon la vitesse des opérations d'insertion.

Exemples

L'exemple suivant utilise Transact-SQL et montre comment créer un type de paramètre table, déclarer une variable pour y faire référence, remplir la liste de paramètres, puis passer les valeurs à une procédure stockée.

USE AdventureWorks2008R2;
GO

/* 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 usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[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 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

Voir aussi

Référence