Márcio Gomes vs SQL – Fight 2

Se você tem menos de 18 anos, ou sofre de problemas cardíacos, ou não tem certeza sobre suas convicções religiosas, ou não manja de UPDATE, então NÃO PROSSIGA.

Eu e o Márcio Gomes, em uma discussão sobre SQL (o que nunca dá bons resultados), nos deparamos com o bendito do ‘WHERE 1=1’, que em um momento de insanidade transformamos em ‘WHERE SQRT(SQUARE(1 + 1)) * 3 = 6’. Para testar o fruto desta capacidade criativa, optamos por comparar planos de execução, de uma query sem WHERE, outra com o ‘WHERE 1=1’ e outra com ‘WHERE SQRT(SQUARE(1 + 1)) * 3 = 6’.

Veja como ‘WHERE SQRT(SQUARE(1 + 1)) * 3 = 6’ tem menor custo que as outras consultas: #sqn

Enganamos o plano de execução do SQL? Onde está seu deus agora?

Para quem duvida, segue o script:

CREATE TABLE T (ID INT NOT NULL, TXT CHAR(32) NOT NULL)

;WITH CTE (ID) AS (
SELECT 1
UNION ALL
SELECT ID + 1 FROM CTE WHERE ID < 100000
)

INSERT INTO T
SELECT ID, REPLICATE('0', 32) FROM CTE
OPTION (MAXRECURSION 0)

UPDATE T SET ID = 1
UPDATE T SET ID = 1 WHERE 1 = 1
UPDATE T SET ID = 1 WHERE SQRT(SQUARE(1 + 1)) * 3 = 6

Transformando colunas em linhas sem UNPIVOT

Olá pessoas,

Após conhecer um pouco do UNPIVOT, percebe-se que este comando não é muito flexível, mesmo sendo um pouco mais maleável que o comando PIVOT. Por desconhecer este comando, ou pela falta de flexibilidade, é comum encontrar outras alternativas para ‘transformar colunas em linhas’, como o exemplo abaixo:

-- Criando uma tabela de teste:

DECLARE @CONTAS TABLE (
    [BANCO] VARCHAR(100),
    [ANO] SMALLINT,
    [INVESTIMENTOS] MONEY,
    [DESPESAS] MONEY
)

INSERT INTO @CONTAS VALUES
('BANCO ALVORADA S/A', 2010, 9613906084.01, 8102644.84),
('BANCO ALVORADA S/A', 2011, 174343.35, 7935411.15),
('BANCO ARBI S/A', 2010, 8202652.29, 114215.13),
('BANCO ARBI S/A', 2011, 8407843.72, 81746.25)

-- Uso do UNION ALL para transformar colunas em linhas:

SELECT [BANCO], [ANO], 'INVESTIMENTOS' AS [TIPO], C.[INVESTIMENTOS] AS [VALOR] FROM @CONTAS C
UNION ALL
SELECT [BANCO], [ANO], 'DESPESAS' AS [TIPO], C.[DESPESAS] AS [VALOR] FROM @CONTAS C
ORDER BY [BANCO], [ANO], [TIPO]

Esta estratégia com UNION ALL requer uma leitura da tabela a mais para cada coluna transformada em linha, ou seja, a tabela no exemplo acima foi lida duas vezes para obter o mesmo resultado que poderia ser obtido lendo a tabela uma única vez com UNPIVOT:

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C
UNPIVOT (
    [VALOR] FOR [TIPO] IN (
        [INVESTIMENTOS],
        [DESPESAS]
    )
) AS U
ORDER BY [BANCO], [ANO], [TIPO]

Com o STATISTICS IO, podemos obter as seguintes informações:

Consulta com UNION ALL:

(8 row(s) affected)
Table ‘#A255182D’. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Consulta com UNPIVOT:

(8 row(s) affected)
Table ‘#A255182D’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Mas como obter flexibilidade, tendo um número de leituras semelhante ao UNPIVOT, e até mesmo ter um plano de execução um pouco melhor?

A resposta pode ser encontrada no CROSS APPLY – que para quem não conhece, este comando funciona de forma um pouco semelhante ao JOIN, executando uma determinada subquery para cada registro da tabela com a qual ele faz o ‘JOIN’ –, que pode ser utilizado com UNION ALL para transformar colunas em linhas:

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C

CROSS APPLY (
    SELECT 'INVESTIMENTOS' AS [TIPO], C.[INVESTIMENTOS] AS [VALOR]
    UNION ALL
    SELECT 'DESPESAS' AS [TIPO], C.[DESPESAS] AS [VALOR]
)
AS U

ORDER BY [BANCO], [ANO], [TIPO]

Ou combinado ao VALUES:

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C

CROSS APPLY (
    VALUES
    ('INVESTIMENTOS', C.[INVESTIMENTOS]),
    ('DESPESAS', C.[DESPESAS])
)
AS U ([TIPO], [VALOR])

ORDER BY [BANCO], [ANO], [TIPO]

Em breve, vou publicar um comparativo mais detalhado entre o UNPIVOT e o CROSS APPLY!

Márcio Gomes vs SQL – Fight 1

Olá pessoas,

A partir desta semana, o Márcio Gomes terá um espaço para expressar todo seu amor ao SQL, então boa diversão!

SQL Go To Hell… PLZ!

por Márcio Gomes

Antes de tudo eu gostaria de agradecer ao Paulo pelo espaço cedido no SQL FROM HELL, não existe lugar melhor na internet para eu expressar meus sentimentos em relação ao SQL.

Que eu ODEIO SQL todo mundo sabe, no entanto o que pouca gente sabe é que o SQL ME ODEIA! Sim… essa relação de guerra e paz começou em 2002 e segue até hoje e posso garantir com todas as letras, o SQL não perde uma única chance de me sacanear, quando tudo parece que vai dar certo de repente algo sobrenatural acontece e somente recorrendo à ajuda do Paulo combinado com duas velas pretas na encruzilhada as coisas voltam ao normal.

Hoje como é a estreia dos relatos sobre a guerra cotidiana tentando sobreviver aos ataques terroristas do SQL Server deixo um print screen mostrando que o .NET Framework concorda comigo.

Exatamente isto que você leu meu caro amigo “NO BANDO DE DADOS” (sic), o SQL é uma espécie de clã, horda, bando de dados armados que criam batalhas digitais que nos post futuros serão descritas aqui no SQL FROM HELL!

Como validar CPF e CNPJ no SQL Server

E ai pessoas, revisando o script de validação de CPF e CNPJ em T-SQL que adaptei em 2009, encontrei algumas melhorias pra deixá-lo mais limpo, espero que gostem:

DECLARE @TEXTO VARCHAR(20) = '02.841.834/0001-55'
 
DECLARE @CPF_CNPJ VARCHAR(20) = ''
 
;WITH SPLIT AS
(
	SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
	UNION ALL
	SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
	FROM SPLIT
	WHERE ID < LEN(@TEXTO)
)
 
SELECT @CPF_CNPJ += LETRA
FROM SPLIT
WHERE LETRA LIKE '[0-9]'
OPTION(MAXRECURSION 0)
 
IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
	SELECT 'Inválido'
	RETURN
END

DECLARE
	@I INT,
	@J INT = 1,
	@N INT = LEN(@CPF_CNPJ),
	@DIGITO1 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1),
	@DIGITO2 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1),
	@TOTAL_TMP INT,
	@COEFICIENTE_TMP INT,
	@DIGITO_TMP INT,
	@VALOR_TMP INT,
	@VALOR1 INT,
	@VALOR2 INT
 
WHILE @J <= 2
BEGIN
	SELECT
		@TOTAL_TMP = 0,
		@COEFICIENTE_TMP = 2,
		@I = @N + @J - 3
	
	WHILE @I >= 0
	BEGIN
		SELECT
			@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
			@TOTAL_TMP += @DIGITO_TMP * @COEFICIENTE_TMP,
			@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1,
			@I -= 1
 
		IF @COEFICIENTE_TMP > 9 AND @N = 14
			SET @COEFICIENTE_TMP = 2
	END
 
	SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
 
	IF (@VALOR_TMP >= 10)
		SET @VALOR_TMP = 0
 
	IF @J = 1
		SET @VALOR1 = @VALOR_TMP
	ELSE
		SET @VALOR2 = @VALOR_TMP

	SET @J += 1
END
 
SELECT
	CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
		THEN 'Válido'
		ELSE 'Inválido'
	END

E o script para criar uma função de validação de CPF e CNPJ:

CREATE FUNCTION [dbo].[UDF_ValidaCpfCnpj] (@TEXTO VARCHAR(20))
RETURNS BIT
AS 
BEGIN 
	DECLARE @CPF_CNPJ VARCHAR(20) = ''
 
	;WITH SPLIT AS
	(
		SELECT 1 AS ID, SUBSTRING(@TEXTO, 1, 1) AS LETRA
		UNION ALL
		SELECT ID + 1, SUBSTRING(@TEXTO, ID + 1, 1)
		FROM SPLIT
		WHERE ID < LEN(@TEXTO)
	)
 
	SELECT @CPF_CNPJ += LETRA
	FROM SPLIT
	WHERE LETRA LIKE '[0-9]'
	OPTION(MAXRECURSION 0)
 
	IF LEN(@CPF_CNPJ) NOT IN (11, 14)
	BEGIN
		RETURN 0
	END

	DECLARE
		@I INT,
		@J INT = 1,
		@N INT = LEN(@CPF_CNPJ),
		@DIGITO1 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1),
		@DIGITO2 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1),
		@TOTAL_TMP INT,
		@COEFICIENTE_TMP INT,
		@DIGITO_TMP INT,
		@VALOR_TMP INT,
		@VALOR1 INT,
		@VALOR2 INT
 
	WHILE @J <= 2
	BEGIN
		SELECT
			@TOTAL_TMP = 0,
			@COEFICIENTE_TMP = 2,
			@I = @N + @J - 3
	
		WHILE @I >= 0
		BEGIN
			SELECT
				@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
				@TOTAL_TMP += @DIGITO_TMP * @COEFICIENTE_TMP,
				@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1,
				@I -= 1
 
			IF @COEFICIENTE_TMP > 9 AND @N = 14
				SET @COEFICIENTE_TMP = 2
		END
 
		SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
 
		IF (@VALOR_TMP >= 10)
			SET @VALOR_TMP = 0
 
		IF @J = 1
			SET @VALOR1 = @VALOR_TMP
		ELSE
			SET @VALOR2 = @VALOR_TMP

		SET @J += 1
	END
 
	RETURN
		CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
			THEN 1
			ELSE 0
		END
END    
GO

SELECT CASE 
	WHEN [dbo].[UDF_ValidaCpfCnpj] ('02.841.834/0001-55') = 1 
	THEN 'Válido' 
	ELSE 'Inválido' 
	END

E um segundo script para criar uma função de validação de CPF e CNPJ, considerando que o CPF ou CNPJ que a função vai receber nunca terá caracteres especiais ou mais/menos dígitos que um CPF ou CNPJ possui:

CREATE FUNCTION [dbo].[UDF_ValidaCpfCnpj] (@CPF_CNPJ VARCHAR(20))
RETURNS BIT
AS 
BEGIN 
	DECLARE
		@I INT,
		@J INT = 1,
		@N INT = LEN(@CPF_CNPJ),
		@DIGITO1 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1),
		@DIGITO2 INT = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1),
		@TOTAL_TMP INT,
		@COEFICIENTE_TMP INT,
		@DIGITO_TMP INT,
		@VALOR_TMP INT,
		@VALOR1 INT,
		@VALOR2 INT
 
	WHILE @J <= 2
	BEGIN
		SELECT
			@TOTAL_TMP = 0,
			@COEFICIENTE_TMP = 2,
			@I = @N + @J - 3
	
		WHILE @I >= 0
		BEGIN
			SELECT
				@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
				@TOTAL_TMP += @DIGITO_TMP * @COEFICIENTE_TMP,
				@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1,
				@I -= 1
 
			IF @COEFICIENTE_TMP > 9 AND @N = 14
				SET @COEFICIENTE_TMP = 2
		END
 
		SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
 
		IF (@VALOR_TMP >= 10)
			SET @VALOR_TMP = 0
 
		IF @J = 1
			SET @VALOR1 = @VALOR_TMP
		ELSE
			SET @VALOR2 = @VALOR_TMP

		SET @J += 1
	END
 
	RETURN
		CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
			THEN 1
			ELSE 0
		END
END    
GO

SELECT 
	CASE WHEN [dbo].[UDF_ValidaCpfCnpj] ('02841834000155') = 1 
	THEN 'Válido' 
	ELSE 'Inválido' 
	END

Quem tiver alguma dica ou sugestão pra melhorar este script, fique a vontade para deixar seu comentário.

Artigos relacionados:

FUNÇÕES: Validação de CNPJ e CPF com T-SQL

FUNÇÕES: Somente Números (com CTE)

FUNÇÕES: Somente Números (com WHILE)