Advanced Querying Techniques in SQL Server

SQL Server version: SQL Server 2022 (applicable from SQL Server 2014) Tool used: Azure Data Studio 1.44+ Demo Database: AdventureWorks2022

Table of Contents

1. Training overview
2. Navigating data hierarchies with recursive queries

• 2.1 Introduction to recursive queries
• 2.2 The structure of the WITH statement
• 2.3 Demo: Iterative queries with the WITH statement
• 2.4 Write recursive Transact-SQL
• 2.5 Demo: Simple recursive queries
• 2.6 T-SQL restrictions for recursive queries
• 2.7 Demo: Navigating a data hierarchy by recursion
• 2.8 Module 2 Summary

3. Data aggregation with PIVOT and UNPIVOT clauses

• 3.1 Introduction to PIVOT and UNPIVOT
• 3.2 Demo: Rotate in a spreadsheet
• 3.3 Demo: Cross-tabulation in Transact-SQL
• 3.4 Understanding PIVOT syntax
• 3.5 Demo: Using PIVOT in Transact-SQL
• 3.6 Demo: PIVOT on several columns (Multi-Pivot)
• 3.7 Demo: Dynamic PIVOT
• 3.8 Understanding UNPIVOT syntax
• 3.9 Demo: Using UNPIVOT and CROSS APPLY
• 3.10 Module 3 Summary

4. Data storage according to the EAV (Entity, Attribute, Value) model

• 4.1 Introduction to the EAV model
• 4.2 Demo: Traditional approach to sparse data
• 4.3 Demo: Refactoring to the EAV model
• 4.4 Demo: Factoring attributes with Foreign Keys
• 4.5 Summary of Progress and Remaining Issues
• 4.6 Demo: Management of types and constraints
• 4.7 Demo: Dynamic constraints
• 4.8 Demo: SPARSE columns
• 4.9 Demo: XML as an alternative to EAV
• 4.10 Demo: From XML to JSON
• 4.11 Module 4 Summary

5. Data Sampling in SQL Server

• 5.1 Introduction to data sampling
• 5.2 Data Sampling Terminology
• 5.3 TABLESAMPLE option syntax
• 5.4 Demo: Using the TABLESAMPLE option
• 5.5 Demo: Stratified Sampling
• 5.6 Demo: Cluster Sampling
• 5.7 Module 5 Summary

6. References and additional resources

1. Training overview

This training explores several advanced querying techniques in Transact-SQL (T-SQL), the SQL dialect used by Microsoft SQL Server. It is aimed at developers and database administrators who have already mastered the basics of SQL — including SELECT queries with JOIN, creating tables and views — and want to go further.

Educational objectives

At the end of this training, you will be able to:

• Navigating data hierarchies using recursive queries with the WITH (Recursive Common Table Expressions) statement
• Aggregate and transform data by pivoting rows into columns and vice versa using the PIVOT and UNPIVOT clauses
• Store optional attributes efficiently using the EAV (Entity, Attribute, Value) model, SPARSE columns, XML or JSON type
• Sample large datasets for statistical analysis using the TABLESAMPLE option

Prerequisites

• Basic knowledge of SQL (SELECT, JOINs, CREATE TABLE, views)
• Familiarity with SQL Server (2014 minimum, 2022 recommended)
• Accessing a SQL Server instance with AdventureWorks2022 database

Technical background

The training was developed with SQL Server 2022 running in a Docker container and Azure Data Studio version 1.44+. All content is applicable to SQL Server 2014 and later. SQL Server 2012 and earlier versions are not covered (SQL Server 2012 reached its end of extended support on July 12, 2022; SQL Server 2014 reached its end of support on July 9, 2024).

Recommended Related Content

• Building Advanced SQL Server Tables and Views
• Statistical Analysis and Big Data
• Using XML and JSON in SQL Server

2. Navigating data hierarchies with recursive queries

Module duration: 22 minutes 6 seconds

2.1 Introduction to recursive queries

This module highlights the use of the WITH statement in SQL Server, also known as Common Table Expression (CTE). The goal is to distinguish the iterative approach from the recursive approach, examine some important restrictions, and then demonstrate how to navigate a data hierarchy.

Recursion versus iteration: Iteration solves a problem by repeating an operation in a loop until a condition is satisfied. Recursion solves a problem by defining itself in relation to a simpler version of the same problem — the function calls itself until it reaches a base case.

Mathematical basis of recursion: Recursion has a strong mathematical foundation dating back centuries. Many sequences are defined as recurrences. In 1960, Dutch computer scientist E.W. Dijkstra published a paper on how to implement recursion on a computer using a stack. Dijkstra is particularly known for his article Go-To Statement Considered Harmful.

The stack approach: Recursion relies on a call stack. Each recursive call stacks a new execution context. When the base case is reached, the stack gradually depopulates. It is both the mechanism that makes recursion possible and the source of potential limitations (stack overflow, degraded performance on large hierarchies).

2.2 The structure of the WITH statement

The general structure of the WITH statement is relatively simple. It begins with the keyword WITH, preferably preceded by a semicolon.

Why put a semicolon before WITH?

The general rule requires that any preceding statement be terminated with a semicolon. In practice, it is strongly recommended to always place a semicolon just before the WITH keyword, even if no statement precedes it — someone could add code before it in the future, which would cause a syntax error.

General structure of a WITH statement

;WITH cte1 (colonne1, colonne2, ...) AS (
    -- Requête SELECT définissant le CTE
    SELECT ...
),
cte2 AS (
    -- Second CTE
    SELECT ...
)
-- Requête externe finale (DML : SELECT, UPDATE, INSERT, DELETE)
SELECT * FROM cte1 JOIN cte2 ON ...

Key points about CTE:

• A CTE may optionally have a list of column names to rename or preempt internal query results. If the query in the CTE contains expressions without aliases, these must be defined in the CTE header.
• The CTE query can be as simple or complex as necessary, pulling data from tables, views, functions and even literal values. It is not possible to call a stored procedure in a CTE.
• CTEs can contain set operators: JOIN, UNION, EXCEPT, etc.
• The final external query can be any valid DML statement (SELECT, UPDATE, INSERT, DELETE).
• Non-recursive CTEs are functionally equivalent to subqueries — you can reverse a WITH into a subquery without changing semantics or performance.
• A CTE can be referenced multiple times in the external query, thus avoiding copying the subquery.
• The rowsets returned by the CTEs are not materialized; they are re-evaluated at each reference.

2.3 Demo: Iterative queries with the WITH statement

This demo shows examples of simple, non-recursive WITH statements running in Azure Data Studio connected to SQL Server 2022 with Microsoft sample databases (AdventureWorks).

Example 1: Rename a column

-- Renommer une colonne via un CTE

;WITH simpleCTE (FortyTwo) AS (
    SELECT 42 AS tweeënveertig -- Nom de colonne en néerlandais
)

SELECT FortyTwo FROM simpleCTE

GO

-- Équivalent avec une sous-requête

SELECT FortyTwo FROM (
    SELECT 42 AS tweeënveertig
) simpleSubquery (FortyTwo) -- Renommage de colonne via alias

GO

Example 2: Two CTEs to filter employees

-- Sélection d'employés en utilisant deux CTE

;WITH Emps AS (
    SELECT * FROM HumanResources.Employee AS e
    WHERE e.BirthDate > '1990-01-01'
),
People AS (
    SELECT * FROM Person.Person AS p
    WHERE p.LastName LIKE 'K%'
)

SELECT CONCAT_WS(', ', p.FirstName, p.LastName, e.JobTitle) AS NameAndTitle 
FROM Emps e
JOIN People p 
    ON p.BusinessEntityID = e.BusinessEntityID

GO

Note: In this example, no column aliases have been defined in the CTEs. If the tables were to change, it could produce unexpected results. It is recommended to alias columns in CTEs.

Example 3: UPDATE via a CTE (with ROLLBACK)

-- Mise à jour de données via un CTE (protégé par une transaction)

BEGIN TRAN  -- Protection contre les modifications indésirables

;WITH cur AS (
    SELECT CurrencyCode, Name 
    FROM Sales.Currency AS c
    WHERE c.CurrencyCode IN ('NLG', 'BEF', 'FRF', 'ITL', 'DEM') 
)

UPDATE cur
SET cur.Name = CONCAT_WS(' ', cur.name, '-', cur.CurrencyCode, 'No longer used, use Euro')
OUTPUT deleted.CurrencyCode, deleted.Name, inserted.Name

ROLLBACK

GO

Important point: CTEs can be used for data modification operations (UPDATE, INSERT, DELETE), not just for SELECT.

2.4 Writing recursive Transact-SQL

The Fibonacci sequence: example of recurrence

The Fibonacci sequence is defined mathematically as follows:

$$F_n = \begin{cases} 0 & \text{si } n = 0 \ 1 & \text{si } n = 1 \ F_{n-1} + F_{n-2} & \text{si } n > 1 \end{cases}$$

This definition is a recurrence: $F_n$ is defined based on the two previous Fibonacci numbers. To calculate $F_{10}$, we must first calculate $F_9$ and $F_8$, and so on. If you do it by hand, it becomes laborious — that’s why computer recursion is valuable.

The sum of integers: another example

$$S_n = \sum_{k=0}^{n} k = \frac{n(n+1)}{2}$$

This formula also has a recursive form: $S_n = S_{n-1} + n$, with $S_0 = 0$.

Structure of a recursive CTE in T-SQL

A recursive CTE in T-SQL always follows this pattern:

;WITH nom_cte (colonne1, colonne2, ...) AS (

    -- 1. Membre ancre (anchor member) : cas de base, non récursif
    SELECT ...

    UNION ALL  -- Obligatoire entre anchor et membre récursif

    -- 2. Membre récursif : fait référence au CTE lui-même
    SELECT ...
    FROM nom_cte
    WHERE -- condition d'arrêt
)

-- Requête externe
SELECT ... FROM nom_cte
OPTION (MAXRECURSION n)  -- Filet de sécurité contre les boucles infinies

Basic rules:

• Recursive member must be preceded by UNION ALL
• Recursive member references the CTE being defined
• Recursive member WHERE clause defines stopping condition
• The MAXRECURSION option limits the number of recursive calls (security)

Comparison: iterative version of Fibonacci

-- Fibonacci en itératif (WHILE loop)

DECLARE @fib TABLE (Fn int, value bigint)

DECLARE @Fn_1 INT = 1,
        @Fn_2 INT = 0

INSERT INTO @fib (Fn, value) VALUES (0, 0)

DECLARE @Fn INT = 2, @maxFn int = 10
WHILE @Fn <= @maxFn
BEGIN
    DECLARE @value INT = @Fn_1 + @Fn_2

    INSERT INTO @fib (Fn, value) VALUES (@Fn, @value)

    SET @Fn_2 = @Fn_1
    SET @Fn_1 = @value    
    SET @Fn += 1
END

SELECT Fn, value
FROM @fib
ORDER BY Fn

GO

2.5 Demo: Simple Recursive Queries

Example 1: Generate natural numbers

-- Génération des nombres naturels (0 à 9)

;WITH Naturals (n)
AS (
    SELECT 0 AS n        -- Membre ancre : cas de base
    
    UNION ALL
    
    SELECT n + 1         -- Membre récursif : s'incrémente de 1
    FROM Naturals
    WHERE n <= 9         -- Condition d'arrêt
)

SELECT n
FROM Naturals
ORDER BY n
OPTION (MAXRECURSION 10)  -- Filet de sécurité : max 10 niveaux

GO

Explanation of MAXRECURSION: This option OPTION (MAXRECURSION n) is a safety net against infinite loops. It is highly recommended when developing new recursive queries. The SQL Server default is 100; a value of 0 means unlimited number of recursions.

Example 2: Generate even numbers with depth tracking

-- Génération des nombres pairs avec colonne de profondeur de récursion

;WITH Naturals (n, rd)
AS (
    SELECT 0 AS n, 0 as rd   -- Membre ancre
    
    UNION ALL
    
    SELECT n + 2, rd + 1     -- Incrément de 2 pour nombres pairs ; rd suit la profondeur
    FROM Naturals
    WHERE n <= 18            -- Condition d'arrêt
)

SELECT n, rd
FROM Naturals
ORDER BY n
OPTION (MAXRECURSION 10)

GO

Debugging Tip: The rd (recursion depth) column is a handy tool when developing recursive queries to visualize how many levels have been traversed.

Example 3: Recursive Fibonacci in T-SQL

-- Suite de Fibonacci F0 à F11 -- Récursif

;WITH Fib (Fn, Fn_Cur, Fn_Next) 
AS (
   -- Membre ancre : deux premières valeurs de la suite
   SELECT 0 AS Fn, 0 AS Fn_Cur, 1 as Fn_Next

   UNION ALL

   -- Membre récursif : calcule le prochain nombre Fibonacci
   SELECT  Fn + 1,
           Fn_Next, 
           CASE Fn 
                WHEN 1 Then 1 
                ELSE Fn_Cur + Fn_Next
           END
   FROM Fib
   WHERE Fn <= 10    -- Condition d'arrêt : 11 nombres au total
)   
-- Affichage des résultats
SELECT Fn, Fn_Next as value FROM Fib
GO

Why not use LAG() in a recursive CTE? During a first attempt, one might be tempted to use LAG() in the recursive part. However, window functions like LAG do not work as expected in a recursive CTE, because only the current rowset is available during the recursive call — previous rows are not accessible via window functions.

2.6 T-SQL restrictions for recursive queries

The following operators and expressions are not allowed in the recursive part of a CTE:

Subtle limitation: Only the current rowset is available during the recursive call. Aggregations across all rows generated up to this point will not work as expected. See the official Microsoft documentation for the complete list and version restrictions.

2.7 Demo: Navigation of a data hierarchy by recursion

Use case: Bill of Materials (bill of materials)

This is the most common use case for recursive CTEs: navigating a hierarchy. In this demo, the AdventureWorks database is used to find all the components of a mountain bike — what we call a Bill of Materials (BOM).

Preliminary data exploration

-- Explorer les catégories de produits
SELECT * FROM Production.ProductCategory

-- Sous-catégories de bicyclettes (CategoryID = 1)
SELECT * FROM Production.ProductSubcategory
WHERE ProductCategoryID = 1

-- Produits dans la sous-catégorie VTT (SubcategoryID = 1)
SELECT * FROM Production.Product
WHERE ProductSubcategoryID = 1

-- Aperçu de la table Bill of Materials
SELECT TOP(10) * FROM Production.BillOfMaterials

The BillOfMaterials table has a foreign key to the Product table: the ProductID in Product matches the ComponentID in BillOfMaterials. It is this self-reference that allows recursive navigation.

Recursive query: Complete nomenclature of a bicycle

-- Requête récursive : tous les composants du vélo Mountain-500 Black, 52 (ProductID = 993)

DECLARE @ProductID INT = 993; -- Mountain-500 Black, 52

;WITH bom
    (ProductAssemblyID, ComponentID, ComponentDesc, PerAssemblyQty, 
     StandardCost, ListPrice, BOMLevel, SortOrder)
AS (

    -- Membre ancre : composants de premier niveau (sous-ensembles directs)
    SELECT b.ProductAssemblyID
        , b.ComponentID
        , p.Name
        , b.PerAssemblyQty
        , p.StandardCost
        , p.ListPrice
        , b.BOMLevel
        , CAST(b.ComponentID AS VARCHAR(100)) AS SortOrder
    FROM Production.BillOfMaterials b
    INNER JOIN Production.Product p
        ON b.ComponentID = p.ProductID
    WHERE b.ComponentID = @ProductID

    UNION ALL
    
    -- Membre récursif : sous-composants de chaque composant parent
    SELECT b.ProductAssemblyID
        , b.ComponentID
        , p.Name
        , b.PerAssemblyQty
        , p.StandardCost
        , p.ListPrice
        , b.BOMLevel
        , CAST(CONCAT(bom.SortOrder, '.', b.ComponentID) AS VARCHAR(100)) AS SortOrder
    FROM bom
    INNER JOIN Production.BillOfMaterials b
        ON b.ProductAssemblyID = bom.ComponentID
    INNER JOIN Production.Product p
        ON b.ComponentID = p.ProductID
)

-- Requête externe : agrégation et présentation de la hiérarchie
SELECT bom.ProductAssemblyID
    , bom.ComponentID
    , REPLICATE('.', bom.BOMLevel) + bom.ComponentDesc AS ComponentName
    , SUM(bom.PerAssemblyQty) AS TotalQuantity
    , bom.ListPrice AS UnitPrice
    , SUM(bom.ListPrice) AS ExtendedPrice
    , bom.BOMLevel
FROM bom
GROUP BY bom.SortOrder
    , bom.ComponentID
    , bom.ComponentDesc
    , bom.ProductAssemblyID
    , bom.BOMLevel
    , bom.StandardCost
    , bom.ListPrice
ORDER BY bom.SortOrder
OPTION (MAXRECURSION 5)

Notable points:

• The SortOrder column is constructed by concatenating component IDs with dots (e.g.: 993.456.789), which allows a natural sort representing the hierarchy.
• REPLICATE('.', bom.BOMLevel) visually indents components according to their level in the hierarchy.
• OPTION (MAXRECURSION 5) limits the recursion depth to 5 levels, which is sufficient for this BOM.

2.8 Module 2 Summary

This module covered queries constructed using the WITH statement (CTE). In summary:

• The non-recursive variant was introduced to show its power and usefulness, particularly as a more readable alternative to subqueries.
• The recursive variant was introduced with its operating mechanism: anchor member + UNION ALL + recursive member.
• Simple examples (natural numbers, Fibonacci) made it possible to understand the syntax before tackling more complex cases.
• A subtle limitation has been highlighted: only the current rowset is available during the recursive call, which prevents certain types of aggregations.
• Hierarchy navigation (Bill of Materials) is the most common application of recursive CTE in SQL Server.

Performance considerations:

• Recursive CTEs can degenerate into line-by-line operations. This can happen on deep hierarchies where each level has only one or two nodes.
• A custom query using JOIN and UNION can outperform a recursive query if we know the maximum recursion depth.
• SQL Server limits recursion to a maximum of 32,767 recursive calls.

3. Data aggregation with PIVOT and UNPIVOT clauses

Module duration: 20 minutes 1 second

3.1 Introduction to PIVOT and UNPIVOT

Data in relational databases is often compared to spreadsheets with rows and columns. The pivot operation is familiar to users of spreadsheets (like Excel). In SQL Server, the PIVOT clause allows you to perform this operation directly in the database.

Why pivot in the database rather than in a spreadsheet?

• If hundreds or thousands of users pivot in Excel, everyone must access the source data — which can impact performance and security.
• Moving processing to the database leverages the power of the server (usually much more capable than desktops) and centralizes security logic.
• A table with millions or billions of rows accessed by many users simultaneously during peak hours can cause serious problems.

Module Summary:

1. Review of the pivot in modern spreadsheets (Excel) to lay the foundations
2. Syntax of the PIVOT clause with AdventureWorks
3. Single PIVOT vs cross-tab
4. Multi-pivot (multi-column pivot)
5. Dynamic PIVOT (unknown columns at the time of writing the query)
6. UNPIVOT clause and its equivalent CROSS APPLY

3.2 Demo: Pivot in a spreadsheet

To illustrate the problem, a demonstration in Microsoft Excel shows how to create a Pivot Table from data in the AdventureWorks Sales.SalesOrderDetail table:

• SalesOrderID as row label
• ProductID as column label
• LineTotal as value

Although this operation is easy in Excel, it suffers from the limitations mentioned above (concurrent access, security, performance). The solution is to move this logic into the database with the PIVOT clause.

3.3 Demo: Cross-tabulation in Transact-SQL

Before using the PIVOT clause, here’s how to perform a cross-tabulation with conditional functions — a technique still available for simple cases.

-- Quels produits ont des IDs inférieurs ou égaux à 715 ?

USE AdventureWorks2022
GO

SELECT DISTINCT [ProductID]
FROM  Sales.SalesOrderDetail
WHERE ProductID <= 715 
ORDER BY ProductID

GO

-- Cross-tabulation : somme des LineTotal par ProductID

;WITH subset AS (
    SELECT  od.SalesOrderID, od.ProductID, od.LineTotal
    FROM    Sales.SalesOrderDetail AS od
    WHERE   ProductID <= 715
)

SELECT SalesOrderID,
    SUM(IIF(ProductID = 707, LineTotal, 0)) AS [707],
    SUM(IIF(ProductID = 708, LineTotal, 0)) AS [708],
    SUM(IIF(ProductID = 709, LineTotal, 0)) AS [709],
    SUM(IIF(ProductID = 710, LineTotal, 0)) AS [710],
    SUM(IIF(ProductID = 711, LineTotal, 0)) AS [711],
    SUM(IIF(ProductID = 712, LineTotal, 0)) AS [712],
    SUM(IIF(ProductID = 713, LineTotal, 0)) AS [713],
    SUM(IIF(ProductID = 714, LineTotal, 0)) AS [714],
    SUM(IIF(ProductID = 715, LineTotal, 0)) AS [715]

FROM subset
GROUP BY SalesOrderID
ORDER BY SalesOrderID

GO

How ​​it works: For each ProductID, the value of LineTotal is included in the sum if the ProductID matches; otherwise, 0 is used. Each sum is aliased by the desired column name. We could have used a CASE instead of IIF, but IIF is more concise.

3.4 Understanding PIVOT syntax

The complete syntax of a PIVOT request follows this pattern:

SELECT
    [colonnes_non_pivotées],          -- Colonnes qui restent "telles quelles"
    [valeurs_pivotées]                -- Colonnes issues du pivot

FROM source_de_données               -- Table, vue, CTE, sous-requête (pas de stored proc)

PIVOT (
    fonction_agregation(colonne_valeur)   -- Ex: SUM(LineTotal)
    FOR colonne_source IN (              -- Colonne dont les valeurs deviennent des noms de colonnes
        [val1], [val2], [val3], ...      -- Valeurs à pivoter (entre crochets pour identifiants numériques)
    )
) AS alias_pivot                     -- Alias obligatoire

ORDER BY ...

Detail of each element:

Note: Numeric values ​​in the IN clause must be enclosed in square brackets [ ] because SQL Server identifiers cannot begin with a number.

3.5 Demo: Using PIVOT in Transact-SQL

Reproduction of the result obtained with cross-tabulation, but using the PIVOT clause:

-- PIVOT pour générer un rapport

USE AdventureWorks2022
GO

;WITH subset AS (  -- Source de données pour PIVOT

    SELECT od.SalesOrderID, od.ProductID, od.LineTotal
    FROM   Sales.SalesOrderDetail AS od
    WHERE  od.ProductID <= 715
)

SELECT SalesOrderID, [707], [708], [709], [710], [711], [712], [713], [714], [715]
FROM subset

-- La clause PIVOT fait le travail de la cross-tabulation

PIVOT (

    SUM(LineTotal)
    FOR ProductID IN (
        [707], [708], [709], [710], [711], [712], [713], [714], [715])
) AS pvt

ORDER BY pvt.SalesOrderID

GO

Results: The results match those of the Excel spreadsheet and cross-tabulation. The difference: PIVOT returns NULL when there is no value (cross-tab returns 0).

3.6 Demo: PIVOT on several columns (Multi-Pivot)

Sometimes you want to pivot on several columns simultaneously. In this example, we want both the total amount and the ordered quantity for each product.

-- Multi-pivot : prix total ET quantité par produit

USE AdventureWorks2022;
GO

;WITH subset AS (

    SELECT od.SalesOrderID, 
           od.ProductID, 
           CONCAT('qty', od.ProductID) AS ProdIdQty,  -- Colonne dérivée : 'qty707', etc.
           od.OrderQty, 
           od.LineTotal AS Price
    FROM Sales.SalesOrderDetail AS od
    WHERE ProductID <= 715
)

SELECT SalesOrderID, 
    [707], [qty707], [708], [qty708], [709], [qty709], [710], [qty710]
FROM  subset

PIVOT (     -- Premier pivot : somme des prix par ProductID
    SUM(Price)
    FOR ProductID IN (
        [707], [708], [709], [710])
) AS pvt1

PIVOT(      -- Second pivot : somme des quantités par ProdIdQty
    SUM(OrderQty)
    FOR ProdIdQty IN (
        [qty707], [qty708], [qty709], [qty710])
) AS pvt2

ORDER BY SalesOrderID

GO

Why create the ProdIdQty column? A source column can only be used once per PIVOT query. If we try to put ProductID in the second PIVOT, SQL Server returns Invalid column name 'ProductId'. The solution is to create a derived column with a different name (ProdIdQty = 'qty' + ProductID) and then use it in the second pivot.

Rule: A source column can only be aggregated once per PIVOT query.

3.7 Demo: Dynamic PIVOT

In the previous examples, the product IDs to pivot were hardcoded. But what to do when the values ​​are not known in advance? This is the role of dynamic PIVOT.

Use case: Top 10 sales by region, which changes monthly.

Step 1: Identify the top 10 products

USE AdventureWorks2022
GO

-- Top 10 des vélos les plus vendus
SELECT TOP(10) od.ProductID, p.Name AS ProductName, SUM(od.LineTotal) Sum_LineTotal
FROM sales.SalesOrderHeader oh
JOIN sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
JOIN Production.Product p       ON od.ProductID = p.ProductID 
GROUP BY od.ProductID, p.Name
ORDER BY Sum_LineTotal DESC

GO

Step 2: Static PIVOT with the top 10

-- PIVOT statique avec les IDs des top 10 produits

;WITH Top_Sales(Region, Territory, ProductId, Sales) AS (

    SELECT st.CountryRegionCode, st.name, p.ProductID, 
           CAST(SUM(od.LineTotal) AS DECIMAL(8,2)) Sum_LineTotal
    FROM sales.SalesOrderHeader oh
    JOIN sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
    JOIN sales.SalesTerritory st    ON oh.TerritoryID = st.TerritoryID
    JOIN Production.Product p       ON od.ProductID = p.ProductID 
    GROUP BY st.CountryRegionCode, st.Name, p.ProductID
)

SELECT Region, Territory, [782],[783],[779],[780],[781],[784],[793],[794],[795],[753]
FROM Top_Sales

PIVOT (
    SUM(Sales)
    FOR ProductID IN (
        [782],[783],[779],[780],[781],[784],[793],[794],[795],[753]
)) AS pvt1

ORDER BY Region, Territory

GO

Step 3: Building the column list dynamically

-- Construire la liste de colonnes dynamiquement (SQL Server 2016 et antérieur)

DECLARE @Cols NVARCHAR(100) = 
(
    SELECT TOP(10) ', ' + QUOTENAME(od.ProductID)
    FROM sales.SalesOrderHeader oh
    JOIN sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
    JOIN Production.Product p       ON od.ProductID = p.ProductID 
    GROUP BY od.ProductID, p.Name
    ORDER BY SUM(od.LineTotal) DESC
    FOR XML PATH('')
)

SET @Cols = SUBSTRING(@cols, 3, 100)
PRINT @Cols

-- Méthode alternative pour SQL Server 2017 et versions ultérieures (STRING_AGG)
SELECT STRING_AGG(QUOTENAME(s.ProductID), ', ')
FROM (
    SELECT TOP 10 od.ProductID
    FROM sales.SalesOrderHeader oh
    JOIN sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
    JOIN Production.Product p       ON od.ProductID = p.ProductID 
    GROUP BY od.ProductID, p.Name
    ORDER BY SUM(od.LineTotal) DESC
) s

QUOTENAME: This function wraps the identifier in square brackets [ ], avoiding problems with identifier names starting with numbers or containing special characters. FOR XML PATH(”): Classic technique for concatenating rows into a single string (SQL Server before 2017). STRING_AGG: Available from SQL Server 2017, more readable than FOR XML PATH('').

Step 4: Injecting the dynamic list into the SQL template

-- Template de requête principale

DECLARE @stmt NVARCHAR(MAX) = N'

    ;WITH Top_Sales(Region, Territory, ProductId, Sales) AS (

        SELECT st.CountryRegionCode, st.name, p.ProductID, 
               CAST(SUM(od.LineTotal) AS DECIMAL(8,2)) Sum_LineTotal
        FROM sales.SalesOrderHeader oh
        JOIN sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
        JOIN sales.SalesTerritory st    ON oh.TerritoryID = st.TerritoryID
        JOIN Production.Product p       ON od.ProductID = p.ProductID 
        GROUP BY st.CountryRegionCode, st.Name, p.ProductID
    )

    SELECT Region, Territory, {@Cols}   -- Colonnes dynamiques substituées ici
    FROM Top_Sales

    PIVOT (
        SUM(Sales)
        FOR ProductID IN ({@Cols})      -- Colonnes dynamiques substituées ici
    ) AS pvt1

    ORDER BY Region, Territory
'

-- Injection des noms de colonnes dans le template
SET @stmt = REPLACE(@stmt, '{@Cols}', @Cols)
PRINT @stmt

-- Exécution de la requête dynamique
EXEC sp_executesql @stmt

Security considerations: When constructing dynamic SQL, be careful to never inject unvalidated user values ​​directly into the SQL string — risk of SQL injection. Here, the values ​​come from QUOTENAME() applied to IDs coming from the database itself, which is secure.

3.8 Understanding UNPIVOT syntax

If PIVOT transforms rows into columns, UNPIVOT does the opposite: it transforms columns into rows.

Important: UNPIVOT is not the exact opposite of PIVOT. PIVOT aggregates values, and it is not possible to disaggregate values ​​with UNPIVOT — you cannot “find the egg from the omelet”. Additionally, UNPIVOT removes rows whose value is NULL.

The syntax of an UNPIVOT request:

SELECT
    [colonnes_non_pivotées],          -- Colonnes de données intactes
    colonne_noms_pivotés,             -- Colonne qui recevra les noms des colonnes pivotées
    colonne_valeurs_pivotées          -- Colonne qui recevra les valeurs des colonnes pivotées

FROM source_de_données

UNPIVOT (
    colonne_valeurs_pivotées         -- Colonne qui recevra les valeurs
    FOR colonne_noms_pivotés IN (    -- Colonne qui recevra les noms des colonnes originales
        colonne1, colonne2, ...      -- Colonnes à "dépivoter"
    )
) AS alias_unpivot

ORDER BY ...

3.9 Demo: Using UNPIVOT and CROSS APPLY

Example 1: UNPIVOT on constant data

-- Exemple simple avec des valeurs constantes

USE AdventureWorks2022
GO

;WITH source(id, c1, c2, c3, c4, c5) AS (
    SELECT * FROM (
        VALUES
            ('a', 1, 2, 3, 4, 5),
            ('b', 6, 7, 8, 9, 10) 
    ) v(id, c1, c2, c3, c4, c5)
)

SELECT id, c1_5, val
FROM source
UNPIVOT (val FOR c1_5 IN
    (c1, c2, c3, c4, c5)
) AS upvt

GO

Result: For each id, columns c1 to c5 are transformed into rows. Column c1_5 contains the original column name, and val contains the corresponding value.

Example 2: UNPIVOT on the result of a PIVOT

-- Dépivoter un tableau précédemment pivoté

;WITH subset AS (
    SELECT od.SalesOrderID, od.ProductID, od.LineTotal AS Price
    FROM   Sales.SalesOrderDetail AS od
    WHERE  od.ProductID <= 715
),

pvt AS (
    SELECT SalesOrderID, [707], [708], [709], [710], [711], [712], [713], [714], [715]
    FROM subset
    PIVOT (
        SUM(Price)
        FOR ProductID IN ([707], [708], [709], [710], [711], [712], [713], [714], [715])
    ) AS pvt
)

-- Décommenter la partie UNPIVOT pour voir le résultat dépivotté
SELECT * FROM pvt

-- SELECT SalesOrderID, ProductId, Price
-- FROM pvt
-- UNPIVOT (Price FOR ProductID IN ([707], [708], [709], [710], [711], [712], [713], [714], [715])) AS upvt
-- ORDER BY SalesOrderID

Note: The result will not be identical to the original data because UNPIVOT cannot disaggregate values, and UNPIVOT removes rows whose value is NULL.

Example 3: Equivalent with CROSS APPLY VALUES

CROSS APPLY with VALUES is an alternative to UNPIVOT, considered by some to be more readable and sometimes more efficient:

-- UNPIVOT via CROSS APPLY VALUES (alternative)

-- SELECT SalesOrderID, a.ProductId, Price
-- FROM pvt
-- CROSS APPLY (VALUES 
--     (707,  [707]),
--     (708,  [708]),
--     (709,  [709]),
--     (710,  [710]),
--     (711,  [711]),
--     (712,  [712]),
--     (713,  [713]),
--     (714,  [714]),
--     (715,  [715])           
-- ) a(ProductId, Price)
-- WHERE Price IS NOT NULL
-- ORDER BY SalesOrderID

Advantages of CROSS APPLY VALUES vs UNPIVOT:

• CROSS APPLY VALUES can include NULL values (by removing WHERE Price IS NOT NULL filter)
• Some find it more readable
• May perform better in some scenarios

3.10 Module 3 Summary

This module covered the PIVOT and UNPIVOT clauses in Transact-SQL:

On the PIVOT side:

• Starting with old-fashioned cross-tab, then transitioning to its equivalent PIVOT
• Demonstration of multi-pivot (several PIVOT clauses in the same query)
• Advanced dynamic PIVOT technique with dynamic SQL, for cases where column names are not known in advance

On the UNPIVOT side:

• Simple static example to present the syntax
• Applying to previously rotated data
• Alternative via CROSS APPLY, considered more readable and potentially more efficient

Points to remember:

• UNPIVOT is not the exact opposite of PIVOT: aggregated data cannot be disaggregated
• UNPIVOT automatically removes rows with NULL values
• PIVOT and UNPIVOT are multipurpose tools; practice them on data you already know

4. Data storage according to the EAV (Entity, Attribute, Value) model

Module duration: 20 minutes 35 seconds

4.1 Introduction to EAV model

In relational database design, we sometimes encounter data domains where certain attributes are present less often than others. If these attributes, when present, take up a lot of storage space, a purely relational design can waste space when they are absent. We call these attributes sparse data.

The EAV (Entity, Attribute, Value) model is one of the solutions to this common problem. It is structured around three concepts:

Module Summary:

1. Exploring sparse attributes with the traditional approach
2. Overview of the EAV model and how to implement it
3. Factoring attributes with foreign keys
4. Managing typing and constraints in an EAV
5. Dynamic constraints
6. Alternatives: SPARSE, XML, JSON columns

4.2 Demo: Traditional approach to sparse data

To illustrate the problem of sparse data, this demo creates a table with columns that do not always contain non-null data.

USE AdventureWorks2022
GO

-- Table traditionnelle avec des colonnes optionnelles (sparse)
DROP TABLE IF EXISTS #Person
CREATE TABLE #Person (
    PersonId    INT IDENTITY PRIMARY KEY,
    FirstName   VARCHAR(100) NOT NULL,
    MiddleName  VARCHAR(100),           -- Pas tout le monde a un deuxième prénom
    LastName    VARCHAR(100) NOT NULL,
    HomePhone   CHAR(10),              -- Certains n'ont qu'un mobile
    WorkPhone   CHAR(10),              -- Certains ne travaillent pas
    MobilePhone CHAR(10),              -- Certains n'ont pas de mobile
    -- Autres attributs potentiels (poids de naissance, taille à 19 ans, 
    -- temps au 100m sprint, etc.) - nombreux et souvent NULL
)

GO

-- Insertion de données de test
INSERT INTO #Person (FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone)
VALUES 
    ('Bobs', 'Your', 'Uncle', '2125551212', NULL, NULL),
    ('Carol', NULL, 'Aunt', NULL, '2125551212', '4165551212'),
    ('Ted', 'Eddie', 'Brother', '8008889999', '2145551212', NULL),
    ('Alice', 'Allie', 'Sister', '8008889999', '2145551212', '2125551212')

GO

-- Visualisation des données
SELECT PersonID, FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone
FROM #Person

GO

Highlighted issue: Many columns are NULL. Space must still be allocated in the table for these optional values. The bigger the table, the greater the waste.

Limitations of the traditional approach:

• SQL Server allows up to 1024 columns per table and up to 30,000 sparse columns
• But in SQL, the developer must define all columns up front when creating the table, or have a process to add new columns over time
• In some organizations, modifying a table schema is a cumbersome operation that we want to avoid

4.3 Demo: Refactoring to the EAV model

The fundamental EAV table: only three columns

USE AdventureWorks2022
GO

-- Création de la table EAV : trois colonnes seulement
DROP TABLE IF EXISTS #PersonEAV
CREATE TABLE #PersonEAV(
    Entity      INT,           -- Identifiant de la personne
    Attribute   VARCHAR(100),  -- Nom de l'attribut ('FirstName', 'HomePhone', etc.)
    [Value]     VARCHAR(100),  -- Valeur de l'attribut (NOTE: 'value' est un mot réservé en T-SQL)
)

Important note: The word value is a reserved word in Transact-SQL. It must be surrounded by [Value] brackets when used as a column name.

Data insertion — NULL lines are omitted

INSERT INTO #PersonEAV(Entity, Attribute, [Value])
VALUES
    (1, 'FirstName', 'Bobs'),
    (1, 'MiddleName', 'Your'),
    (1, 'LastName', 'Uncle'),
    (1, 'HomePhone', '2125551212'),
    (2, 'FirstName', 'Carol'),
    -- (2, 'MiddleName', 'Your'),    -- Omis car NULL -> pas de ligne insérée
    (2, 'LastName', 'Aunt'),
    -- (2, 'HomePhone', '2125551212'), -- Omis
    (2, 'WorkPhone', '2125551212'),
    (2, 'MobilePhone', '4165551212'),
    (3, 'FirstName', 'Ted'),
    (3, 'MiddleName', 'Eddie'),
    (3, 'LastName', 'Brother'),
    (3, 'HomePhone', '8008889999'),
    (3, 'WorkPhone', '2145551212'),
    -- (3, 'MobilePhone', '4165551212') -- Omis
    (4, 'FirstName', 'Alice'),
    (4, 'MiddleName', 'Allie'),
    (4, 'LastName', 'Sister'),
    (4, 'HomePhone', '8008881234'),
    (4, 'WorkPhone', '2145551212'),
    (4, 'MobilePhone', '2125551212')

GO

Observations on the EAV table:

• More rows are needed in an EAV table to cover the same data as a relational table
• We omit the lines when the data is absent or NULL — this is the main advantage
• We obtain a thin but tall table: 3 columns, many more rows

Querying EAV data with PIVOT

-- Lecture des données EAV sous forme relationnelle via PIVOT
SELECT Entity as PersonID, FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone
FROM #PersonEAV

PIVOT (
    MAX([Value])
    FOR Attribute IN (FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone)
) pvt
ORDER BY Entity

GO

Why MAX([Value])? The MAX function works for most data types, including VARCHAR. Since the personal data here is all of type VARCHAR, MAX is a good generic choice. We could also use MIN.

4.4 Demo: Factoring attributes with Foreign Keys

Previous demo problem: Attribute names are repeated in each line (eg: ‘FirstName’ appears as many times as there are people). Worse, since names are data, entry errors or inconsistencies can easily arise.

Solution: Create a separate table for attributes, then reference it from the EAV table via a Foreign Key.

USE master
GO

-- Création d'une base de données dédiée
CREATE DATABASE EAV
GO

USE EAV;
GO

-- Table des attributs : source unique de vérité pour les noms d'attributs
DROP TABLE IF EXISTS Attributes
CREATE TABLE Attributes(
    AttributeID INT IDENTITY PRIMARY KEY,    -- ID auto-incrémenté
    Attribute   VARCHAR(100) UNIQUE,          -- Contrainte UNIQUE : pas de doublons
)

-- Insertion des attributs valides
INSERT INTO Attributes (Attribute)
VALUES
    ('FirstName'),
    ('MiddleName'),
    ('LastName'),
    ('HomePhone'),
    ('WorkPhone'),
    ('MobilePhone')

-- Table EAV avec clé étrangère vers la table Attributes
DROP TABLE IF EXISTS PersonEAV
CREATE TABLE PersonEAV(
    Entity      INT, 
    AttributeID INT,                         -- Référence à Attributes.AttributeID
    [Value]     VARCHAR(100),
    CONSTRAINT PK_PersonAV PRIMARY KEY (Entity, AttributeID)  -- Clé primaire composite
)

-- Ajout de la contrainte Foreign Key
ALTER TABLE PersonEAV
   ADD CONSTRAINT FK_Person_Attribute FOREIGN KEY (AttributeID)
      REFERENCES Attributes (AttributeID)

GO

Retrieving attribute IDs for insertion

DECLARE @FirstNameID INT, @MiddleNameID INT, @LastNameID INT, 
        @HomePhoneID INT, @WorkPhoneID INT, @MobilePhoneID INT

SELECT @FirstNameID    = AttributeID FROM Attributes WHERE Attribute = 'FirstName'
SELECT @MiddleNameID   = AttributeID FROM Attributes WHERE Attribute = 'MiddleName'
SELECT @LastNameID     = AttributeID FROM Attributes WHERE Attribute = 'LastName'
SELECT @HomePhoneID    = AttributeID FROM Attributes WHERE Attribute = 'HomePhone'
SELECT @WorkPhoneID    = AttributeID FROM Attributes WHERE Attribute = 'WorkPhone'
SELECT @MobilePhoneID  = AttributeID FROM Attributes WHERE Attribute = 'MobilePhone'

SELECT @FirstNameID AS FirstNameID, @MiddleNameID AS MiddleNameID, 
       @LastNameID AS LastNameID, @HomePhoneID AS HomePhoneID, 
       @WorkPhoneID AS WorkPhoneID, @MobilePhoneID AS MobilePhoneID

-- Insertion dans la table EAV avec les IDs
INSERT INTO PersonEAV(Entity, AttributeId, [Value])
VALUES
    (1, @FirstNameID, 'Bobs'),
    (1, @MiddleNameID, 'Your'),
    (1, @LastNameID, 'Uncle'),
    (1, @HomePhoneID, '2125551212'),
    (2, @FirstNameID, 'Carol'),
    (2, @LastNameID, 'Aunt'),
    (2, @WorkPhoneID, '2125551212'),
    (2, @MobilePhoneID, '4165551212'),
    (3, @FirstNameID, 'Ted'),
    (3, @MiddleNameID, 'Eddie'),
    (3, @LastNameID, 'Brother'),
    (3, @HomePhoneID, '8008889999'),
    (3, @WorkPhoneID, '2145551212'),
    (4, @FirstNameID, 'Alice'),
    (4, @MiddleNameID, 'Allie'),
    (4, @LastNameID, 'Sister'),
    (4, @HomePhoneID, '8008881234'),
    (4, @WorkPhoneID, '2145551212'),
    (4, @MobilePhoneID, '2125551212')

GO

Reading with join and PIVOT

;WITH src(Entity, Attribute, [Value]) AS (
    SELECT p.Entity, a.Attribute, p.[Value]
    FROM PersonEAV p
    JOIN Attributes a ON p.AttributeID = a.AttributeID
)

SELECT Entity as PersonID, FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone
FROM src

PIVOT (
    MAX([Value])
    FOR Attribute IN (FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone)
) pvt
ORDER BY Entity

GO

4.5 Summary of Progress and Remaining Issues

At this stage, we have:

• Replaced multiple columns with a 2-table EAV model (Attributes + PersonEAV)
• Eliminated duplication of attribute names by externalizing them into a dedicated table
• Ensured referential integrity with a Foreign Key

But two major problems remain:

Both of these issues will be addressed in subsequent demos.

4.6 Demo: Managing types and constraints

Data types and SQL_VARIANT

USE AdventureWorks2022;
GO

-- Table avec colonnes typées classiques
DROP TABLE IF EXISTS #types
CREATE TABLE #types (a INT, b DATE, c UNIQUEIDENTIFIER, d DECIMAL(8,2))

-- Tentative d'insertion avec des valeurs invalides -> erreur de type
INSERT INTO #types (a, b, d, c)
    Values ('a', 'b', 'c', 'd')

GO

-- Table avec colonnes de type SQL_VARIANT (accepte différents types)
DROP TABLE IF EXISTS #types
CREATE TABLE #types (a SQL_VARIANT, b SQL_VARIANT, c SQL_VARIANT, d SQL_VARIANT)

-- Insertion avec des données implicitement typées
INSERT INTO #types (a, b, c, d)
    VALUES (42, CURRENT_TIMESTAMP, NEWID(), 3.14)

GO

-- Vérification des types inférés via SQL_VARIANT_PROPERTY
SELECT a as [Value], 
       SQL_VARIANT_PROPERTY(a, 'BaseType')    AS [BaseType], 
       SQL_VARIANT_PROPERTY(a, 'TotalBytes')  AS [TotalBytes], 
       SQL_VARIANT_PROPERTY(a, 'MaxLength')   AS [MaxLength]
FROM #types
UNION
SELECT b, SQL_VARIANT_PROPERTY(b, 'BaseType') AS [BaseType], 
       SQL_VARIANT_PROPERTY(b, 'TotalBytes') AS [TotalBytes], 
       SQL_VARIANT_PROPERTY(b, 'MaxLength') AS [MaxLength]
FROM #types
UNION
SELECT c, SQL_VARIANT_PROPERTY(c, 'BaseType') AS [BaseType], 
       SQL_VARIANT_PROPERTY(a, 'TotalBytes') AS [TotalBytes], 
       SQL_VARIANT_PROPERTY(a, 'MaxLength') AS [MaxLength]
FROM #types
UNION
SELECT d, SQL_VARIANT_PROPERTY(d, 'BaseType') AS [BaseType], 
       SQL_VARIANT_PROPERTY(d, 'TotalBytes') AS [TotalBytes], 
       SQL_VARIANT_PROPERTY(d, 'MaxLength') AS [MaxLength]
FROM #types

GO

SQL_VARIANT: This data type can contain different types up to 8016 bytes maximum. The SQL_VARIANT_PROPERTY function allows you to query metadata of type: BaseType, TotalBytes, MaxLength, Precision, Scale, Collation.

CHECK constraints and constraint naming

-- Table avec contrainte CHECK nommée (bonne pratique)
DROP TABLE IF EXISTS #checks
CREATE TABLE #checks (
    a INT,
    CONSTRAINT [a not <= 42] CHECK (a <= 42))  -- Nom descriptif de la contrainte

-- Insertion avec valeur invalide -> message d'erreur incluant le nom de la contrainte
INSERT INTO #checks(a) VALUES (43)

GO

Best practice: Always name constraints descriptively. When a constraint is violated, the error message includes its name, making diagnosis easier.

Typing simulation and constraints in an EAV

-- Table EAV avec SQL_VARIANT pour les valeurs
DROP TABLE IF EXISTS #EAV
CREATE TABLE #EAV (EntityID int, AttributeID int, [Value] SQL_VARIANT)

-- Variables de test
DECLARE @EntityID int = 42
DECLARE @AttributeID int = 42

-- DECLARE @Value42 SQL_VARIANT = 42            -- Valeur valide
DECLARE @Value42 SQL_VARIANT = 'The Answer'  -- Valeur invalide (string au lieu d'int)

-- Vérification manuelle du type et de la valeur avant insertion
DECLARE @msg VARCHAR(100) = (
    SELECT CASE
        WHEN SQL_VARIANT_PROPERTY(@Value42, 'BaseType') <> 'int'
            THEN 'Value is not an integer'
        WHEN CAST(@Value42 AS INT) > 42
            THEN 'Value not <= 42'
    END AS msg
)    

PRINT @msg

IF @msg IS NULL 
    BEGIN
        INSERT INTO #EAV (EntityID, AttributeID, [Value])
        VALUES (@EntityID, @AttributeID, @Value42)
    END
ELSE 
    THROW 51000, @msg, 1

GO

4.7 Demo: Dynamic constraints

To avoid having to write the validation code manually on each insert, it is possible to store the validation logic in the attributes table and execute it dynamically with sp_executesql.

Setting up tables with dynamic constraints

USE EAV;
GO

-- Validation via SQL dynamique
-- DECLARE @Value42 SQL_VARIANT = 42          -- Valeur valide
DECLARE @Value42 SQL_VARIANT = 'The Answer' -- Valeur invalide

DECLARE @DynamicConstraint NVARCHAR(MAX) = N'
    SELECT @msg = CASE
        WHEN SQL_VARIANT_PROPERTY(@Value, ''BaseType'') <> ''int''
            THEN ''Value is not an integer''
        WHEN CAST(@Value AS INT) > 42
            THEN ''Value not <= 42''
    END
'

DECLARE @msg VARCHAR(100)

EXEC sp_executesql 
    @stmt   = @DynamicConstraint, 
    @params = N'@Value sql_variant, @msg varchar(100) OUTPUT', 
    @Value  = @Value42, 
    @msg    = @msg OUTPUT

PRINT @msg

GO

Attribute table with Constraint column

-- Tables avec support des contraintes dynamiques

DROP TABLE IF EXISTS Attributes
CREATE TABLE Attributes(
    AttributeID  INT IDENTITY PRIMARY KEY, 
    Attribute    VARCHAR(100) UNIQUE,
    [Constraint] NVARCHAR(4000),  -- Colonne stockant le SQL de validation
)

DROP TABLE IF EXISTS PersonEAV
CREATE TABLE PersonEAV(
    Entity      INT, 
    AttributeID INT,
    [Value]     SQL_VARIANT,  -- SQL_VARIANT pour supporter plusieurs types
    CONSTRAINT PK_PersonAV PRIMARY KEY (Entity, AttributeID)
)

ALTER TABLE PersonEAV
   ADD CONSTRAINT FK_Person_Attribute FOREIGN KEY (AttributeID)
      REFERENCES Attributes (AttributeID)

GO

Inserting a constraint into the Attributes table

DECLARE @DynamicConstraint NVARCHAR(MAX) = N'
    SELECT @msg = CASE
        WHEN SQL_VARIANT_PROPERTY(@Value, ''BaseType'') <> ''int''
            THEN ''Value is not an integer''
        WHEN CAST(@Value AS INT) > 42
            THEN ''Value not <= 42''
    END
'

INSERT INTO Attributes(Attribute, [Constraint])
    Values('TheAnswer', @DynamicConstraint)

SELECT * FROM Attributes

GO

Use of dynamic constraint before insertion

DECLARE @stmt NVARCHAR(4000) = (
    SELECT [Constraint] FROM Attributes WHERE Attribute = 'TheAnswer'
)

PRINT @stmt

DECLARE @Value42 SQL_VARIANT = 42       -- Valeur valide
-- DECLARE @Value42 SQL_VARIANT = 'The Answer' -- Valeur invalide

DECLARE @msg VARCHAR(100)

EXEC sp_executesql 
    @stmt   = @stmt, 
    @params = N'@Value sql_variant, @msg varchar(100) OUTPUT', 
    @Value  = @Value42, 
    @msg    = @msg OUTPUT

PRINT @msg

DECLARE @TheAnswerID INT = (
    SELECT AttributeID FROM Attributes WHERE Attribute = 'TheAnswer'
)

IF @msg IS NULL 
    BEGIN
        INSERT INTO PersonEAV (Entity, AttributeID, [Value])
        VALUES (42, @TheAnswerID, @Value42)
    END
ELSE 
    THROW 51000, @msg, 1

SELECT * FROM PersonEAV

GO

Advantage: Each attribute can have its own validation logic stored in the database. To add a new attribute with specific rules, simply insert a new row in the Attributes table — no code changes required.

4.8 Demo: SPARSE columns

SPARSE columns are available since SQL Server 2008. They are beneficial when more than 20% of columns are often NULL, depending on data types.

Advantages of SPARSE columns:

• Occupy no space when value is NULL or 0
• You can use ordinary DML statements (SELECT, INSERT, UPDATE, DELETE) by referencing sparse columns by name
• Constraints can be defined on sparse columns as on normal columns
• Columns are typed (no loss of typing)
• Up to 30,000 sparse columns per table

-- Table avec colonnes SPARSE

DROP TABLE IF EXISTS #Person
CREATE TABLE #Person (
    PersonId    INT IDENTITY PRIMARY KEY,
    FirstName   VARCHAR(100) NOT NULL,        -- Non-sparse : toujours présent
    MiddleName  VARCHAR(100) SPARSE,          -- Sparse : souvent NULL
    LastName    VARCHAR(100) NOT NULL,         -- Non-sparse : toujours présent
    HomePhone   CHAR(10) SPARSE,              -- Sparse : optionnel
    WorkPhone   CHAR(10) SPARSE,              -- Sparse : optionnel
    MobilePhone CHAR(10) SPARSE,              -- Sparse : optionnel
    TheAnswer   INT SPARSE 
    CONSTRAINT [TheAnswer must be <= 42] CHECK (TheAnswer <= 42)  -- Contrainte sur colonne sparse
)

-- Insertion normale (identique à une table non-sparse)
INSERT INTO #Person (FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone, TheAnswer)
VALUES 
    ('Bobs', 'Your', 'Uncle', '2125551212', NULL, NULL, NULL),
    ('Carol', NULL, 'Aunt', NULL, '2125551212', '4165551212', 42),
    ('Ted', 'Eddie', 'Brother', '8008889999', '2145551212', NULL, 42),
    ('Alice', 'Allie', 'Sister', '8008889999', '2145551212', '2125551212', NULL)

SELECT * FROM #Person

-- Test de la contrainte CHECK
INSERT INTO #Person (FirstName, MiddleName, LastName, HomePhone, WorkPhone, MobilePhone, TheAnswer)
VALUES 
    ('Christine', 'A', 'Friend', '8008889999', '2145551212', '2125551212', 43)  -- Erreur attendue !

GO

When to use SPARSE vs EAV? SPARSE columns are an excellent alternative to EAV when you don’t need more than 30,000 columns. They retain the typing, constraints, and simplicity of the relational model, while saving disk space for NULL values. The full discussion of SPARSE columns is beyond the scope of this course.

4.9 Demo: XML as an alternative to EAV

SQL Server 2005 (version 9.x) introduced the XML data type and the syntax to handle it. It has long been a good way to store hierarchically structured data, and is well suited to the use case of a long list of optional attributes.

-- Table avec une colonne XML pour les attributs optionnels

DROP TABLE IF EXISTS #Person
CREATE TABLE #Person (
    PersonId    INT IDENTITY PRIMARY KEY,
    FirstName   VARCHAR(100) NOT NULL,
    MiddleName  VARCHAR(100) NULL,
    LastName    VARCHAR(100) NOT NULL,
    Attributes  XML,               -- Type XML natif de SQL Server
)

-- Insertion : les attributs optionnels sont stockés sous forme XML
INSERT INTO #Person (FirstName, MiddleName, LastName, Attributes)
VALUES 
    ('Bobs', 'Your', 'Uncle', 
        '<Attributes HomePhone="2125551212" />'
        ),
    ('Carol', NULL, 'Aunt', 
        '<Attributes WorkPhone="4165551212" />'
        ),
    ('Ted', 'Eddie', 'Brother', 
        '<Attributes HomePhone="8008889999" WorkPhone="2145551212" />'
        ),
    ('Alice', 'Allie', 'Sister', 
        '<Attributes HomePhone="8008889999" WorkPhone="2145551212" MobilePhone="2125551212" />'
        )

SELECT * FROM #Person

GO

-- Extraction des numéros de téléphone domicile via XQuery
SELECT Firstname, Lastname, 
       Attributes.value('(/Attributes/@HomePhone)[1]', 'nvarchar(10)') AS HomePhone
FROM #Person

GO

Note: XML is a broad topic in SQL Server, far beyond what can be covered here. The value() function is one of SQL Server’s native XML methods for extracting scalar values ​​from an XML column via XQuery.

4.10 Demo: From XML to JSON

JSON is newer than XML in SQL Server. There is no native JSON data type in SQL Server — we use NVARCHAR(MAX).

-- Table avec colonne NVARCHAR(MAX) pour stocker du JSON

DROP TABLE IF EXISTS #Person
CREATE TABLE #Person (
    PersonId    INT IDENTITY PRIMARY KEY,
    FirstName   VARCHAR(100) NOT NULL,
    MiddleName  VARCHAR(100) NULL,
    LastName    VARCHAR(100) NOT NULL,
    Attributes  NVARCHAR(MAX),   -- JSON stocké en NVARCHAR (pas de type JSON natif)
)

-- Insertion avec du JSON valide
INSERT INTO #Person (FirstName, MiddleName, LastName, Attributes)
VALUES 
    ('Bobs', 'Your', 'Uncle', 
        '{"HomePhone": "2125551212"}'
        ),
    ('Carol', NULL, 'Aunt', 
        '{"WorkPhone": "4165551212"}'
        ),
    ('Ted', 'Eddie', 'Brother',   -- Note: 'Brother}' dans le fichier original (coquille)
        '{"HomePhone": "8008889999", "WorkPhone": "2145551212"}'
        ),
    ('Alice', 'Allie', 'Sister', 
        '{"HomePhone": "8008889999", "WorkPhone": "2145551212", "MobilePhone": "2125551212"}'
        )

SELECT * FROM #Person

GO

-- Extraction d'une valeur JSON via JSON_VALUE
SELECT Firstname, Lastname, 
       JSON_VALUE(Attributes, '$.HomePhone') AS HomePhone
FROM #Person

GO

XML vs JSON comparison:

4.11 Module 4 Summary

This module covered sparse data management in SQL Server:

The EAV model:

• Allows you to create in practice a mini database within a database, with only two tables: an attribute table and an EAV table
• Possibility to extend the idea with an entity table for different entity types
• The biggest advantage: no need to predefine columns, new attributes can be added easily and safely

The main disadvantage of the EAV model:

• All eggs in one basket (one table for everything), which can affect performance if the data is large
• We can no longer use the classic SQL operators (SELECT, JOIN, UNION) simply — the use of PIVOT is necessary
• Loss of native typing and constraints (resolved by techniques in this module)

Alternatives:

• SPARSE columns: Up to 30,000 columns, no space occupied when value is NULL/0, typing and constraints preserved — excellent when you know the attributes in advance
• XML: Available since SQL Server 2005, proven, suitable for structured and unstructured data, but a bit verbose
• JSON: Newer, less verbose, directly usable by most modern programming languages

Conclusion: There is no universal solution. Each option has its advantages and disadvantages. Take the time to understand these options and experiment with your own data.

5. Data Sampling in SQL Server

Module duration: 9 minutes 41 seconds

5.1 Introduction to Data Sampling

When faced with very large databases, it may be wise to look for statistical tools to make large problems smaller and more manageable. This is the idea behind data sampling.

Module Summary:

1. Review of what sampling is and the types used in statistical analysis
2. Introduction to the TABLESAMPLE option in Transact-SQL
3. Building queries for common sampling methods
4. Summary

5.2 Data Sampling Terminology

Common Sampling Methods:

5.3 Syntax of the TABLESAMPLE option

In SQL Server, data sampling is performed via the TABLESAMPLE option of the FROM clause.

SELECT ...
FROM table_source alias    -- Table locale ou résultat d'un JOIN
TABLESAMPLE [SYSTEM] (taille_echantillon [PERCENT | ROWS])
[REPEATABLE (seed)]
WHERE ...

Detail of each element:

How TABLESAMPLE works: SQL Server randomly selects data pages (each SQL Server page is 8192 raw bytes). It estimates how many pages are needed, selects a random subset of them, and then returns all the rows on those pages. Since pages are not always full and line sizes may vary, the number of lines returned will not be exactly the specified value. If the specified size is very small, no pages are selected and no rows are returned.

5.4 Demo: Using the TABLESAMPLE option

USE AdventureWorks2022;
GO

-- Requête sans TABLESAMPLE (toutes les données)
SELECT TOP (10) s.OrderQty, s.ProductID, s.LineTotal
FROM Sales.SalesOrderDetail s
    -- TABLESAMPLE (1000 ROWS)
ORDER BY LineTotal DESC;

GO

With TABLESAMPLE:

-- Avec TABLESAMPLE (50 PERCENT) : environ la moitié des commandes
SELECT TOP (10) s.OrderQty, s.ProductID, s.LineTotal
FROM Sales.SalesOrderDetail s
    TABLESAMPLE (50 PERCENT)
ORDER BY LineTotal DESC;

Analysis by region with standard deviation:

USE AdventureWorks2022
GO

-- Analyse des ventes par région avec TABLESAMPLE et écart-type
SELECT TOP(10)
    COUNT(*) AS #Rows, 
    st.CountryRegionCode AS Region, 
    st.name AS Territory, 
    p.ProductID, 
    CAST(SUM(od.LineTotal) AS DECIMAL(8,2)) Sum_LineTotal,
    CAST(STDEV(od.LineTotal) AS DECIMAL(8,2)) StdDev_LineTotal

FROM Sales.SalesOrderHeader oh
TABLESAMPLE (10 PERCENT)  -- REPEATABLE (42) -- Option pour résultats reproductibles
JOIN Sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
JOIN Sales.SalesTerritory st    ON oh.TerritoryID = st.TerritoryID
JOIN Production.Product p       ON od.ProductID = p.ProductID 
GROUP BY st.CountryRegionCode, st.Name, p.ProductID
ORDER BY Sum_LineTotal DESC

GO

Note: TABLESAMPLE applies to the table in the FROM clause — here SalesOrderHeader. Joined tables (SalesOrderDetail, SalesTerritory, Product) are not directly sampled; only rows matching the sample keys of SalesOrderHeader are returned. REPEATABLE (42): The seed 42 will make the results repeatable between executions, as long as the table has not been modified. This is why it’s called “pseudo-random” — a true random number cannot be forced to repeat itself.

5.5 Demo: Stratified Sampling

Stratified sampling involves dividing data into subgroups (strata) based on attributes and then sampling on those subgroups.

USE AdventureWorks2022
GO

-- Strate 1 : VTT (Mountain Bikes)
SELECT TOP(10)
    COUNT(*) AS #Rows, 
    st.CountryRegionCode AS Region, 
    st.name AS Territory, 
    p.Name, 
    CAST(SUM(od.LineTotal) AS DECIMAL(8,2)) Sum_LineTotal,
    CAST(STDEV(od.LineTotal) AS DECIMAL(8,2)) StdDev_LineTotal

FROM Sales.SalesOrderHeader oh
TABLESAMPLE (10 PERCENT)  -- REPEATABLE (42)
JOIN Sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
JOIN Sales.SalesTerritory st    ON oh.TerritoryID = st.TerritoryID
JOIN Production.Product p       ON od.ProductID = p.ProductID 
WHERE p.Name LIKE 'Mountain%'   -- Filtre de strate : Mountain Bikes seulement
GROUP BY st.CountryRegionCode, st.Name, p.Name
ORDER BY Sum_LineTotal DESC

GO

-- Strate 2 : Vélos de route (Road Bikes)
SELECT TOP(10)
    COUNT(*) AS #Rows, 
    st.CountryRegionCode AS Region, 
    st.name AS Territory, 
    p.Name, 
    CAST(SUM(od.LineTotal) AS DECIMAL(8,2)) Sum_LineTotal,
    CAST(STDEV(od.LineTotal) AS DECIMAL(8,2)) StdDev_LineTotal

FROM Sales.SalesOrderHeader oh
TABLESAMPLE (10 PERCENT)  -- REPEATABLE (42)
JOIN Sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
JOIN Sales.SalesTerritory st    ON oh.TerritoryID = st.TerritoryID
JOIN Production.Product p       ON od.ProductID = p.ProductID 
WHERE p.Name LIKE 'Road%'       -- Filtre de strate : Road Bikes seulement
GROUP BY st.CountryRegionCode, st.Name, p.Name
ORDER BY Sum_LineTotal DESC

GO

Principle: We start from the query from the previous demo and duplicate it by adding WHERE clauses to subdivide the products into mountain bikes and road bikes. The TABLESAMPLE clause then operates on each filtered subset.

5.6 Demo: Cluster Sampling

Cluster sampling differs from the previous two demos: the TABLESAMPLE clause is not used. The idea is to first divide the data into clusters (clusters/groups), then randomly select a subset of these clusters. Each selected cluster is treated as a mini-dataset and all its members are included in the sample.

USE AdventureWorks2022
GO

-- Sélection aléatoire d'une famille de vélos parmi Mountain, Road, Touring
DECLARE @RandomInt int = FLOOR(RAND()*3+1)  -- Entier aléatoire entre 1 et 3

-- Analyse des ventes pour la famille de vélos sélectionnée aléatoirement
SELECT TOP(10)
    COUNT(*) AS #Rows, 
    st.CountryRegionCode AS Region, 
    st.name AS Territory, 
    p.Name,
    CAST(SUM(od.LineTotal) AS DECIMAL(8,2)) Sum_LineTotal,
    CAST(STDEVP(od.LineTotal) AS DECIMAL(8,2)) StdDevP_LineTotal

FROM Sales.SalesOrderHeader oh
JOIN Sales.SalesOrderDetail od  ON oh.SalesOrderID = od.SalesOrderID
JOIN Sales.SalesTerritory st    ON oh.TerritoryID = st.TerritoryID
JOIN Production.Product p       ON od.ProductID = p.ProductID 
WHERE p.Name LIKE CHOOSE(@RandomInt, 'Mountain', 'Road', 'Touring') + '%'
-- CHOOSE(@RandomInt, 'Mountain', 'Road', 'Touring') retourne 
-- 'Mountain' si @RandomInt=1, 'Road' si 2, 'Touring' si 3
GROUP BY st.CountryRegionCode, st.Name, p.Name
ORDER BY Sum_LineTotal DESC

Functions used:

Open question: In this example, only one bike family is selected. How to select more than one family? One could use multiple calls to RAND() to obtain several different indices, or use a table variable to store the selected families and perform a JOIN on them.

5.7 Module 5 Summary

This module covered data sampling in SQL Server:

• Review of what data sampling is and the various methods used
• Presentation of the syntax of the TABLESAMPLE option and its sub-options
• Practical demonstrations of sampling queries with and without TABLESAMPLE

Important points:

• Random sampling via TABLESAMPLE is a fast and efficient method, but returns an approximate number of rows (based on SQL Server pages)
• stratified sampling requires adding WHERE clauses to subdivide the data into strata
• Cluster sampling does not require TABLESAMPLE — we randomly select an entire category
• Convenience sampling was intentionally not covered because in a database all data is equally accessible — so there is no need for special queries
• SQL Server limits TABLESAMPLE sources to local tables — no views, subqueries, CTEs or table functions

6. References and additional resources

Official Microsoft Documentation

• Common Table Expressions (WITH) — Microsoft Docs
• PIVOT and UNPIVOT — Microsoft Docs
• TABLESAMPLE — Microsoft Docs
• Sparse Columns — Microsoft Docs
• SQL_VARIANT Data Type — Microsoft Docs
• JSON in SQL Server — Microsoft Docs
• XML in SQL Server — Microsoft Docs
• sp_executesql — Microsoft Docs

Recommended Pluralsight training courses

• Building Advanced SQL Server Tables and Views
• Statistical Analysis and Big Data
• Using XML and JSON in SQL Server

Historical resources mentioned in the course

• E.W. Dijkstra — “Go-To Statement Considered Harmful”: available in the ACM archives

Tools and environment

Search Terms

querying · techniques · sql · server · microsoft · databases · data · pivot · recursive · unpivot · constraints · dynamic · eav · queries · sampling · constraint · column · fibonacci · model · statement · syntax · t-sql · transact-sql · apply