Thiago Zavaschi R2 www.zavaschi.com

29Sep/090

Entendendo as Common Table Expressions – CTE – Parte 3 (O Retorno)

Sabe quando você termina uma coisa, mas fica na cabeça de que esqueceu alguma coisa?

Poisé… alguns dos meus posts anteriores trataram de Common Table Expressions e eu não mencionei outra característica muito interessante.

Lembram que o result set da CTE só existe no escopo da query seguinte? Mas será que não é possivél eu ir juntando várias CTE’s e no final fazer uma query que manipule o conjunto de todas?

É sim! Vejam o seguinte exemplo: 

;WITH cteA(a)
AS
(
    SELECT 1
    UNION ALL
    SELECT a + 1 FROM cteA WHERE a < 10
), cteB(b)
AS
(
    SELECT 1
    UNION ALL
    SELECT b + 1 FROM cteB WHERE b < 10
), cteC(c)
AS
(
    SELECT 5
)
SELECT ((SELECT SUM(b) from cteB) + (SELECT SUM(a) from cteA)) /
((SELECT COUNT(b) from cteB) + (SELECT COUNT(a) from cteA)) *
(SELECT c FROM cteC)

São 3 CTEs sendo executadas. O retorno das duas primeiras já nos é conhecido: é a contagem de 1 a 10 (visto no post anterior), e o retorno da última é 5, bastante trivial.

Esse post não serve apenas para demonstrar essa capacidade, mas também para alertar para os perigos que podem vir atrelados.

E o meu recado é: CUIDADO com a comodidade! Senão a legibilidade ganha com as CTEs de nada servirá. A explicação é facilmente observada através do plano de execução da query acima.

StmtText
----------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1026]=(([Expr1004]+[Expr1010])/([Expr1016]+[Expr1022]))*(5)))
       |--Nested Loops(Inner Join)
            |--Nested Loops(Inner Join)
            |    |--Nested Loops(Inner Join)
            |    |    |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1045]=(0) THEN NULL ELSE [Expr1046] END))
            |    |    |    |--Stream Aggregate(DEFINE:([Expr1045]=COUNT_BIG([Recr1003]), [Expr1046]=SUM([Recr1003])))
            |    |    |         |--Index Spool(WITH STACK)
            |    |    |              |--Concatenation
            |    |    |                   |--Compute Scalar(DEFINE:([Expr1041]=(0)))
            |    |    |                   |    |--Constant Scan(VALUES:(((1))))
            |    |    |                   |--Assert(WHERE:(CASE WHEN [Expr1043]>(100) THEN (0) ELSE NULL END))
            |    |    |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1043], [Recr1001]))
            |    |    |                             |--Compute Scalar(DEFINE:([Expr1043]=[Expr1042]+(1)))
            |    |    |                             |    |--Table Spool(WITH STACK)
            |    |    |                             |--Compute Scalar(DEFINE:([Expr1002]=[Recr1001]+(1)))
            |    |    |                                  |--Filter(WHERE:(STARTUP EXPR([Recr1001]<(10))))
            |    |    |                                       |--Constant Scan
            |    |    |--Compute Scalar(DEFINE:([Expr1022]=CONVERT_IMPLICIT(int,[Expr1051],0)))
            |    |         |--Stream Aggregate(DEFINE:([Expr1051]=COUNT([Recr1021])))
            |    |              |--Index Spool(WITH STACK)
            |    |                   |--Concatenation
            |    |                        |--Compute Scalar(DEFINE:([Expr1047]=(0)))
            |    |                        |    |--Constant Scan(VALUES:(((1))))
            |    |                        |--Assert(WHERE:(CASE WHEN [Expr1049]>(100) THEN (0) ELSE NULL END))
            |    |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1049], [Recr1019]))
            |    |                                  |--Compute Scalar(DEFINE:([Expr1049]=[Expr1048]+(1)))
            |    |                                  |    |--Table Spool(WITH STACK)
            |    |                                  |--Compute Scalar(DEFINE:([Expr1020]=[Recr1019]+(1)))
            |    |                                       |--Filter(WHERE:(STARTUP EXPR([Recr1019]<(10))))
            |    |                                            |--Constant Scan
            |    |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[Expr1056],0)))
            |         |--Stream Aggregate(DEFINE:([Expr1056]=COUNT([Recr1015])))
            |              |--Index Spool(WITH STACK)
            |                   |--Concatenation
            |                        |--Compute Scalar(DEFINE:([Expr1052]=(0)))
            |                        |    |--Constant Scan(VALUES:(((1))))
            |                        |--Assert(WHERE:(CASE WHEN [Expr1054]>(100) THEN (0) ELSE NULL END))
            |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1054], [Recr1013]))
            |                                  |--Compute Scalar(DEFINE:([Expr1054]=[Expr1053]+(1)))
            |                                  |    |--Table Spool(WITH STACK)
            |                                  |--Compute Scalar(DEFINE:([Expr1014]=[Recr1013]+(1)))
            |                                       |--Filter(WHERE:(STARTUP EXPR([Recr1013]<(10))))
            |                                            |--Constant Scan
            |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [Expr1061]=(0) THEN NULL ELSE [Expr1062] END))
                 |--Stream Aggregate(DEFINE:([Expr1061]=COUNT_BIG([Recr1009]), [Expr1062]=SUM([Recr1009])))
                      |--Index Spool(WITH STACK)
                           |--Concatenation
                                |--Compute Scalar(DEFINE:([Expr1057]=(0)))
                                |    |--Constant Scan(VALUES:(((1))))
                                |--Assert(WHERE:(CASE WHEN [Expr1059]>(100) THEN (0) ELSE NULL END))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1059], [Recr1007]))
                                          |--Compute Scalar(DEFINE:([Expr1059]=[Expr1058]+(1)))
                                          |    |--Table Spool(WITH STACK)
                                          |--Compute Scalar(DEFINE:([Expr1008]=[Recr1007]+(1)))
                                               |--Filter(WHERE:(STARTUP EXPR([Recr1007]<(10))))
                                                    |--Constant Scan

Poisé, é executado tudo separado. Se houverem processamento desnecessários nas CTEs, poderemos encontrar sérios gargalos nestas querys!

Então pessoal, era mais isso que eu queria mostrar deste fantástico recurso! Usem CTE’s, e para os que usarem, usem sabendo como funcionam! :)

E para quem não acompanhou, segue os links dos posts anteriores:

Entendendo as Common Table Expressions – CTE – Parte 1

http://thiagozavaschi.spaces.live.com/blog/cns!8DE5A8EFC1819ECA!390.entry

Entendendo as Common Table Expressions – CTE – Parte 2 (Final)

http://thiagozavaschi.spaces.live.com/blog/cns!8DE5A8EFC1819ECA!391.entry

Abraços,
Thiago Zavaschi

Tagged as: , No Comments
1Jun/091

Repostagem – Função Split no SQL Server

 

Então pessoal, atenção que é uma repostagem! O post sobre Common Table Expressions (CTE) e recursividade já está postado! O de CLR ainda não hehe, mas um dia cumpro todas as promessas!

Função Split no SQL Server

Uma função que sempre senti falta no SQL Server é uma função para split, aonde passaríamos dois parâmetros:

    • Frase a ser “splitada”.
    • Delimitador.

E teríamos uma lista de palavras originadas da frase passada, separada em cada delimitador.

Já desenvolvi várias versões para fazer split, mas a que considero a melhor (e também bastante popular) é a seguinte: 

CREATE FUNCTION dbo.fnSplit(
    @frase VARCHAR(max)
  , @delimitador VARCHAR(max) = ','
) RETURNS @result TABLE (item VARCHAR(8000)) 

BEGIN
DECLARE @parte VARCHAR(8000)
WHILE CHARINDEX(@delimitador,@frase,0) <> 0
BEGIN
SELECT
  @parte=RTRIM(LTRIM(
          SUBSTRING(@frase,1,
        CHARINDEX(@delimitador,@frase,0)-1))),
  @frase=RTRIM(LTRIM(SUBSTRING(@frase,
          CHARINDEX(@delimitador,@frase,0)
        + LEN(@delimitador), LEN(@frase))))
IF LEN(@parte) > 0
  INSERT INTO @result SELECT @parte
END 

IF LEN(@frase) > 0
INSERT INTO @result SELECT @frase
RETURN
END
GO

Para testar é simples: 

SELECT * FROM dbo.fnSplit('separar por espaço em branco', ' ')

----

Se desejar, há outra forma, utilizando tabela temporária, sem função: 

SET NOCOUNT ON
DECLARE @ARRAY VARCHAR(8000), @DELIMITADOR VARCHAR(100), @S VARCHAR(8000)  

SELECT @ARRAY = 'separar por espaço em branco'
SELECT @DELIMITADOR = ' '  

IF LEN(@ARRAY) > 0 SET @ARRAY = @ARRAY + @DELIMITADOR
CREATE TABLE #ARRAY(ITEM_ARRAY VARCHAR(8000))  

WHILE LEN(@ARRAY) > 0
BEGIN
    SELECT @S = LTRIM(SUBSTRING(@ARRAY, 1,
    CHARINDEX(@DELIMITADOR, @ARRAY) - 1))
    INSERT INTO #ARRAY (ITEM_ARRAY) VALUES (@S)
    SELECT @ARRAY = SUBSTRING(@ARRAY,
    CHARINDEX(@DELIMITADOR, @ARRAY) + 1, LEN(@ARRAY))
END  

-- MOSTRANDO O RESULTADO JÁ POPULADO NA TABELA TEMPORÁRIA  
SELECT * FROM #ARRAY
DROP TABLE #ARRAY
SET NOCOUNT OFF

 

Simples não?

Estas maneiras mostradas resolvem a necessidade da falta do split? Sim!

Porém, desde o SQL Server 2005 temos o recurso das CTEs (Common Table Expression), que também podem ser utilizadas para split. A seguir mostro o exemplo que considero mais elegante para tal tarefa, utiliza recursividade ao invés do loop: 

DECLARE @s VARCHAR(8000), @d VARCHAR(10)
SET @s = 'separar por espaço em branco'
SET @d = ' ' 

;WITH split(i,j) AS
(
SELECT i = 1, j = CHARINDEX(@d, @s + @d)
UNION ALL
SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split
   WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0
)
SELECT SUBSTRING(@s,i,j-i)
FROM split

Observação importante sobre CTEs:

Ao utilizar CTEs recursivas devemos tomar cuidado com o número máximo de recursões permitidas.

Por padrão esse número é igual a 100. Isso na prática significa que eu só poderia ter 100 delimitadores dentro da minha variável @s.

Caso a recursão máxima seja alcançada, termos uma mensagem igual a essa:

“The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

Para aumentar este valor, podemos acionar um parâmetro extra para a CTE: maxrecursion.

Na prática, para trocar a recursão máxima para 1000, devemos fazer: 

DECLARE @s VARCHAR(8000), @d VARCHAR(10)
SET @s = 'separar por espaço em branco'
SET @d = ' ' 

;WITH split(i,j) AS
(
SELECT i = 1, j = CHARINDEX(@d, @s + @d)
UNION ALL
SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split
   WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0
)
SELECT SUBSTRING(@s,i,j-i)
FROM split
OPTION (maxrecursion 1000)

 

O valor máximo para o parâmetro MAXRECURSION é de 32767, então fiquem atentos. Caso seja necessário uma CTE recursiva com mais de 32767 iterações, então devemos pensar numa estratégia para executar mais de um split (ou mais de uma vez a operação desejada) por iteração. Em casos muito específicos, podemos deixar o máximo de recursão infinito (MAXRECURSION = 0).

Não entendeu muito bem o que é uma CTE? Pode recorrer ao BOL, ou esperar o meu próximo post (há este e o de cursores para postar) que abordarei BEM detalhadamente o que é uma CTE, sintaxe, como funciona, seus usos comuns, e a questão de como funciona a recursividade. Então fiquem atentos! 

---

Depois destas três maneiras mostradas, ainda há uma quarta possibilidade (a partir do SQL Server 2005 também) que é utilizar as chamadas Functions CLR (programadas em C# por exemplo). O por quê? Porque operações em strings são custosas para o SQL Server, então uma função CLR poderia se comportar de maneira muito interessante (performática) neste caso.

Também não sabe o que é CLR ou como funciona? Então aguarde os próximos posts também!

Mas eu ainda continuo na esperança de algum dia termos isso diretamente no SQL Server, de uma maneira mais otimizada. :)

Abraços,

Thiago Zavaschi

Tagged as: , , 1 Comment
   

:)

Categorias

Webcasts

Recomendados

Arquivos