E agora DBA?: junho 2011

A primeira a gente nunca esquece!

Leitores amigos,

A pouco mais de um ano iniciei o E Agora DBA? com esse post. Um dos objetivos desse blog era um desafio pessoal - "Conseguir uma certificação oficial da Microsoft sem a necessidade de cursar os caros cursos oficiais".
Eu sempre tive muito receio em investir dois ou tres mil reais em formação e cair em uma turma onde o orientador apenas lê o material oficial e repete os exercicios em um data show. Ler eu ja sei e faço bem mais barato. Mas como sempre acontece, o corre corre nos faz deixar alguns planos de lado. Um bom tempo se passou e o assunto na verdade tinha ficado até esquecido.
Foi mais ou menos na mesma época que retomei esse blog que a idéia voltou ao meu radar.

Então eu arrisquei. Tirei o escorpião do bolso e paguei os US$ 80,00 e comecei uma revisão com esse livro aqui. Eram tres semanas para preencher as lacunas que faltavam para chegar na MCTS: Database Development.

Então, em uma segunda feira chuvosa estava eu na Avenida Paulista aguardando o horário do exame, tomando um suco de laranja e nervoso como um vestibulando. Subi ao centro de exames. E cai dentro do teste. E passei!!

A alegria de conseguir passar em um teste como esse não é puro exibicionismo. O profissional de TI é por natureza um entusiasta que gosta de desafios, a aprovação é colocar a prova tudo o que voce sabe sobre o assunto e converter isso em uma métrica. A meta são os 700 pontos. Passa-los é uma vitória.

Estou feliz meus amigos. Mas nao existe felicidade eterna. Portanto.. MCITPRO aqui vou eu!

Nossos primeiros erros

O objetivo deste post é bem simples. Quando iniciamos nossa vida de DBA/Desenvolvedor nos deparamos com erros simples que nos roubam um tempo precioso na procura pela resposta. Acredito que esse tempo se perde pela mensagem de erro dificil ou pela tradução para o portugues que é terrivel no SQL. (eu prefiro usar tudo no inglês mesmo)

-- Crie essa simples tabela.

CREATE TABLE PrimeirosErros (ErroID int identity(1,1) PRIMARY KEY,Erro varchar(5))

-- Primeiro erro

INSERT INTO PrimeirosErros (Erro) VALUES ('123456')

-- Erro --------------

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

Msg 8152, Level 16, State 14, Line 1

Dados de cadeia ou binários seriam truncados.

----------------------

Explicação: Voce está inserindo um valor que estoura o tamanho maximo da coluna (Valor com 6 caracteres, campo definido para 5 caracteres)

Basta reduzir o tamanho que voce está inserindo ou aumentar a capacidade da coluna que voce está Parece besta, mas ja vi muito cara bom ficar um pouco perdido com essa mensagem esquisita.

-- Segundo erro:

INSERT INTO PrimeirosErros VALUES (1,'Teste' )

-- Erro --------------

Msg 8101, Level 16, State 1, Line 1

Um valor explícito para a coluna de identidade na tabela 'PrimeirosErros' só pode ser especificado quando uma lista de colunas é usada e o valor de IDENTITY_INSERT é ON.

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'PrimeirosErros' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Explicação: A coluna é identity (no popular auto-numérica). Como voce está forçando um valor para a coluna identity, voce deve ligar o INDENTY_INSERT da tabela para permitir a inclusão

SET IDENTITY_INSERT PrimeirosErros ON

INSERT INTO PrimeirosErros VALUES (1,'Teste' )

Ué...o erro continuou! E agora DBA?

A resposta continua simples, além de habilitar a inserção de valores explicitos na coluna voce deve especificar o insert da maneira mais explicita possivel, isto é,

incluindo a lista de colunas que receberão os dados

INSERT INTO PrimeirosErros (ErroID,Erro) VALUES (1,'Teste' )

Mas, por favor, não pare por ai. Nunca se esqueça de desligar o identity_insert. Senão ao executar o de baixo....

INSERT INTO PrimeirosErros (Erro) VALUES ('Teste2' )

-- Erro --------------

Msg 545, Level 16, State 1, Line 1

Explicit value must be specified for identity column in table 'PrimeirosErros' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Msg 545, Level 16, State 1, Line 1

Deve ser especificado um valor explícito para a coluna de identidade na tabela 'PrimeirosErros' quando IDENTITY_INSERT está definido como ON ou quando um usuário de replicação insere em uma coluna de identidade NOT FOR REPLICATION.

----------------------

Voce deve desligar o IDENTITY_INSERT da tabela para voltar tudo ao normal.

SET IDENTITY_INSERT PrimeirosErros OFF

Espero que essa pequena explicação tenha poupado seu tempo, os erros fazem parte de nossas vidas.
Se eles não existissem, talvez metade de nós estivesse desempregada :D

Abraços!

Felipe Antunes

AVISO:ESSE POST É UM SCRIPT! SE VOCE DESEJAR, VOCE PODE COPIA-LO E COLA-LO DIRETAMENTE NO SEU MANAGEMENT STUDIO

CTE Recursiva - Uma consulta elegante

    Pessoal, muito obrigado pelo retorno do post anterior, no total foram duas centenas de pageviews,cinco emails, três comentarios, um telefonema e um visitante croata(sim é verdade!).
    Motivado por esse retorno sigo com a continuação:
  CTE com Recursividade!!!

   Veja o caso “CTE no Busão”:

       Imagine uma determinada linha de onibus que realiza 10 paradas entre seu trajeto de Guarulhos a Campinas. O onibus possui um sistema que registra a data/hora de cada parada e qual foi a distancia percorrida entre uma parada e outra. Scrip Exemplo

-- Estrutura

CREATE TABLE Paradas (

             ParadaID int identity (1,1) PRIMARY KEY, -- PK
             DataParada datetime, -- Data/hora da parada
             Distancia int, -- distancia percorrida da ultima parada até agora
             ParadaAnteriorID int -- id da parada anterior
             )
-- Populando
INSERT INTO Paradas (DataParada,Distancia)
VALUES (GETDATE(),0)
GO

INSERT INTO Paradas (DataParada,Distancia,ParadaAnteriorID)
SELECT TOP 1
       DATEADD(MINUTE ,(RAND() * 100)+ 5,DataParada) ,
       CAST(RAND() * 33 as Int )+ 5 ,
       @@IDENTITY
FROM
       Paradas
ORDER BY DataParada DESC
GO 10

Dando um SELECT simples temos o seguinte resultado

ParadaID DataParada Distancia ParadaAnteriorID

----------- ----------------------- ----------- ----------------
1           2011-06-07 17:23:59.543 0           NULL
2           2011-06-07 18:10:59.543 36          1
3           2011-06-07 19:31:59.543 30          2
4           2011-06-07 20:17:59.543 5           3
5           2011-06-07 21:37:59.543 27          4
6           2011-06-07 23:04:59.543 21          5
7           2011-06-07 23:19:59.543 37          6
8           2011-06-08 00:31:59.543 21          7
9           2011-06-08 01:13:59.543 6           8
10          2011-06-08 02:18:59.543 23          9
11          2011-06-08 02:37:59.543 28          10

--------------------------------------------------------------------

Eis então que chega o Gerente da companhia de Viação e te pede um relatório que exiba cada parada e quantos KMs foram percorridos do inicio da viagem até aquele momento. Ai que entra o CTE.

Uma CTE recursiva começa com a chamada Ancora. O registro Ancora é o fio por onde voce puxa a recursão. No nosso caso começamos com o registro inicial da viagem.

-- Ancora
SELECT
       DataParada,
       Distancia,
       ParadaID
FROM
       Paradas
WHERE
       ParadaAnteriorID IS NULL

    Na sequência via UNION ALL montamos uma query semelhante fazendo um Join com a própria CTE , veja abaixo

WITH CTE_Viagem (DataParada,KMRodados,ParadaID)

AS (
-- Ancora
SELECT
       DataParada,
       Distancia,
       ParadaID
FROM
       Paradas
WHERE
       ParadaAnteriorID IS NULL
UNION ALL
-- Recursão
SELECT
       P.DataParada,
       P.Distancia + C.KMRodados,
/* Pulo do Gato! Acima é que se soma a distancia atual com a distancia da anterior */
       P.ParadaID
FROM
       Paradas P
INNER JOIN CTE_Viagem C -- NOTE AQUI A CTE!!!
             ON P.ParadaAnteriorID = C.ParadaID
       )

SELECT
       DataParada,KMRodados,ParadaID
FROM
       CTE_Viagem

E olha que maravilha de resultado!!! E como é elegante.

DataParada              KMRodados   ParadaID
----------------------- ----------- -----------
2011-06-07 17:23:59.543 0           1
2011-06-07 18:10:59.543 36          2
2011-06-07 19:31:59.543 66          3
2011-06-07 20:17:59.543 71          4
2011-06-07 21:37:59.543 98          5
2011-06-07 23:04:59.543 119         6
2011-06-07 23:19:59.543 156         7
2011-06-08 00:31:59.543 177         8
2011-06-08 01:13:59.543 183         9
2011-06-08 02:18:59.543 206         10
2011-06-08 02:37:59.543 234         11

     Não contente (e preguiçoso em fazer contas) o mesmo gerente te pede para colocar no relatório a quantidade em minutos que a viagem demorou até aquele momento. E voce simplesmente adiciona o seguinte:

WITH CTE_Viagem (DataParada,KMRodados,TempoViagem,ParadaID)

AS
(
-- Ancora
SELECT
       DataParada,
       Distancia,
       0 AS TempoViagem,
       ParadaID
FROM
       Paradas
WHERE
       ParadaAnteriorID IS NULL
UNION ALL
-- Recursão
SELECT
       P.DataParada,
       P.Distancia + C.KMRodados,
       C.TempoViagem + DATEDIFF(minute,C.DataParada,P.DataParada),--Pulo do Gato!
       P.ParadaID
FROM
       Paradas P
INNER JOIN CTE_Viagem C ON P.ParadaAnteriorID = C.ParadaID
       )
SELECT
       DataParada,KMRodados,TempoViagem,ParadaID
FROM
           CTE_Viagem

E resultado:

DataParada KMRodados TempoViagem ParadaID

----------------------- ----------- ----------- -----------
2011-06-07 17:23:59.543 0           0           1
2011-06-07 18:10:59.543 36          47          2
2011-06-07 19:31:59.543 66          128         3
2011-06-07 20:17:59.543 71          174         4
2011-06-07 21:37:59.543 98          254         5
2011-06-07 23:04:59.543 119         341         6
2011-06-07 23:19:59.543 156         356         7
2011-06-08 00:31:59.543 177         428         8
2011-06-08 01:13:59.543 183         470         9
2011-06-08 02:18:59.543 206         535         10
2011-06-08 02:37:59.543 234         554         11

Demais!

Explicações técnicas, detalhadas e sem emoção a seguir no nosso amigo books online

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Abraços!!!

Voce já usou uma CTE?

CTE é um recurso adicionado no SQL 2005 e pouquissimo utilizado por nossas bandas. Elas são parecidas com subqueries,você cria e nomeia um SELECT qualquer e ela passa a se comportar como uma tabela dentro do seu batch.
Diferentemente da subquery, a CTE pode se autoreferenciar e ser reaproveitado diversas vezes em um mesmo comando. Além de fornecer recursividade de um jeito bem bacana

Em um exemplo pratico.

Imagine que temos uma tabela padrão de clientes e uma tabela que armazena todas as compras que o cliente armazenou. Segue os scripts

-- Criando as tabelas

CREATE TABLE Cliente

      (
       ClienteID int identity(1,1) PRIMARY KEY,
       Cliente varchar(100) NOT NULL
       )
GO
CREATE TABLE Compras
      (
       CompraID int identity(1,1) PRIMARY KEY,
       ClienteID int NOT NULL FOREIGN KEY REFERENCES Cliente(ClienteID),
       DataCompra datetime
        )

-- Populando as tabelas (opcional, voce pode inventar seu proprio metodo de população)

INSERT INTO Cliente (Cliente)

SELECT UPPER(name) FROM master.sys.objects

INSERT INTO Compras (ClienteID,DataCompra)

SELECT
      Cliente.ClienteID,
      CAST(dateadd(MINUTE,(RAND(clienteid) * 100000),creation_time) as DATE)
FROM sys.dm_os_threads
CROSS JOIN Cliente
GROUP BY
     CAST(dateadd(MINUTE,(RAND(clienteid) * 100000),creation_time) as DATE),Cliente.ClienteID

------------------------------------------------------------

Imagine a necessidade de um campo DataUltimaCompra na tabela Cliente.

Um UPDATE entre as tabelas mais um Group By não é possivel. Com uma CTE a coisa fica mais simples. Veja abaixo:

-- Adiciona o campo

ALTER TABLE Cliente ADD DataUltimaCompra datetime

-- Declaração da CTE

;WITH CTE_UltimaCompra (ClienteID,UltimaCompra)
AS
(     SELECT
            ClienteID,
            MAX(DataCompra) UltimaCompra
      FROM Compras
      GROUP BY

ClienteID

)

-- Comando utilizando a CTE como se fosse uma tabela

UPDATE C
SET C.DataUltimaCompra = CTE.UltimaCompra
FROM Cliente C
INNER JOIN CTE_UltimaCompra CTE ON C.ClienteID = CTE.ClienteID

-- Com uma pequena modificação voce pode até colocar esse script em uma trigger

-- e manter esse campo atualizado na inserção de uma nova compra

CREATE TRIGGER tr_AtualizaUltimaCompra

ON Compras
FOR Insert,Update
AS
      WITH CTE_UltimaCompra (ClienteID,UltimaCompra)
      AS
      (     SELECT
                  C.ClienteID,
                  MAX(C.DataCompra) UltimaCompra
            FROM Compras C
            inner join inserted I ON C.ClienteID = I.ClienteID
            -- Atenção ao pulo do gato acima
            GROUP BY
                 C.ClienteID
      )

UPDATE C

      SET C.DataUltimaCompra = CTE.UltimaCompra
      FROM Cliente C
      INNER JOIN CTE_UltimaCompra CTE ON C.ClienteID = CTE.ClienteID

     No próximo post coloco um exemplo de recursividade com CTE.

     Abaixo o link do nosso querido Books Online com uma descrição completa

http://msdn.microsoft.com/en-us/library/ms175972.aspx

Abraços!