CREATE FUNCTION (Transact-SQL)

Article
04/27/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Crée une fonction définie par l’utilisateur (UDF), qui est une routine Transact-SQL ou CLR (Common Language Runtime). Une fonction définie par l’utilisateur accepte des paramètres, effectue une action telle qu’un calcul complexe et retourne le résultat de cette action en tant que valeur. La valeur retournée peut être une valeur scalaire (unique) ou une table. Utilisez cette instruction pour créer une routine réutilisable, exploitable :

Dans les instructions Transact-SQL telles que SELECT
Dans les applications qui appellent la fonction
dans la définition d'une autre fonction définie par l'utilisateur ;
pour paramétrer une vue ou améliorer la fonctionnalité d'une vue indexée ;
pour définir une colonne dans une table ;
Pour définir une CHECK contrainte sur une colonne
pour remplacer une procédure stockée.
Utiliser une fonction inline comme prédicat de filtre pour une stratégie de sécurité

L’intégration du CLR .NET Framework à SQL Server est décrite dans cet article. L’intégration clR ne s’applique pas à Azure SQL Database.

Pour Azure Synapse Analytics ou Microsoft Fabric, consultez CREATE FUNCTION (Azure Synapse Analytics et Microsoft Fabric).

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour les fonctions scalaires Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Syntaxe pour les fonctions table inline Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Syntaxe pour les fonctions table à instructions multiples Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Syntaxe pour les clauses de fonction Transact-SQL.

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ , ...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

Syntaxe des fonctions scalaires CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Syntaxe des fonctions table CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Syntaxe pour les clauses de fonction CLR.

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

Syntaxe OLTP en mémoire pour les fonctions scalaires définies par l’utilisateur compilées en mode natif.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Arguments

OR ALTER

S’applique à : SQL Server 2016 (13.x) SP 1 et versions ultérieures, et Azure SQL Database.

Modifie, de manière conditionnelle, la fonction uniquement si elle existe déjà.

La syntaxe facultative OR ALTER est disponible pour CLR, à partir de SQL Server 2016 (13.x) SP 1 CU 1.

schema_name

Nom du schéma auquel appartient la fonction définie par l’utilisateur.

function_name

Nom de la fonction définie par l’utilisateur. Les noms de fonctions doivent respecter les règles applicables aux identificateurs et doivent être uniques dans la base de données et pour son schéma.

Les parenthèses sont requises après le nom de la fonction, même si un paramètre n’est pas spécifié.

@parameter_name

Paramètre dans la fonction définie par l’utilisateur. Un ou plusieurs paramètres peuvent être déclarés.

Une fonction peut comprendre au maximum 2 100 paramètres. La valeur de chaque paramètre déclaré doit être fournie par l'utilisateur lors de l'exécution de la fonction, sauf si vous définissez une valeur par défaut pour le paramètre.

Spécifiez un nom de paramètre en plaçant le signe @ comme premier caractère. Le nom de paramètre doit suivre les règles applicables aux identificateurs. Un paramètre étant local à une fonction, vous pouvez utiliser le même nom dans d'autres fonctions. Les paramètres peuvent prendre la place uniquement de constantes ; Ils ne peuvent pas être utilisés au lieu de noms de tables, de noms de colonnes ou de noms d’autres objets de base de données.

ANSI_WARNINGS n’est pas respecté lorsque vous passez des paramètres dans une procédure stockée, une fonction définie par l’utilisateur ou lorsque vous déclarez et définissez des variables dans une instruction batch. Par exemple, si une variable est définie comme char(3) et qu’une valeur de plus de trois caractères lui est attribuée, les données sont tronquées en fonction de la taille définie, et l’instruction INSERT ou UPDATE réussit.

[ type_schema_name. ] parameter_data_type

Type de données de paramètre, et éventuellement le schéma auquel il appartient. Dans le cas des fonctions Transact-SQL, tous les types de données, notamment les types CLR définis par l’utilisateur et les types de tables définis par l’utilisateur, sont autorisés à l’exception du type de données timestamp. Pour les fonctions CLR, tous les types de données, y compris les types CLR définis par l’utilisateur, sont autorisés à l’exception du texte, du ntext, de l’image, des types de tables définis par l’utilisateur et des types de données timestamp . Les types noncalaires, le curseur et la table ne peuvent pas être spécifiés en tant que type de données de paramètre dans les fonctions Transact-SQL ou CLR.

Si type_schema_name n’est pas spécifié, la Moteur de base de données recherche le scalar_parameter_data_type dans l’ordre suivant :

Schéma qui contient les noms des types de données système SQL Server.
Le schéma par défaut de l'utilisateur actuel dans la base de données active
Schéma dbo dans la base de données active.

[ = valeur par défaut ]

Valeur par défaut pour le paramètre. Si une valeur default est définie, la fonction peut être exécutée sans spécifier de valeur pour ce paramètre.

Les valeurs de paramètre par défaut peuvent être spécifiées pour les fonctions CLR, à l’exception des types de données varchar(max) et varbinary(max).

Lorsqu’un paramètre de la fonction a une valeur par défaut, le mot clé DEFAULT doit être spécifié lorsque la fonction est appelée pour récupérer la valeur par défaut. Ce comportement est différent de l'utilisation de paramètres avec des valeurs par défaut dans des procédures stockées pour lesquelles l'omission du paramètre implique également la prise en compte de la valeur par défaut. Toutefois, le DEFAULT mot clé n’est pas obligatoire lors de l’appel d’une fonction scalaire à l’aide de l’instruction EXECUTE .

READONLY

Indique que le paramètre ne peut pas être mis à jour ou modifié dans la définition de la fonction. READONLY est requis pour les paramètres de type de table définis par l’utilisateur et ne peut pas être utilisé pour un autre type de paramètre.

return_data_type

Valeur de retour d’une fonction scalaire définie par l’utilisateur. Dans le cas des fonctions Transact-SQL, tous les types de données, notamment les types CLR définis par l’utilisateur, sont autorisés, à l’exception du type de données timestamp. Dans le cas des fonctions CLR, tous les types de données, notamment les types CLR définis par l’utilisateur, sont autorisés, à l’exception des types de données text, ntext, image et timestamp. Les types noncalaires, le curseur et la table ne peuvent pas être spécifiés en tant que type de données de retour dans les fonctions Transact-SQL ou CLR.

function_body

Spécifie qu’une série d’instructions Transact-SQL, qui, ensemble, ne produisent pas d’effet secondaire, comme la modification d’une table, définissent la valeur de la fonction. function_body est utilisé seulement dans les fonctions scalaires et dans les fonctions table à instructions multiples.

Dans les fonctions scalaires, function_body est une série d’instructions Transact-SQL qui, ensemble, prennent une valeur scalaire.

Dans MSTVFs, function_body est une série d’instructions Transact-SQL qui remplissent une TABLE variable de retour.

scalar_expression

Indique la valeur scalaire retournée par la fonction scalaire.

TABLE

Indique que la valeur de retour de la fonction table est une table. Seules des constantes et des @local_variables peuvent être passés aux fonctions table.

Dans les fichiers TVF inline, la TABLE valeur de retour est définie par le biais d’une instruction unique SELECT . Les fonctions inline n’ont pas de variables de retour associées.

Dans msTVFs, @return_variable est une TABLE variable, utilisée pour stocker et accumuler les lignes qui doivent être retournées comme valeur de la fonction. @return_variable peut être spécifié uniquement pour les fonctions Transact-SQL, et pas pour les fonctions CLR.

select_stmt

Instruction unique SELECT qui définit la valeur de retour d’une fonction table inline (TVF).

ORDER (<order_clause>)

Spécifie l'ordre dans lequel les résultats sont retournés à partir de la fonction table. Pour plus d’informations, consultez la section Utiliser l’ordre de tri dans les fonctions table CLR plus loin dans cet article.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

S’applique à : SQL Server 2008 (10.0.x) SP 1 et versions ultérieures.

Spécifie l'assembly et la méthode auxquels doit se référer le nom de la fonction créée.

assembly_name : doit correspondre à une valeur de la colonne name de SELECT * FROM sys.assemblies;.

Nom utilisé dans l’instruction CREATE ASSEMBLY .
class_name : doit correspondre à une valeur de la colonne assembly_name de SELECT * FROM sys.assembly_modules;.

La valeur contient souvent un point incorporé. Dans ce cas, la syntaxe Transact-SQL exige que la valeur soit limitée avec une paire de crochets ([]) ou avec une paire de guillemets doubles ("").
method_name : doit correspondre à une valeur de la colonne method_name de SELECT * FROM sys.assembly_modules;.

La méthode doit être statique.

Dans un exemple classique pour MyFood.dll, dans lequel tous les types se trouvent dans l’espace MyFood de noms, la EXTERNAL NAME valeur peut être MyFood.[MyFood.MyClass].MyStaticMethod.

Par défaut, SQL Server ne peut pas exécuter du code CLR. Vous pouvez créer, modifier et supprimer des objets de base de données qui référencent des modules Common Language Runtime. Toutefois, vous ne pouvez pas exécuter ces références dans SQL Server tant que vous n’avez pas activé l’option clr. Pour activer cette option, utilisez sp_configure. Cette option n'est pas disponible dans une base de données autonome.

<> table_type_definition ( { <column_definition column_constraint><| <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

Définit le type de données de table pour une fonction Transact-SQL. La déclaration de table comprend des définitions de colonne et des contraintes de colonne ou de table. La table est toujours placée dans le groupe de fichiers primaire.

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

S’applique à : SQL Server 2008 (10.0.x) SP 1 et versions ultérieures, et Azure SQL Database (préversion dans certaines régions).

Définit les types de données de table pour une fonction CLR. La déclaration de table ne comprend que des types de données et des noms de colonne. La table est toujours placée dans le groupe de fichiers primaire.

NULL | NOT NULL

Pris en charge uniquement pour les fonctions scalaires définies par l’utilisateur et compilées en mode natif. Pour plus d’informations, consultez Fonctions scalaires définies par l’utilisateur pour l’OLTP en mémoire.

NATIVE_COMPILATION

Indique si une fonction définie par l’utilisateur est en mode natif compilée. Cet argument est obligatoire pour les fonctions scalaires définies par l’utilisateur et compilées en mode natif.

BEGIN ATOMIC WITH

Obligatoire, et uniquement pris en charge, pour les fonctions scalaires compilées en mode natif défini par l’utilisateur. Pour plus d’informations, consultez Atomic Blocks in Native Procedures.

SCHEMABINDING

L’argument SCHEMABINDING est requis pour les fonctions scalaires définies par l’utilisateur compilées en mode natif.

EXECUTE AS

EXECUTE AS est requis pour les fonctions scalaires définies par l’utilisateur compilées en mode natif.

<> function_option ::= et <clr_function_option> ::=

Spécifie que la fonction a une ou plusieurs des options suivantes.

ENCRYPTION

S’applique à : SQL Server 2008 (10.0.x) SP 1 et versions ultérieures.

Indique que le Moteur de base de données convertit le texte d’origine de l’instruction CREATE FUNCTION en format obfusqué. La sortie de l’obfuscation n’est pas directement visible dans les affichages catalogue. Les utilisateurs qui n’ont pas accès aux tables système ou aux fichiers de base de données ne peuvent pas récupérer le texte obfusqué. Toutefois, le texte est disponible pour les utilisateurs privilégiés qui peuvent accéder aux tables système via la connexion de diagnostic pour les administrateurs de base de données ou accéder directement aux fichiers de base de données. Les utilisateurs qui peuvent associer un débogueur au processus serveur peuvent également récupérer la procédure d'origine de la mémoire au moment de l'exécution. Pour plus d’informations sur l’accès aux métadonnées système, consultez Configuration de la visibilité des métadonnées.

L'utilisation cette option empêche la publication de la fonction dans le cadre de la réplication SQL Server. Cette option ne peut pas être spécifiée pour les fonctions CLR.

SCHEMABINDING

Indique que la fonction est liée aux objets de base de données auxquels elle fait référence. Quand SCHEMABINDING elle est spécifiée, les objets de base ne peuvent pas être modifiés d’une manière qui affecterait la définition de la fonction. Cette dernière doit d'ailleurs être modifiée ou supprimée au préalable pour supprimer les dépendances par rapport à l'objet qui doit être modifié.

La liaison de la fonction aux objets auxquels elle fait référence est supprimée uniquement quand l’une des actions suivantes se produit :

La fonction est supprimée.
La fonction est modifiée avec l’instruction ALTER, sans spécification de l’option SCHEMABINDING.

Une fonction peut être liée au schéma uniquement si les conditions suivantes sont vérifiées :

La fonction est une fonction Transact-SQL.
Les fonctions utilisateur et vues référencées par la fonction sont également liées au schéma.
La fonction fait référence aux objets à partir d'un nom en deux parties.
La fonction et les objets auxquels elle fait référence appartiennent à la même base de données.
L’utilisateur qui a exécuté l’instruction CREATE FUNCTION dispose de l’autorisation REFERENCES sur les objets de base de données référencés par la fonction.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Spécifie l’attribut OnNULLCall d’une fonction scalaire. S’il n’est pas spécifié, CALLED ON NULL INPUT il est implicite par défaut. En d’autres termes, le corps de la fonction s’exécute même s’il NULL est passé en tant qu’argument.

Si RETURNS NULL ON NULL INPUT elle est spécifiée dans une fonction CLR, elle indique que SQL Server peut retourner NULL quand l’un des arguments qu’il reçoit est NULL, sans appeler réellement le corps de la fonction. Si la méthode d’une fonction CLR spécifiée dans <method_specifier> a déjà un attribut personnalisé qui indique RETURNS NULL ON NULL INPUT, mais l’instruction CREATE FUNCTION indique CALLED ON NULL INPUT, l’instruction CREATE FUNCTION est prioritaire. L’attribut OnNULLCall ne peut pas être spécifié pour les fonctions table CLR.

EXECUTE AS

Spécifie le contexte de sécurité dans lequel la fonction définie par l'utilisateur est exécutée. Par conséquent, vous pouvez choisir le compte d'utilisateur que SQL Server doit utiliser pour valider les autorisations sur tous les objets de base de données référencés par la fonction.

EXECUTE AS ne peut pas être spécifié pour les fonctions table inline.

Pour plus d’informations, consultez Clause EXECUTE AS (Transact-SQL).

INLINE = { ON | OFF }

S’applique à : SQL Server 2019 (15.x) et versions ultérieures et Azure SQL Database.

Spécifie si cette fonction UDF scalaire doit être inline ou non. Cette clause s’applique uniquement aux fonctions scalaires définies par l’utilisateur. La clause INLINE n’est pas obligatoire. Si la INLINE clause n’est pas spécifiée, elle est automatiquement définie ON sur ou OFF selon que la fonction UDF est inlineable. Si INLINE = ON elle est spécifiée, mais que la fonction UDF est détectée comme non inline, une erreur est levée. Pour plus d’informations, consultez Incorporation des fonctions UDF scalaires.

<> column_definition ::=

Définit le type de données de la table. La déclaration de table comprend des contraintes et des définitions de colonne. Pour les fonctions CLR, seuls column_name et data_type peuvent être spécifiés.

column_name

Nom d’une colonne dans la table. Les noms de colonnes doivent respecter les règles des identificateurs et doivent être uniques au sein de la table. column_name peut être composé de 1 à 128 caractères.

data_type

Indique le type de données de la colonne. Dans le cas des fonctions Transact-SQL, tous les types de données, notamment les types CLR définis par l’utilisateur, sont autorisés, à l’exception du type de données timestamp. Pour les fonctions CLR, tous les types de données, y compris les types CLR définis par l’utilisateur, sont autorisés à l’exception du texte, ntext, image, char, varchar, varchar(max) et timestamp. Le curseur de type noncalaire ne peut pas être spécifié en tant que type de données de colonne dans les fonctions Transact-SQL ou CLR.

DEFAULT constant_expression

Spécifie la valeur fournie pour la colonne lorsque vous n'avez pas spécifié explicitement de valeur lors d'une insertion. constant_expression est une constante, NULLou une valeur de fonction système. DEFAULT les définitions peuvent être appliquées à n’importe quelle colonne, sauf celles qui ont la IDENTITY propriété. DEFAULT ne peut pas être spécifié pour les fonctions table CLR.

COLLATE collation_name

Indique le classement de la colonne. Si l'argument n'est pas spécifié, c'est le classement par défaut de la base de données qui est affecté à la colonne. Le nom du classement peut être un nom de classement Windows ou SQL. Pour obtenir la liste des classements et des informations supplémentaires, consultez Nom de classement Windows (Transact-SQL) et Nom du classement SQL Server (Transact-SQL).

La COLLATE clause peut être utilisée pour modifier les classements uniquement des colonnes des types de données char, varchar, nchar et nvarchar . COLLATE ne peut pas être spécifié pour les fonctions table CLR.

ROWGUIDCOL

Indique que la nouvelle colonne est une colonne d'identificateur unique global de ligne. Une seule colonne uniqueidentifier par table peut être désignée comme ROWGUIDCOL colonne. La ROWGUIDCOL propriété ne peut être affectée qu’à une colonne uniqueidentifier .

La ROWGUIDCOL propriété n’applique pas l’unicité des valeurs stockées dans la colonne. Elle ne génère pas non plus automatiquement de valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, utilisez la NEWID fonction sur INSERT les instructions. Une valeur par défaut peut être spécifiée ; toutefois, NEWID ne peut pas être spécifié comme valeur par défaut.

IDENTITY

Indique que la nouvelle colonne est une colonne d'identité. Lorsqu'une ligne est ajoutée à la table, SQL Server affecte une valeur incrémentée unique à la colonne. Les colonnes d’identité sont généralement utilisées avec PRIMARY KEY des contraintes pour servir d’identificateur de ligne unique pour la table. La propriété IDENTITY peut être affectée à des colonnes tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Une seule colonne d'identité peut être créée par table. Les valeurs par défaut liées et DEFAULT les contraintes ne peuvent pas être utilisées avec une colonne d’identité. Vous devez spécifier à la fois seed et increment, ou bien aucun des deux. Si vous n'en spécifiez aucun, la valeur par défaut est (1,1).

IDENTITY ne peut pas être spécifié pour les fonctions table CLR.

seed

Valeur entière à affecter à la première ligne de la table.

increment

Valeur entière à ajouter à la valeur initiale pour les lignes successives de la table.

<> column_constraint ::= et <table_constraint> ::=

Définit la contrainte d'une colonne ou table spécifiée. Pour les fonctions CLR, le seul type de contrainte autorisé est NULL. Les contraintes nommées ne sont pas autorisées.

NULL | NOT NULL

Détermine si les valeurs Null sont autorisées dans la colonne. NULL n’est pas strictement une contrainte, mais peut être spécifiée comme NOT NULL. NOT NULL ne peut pas être spécifié pour les fonctions table CLR.

PRIMARY KEY

Contrainte qui applique l’intégrité de l’entité pour une colonne spécifiée via un index unique. Dans les fonctions définies par l’utilisateur table, la PRIMARY KEY contrainte peut être créée sur une seule colonne par table. PRIMARY KEY ne peut pas être spécifié pour les fonctions table CLR.

UNIQUE

Contrainte assurant l’intégrité de l’entité d’une colonne ou de plusieurs colonnes spécifiées au moyen d’un index unique. Une table peut avoir plusieurs UNIQUE contraintes. UNIQUE ne peut pas être spécifié pour les fonctions table CLR.

CLUSTERED et NONCLUSTERED

Indiquez qu’un index cluster ou non cluster est créé pour la contrainte ou UNIQUE la PRIMARY KEY contrainte. PRIMARY KEY les contraintes utilisent CLUSTERED, et UNIQUE les contraintes utilisent NONCLUSTERED.

CLUSTERED peut être spécifié pour une seule contrainte. Si CLUSTERED elle est spécifiée pour une UNIQUE contrainte et qu’une PRIMARY KEY contrainte est également spécifiée, l’utilisation PRIMARY KEY est utilisée NONCLUSTERED.

CLUSTERED et NONCLUSTERED ne peut pas être spécifié pour les fonctions table CLR.

CHECK

Contrainte qui assure l'intégrité du domaine en limitant les valeurs possibles pouvant être entrées dans une ou plusieurs colonnes. CHECK les contraintes ne peuvent pas être spécifiées pour les fonctions table CLR.

logical_expression

Expression logique qui retourne TRUE ou FALSE.

<> computed_column_definition ::=

Spécifie une colonne calculée. Pour plus d’informations sur les colonnes calculées, consultez CREATE TABLE (Transact-SQL).

column_name

Nom de la colonne calculée.

computed_column_expression

Expression définissant la valeur d’une colonne calculée.

<index_option> ::=

Spécifie les options d’index pour l’index ou UNIQUE l’indexPRIMARY KEY. Pour plus d’informations sur les options d’index, consultez CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }

Spécifie le remplissage de l'index. Par défaut, il s’agit de OFF.

FILLFACTOR = fillfactor

Spécifie un pourcentage qui indique le taux de remplissage appliqué par le Moteur de base de données au niveau feuille de chaque page d’index lors de la création ou de la modification de l’index. fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0.

IGNORE_DUP_KEY = { ON | OFF }

Spécifie la réponse d'erreur lorsqu'une opération d'insertion essaie d'insérer des valeurs de clés en double dans un index unique. L’option IGNORE_DUP_KEY s’applique uniquement aux opérations d’insertion après la création ou la régénération de l’index. Par défaut, il s’agit de OFF.

STATISTICS_NORECOMPUTE = { ON | OFF}

Spécifie si les statistiques de distribution sont recalculées. Par défaut, il s’agit de OFF.

ALLOW_ROW_LOCKS = { ON | OFF }

Indique si les verrous de ligne sont autorisés ou non. Par défaut, il s’agit de ON.

ALLOW_PAGE_LOCKS = { ON | OFF }

Indique si les verrous de page sont autorisés. Par défaut, il s’agit de ON.

Bonnes pratiques

Si une fonction définie par l’utilisateur n’est pas créée avec la SCHEMABINDING clause, les modifications apportées aux objets sous-jacents peuvent affecter la définition de la fonction et produire des résultats inattendus lorsqu’elles sont appelées. Nous vous recommandons d’implémenter l’une des méthodes suivantes pour vous assurer que la fonction ne devient pas obsolète en raison des modifications apportées à ses objets sous-jacents :

Spécifiez la WITH SCHEMABINDING clause lorsque vous créez la fonction. Cette option garantit que les objets référencés dans la définition de fonction ne peuvent pas être modifiés, sauf si la fonction est également modifiée.
Exécutez la procédure stockée sp_refreshsqlmodule après avoir modifié tout objet spécifié dans la définition de la fonction.

Pour plus d’informations et des considérations sur les performances relatives aux fonctions table inline (tvfs inline) et aux fonctions table à plusieurs instructions (MSTVF), consultez Créer des fonctions définies par l’utilisateur (Moteur de base de données).

Types de données

Si des paramètres sont spécifiés dans une fonction CLR, ils doivent être de types SQL Server tels que préalablement définis pour scalar_parameter_data_type. Pour plus d’informations sur la comparaison des types de données système SQL Server aux types de données d’intégration CLR ou aux types de données common language runtime .NET Framework, consultez Mappage des données de paramètre CLR.

Pour que SQL Server référence la méthode correcte lorsqu’elle est surchargée dans une classe, la méthode indiquée doit <method_specifier> avoir les caractéristiques suivantes :

Recevez le même nombre de paramètres que spécifié dans [ , ...n ].
Recevoir tous les paramètres par valeur, non par référence.
Utilisez des types de paramètres compatibles avec les types spécifiés dans la fonction SQL Server.

Si le type de données de retour de la fonction CLR spécifie un type de table (RETURNS TABLE), le type de données de retour de la méthode doit <method_specifier> être de type IEnumerator ou IEnumerable, et suppose que l’interface est implémentée par le créateur de la fonction. Contrairement aux fonctions Transact-SQL, les fonctions CLR ne peuvent pas inclure PRIMARY KEY, UNIQUEou CHECK les contraintes dans <table_type_definition>. Les types de données des colonnes spécifiés dans <table_type_definition> doivent correspondre aux types des colonnes correspondantes du jeu de résultats retourné par la méthode dans <method_specifier> au moment de l’exécution. Cette vérification de type n’est pas effectuée au moment de la création de la fonction.

Pour plus d’informations sur la façon de programmer des fonctions CLR, consultez Fonctions CLR définies par l’utilisateur.

Notes

Les fonctions scalaires peuvent être appelées où des expressions scalaires sont utilisées, qui inclut des colonnes calculées et CHECK des définitions de contrainte. Les fonctions scalaires peuvent également être exécutées à l’aide de l’instruction EXECUTE (Transact-SQL). Les fonctions scalaires doivent être appelées avec au moins le nom en deux parties de la fonction (<schema>.<function>). Pour plus d’informations sur les noms en plusieurs parties, consultez Conventions de la syntaxe Transact-SQL (Transact-SQL). Les fonctions table peuvent être appelées là où des expressions de table sont autorisées dans la clause FROM des instructions SELECT, INSERT, UPDATE ou DELETE. Pour plus d’informations, consultez Exécuter des fonctions définies par l’utilisateur.

Interopérabilité

Les instructions suivantes sont valides dans une fonction :

Instructions d'affectation
Instructions de contrôle de flux, à l’exception des instructions TRY...CATCH.
Instructions DECLARE définissant des variables de données locales et des curseurs locaux.
Instructions SELECT qui contiennent des listes de sélection avec des expressions affectant des valeurs à des variables locales.
Opérations de curseur faisant référence à des curseurs locaux déclarés, ouverts, fermés et libérés dans la fonction. Seules FETCH les instructions qui attribuent des valeurs à des variables locales à l’aide de la INTO clause sont autorisées ; FETCH les instructions qui retournent des données au client ne sont pas autorisées.
Instructions INSERT, UPDATE et DELETE modifiant des variables de table locales.
Instructions EXECUTE appelant des procédures stockées étendues.

Pour plus d’informations, consultez Créer des fonctions définies par l’utilisateur (Moteur de base de données).

Interopérabilité des colonnes calculées

Les fonctions ont les propriétés suivantes. Les valeurs de ces propriétés déterminent si les fonctions sont utilisables dans des colonnes calculées pouvant être indexées ou rendues persistantes.

Propriété	Description	Notes
`IsDeterministic`	La fonction est déterministe ou non déterministe.	L'accès local aux données est autorisé dans les fonctions déterministes. Par exemple, les fonctions qui retournent toujours le même résultat chaque fois qu’elles sont appelées à l’aide d’un ensemble spécifique de valeurs d’entrée et avec le même état de la base de données sont étiquetées déterministes.
`IsPrecise`	La fonction est précise ou imprécise.	Les fonctions imprécises contiennent des opérations telles que les opérations à virgule flottante.
`IsSystemVerified`	Les propriétés de précision et de déterminisme de la fonction peuvent être vérifiées par SQL Server.
`SystemDataAccess`	La fonction accède aux données système (catalogues système ou tables système virtuelles) dans l'instance locale de SQL Server.
`UserDataAccess`	La fonction accède aux données utilisateur dans l'instance locale de SQL Server.	Comprend les tables définies par l'utilisateur et les tables temporaires, mais pas les variables de table.

Les propriétés de précision et de déterminisme des fonctions Transact-SQL sont automatiquement déterminées par SQL Server. Les propriétés d'accès aux données et de déterminisme des fonctions CLR peuvent être spécifiées par l'utilisateur. Pour plus d’informations, consultez intégration clR : attributs personnalisés pour les routines CLR.

Pour afficher les valeurs actuelles de ces propriétés, utilisez OBJECTPROPERTYEX (Transact-SQL).

Important

Les fonctions doivent être créées avec SCHEMABINDING pour être déterministes.

Une colonne calculée qui appelle une fonction définie par l'utilisateur peut être utilisée dans un index lorsque la fonction possède les valeurs de propriété suivantes :

IsDeterministic est true
IsSystemVerified est true (sauf si la colonne calculée est conservée)
UserDataAccess est false
SystemDataAccess est false

Pour plus d’informations, consultez Index sur les colonnes calculées.

Appeler des procédures stockées étendues à partir de fonctions

La procédure stockée étendue, lors de son appel à partir d’une fonction, ne peut pas retourner les jeux de résultats au client. Toutes les API ODS qui retournent des jeux de résultats au client, retournent FAIL. La procédure stockée étendue peut se reconnecter à une instance de SQL Server ; Toutefois, il ne doit pas essayer de joindre la même transaction que la fonction qui a appelé la procédure stockée étendue.

Comme pour les appels à partir d’un lot ou d’une procédure stockée, la procédure stockée étendue est exécutée dans le contexte du compte de sécurité Windows sous lequel SQL Server s’exécute. Le propriétaire de la procédure stockée doit prendre en compte ce scénario lors de l’octroi EXECUTE d’autorisations aux utilisateurs.

Limites

Les fonctions définies par l’utilisateur ne permettent pas d’exécuter des actions qui modifient l’état des bases de données.

Les fonctions définies par l’utilisateur ne peuvent pas contenir de clause OUTPUT INTO avec une table comme cible.

Les instructions Service Broker suivantes ne peuvent pas être incluses dans la définition d’une fonction Transact-SQL définie par l’utilisateur :

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

Les fonctions définies par l'utilisateur peuvent être imbriquées ; en d'autres termes, une fonction définie par l'utilisateur peut en appeler une autre. Le niveau d'imbrication est incrémenté lorsque la fonction appelée commence à s'exécuter, et décrémenté lorsque l'exécution est terminée. Les fonctions définies par l'utilisateur peuvent être imbriquées jusqu'à 32 niveaux. Le dépassement des niveaux d'imbrication maximum autorisés, provoque l'échec de la totalité de la chaîne de fonctions appelantes. Toute référence à du code managé depuis une fonction Transact-SQL définie par l'utilisateur compte pour un niveau parmi les 32 niveaux d'imbrication possibles. Les méthodes appelées à partir du code managé ne comptent pas par rapport à cette limite.

Utiliser l’ordre de tri dans les fonctions table CLR

Quand vous utilisez la clause ORDER dans des fonctions table CLR, prenez en compte les indications suivantes :

Vous devez faire en sorte que les résultats soient toujours triés dans l'ordre spécifié. Si les résultats ne sont pas dans l’ordre spécifié, SQL Server génère un message d’erreur lorsque la requête est exécutée.
Si une clause ORDER est spécifiée, la sortie de la fonction table doit être triée en fonction du classement de la colonne (explicite ou implicite). Par exemple, si le classement de colonne est chinois, les résultats retournés doivent être triés en fonction des règles de tri chinoises. (Le classement est spécifié dans la DDL de la fonction table ou obtenu à partir du classement de base de données.)
SQL Server vérifie toujours la ORDER clause si elle est spécifiée, tout en retournant des résultats, si le processeur de requêtes l’utilise ou non pour effectuer d’autres optimisations. Utilisez la ORDER clause uniquement si vous savez qu’elle est utile pour le processeur de requêtes.
Le processeur de requêtes SQL Server tire automatiquement parti de la clause ORDER dans les cas suivants :
- Requêtes d’insertion dans lesquelles la clause ORDER est compatible avec un index.
- Clauses ORDER BY qui sont compatibles avec la clause ORDER.
- Agrégats, où GROUP BY est compatible avec la clause ORDER.
- Agrégats DISTINCT dans lesquels les colonnes distinctes sont compatibles avec la clause ORDER.

La ORDER clause ne garantit pas les résultats ordonnés lorsqu’une SELECT requête est exécutée, sauf si ORDER BY elle est également spécifiée dans la requête. Pour plus d’informations sur la façon de rechercher par requête les colonnes incluses dans l’ordre de tri pour les fonctions table, consultez sys.function_order_columns (Transact-SQL).

Métadonnées

Le tableau suivant répertorie les affichages catalogue système que vous pouvez utiliser pour retourner des métadonnées sur les fonctions définies par l'utilisateur.

Vue système	Description
sys.sql_modules	Consultez l’exemple E dans la section Exemples.
sys.assembly_modules	Affiche des informations sur les fonctions définies par l'utilisateur CLR.
sys.parameters	Affiche des informations sur les paramètres définis dans les fonctions définies par l'utilisateur.
sys.sql_expression_dependencies	Affiche les objets sous-jacents référencés par une fonction.

Autorisations

Nécessite l’autorisation CREATE FUNCTION dans la base de données et l’autorisation ALTER sur le schéma dans lequel la fonction est créée. Si la fonction spécifie un type défini par l’utilisateur, elle nécessite l’autorisation EXECUTE sur le type.

Exemples

Pour obtenir d’autres exemples et considérations sur les performances relatives aux fonctions définies par l’utilisateur, consultez Créer des fonctions définies par l’utilisateur (Moteur de base de données).

R : Utiliser une fonction scalaire définie par l’utilisateur qui calcule la semaine ISO

L'exemple suivant crée la fonction définie par l'utilisateur ISOweek. Cette fonction prend un argument date et calcule le numéro de semaine ISO. Pour que ce calcul puisse être correctement réalisé, SET DATEFIRST 1 doit être appelée avant la fonction.

L’exemple montre également l’utilisation de la clause EXECUTE AS (Transact-SQL) pour spécifier le contexte de sécurité dans lequel une procédure stockée peut être exécutée. Dans l’exemple, l’option CALLER spécifie que la procédure est exécutée dans le contexte de l’utilisateur qui l’appelle. Les autres options que vous pouvez spécifier sont SELF, OWNER et user_name.

Voici l’appel de fonction. DATEFIRST est défini sur 1.

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

Voici le jeu de résultats.

ISO Week
----------------
52

B. Créer une fonction table incluse

L’exemple suivant retourne une fonction table incluse dans la base de données AdventureWorks2022. Pour chaque produit vendu au magasin, il retourne trois colonnes : ProductID, Name et la somme des totaux annuels par magasin sous YTD Total.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Pour appeler la fonction, exécutez la requête suivante :

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Créer une fonction table à plusieurs instructions

L'exemple suivant crée la fonction table fn_FindReports(InEmpID) dans la base de données AdventureWorks2022. Lorsqu'elle est fournie avec un ID d'employé valide, la fonction retourne une table répertoriant tous les employés qui rendent compte à l'employé directement ou indirectement. La fonction utilise une expression de table commune (CTE, Common Table Expression) récursive pour générer la liste hiérarchique des employés. Pour plus d’informations sur les expressions CTE, consultez WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE (
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR(255) NOT NULL,
    LastName NVARCHAR(255) NOT NULL,
    JobTitle NVARCHAR(50) NOT NULL,
    RecursionLevel INT NOT NULL
    )
    --Returns a result set that lists all the employees who report to the
    --specific employee directly or indirectly.
AS
BEGIN
    WITH EMP_cte (
        EmployeeID,
        OrganizationNode,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
        ) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        FROM HumanResources.Employee e
        INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        )
    -- Copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
    FROM EMP_cte

    RETURN
END;
GO

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. Créer une fonction CLR

L’exemple crée la fonction CLRlen_s. Avant que la fonction ne soit créée, l'assembly SurrogateStringFunction.dll est inscrit dans la base de données locale.

S’applique à : SQL Server 2008 (10.0.x) SP 1 et versions ultérieures.

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Pour obtenir un exemple de création d’une fonction table CLR, consultez Fonctions table CLR.

E. Afficher la définition des fonctions définies par l’utilisateur

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

La définition des fonctions créées à l’aide de l’option ENCRYPTION ne peut pas être affichée à l’aide sys.sql_modulesde ; toutefois, d’autres informations sur les fonctions chiffrées sont affichées.

Partage via

CREATE FUNCTION (Transact-SQL)

Syntaxe

Arguments

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = valeur par défaut ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

<> table_type_definition ( { <column_definition column_constraint><| <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

NULL | NOT NULL

NATIVE_COMPILATION

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

<> function_option ::= et <clr_function_option> ::=

ENCRYPTION

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<> column_definition ::=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

<> column_constraint ::= et <table_constraint> ::=

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED et NONCLUSTERED

CHECK

logical_expression

<> computed_column_definition ::=

column_name

computed_column_expression

<index_option> ::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF}

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

Bonnes pratiques

Types de données

Notes

Interopérabilité

Interopérabilité des colonnes calculées

Appeler des procédures stockées étendues à partir de fonctions

Limites

Utiliser l’ordre de tri dans les fonctions table CLR

Métadonnées

Autorisations

Exemples

R : Utiliser une fonction scalaire définie par l’utilisateur qui calcule la semaine ISO

B. Créer une fonction table incluse

C. Créer une fonction table à plusieurs instructions

D. Créer une fonction CLR

E. Afficher la définition des fonctions définies par l’utilisateur

Contenu connexe

Commentaires

Ressources supplémentaires