Paramètres table (Moteur de base de données)
Les paramètres table sont un nouveau type de paramètre dans SQL Server 2008. 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 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 table sont comme les tableaux de paramètres dans OLE DB et ODBC, mais ils offrent plus de souplesse 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.
Notes
Transact-SQL passe des paramètres table aux routines par référence afin d'éviter d'effectuer une copie des données d'entrée.
Vous pouvez créer et exécuter des routines Transact-SQL avec des paramètres table et les appeler depuis du code Transact-SQL, des clients gérés et natifs dans tout langage managé.
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 :
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).
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).
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).
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.
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
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.
Restrictions
Les paramètres table ont les restrictions suivantes :
SQL Server ne maintient pas de statistiques sur les colonnes de paramètres table.
Les paramètres table doivent être passés comme paramètres READONLY d'entrée 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.
Portée
Un paramètre table a comme portée la procédure stockée, la fonction ou le texte Transact-SQL dynamique, exactement comme d'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 table dans des instructions Transact-SQL dynamiques et passer ces variables comme paramètres table à des procédures stockées et des fonctions.
Sécurité
Les autorisations pour les paramètres table suivent le modèle de sécurité d'objet pour SQL Server en utilisant les mots clés Transact-SQL suivants : CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, et REVOKE.
Affichages catalogue
Pour obtenir des informations associées aux paramètres table, vous pouvez interroger les affichages catalogue suivants : sys.parameters (Transact-SQL), sys.types (Transact-SQL)et sys.table_types (Transact-SQL).
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.
Source de données |
Logique de serveur |
Nombre de lignes |
Meilleure technologie |
|---|---|---|---|
Fichier de données mis en forme sur le serveur |
Insertion directe |
< 1000 |
BULK INSERT |
Fichier de données mis en forme sur le serveur |
Insertion directe |
> 1000 |
BULK INSERT |
Fichier de données mis en forme sur le serveur |
Complexe |
< 1000 |
Paramètres table |
Fichier de données mis en forme sur le serveur |
Complexe |
> 1000 |
BULK INSERT |
Processus de client distant |
Insertion directe |
< 1000 |
Paramètres table |
Processus de client distant |
Insertion directe |
> 1000 |
BULK INSERT |
Processus de client distant |
Complexe |
< 1000 |
Paramètres table |
Processus de client distant |
Complexe |
> 1000 |
Paramètres table |
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