SQL Server vu par Christian Robert

SQL Server : Générer une table de nombres ou une fonction

Les tables de nombre sont très utiles lors de certaines requêtes complexes. Elles peuvent par exemple servir à compléter des trous entre des dates, réaliser des incréments de manières simple, etc.

Comment générer une telle table ?

Voici plusieurs méthodes pour cela…

Création d'une nouvelle table

Il est possible de constituer une table contenant les nombres en question :

CREATE TABLE dbo.Nombres(nums INT NOT NULL PRIMARY KEY)

La clef primaire permet d'avoir un index définie sur le champ, ce qui permettra un gain de vitesse le cas échéant pour certaines jointures.

Puis on procède au remplissage de la table

DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nombres VALUES(1);

WHILE @rc * 2 <= @max
BEGIN

INSERT INTO dbo.Nombres SELECT nums + @rc FROM dbo.Nombres;

SET @rc = @rc * 2;

END

INSERT INTO dbo.Nombres
SELECT nums + @rc FROM dbo.Nombres WHERE nums + @rc <= @max;
GO

On pourrait aussi faire une boucle simple, mais cette méthode permet de remplir la table beaucoup plus rapidement. La méthode est reprise des exemples des excellents livres : « Inside TSQL » http://www.sql.co.il/books/insidetsql2005/

Utilisation de spt_values

Il est possible d'utiliser une table particulière qui existe dans la base de données « master » qui contient tout un tas de constantes utiles à SQL Server. On retrouve parmi ces constantes, des nombres de 0 à 2047 ce qui peut se révéler utile, dans la mesure où cette table existe depuis au moins SQL Server 7.

SELECT number FROM master.dbo.spt_values WHERE type = 'P'

Il n'y a que 2048 valeurs, mais au moins c'est disponible quelque soit le serveur :o)

Création d'une fonction de type table

Dernière solution, disponible uniquement avec SQL Server 2005, une fonction de type table, renvoyant une liste de valeur dont on fournit la limite maximale en paramètre

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN

WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO

On appellera la fonction de cette manière :

      select * from dbo.fn_nums(100)

La méthode est reprise des exemples des excellents livres : « Inside TSQL » http://www.sql.co.il/books/insidetsql2005/

Bon compte…