• Article

Fonctions (moteur de base de données)

SQL Server offre des fonctions intégrées permettant d'effectuer certaines opérations.

Les fonctions peuvent être utilisées ou incluses dans :

  • la liste de sélection d'une requête utilisant une instruction SELECT pour retourner une valeur ;

    SELECT DB_NAME();
GO

  • la condition de recherche d'une clause WHERE dans une instruction SELECT ou dans une instruction de modification de données (SELECT, INSERT, DELETE ou UPDATE) pour restreindre le nombre de lignes répondant à la requête ;

    USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
FROM Sales.SalesOrderDetail
WHERE OrderQty =
   (SELECT MAX(OrderQty) FROM Sales.SalesOrderDetail);
GO

  • la condition de recherche (clause WHERE) d'une vue créée dynamiquement pour qu'elle s'adapte à l'utilisateur ou à l'environnement au moment de l'exécution ;

    CREATE VIEW ShowMyEmploymentInfo AS
SELECT FirstName, LastName
FROM Person.Contact
WHERE ContactID = SUSER_SID();
GO

  • toute expression ;

  • une contrainte CHECK ou un déclencheur pour rechercher les valeurs spécifiées lorsque les données sont insérées ;

    CREATE TABLE SalesContacts
   (SalesRepID   INT PRIMARY KEY CHECK (SalesRepID = SUSER_SID() ),
   ContactName   VARCHAR(50) NULL,
   ContactPhone   VARCHAR(13) NULL);
GO

  • une contrainte DEFAULT ou un déclencheur afin de fournir une valeur si aucune valeur n'est spécifiée par une instruction INSERT.

    CREATE TABLE SalesContacts
   (
   SalesRepID   INT PRIMARY KEY CHECK (SalesRepID = SUSER_SID() ),
   ContactName   VARCHAR(50) NULL,
   ContactPhone   VARCHAR(13) NULL,
   WhenCreated   DATETIME DEFAULT GETDATE(),
   Creator      INT DEFAULT SUSER_SID()
   );
GO

Les fonctions sont toujours suivies de parenthèses, même lorsqu'elles ne contiennent aucun paramètre. Les fonctions niladic utilisées avec le mot clé DEFAULT constituent une exception à cette règle. Les fonctions niladic n'admettent pas de paramètre. Pour plus d'informations sur le mot clé DEFAULT, consultez ALTER TABLE (Transact-SQL) et CREATE TABLE (Transact-SQL).

Les paramètres utilisés pour spécifier une base de données, un serveur, une connexion d'accès ou un utilisateur de base de données sont parfois facultatifs. S'ils ne sont pas spécifiés, ils correspondent à la base de données, au serveur hôte, à la connexion d'accès ou à l'utilisateur actif par défaut.

Les fonctions peuvent être imbriquées.

Catégories de fonctions

Le tableau suivant répertorie les catégories pour les fonctions SQL Server.

Catégorie de fonction

Description

Fonctions d'agrégation (Transact-SQL)

Fonctions permettant d'effectuer des opérations qui combinent plusieurs valeurs en une seule. Exemples de fonctions d'agrégation : COUNT, SUM, MIN et MAX.

Fonctions de configuration

Fonctions scalaires retournant des informations sur les paramètres de configuration.

Fonctions cryptographiques (Transact-SQL)

Fonctions prenant en charge le chiffrement, le déchiffrement, la signature numérique et la validation des signatures numériques.

Fonctions curseur

Fonctions retournant des informations sur l'état d'un curseur.

Fonctions de date et d'heure

Fonctions modifiant les valeurs de date et d'heure.

Fonctions mathématiques

Fonctions permettant d'effectuer des opérations trigonométriques, géométriques et d'autres opérations numériques.

Fonctions de métadonnées

Fonctions retournant des informations sur les attributs des bases de données et sur les objets de base de données.

Fonctions de classement

Fonctions non déterministes qui retournent une valeur de classement pour chaque ligne dans une partition.

Fonctions Rowset (Transact-SQL)

Fonctions retournant les ensembles de lignes qui peuvent remplacer une référence de table dans une instruction Transact-SQL.

Fonctions de sécurité

Fonctions retournant des informations concernant les utilisateurs et les rôles.

Fonctions de chaînes

Fonctions modifiant les valeurs char, varchar, nchar, nvarchar, binary et varbinary.

Fonctions système

Fonctions permettant d'exploiter les objets et les options de différents niveaux système ou d'obtenir un état.

Fonctions statistiques système (Transact-SQL)

Fonctions retournant des informations sur la performance de SQL Server.

Fonctions text et image

Fonctions modifiant les valeurs text et image.

Fonctions déterministes et non déterministes

Dans SQL Server, les fonctions sont classées en trois groupes : strictement déterministes, déterministes ou non déterministes.

Une fonction est strictement déterministe lorsqu'elle retourne toujours les mêmes résultats pour un ensemble spécifique de valeurs d'entrée.

Pour les fonctions définies par l'utilisateur, une notion moins rigide du déterminisme est appliquée. Une fonction définie par l'utilisateur est déterministe lorsqu'elle retourne toujours les mêmes résultats pour un ensemble spécifique de valeurs d'entrée et d'état de base de données. Si la fonction n'est pas strictement déterministe, elle peut être déterministe dans ce sens si elle accède à des données.

Une fonction non déterministe peut retourner des résultats différents lors d'appels répétés avec le même ensemble de valeurs d'entrée. Par exemple, la fonction GETDATE() est non déterministe. SQL Server impose des restrictions sur différentes classes de non-déterminisme. Les fonctions non déterministes doivent donc être utilisées avec précaution.

Pour les fonctions intégrées, le déterminisme et le déterminisme strict sont identiques. Pour les fonctions Transact-SQL définies par l'utilisateur, le système vérifie la définition et empêche la définition de fonctions non déterministes. Toutefois, une fonction accédant à des données ou non liée à un schéma n'est pas considérée comme étant strictement déterministe. Dans le cas des fonctions CLR (common language runtime), les définitions de fonctions spécifient les propriétés déterministes, d'accès aux données et d'accès aux données système de chaque fonction, mais ces propriétés n'étant pas vérifiées par le système, ces fonctions ne sont pas considérées comme strictement déterministes.

L'absence de déterminisme d'une fonction limite son champ d'utilisation. Seules les fonctions déterministes peuvent être invoquées dans les vues indexées, les colonnes calculées indexées, les colonnes calculées persistantes ou les définitions de fonctions Transact-SQL définies par l'utilisateur.

L'absence de déterminisme strict d'une fonction peut empêcher des optimisations des performances précieuses. Certaines étapes de réorganisation de plans sont ignorées pour en préserver l'exactitude. En outre, le nombre, l'ordre et le minutage des appels aux fonctions définies par l'utilisateur dépendent de l'implémentation. Ne vous servez pas de cette sémantique d'invocation. Excepté les expressions RAND intégrées non déterministes constantes à l'exécution et la famille GETDATE, le nombre, l'ordre et le minutage des appels dépendent du plan choisi.

Meilleures pratiques recommandées

Nous vous recommandons de respecter les instructions suivantes à chaque fois que cela est possible :

1. Lorsque vous avez le choix, optez pour des fonctions strictement déterministes. Liez notamment toutes vos fonctions Transact-SQL à un schéma.

2. Réservez l'utilisation de fonctions non déterministes aux listes de sélections les plus à l'extérieur.

3. N'utilisez pas les fonctions non déterministes dans des requêtes ayant un impact sur les performances.

4. Ne tenez aucun compte du nombre, de l'ordre et du minutage des appels car ils dépendent de l'implémentation.

Pour plus d'informations, consultez Fonctions déterministes et non déterministes.