Réponse.

Dans SQL, on peut réaliser la logique métier grâce à des fonctions définies par l'utilisateur (User Defined Functions, UDF), ce qui permet de transférer une partie des calculs et des règles métier dans la base de données.

Les fonctions scalaires retournent une seule valeur et sont appelées dans des expressions — par exemple, pour calculer un total en fonction des paramètres :

CREATE FUNCTION dbo.GetDiscount(@price DECIMAL(10,2), @loyaltyLevel INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @price * CASE 
        WHEN @loyaltyLevel = 1 THEN 0.95
        WHEN @loyaltyLevel = 2 THEN 0.90
        ELSE 1.0
    END
END;
-- Exemple d'utilisation :
SELECT Name, dbo.GetDiscount(Price, LoyaltyLevel) AS DiscountPrice FROM Products;

Les fonctions tabulaires retournent un ensemble de lignes (table) :

CREATE FUNCTION dbo.ActiveOrders(@userId INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Orders WHERE UserId = @userId AND Status = 'Active'
);
-- Utilisation :
SELECT * FROM dbo.ActiveOrders(123);

Différences, limitations et recommandations :

Les UDF scalaires ont souvent de faibles performances sur de grands ensembles de données, car elles sont appelées par ligne ;
Les UDF tabulaires s'intègrent mieux dans les plans de requêtes et peuvent être utilisées comme des tables ordinaires ;
La logique nécessitant l'accès à des ressources externes ou un traitement métier complexe doit être réalisée en dehors de SQL.

Question piège.

Quelle est la différence entre une fonction tabulaire INLINE et une fonction tabulaire multi-instructions (multi-statement) ? Comment ce choix impacte-t-il les performances ?

Réponse et exemple :

La fonction INLINE (single-statement, retourne immédiatement SELECT) est optimisée comme partie intégrante de la requête principale, ne crée pas de table temporaire et fonctionne plus rapidement, le plan de requête est commun.
La fonction multi-instructions crée une table temporaire (une variable de table), ce qui dérange généralement le plan de requête, des erreurs d'estimation de cardinalité peuvent se produire, entraînant une baisse des performances.

-- INLINE
CREATE FUNCTION dbo.InlineActiveOrders(@userId INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Orders WHERE UserId = @userId AND Status = 'Active'
);

-- Multi-instructions
CREATE FUNCTION dbo.MultiActiveOrders(@userId INT)
RETURNS @result TABLE (...)
AS
BEGIN
    INSERT INTO @result SELECT ...
    -- logique
    RETURN
END

Histoire

Projet : Analyse financière. Nous avons utilisé des UDF scalaires dans la partie SELECT d'une requête de rapport sur un grand ensemble de tables : SELECT Amount, dbo.CalcTax(Amount, Type) FROM Transactions. La requête prenait 5 à 10 minutes en raison du traitement ligne par ligne des UDF. Nous l'avons réécrite avec un CASE intégré : le temps est descendu à quelques secondes.

Histoire

Projet : E-commerce. Pour rechercher les paniers des utilisateurs, nous avons utilisé des fonctions tabulaires multi-instructions, où la logique de filtrage était répartie sur plusieurs étapes. Il s'est avéré que SQL ne construisait pas de plan optimal et effectuait un scan, même si un seul élément était nécessaire. Le remplacement par une fonction INLINE a immédiatement accéléré les requêtes de 50 fois.

Histoire

Projet : CRM. Nous avons transféré la logique métier de calcul des bonus dans une UDF complexe. Après quelques mois, nous avons réalisé que les formules métier avaient changé, mais mettre à jour la fonction était difficile en raison de son intégration stricte avec les données. Dans un produit itératif, l'UDF a entraîné des coûts élevés pour coordonner les changements entre le backend et la base de données.