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

Trabalhando com datas – Lista de meses e dias da semana

Pessoal, no último post relacionei funções para trabalhar com campos do tipo data, acabei recebendo um bom feedback e algumas perguntas sobre como relacionar todos os meses ou dias da semana por meio de query. Para responder estas perguntas, relacionei formas de gerar listas com os meses e dias da semana por meio de CTE.

Nestas duas primeiras queries, faço uso de um CTE recursivo básico, com as funções DATENAME e DATEADD e a instrução SET LANGUAGE para definir o idioma ‘Brasileiro’. Estas queries são bem mais simples e tornam mais fácil trazer novos campos. 

Tips:
1: Os campos de data quando somados a tipos inteiros, adicionam o valor dos inteiros em dias.
2: A função DATEADD simplifica a adição de outros quantitativos como meses, horas e anos.
3: O inteiro ‘0’ (zero) quando convertido para data se torna ‘1900-01-01 00:00:00.000’, uma segunda-feira.
4: O inteiro ‘-1’ quando convertido para data se torna ‘1899-12-31 00:00:00.000’, um domingo.

Gerando uma lista de meses:

SET LANGUAGE 'Brazilian'
;WITH Meses AS
(
  SELECT 1 AS IdMes, DATENAME(MONTH, 0) AS NomeMes
  UNION ALL
  SELECT IdMes + 1, DATENAME(MONTH, DATEADD(MONTH, IdMes, 0))
  FROM Meses
  WHERE IdMes < 12
)
SELECT * FROM Meses

Gerando uma lista de dias da semana começando pelo domingo:

SET LANGUAGE 'Brazilian'
;WITH DiaSemana AS
(
  SELECT 1 AS idDSe, DATENAME(WEEKDAY, -1) AS nomeDSe
  UNION ALL
  SELECT idDSe + 1, DATENAME(WEEKDAY, idDSe -1)
  FROM DiaSemana
  WHERE idDSe < 7
)
SELECT idDSe, nomeDSe FROM DiaSemana

Ou pela Segunda-Feira:

SET LANGUAGE 'Brazilian'
;WITH DiaSemana AS
(
  SELECT 1 AS idDSe, DATENAME(WEEKDAY, 0) AS nomeDSe
  UNION ALL
  SELECT idDSe + 1, DATENAME(WEEKDAY, idDSe)
  FROM DiaSemana
  WHERE idDSe < 7
)
SELECT idDSe, nomeDSe FROM DiaSemana

Nas duas próximas queries, avanço um pouco a complexidade das CTEs, utilizando a view syslanguage e uma CTE de Split que modifiquei do site do Zavaschi. Elas realmente se diferenciam pelo campo utilizado da syslanguages facilitando alterações neste sentido.

Gerando uma lista dos meses:

DECLARE @Texto VARCHAR(8000),
        @Delimitador CHAR
SET @Texto = (SELECT months FROM sys.syslanguages WHERE alias = 'Brazilian')
SET @Delimitador = ','
;WITH _SPLIT(ID, _INDEX, _LENGTH) AS
(
  SELECT
    1,
    1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador)
UNION ALL
  SELECT
    ID + 1,
    _LENGTH + 1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1)
  FROM _SPLIT
  WHERE CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1) <> 0
)
, Meses (idMes, nomeMes) AS
(
  SELECT
    ID,
    SUBSTRING(@Texto, _INDEX, _LENGTH - _INDEX)
  FROM _SPLIT
)
SELECT idMes, nomeMes FROM Meses

Gerando uma lista de dias da semana:

DECLARE @Texto VARCHAR(8000),
        @Delimitador CHAR
SET @Texto = (SELECT days FROM sys.syslanguages WHERE alias = 'Brazilian')
SET @Delimitador = ','
;WITH _SPLIT(ID, _INDEX, _LENGTH) AS
(
  SELECT
    1,
    1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador)
UNION ALL
  SELECT
    ID + 1,
    _LENGTH + 1,
    CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1)
  FROM _SPLIT
  WHERE CHARINDEX(@Delimitador, @Texto + @Delimitador, _LENGTH + 1) <> 0
)
, DiaSemana (idDSe, nomeDSe) AS
(
  SELECT
    ID,
    SUBSTRING(@Texto, _INDEX, _LENGTH - _INDEX)
  FROM _SPLIT
)
SELECT idDSe, nomeDSe FROM DiaSemana

Eu espero que elas respondam as necessidades que venham a aparecer!

Não se esqueçam de comentar!

Trabalhando com datas – dia/mes/ano

Durante todos os dias me deparo com muitas queries trabalhando com datas, principalmente em T-SQL, algumas bem complexas e outras simples, que muita gente ainda insiste em complicar. O MVP Gustavo Maia Aguiar trabalhou alguns tópicos neste sentido no seu blog, mas o meu objetivo é trabalhar do mais simples (formato da data) até passear um pouco em cálculos complexos com datas nos próximos tópicos.

Neste primeiro post demonstrarei como formatar campos e variáveis do tipo DATETIME para dia/mês/ano, utilizando a função Convert. O motivo de trabalhar isso é pelo fato de encontrar muitas vezes, algo assim:

Cabeçalho para demonstrações:

DECLARE @data DATETIME
SET @data = GETDATE()

Não faça isso:

SELECT CAST(DAY(@data), VARCHAR) + '/' + CAST(MONTH(@data), VARCHAR) + '/' + CAST(YEAR(@data), VARCHAR)

E até mesmo algumas coisas assim:

Também não faça isso:

SELECT
  (CASE WHEN DAY(@data) > 9
    THEN ''
    ELSE '0'
   END) +
CAST(DATE(@data) AS VARCHAR) +
  (CASE WHEN MONTH(@data) > 9
    THEN '/'
    ELSE '/0'
   END) +
CAST(MONTH(@data) AS VARCHAR) +
  '/' +
CAST(YEAR(@data) AS VARCHAR)

Se o objetivo era demonstrar o conhecimento em lógica, realmente os dois exemplos acima já me impressionam, pois não é qualquer um que chega a este raciocínio. Mas vamos para a versão que chamo de forma elegante:

SELECT CONVERT(VARCHAR, @data, 103)

Gostou da simplicidade, dê uma olhada no tópico de CAST e CONVERT da biblioteca do MSDN, que possui uma relação gigante de formatos possíveis.
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Consultas desordenadas e aleatórias

A idéia inicial deste post é trabalhar com ordem desordenada! Não se surpreenda, nem diga que estou louco, mas o que estou tratando é algo comum do dia-a-dia.

Vamos à situação, imagina que você possui uma tabela de categoria de produtos, com os seguintes dados:

Código Nome
1 Sapatos
2 Cintos
3 Chapeis
4 Calçar
5 Casacos
6 Camisas
7 Meias
8 Pijamas
9 Outros

 

Mas agora o cliente deseja que sempre a categoria “Outros” apareça em primeiro em um determinado relatório. Ok, você em minutos vai à query:

SELECT Código, Nome FROM Categoria

E altera para:

SELECT Código, Nome FROM Categoria ORDER BY Código DESC

Resultado disso, agora o cliente te avisa que quer as categorias bagunçadas… Possivelmente isso quer dizer, que ele quer que a categoria “Outros” fique como primeira, mas que as outras continuem em alguma ordem (possivelmente por nome). Agora temos as seguintes soluções:

Criar uma coluna nova na tabela para determinar a ordem da tabela? Ok, é uma ótima idéia, se você possui acesso para alterar a estrutura das tabelas do banco de dados. Sem contar que será necessário reordenar os valores da coluna a cada nova categoria.

Código Nome Ordem
1 Sapatos 9
2 Cintos 6
3 Chapeis 5
4 Calçar 2
5 Casacos 4
6 Camisas 3
7 Meias 7
8 Pijamas 8
9 Outros 1

 

Ou fazer união do registro “Outros” com os outros resultados:

SELECT Código, Nome FROM Categoria
  WHERE Código = 9
UNION
SELECT Código, Nome FROM Categoria
  WHERE Código <> 9

Ok, não funcionou a união… Então o jeito é apelar:

SELECT Código, Nome, '' AS Ordem FROM Categoria
  WHERE Código = 9
UNION
SELECT Código, Nome, Nome AS Ordem FROM Categoria
  WHERE Código <> 9
ORDER BY Ordem

Bem, segue agora minha idéia de solução, ela executa 20% mais rápido, possui um plano de execução bem menor, mais a facilidade de manutenção:

SELECT Código, Nome FROM Categoria
ORDER BY
  CASE Código
    WHEN 8 THEN ''
    ELSE Nome
  END;

Propostas as soluções, agora é escolher a melhor e aplicá-la.

A possibilidade de usar CASE ou funções para ordenação permite solucionar necessidades diversas de ordenação, como gerar uma consulta ordenada aleatoriamente:

SELECT * FROM Tabela
ORDER BY NEWID()

E algumas queries estranhas:

SELECT * FROM Tabela
ORDER BY
  CASE
    WHEN Código > 5 THEN 0 – Código
    ELSE Código
  END;

Se alguém tiver alguma outra proposta, por favor, informem.

Até o próximo post!

Replicação – Bug no nome do servidor

Um problema do “cão” me perseguiu em uma consultoria e também o Zavaschi num treinamento, onde não conseguíamos trabalhar com replicação do SQL Server com computadores que tiveram seus nomes alterados.

No caso do Zavaschi, foi porque as VMs do treinamento tinham o mesmo nome e MAC Address, fazendo o servidor DHCP entender que todas VMs eram um computador só. Assim foi necessário mudar o nome das VMs e o MAC Address de suas placas de redes “virtuais”.

No meu caso foi mais simples, alguém instalou o SQL Server e depois mudou o nome do computador.

Mas tanto no meu caso e no caso do Zavaschi, a replicação não funcionava mais…

Foi então que uma luz do submundo do SQL Server nos revelou uma system view das trevas chamada de “sys.servers”, executando um SELECT nela e o que vimos? O nome antigo do servidor ainda estava guardado dentro do SQL Server, what hell was that !?!?

SELECT * FROM sys.servers

Outra luz vinda do submundo nos revelou a solução… Vamos matar o servidor e criar outro!!! Não pegamos os machados e picaretas para atacar o servidor! Mas utilizamos algo pior, usamos o SQL…

EXEC sp_dropserver 'nome_antigo_do_computador'
GO
EXEC sp_addserver 'nome_atual_do_computador ', LOCAL
GO

Reiniciamos o serviço do SQL Server. Daí eu pensei: se não funcionar, estarei no inferno mesmo! Mas felizmente o SQL conseguiu realizar o procedimento corretamente… Ufa…

Depois disso a replicação começou a funcionar corretamente.

Mas minha mente insana teimou por testar uma situação: Se eu somente executar o sp_dropserver (ou executar errado o sp_addserver), depois reiniciar o serviço, o que vai acontecer?

Claro que executei isso em um SQL Server pessoal, daí matei o SQL Server e a partir daí, nem o SQL Server Management Studio funcionava mais com este serviço…

Tive de optar por outra solução, gerei um arquivo SQL com esta query:

EXEC sp_addserver 'nome_atual_do_computador ', LOCAL
GO

E na pasta do “C:\Arquivos de programas\Microsoft SQL Server\90\Tools\Binn”, executei o osql.exe com o arquivo SQL, utilizando o seguinte comando DOS:

osql -U usuario_sa -P senha_do_usuario_sa -i nome_do_arquivo

Reiniciei o serviço do SQL Server 2005 novamente, e as coisas começaram a rodar corretamente.

Pronto! Agora eu podia matar o servidor sem medo!rsss

Google Fusion Tables?

A novidade da semana é Google Fusion Tables! Legal não tem query e são 250 MB de dados de graça, mas ainda é muito cedo para falar bem dele… Ele é um serviço versão Pré-Alfa, tem a mesma integração de outros serviços como o Google Maps, tem também muitos bugs e funcionalidades ainda parcialmente desenvolvidas ou ainda não implantadas. Na verdade, tirando os problemas que já são esperados de um serviço Pré-Alfa, só eu queria usar SQL ou LINQ para fazer consultas nele…rsss

Para demonstrar os bugs e algumas funcionalidades dele, vou criar uma massa de dados. Acho que 18798 linhas são o suficiente:

2009-06-14_1

A massa de dados no Excel ficou perfeita:

2009-06-14_2

Mas vamos ver como ela fica no GFT, primeiro passo, importando a massa de dados:

2009-06-14_3

Segundo passo, o que o GFT fez com meus dados???

2009-06-14_4

Mais uma chance, agora com um arquivo Excel, parecia que não iria importar nunca…

2009-06-14_5

E os dados ficaram corretos! Agora somente nomear as colunas e prosseguir:

2009-06-14_6

Salvando a tabela e os comentários:

2009-06-14_7

Por fim, tudo ocorreu bem.

2009-06-14_8

Agora vamos às opções, legal não tem count nas agregações… e pelo que ele esta aggregando??? AddressLine1??? Só por que tem número no início é um valor mensurável???rsss

2009-06-14_9

Esta bem, será uma ótima ferramenta, principalmente pelas opções de gráficos e colaboração. Quando lançarem a versão final vou querer “brincar de novo”, mas por enquanto fico com outro Fussion Tables (at: http://www.fusiontables.com/en/index.php/Products/ ) por causa da mesa de snooker em baixo.