E vamos para mais um exemplo de Deadlock…

Artigos relacionados:
Exemplo de Deadlock no SQL Server
Monitorando Deadlocks do SQL Server com SMO

Fonte:
https://www.facebook.com/1mpics

A verdade sobre a instalação em Português do SQL Server

Um belo exemplo de uma Query From Hell

E ai pessoas, fazendo um pingback de um link que o @zavaschi passou ontem no twitter (RT original do @dsfnet) que realmente mostra um belo exemplo de uma Query From Hell:

http://thedailywtf.com/Articles/The-Query-of-Despair.aspx

Se alguem quiser encaminhar outros exemplos para publicar no blog, fiquem a vontade!

Query From Hell? – SQL Server Spatial Data e um cubo 3D

Quando lançaram a feature de Spatial Data do SQL Server fiquei impressionado com as possibilidades de trabalhar com tipos geométricos e geográficos no SQL Server, principalmente pelo fato de que naquele ano, eu estava na equipe de desenvolvimento de um projeto de geolocalização e roteirização, onde usávamos bastante features como Spatial Data e aplicações com mapas (Web e Mobile).

Só que desde aquela época, via que além das propriedades X e Y, existia a propriedade Z, que não afetava em nada os métodos para calculo de distancia e outros. Pois, logo que existe X Y Z, seria possível gravar dados geométricos não somente 2D no banco de dados, mas também 3D… Mas não, a propriedade Z realmente não permitia isso.

Para matar minha vontade de gerar “algo em 3D” no SQL Server, peguei uma biblioteca de uma aplicação Windows Forms do site (Visual C# Kicks – http://www.vcskicks.com), adequei a biblioteca para ser SAFE no SQL Server e trabalhar com Spatial Data, assim criei um pequeno monstro:

Infelizmente não consegui implementar 100% o algoritmo de rotação do objeto 3D que funcionou perfeitamente na aplicação Windows (http://www.vcskicks.com/3d-graphics-improved.php). Quem quiser conhecer a biblioteca modificada, pode conferi-la abaixo:

Uma imaginação bem fértil para usar LIKE

O que Joãozinho faz quando ele não quer usar o IN e tem uma imaginação bem fértil para usar LIKE?

Ele torna um simples IN:

SELECT * FROM Clientes
WHERE Codigo IN (12, 34, 19)

Em um LIKE “bem” elaborado:

SELECT * FROM Clientes
WHERE ' 12, 34, 19,' LIKE '% ' + CAST(Codigo AS VARCHAR) + ',%'

Agora que Joãozinho esta feliz, vamos ver o plano de execução?

Como Joãozinho conseguiu transformar um simples “clustered index seek” em um “clustered index scan” com custo aproximadamente 99x maior que a consulta com IN, digamos que esta na hora do Joãozinho parar de inventar moda…

Query 4 Fun – SQL Calendar!!!

Eu sou aquele tipo de idealista que acredita que é possível fazer qualquer coisa, até mesmo sem a ferramenta certa e que o único limite que existe é o “ambiente”, no caso da informática, o hardware. Gosto de afirmar isso aos meus alunos e companheiros, e como desafio, gosto de propor a criação de algo inusitado (utilizando os recursos que estão aprendendo), provando assim que não sou o único louco deste mundo!

Para demonstrar o que seria este algo inusitado, meu exemplo de como utilizar CTE, PIVOT e funções de data para criar algo para o qual o SQL “não nasceu”, exemplo:

DECLARE @Mes INT
,@Ano INT

SELECT @Mes = 8
,@Ano = 2010

;WITH CTE (Data) AS
(
  SELECT DATEADD(year, @Ano - 1900, DATEADD(month, @Mes - 1, 0))
  UNION ALL
  SELECT Data+1
  FROM CTE
  WHERE MONTH(Data + 1) = @Mes
)
SELECT
  [1] AS [Domingo]
, [2] AS [Segunda-Feira]
, [3] AS [Terça-Feira]
, [4] AS [Quarta-Feira]
, [5] AS [Quinta-Feira]
, [6] AS [Sexta-Feira]
, [7] AS [Sábado]
FROM (
	SELECT DAY(Data) AS Dia, DATEPART(weekday, Data) DiaSemana, DATEPART(week, Data) Semana
	FROM CTE
) AS Datas
PIVOT
(
	MAX(Dia) FOR DiaSemana
	IN ([1], [2], [3], [4], [5], [6], [7])
) AS A

E como resultado, o SQL Calendar:

Query From Hell – Como 100 é menor que 11???

Você sabia que 100 é maior que 10, mas 100 é menor que 11, e mesmo que 1000 seja maior que 100, 1000 ainda será menor que 99? Não entendeu? Acha que estou ficando louco, então veja bem isso:

Comparação de texto	Comparação de valores numéricos

Não se trata de um bug, pois o algoritmo de comparação de textos compara caracteres por caracteres iniciando pela esquerda, isso quer dizer que “ANA BEATRIZ” é menor que “ANA PAULA” visto que “B” é menor que “P” (a mesma comparação utilizada pelo ORDER BY).

O fato do texto “10” ser menor que “9” pode soar meio estranho, mas o primeiro caractere dos dois textos diz que “1” é menor que “9”, logo o texto “10” é menor que “9”, mesmo que o valor numérico 10 seja menor que o valor numérico 9.

Para demonstrar melhor este fato, segue um esboço de como funciona o algoritmo de comparação de texto:

Muitos problemas podem surgir quando trabalhamos com campos do tipo texto que deveriam ser tratado como numéricos, como no caso da utilização da funções MIN/MAX ou até mesmo no ORDER BY, que pode ter resultados bem diferentes da comparação feita com valores numéricos:

Query From Hell? – Comparando a estrutura de tabelas diferentes

Boa noite pessoal,

Finalmente tive tempo para respirar e escrever um artigo neste novo ano. Para este artigo, trago algo que normalmente me deparo quando se trabalha com muitas versões de bancos de dados, que é comparar a estrutura das tabelas entre versões de banco de dados ou até mesmo quando existem tabelas semelhantes dentro de um mesmo banco de dados. Desta forma, preparei algumas consultas que relacionam as colunas de tabelas semelhantes que estejam ou não no mesmo banco de dados, mas o conceito é facilmente aplicado para outros tipos de objetos como tabelas, chaves, índices e relacionamentos.

Para situações mais complexas recomendo a utilização de ferramentas como o Visual Studio Database Edition ou o RedGate SQL Compare e Data Compare. Mas como nem sempre é possível contar com estes recursos por não serem ferramentas gratuitas, sempre é bom ter algumas consultas para situações críticas ou verificações mais urgentes.

1. Colunas de uma tabela

Para identificar as colunas de uma tabela, é possível utilizar tanto INFORMATION_SCHEMA.COLUMNS ou sys.columns ou até mesmo sys.syscolumns, conforme abaixo:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'nome da tabela'


SELECT *
FROM sys.columns
WHERE [object_id] = OBJECT_ID('nome da tabela')


SELECT *
FROM sys.syscolumns
WHERE [id] = OBJECT_ID('nome da tabela')


2. Colunas diferentes entre duas tabelas

Nesta primeira consulta teremos como resultado as colunas diferentes entre duas tabelas, utilizando ‘FULL OUTER JOIN’ (ver artigo), tratando as diferenças no momento da seleção:

USE [AdventureWorks]
GO
SELECT
 ISNULL(A.COLUMN_NAME, B.COLUMN_NAME)
 AS [Tabela]
,CASE WHEN A.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + B.TABLE_NAME
 WHEN B.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + A.TABLE_NAME
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT COLUMN_NAME, TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
) A
FULL OUTER JOIN
(
 SELECT COLUMN_NAME, TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'AddressType'
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
GO


3. Colunas diferentes quando se utiliza schemas

Quando se utiliza a view sys.columns, informe dentro da função OBJECT_ID o nome do schema seguido do nome da tabela. No exemplo, é utilizado o schema ‘Person’:

USE [AdventureWorks]
GO
SELECT
 ISNULL(A.[name], B.[name])
 AS [Coluna]
,CASE WHEN A.[name] IS NULL
 THEN 'SOMENTE EM ' + B.[table]
 WHEN B.[name] IS NULL
  THEN 'SOMENTE EM ' + A.[table]
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT [name]
 ,'[Person].[Address]' as [table]
 FROM sys.columns
 WHERE [object_id] = OBJECT_ID('[Person].[Address]')
) A
FULL JOIN
(
 SELECT [name]
 ,'[Person].[AddressType]' as [table]
 FROM sys.columns
 WHERE [object_id] = OBJECT_ID('[Person].[AddressType]')
) B
ON A.[name] = B.[name]
GO


Quando se utiliza INFORMATION_SCHEMA.COLUMNS, é necessário especificar ‘TABLE_SCHEMA’:

USE [AdventureWorks]
GO
SELECT
 ISNULL(A.COLUMN_NAME, B.COLUMN_NAME)
 AS [Tabela]
,CASE WHEN A.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + B.TABLE_NAME
 WHEN B.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + A.TABLE_NAME
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT COLUMN_NAME
 ,TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
 AND TABLE_SCHEMA = 'Person'
) A
FULL OUTER JOIN
(
 SELECT COLUMN_NAME
 ,TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'AddressType'
 AND TABLE_SCHEMA = 'Person'
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
GO


4. Colunas diferentes de tabelas de bancos de dados diferentes

A consulta para a situação mais comum no meu dia-a-dia, dois bancos de dados de versões diferentes. Observe que nos exemplos que é necessário especificar o banco de dados das views.

Utilizando sys.columns:

USE [master]
GO
SELECT
 ISNULL(A.[name], B.[name])
 AS [Coluna]
,CASE WHEN A.[name] IS NULL
 THEN 'SOMENTE EM ' + B.[table]
 WHEN B.[name] IS NULL
  THEN 'SOMENTE EM ' + A.[table]
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT [name]
 ,'[AdventureWorks2008].[Person].[Address]' as [table]
 FROM [AdventureWorks2008].sys.columns
 WHERE [object_id] = OBJECT_ID('[AdventureWorks2008].[Person].[Address]')
) A
FULL JOIN
(
 SELECT [name]
 ,'[AdventureWorks].[Person].[Address]' as [table]
 FROM [AdventureWorks].sys.columns
 WHERE [object_id] = OBJECT_ID('[AdventureWorks].[Person].[Address]')
) B
ON A.[name] = B.[name]
GO

Utilizando INFORMATION_SCHEMA:

USE [master]
GO
SELECT
 ISNULL(A.COLUMN_NAME, B.COLUMN_NAME)
 AS [Tabela]
,CASE WHEN A.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + B.TABLE_NAME
 WHEN B.COLUMN_NAME IS NULL
 THEN 'SOMENTE EM ' + A.TABLE_NAME
 ELSE 'OK' END
 AS [Resultado]
FROM
(
 SELECT COLUMN_NAME
 , TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM [AdventureWorks2008].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
 AND TABLE_SCHEMA = 'Person'
) A
FULL OUTER JOIN
(
 SELECT COLUMN_NAME
 , TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME
 FROM [AdventureWorks].INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'Address'
 AND TABLE_SCHEMA = 'Person'
) B
ON A.COLUMN_NAME = B.COLUMN_NAME
GO


Eu tinha preparado algumas consultas mais complexas para comparação e merge de bancos, mas creio que somente com o conceito utilizado das consultas acima, seja possível desenvolver consultas para outros tipos de comparação.

Espero que tenham gostado, até o próximo artigo!

Query From Hell? – Episódio 2

Estes dias ressuscitaram uma das queries de envio de email que já utilizei, dai recordei como era enviar email com os antepassados do SQL Server 2005. Neles era preciso usar o SQL Mail, uma das funcionalidades mais bugadas que já existiram, algo em torno de 300 atualizações só para arrumá-la e tópicos permanentes de erros no Microsoft Suport, mas o SQL Mail deverá morrer depois do SQL Server 2008, COMMIT! e vida longa ao Database Mail!

Mas ainda nesta época, uma das maneiras de evitar o SQL Mail e enviar email em formato HTML pelo SQL Server era utilizando “OLE Automation”, ou seja, programar com componentes do Sistema Operacional (ex. CDONTS).

Como funcionava, e funciona até hoje, segue a “query from hell” que demonstra o uso “OLE Automation” para envio de emails, a salvação de muitos desenvolvedores no SQL Server 2000.

SP_CONFIGURE ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
SP_CONFIGURE ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
DECLARE  @FROM_ADDRESS   VARCHAR(100) = ‘sql@hell.com’,
         @TO_ADDRESS     VARCHAR(1000) = ‘sql@hell.com’,
         @CC_ADDRESS     VARCHAR(1000) = ”,
         @BCC_ADDRESS    VARCHAR(1000) = ”,
         @SUBJECT_EMAIL  VARCHAR(200) = ‘Ole Automation Procedures Test’,
         @BODY_EMAIL     VARCHAR(MAX) = ‘This is a test e-mail sent from Ole Automation Procedures on ALEPH.’,
         @TYPE_EMAIL     INT = 0,
         @OMAIL          INT,
         @RESULT         INT

SET @FROM_ADDRESS = REPLACE(@FROM_ADDRESS,’ ‘,”)
SET @FROM_ADDRESS = REPLACE(@FROM_ADDRESS,’;',’,')
SET @TO_ADDRESS = REPLACE(@TO_ADDRESS,’ ‘,”)
SET @TO_ADDRESS = REPLACE(@TO_ADDRESS,’;',’,')
SET @CC_ADDRESS = REPLACE(@CC_ADDRESS,’ ‘,”)
SET @CC_ADDRESS = REPLACE(@CC_ADDRESS,’;',’,')
SET @BCC_ADDRESS = REPLACE(@BCC_ADDRESS,’ ‘,”)
SET @BCC_ADDRESS = REPLACE(@BCC_ADDRESS,’;',’,')

EXEC @RESULT = SP_OACREATE
  ‘CDONTS.NEWMAIL’ ,
  @OMAIL OUT
IF @RESULT = 0
  BEGIN
    EXEC @RESULT = SP_OASETPROPERTY
      @OMAIL ,
      ‘FROM’ ,
      @FROM_ADDRESS
    
    EXEC @RESULT = SP_OASETPROPERTY
      @OMAIL ,
      ‘TO’ ,
      @TO_ADDRESS
    
    IF @CC_ADDRESS <> ”
      BEGIN
        EXEC @RESULT = SP_OASETPROPERTY
          @OMAIL ,
          ‘CC’ ,
          @CC_ADDRESS
      END
    
    IF @BCC_ADDRESS <> ”
      BEGIN
        EXEC @RESULT = SP_OASETPROPERTY
          @OMAIL ,
          ‘BCC’ ,
          @BCC_ADDRESS
      END
    
    EXEC @RESULT = SP_OASETPROPERTY
      @OMAIL ,
      ‘SUBJECT’ ,
      @SUBJECT_EMAIL
    
    EXEC @RESULT = SP_OASETPROPERTY
      @OMAIL ,
      ‘BODY’ ,
      @BODY_EMAIL
    
    EXEC @RESULT = SP_OASETPROPERTY
      @OMAIL ,
      ‘BODYFORMAT’ ,
      @TYPE_EMAIL
    
    EXEC @RESULT = SP_OASETPROPERTY
      @OMAIL ,
      ‘MAILFORMAT’ ,
      @TYPE_EMAIL
    
    EXEC @RESULT = SP_OAMETHOD
      @OMAIL ,
      ‘SEND’ ,
      NULL
    
    EXEC SP_OADESTROY
      @OMAIL
  END 

Se gostar dessa, dê uma olhada em outra, que utiliza “OLE Automation” para manipular o Excel pelo SQL Server:

http://mail.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/#sixth

Para quem quer facilidade para enviar email pelo SQL, consulte o tutorial sobre como configurar o Database Mail do SQL Server, escrito pelo Zavaschi:

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

Referências:

http://support.microsoft.com/kb/312839/pt-br

Query From Hell? – Episódio 1

Fui pesquisar as palavras chaves do blog e me deparei com a seguinte query:

SELECT count(log_id) as tot, max(log_datetime) as latest,
case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
FROM tblLog
WHERE log_referer LIKE ‘%q=%’ GROUP BY case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end
as log_referer ORDER BY tot desc, case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end

E pensei… Será que consegui encontrar uma query “do mal”??? Mas isso durou poucos segundo, até descobrir a presença deste trecho de código dentro da query 3 vezes:

case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1
when -1 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1))
else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3))
end

E perceber que o indivíduo que desenvolveu o código, somente queria formatar de forma condicional o resultado, agrupar e ordenar por este mesmo formato, algo facilmente simplificado por uma função scalar… deixando o código da seguinte forma:

CREATE FUNCTION dbo.FUNCAO(@log_referer VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @resultado VARCHAR(MAX)SELECT @resultado =
CASE charindex('&',right(@log_referer,len(@log_referer)-charindex('q=',@log_referer)+1))-1
WHEN -1 THEN
lower(left(right(@log_referer,len(@log_referer)-charindex('q=',@log_referer)-1),len(right(@log_referer,len(@log_referer)-charindex('q=',@log_referer)+1))-1))
ELSE
lower(left(right(@log_referer,len(@log_referer)-charindex('q=',@log_referer)-1),charindex('&',right(@log_referer,len(@log_referer)-charindex('q=',@log_referer)+1))-3))
ENDRETURN @resultado
END
GO

No final ficaria algo assim a query:

SELECT count(log_id) as tot
, max(log_datetime) as latest
, dbo.FUNCAO(log_referer)

FROM tblLog
WHERE log_referer LIKE ‘%q=%’
GROUP BY dbo.FUNCAO(log_referer)
AS log_referer

ORDER BY tot desc
, dbo.FUNCAO(log_referer)

Até a próxima!

Origem da query: http://blog.zog.org/2003/04/the_sql_query_f.html