WHILE vs CTE – Popular tabela de testes

Boa noite pessoas!

Por questões pessoais acabei não sendo muito fã de WHILE (laço de repetição) em código T-SQL, principalmente pelo fato de WHILE lembrar CURSOR e CURSOR levam a algumas rotinas nada performáticas, no lugar de desenvolver consultas SQL um pouco mais elaboradas.

Para popular tabelas de testes e demonstração é comum encontrar exemplo com WHILE, mas quero deixar minha proposta utilizando CTE.

No código abaixo crio uma tabela comum encontrada em demonstrações de Sql Tuning, por utilizar uma coluna do tipo char de tamanho quase absurdo.

Obs.: Se for utilizar WHILE, utilize “SET NOCOUNT ON” para evitar ter receber um monte de mensagens de “linhas afetadas”.

CREATE TABLE TabelaDeTestes
(
  idTab INT,
  textoTab CHAR(5000)
)
GO

Declaro a variável que será incrementada durante a execução do WHILE:

DECLARE @I INT
SET @I = 0

Enfim o desenvolvo o WHILE populando a tabela de teste com a variável incrementada e um texto gerado pela função NEWID (Função responsável por gerar GUID no T-SQL).

WHILE @I < 1000
BEGIN
  SET @I = @I + 1
  INSERT INTO TabelaDeTestes (idTab, textoTab)
  VALUES (@I, NEWID())
END
GO

 Ok, feito o método tradicional com WHILE, vamos para a minha proposta com CTE. Primeiramente crio um CTE recursivo. 

;WITH Cte (idTab, textoTab)
AS
(
SELECT 1, NEWID()
UNION ALL
SELECT idTab + 1, NEWID() FROM Cte
WHERE idTab <= 1000
)

Depois um tradicional “INSERT INTO tabela SELECT” com um HINT para aumentar o limite de recursividade da CTE para 1000. 

INSERT INTO TabelaDeTestes (idTab, textoTab)
SELECT idTab, textoTab FROM Cte
OPTION (MAXRECURSION 1000)

De qualquer forma CTE possui várias limitações, seja pelo número de recursividades que por padrão é 100 e com o HINT utilizado até 32767, mas seu desempenho é muito superior as ronitas desenvolvidas com o WHILE, vale a pena testar.

Espero que tenham gostado do post e os motivado a desenvolver consultas bem elaboradas em SQL, deixando sempre como última alternativa utilização de estruturas WHILE e afins, então pessoas até o próximo post!

 

Posts relacionados:

Gerando lista de meses e dias da semana com CTE:
https://sqlfromhell.wordpress.com/2009/08/15/trabalhando-com-datas-lista-de-meses-e-dias-da-semana/

Gerando consultas desordenadas e aleatórias:
https://sqlfromhell.wordpress.com/2009/07/25/consultas-desordenadas-e-aleatorias/

Anúncios

7 pensamentos sobre “WHILE vs CTE – Popular tabela de testes

  1. Olá Paulo,

    Interessante essa técnica. Já fiz coisas parecidas com tabelas de números, mas ainda não tinha pensado em utilizar CTEs recursivas para fazer cargas. O livro de Itzik Ben Gan (Inside T-SQL Querying) também tem algumas técnicas bem interessantes baseadas em Loop, mas um pouco mais performáticas.

    [ ]s,

    Gustavo

    • Gustavo, eu fico honrado em receber um comentário seu, principalmente em um assunto que você tem tanto domínio, como CTE. Obrigado pela indicação do livro e muito obrigado pelo comentário!

      Obs. Gostei muito de seu artigo sobre CTE na revista Mundo .Net

  2. Paulo,
    Só complementando o seu post:
    O limite máximo de recursões que pode ser definido em uma CTE é realmente de 32767. Porém podemos usar o valor 0 no HINT maxrecursion para que tenhamos “ilimitadas” recursões.

    Hoje posto sobre CTEs, depois da uma conferida 🙂

    Bom artigo!
    Abraço,
    Thiago Zavaschi

  3. Pingback: CTE – Introdução « Sql From Hell.com

  4. Pingback: Entendendo as Common Table Expressions – CTE – Parte 2 (Final) « Thiago Zavaschi

  5. Pingback: FUNÇÕES: Listas de valores numéricos « Sql From Hell.com

Deixe um comentário

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