Criando uma tabela com uma seqüência de números – Parte II

Boa Tarde Pessoal,

Há algum tempo atrás, demonstrei como criar uma tabela de números no SQL Server. A utilização de uma tabela de números é útil em algumas consultas como "Seleção de datas não armazenadas" e "O problema dos IDs ausentes". A primeira vez que demonstrei como fazer isso, utilizei o recurso de Common Table Expression (CTE) especificado no padrão ANSI99 e disponível a partir do SQL Server 2005. Analisando as estatísticas de acesso do blog, tenho notado que esse é um dos artigos de maior acesso. Sendo assim, resolvi atualizá-lo com outras formas performáticas de obter a tabela de números.

Tabela de números com o SQL Server 2005

O uso da CTE pode ser feito para se especificar os valores necessários e ser utilizada posteriormente. Ex:

WITH Nums (Num) As (
SELECT 0 UNION ALL SELECT 1 UNION ALL
SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9)

SELECT (N1.Num * 10) + N2.Num + 1 As Num
FROM Nums As N1 CROSS JOIN Nums As N2
ORDER BY Num

Nas últimas semanas, o Fabiano Neves Amorim me surpreendeu com um código bem interessante que produz a mesma coisa com menos linhas de código. Ele utilizou-se da CTE também, mas de forma recursiva. Adaptando a solução por ele proposta teríamos a seguinte construção:

WITH Nums (Num) As (
    SELECT 1
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num < 100)

SELECT Num FROM Nums

A evolução do script permite um encapsulamento em uma function. Ex:

CREATE FUNCTION dbo.FNRetNum (@Limite TINYINT)
RETURNS TABLE
AS RETURN
(

WITH Nums (Num) As (
    SELECT 1
    UNION ALL
    SELECT Num + 1 FROM Nums
    WHERE Num <
        CASE WHEN @Limite >= 100 THEN 100
        ELSE @Limite END)

SELECT Num FROM Nums)

GO

— Retornar os números de 1 a 3
SELECT Num FROM dbo.FNRetNum(3)

— Retornar os números de 1 a 8
SELECT Num FROM dbo.FNRetNum(8)

— Retornar os números de 1 a 9
SELECT Num FROM dbo.FNRetNum(9)

Caso haja necessidade de retornar mais de 100 números é possível combinar mais de uma execução dessa função através de um CROSS JOIN (semelhante à implementação anterior). O limite de 100 foi estipulado porque por padrão a CTE é limitada a 100 níveis de recursividade (embora seja possível de ser superado com o MAXRECURSION). Uma tabela de 100 números também constitui uma boa base para implementações maiores através do CROSS JOIN.

Tabela de Números com o SQL Server 2008

O SQL Server 2008 evoluiu o T-SQL e adicionou novas construções. Seguinda a linha do artigo anterior, é possível construir uma tabela com 10 números e apartir daí combiná-los de forma a obter conjuntos maiores como 100, 1.000, 10.000, etc.

SELECT (N1.Num * 10) + N2.Num + 1 As Num FROM
(VALUES
    (0),(1),(2),(3),(4),
    (5),(6),(7),(8),(9)) As N1 (Num),

(VALUES
    (0),(1),(2),(3),(4),
    (5),(6),(7),(8),(9)) As N2 (Num)

Se esse código for encapsulado em uma function é possível produzir um resultado semelhante às implementações anteriores.

CREATE FUNCTION dbo.FNRetNum (@Limite TINYINT)
RETURNS TABLE
AS RETURN
(

SELECT (N1.Num * 10) + N2.Num + 1 As Num FROM
(VALUES
    (0),(1),(2),(3),(4),
    (5),(6),(7),(8),(9)) As N1 (Num),

(VALUES
    (0),(1),(2),(3),(4),
    (5),(6),(7),(8),(9)) As N2 (Num)

WHERE (N1.Num * 10) + N2.Num + 1 <= @Limite)

GO

— Retornar os números de 1 a 3
SELECT Num FROM dbo.FNRetNum(3)

— Retornar os números de 1 a 8
SELECT Num FROM dbo.FNRetNum(8)

— Retornar os números de 1 a 9
SELECT Num FROM dbo.FNRetNum(9)

Bem, caso alguém queira criar uma tabela de números, há agora diversas maneiras de fazê-lo. Espero ver algo mais nativo nas próximas versões do SQL Server assim como existe o DUAL no ORACLE e a sysibm.sysdummy1 no DB2 UDB.

[ ]s,

Gustavo

3 Respostas para “Criando uma tabela com uma seqüência de números – Parte II

  1. Fabiano Neves

    Maia, obrigado pela referência…Só uma coisa, se quiser uma tab com mais de 100, pode usar o hint MAXRECURSION…ex:WITH Nums (Num) As ( SELECT 1 UNION ALL SELECT Num + 1 FROM Nums WHERE Num < 500)SELECT Num FROM Nums OPTION(MAXRECURSION 0)Abraço.

  2. Oi Fabiano,Bem lembrado. Eu havia me esquecido desse detalhe. Já fiz um pequeno update no artigo.Abs,

  3. o meu muito obrigado!

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s