SQL GO!!!

select 'Blog focado em SQL Server' from blog where nome ='SQLGO.wordpress.com'

Arquivos da Categoria: TSQL

Cast e Convert

 

Para trabalhar com a conversão de dados é necessário que os tipos de dados sejam compatíveis entre si, como por exemplo, o numero 1 pode ser representado como smallint ou como bigint.

Cast é uma função do padrão ANSI e Convert é uma função do engine do SQL Server. Convert pode ser utilizado para formatação de valores do tipo datetime e money.

 

Sintaxe

Cast – CAST (expression AS data_type [ (length ) ] )

Convert – CONVERT (data_type [ (length ) ] ,expression [ ,style ] )

 

Conversão implícita – O engine do Sql Server realiza a conversão de forma automática sem especificar a função Cast ou Convert.

 

DECLARE @Num1 AS INT = 10000000,@Num2 AS TINYINT=3

–O SQL Server realizou a conversão automaticamente, o tinyint inicia do 0 ate 255

SELECT @Num1+@Num2

image

 

Conversão explicita – É necessário especificar a função Cast ou Convert.

SELECT GETDATE() AS ‘Valor sem converter’

SELECT CAST(GETDATE() AS TIME) as ‘CAST’

SELECT CONVERT(TIME, GETDATE()) as ‘CONVERT’

 

image

 

A imagem abaixo mostra todas as conversões de tipos de dados explícitas e implícitas que são permitidas para tipos de dados fornecidos pelo sistema SQL Server

 

Data type conversion table

 

Estilos de data e hora

O Convert também pode ser utilizado para formatar dados do tipo datetime e money, nesse caso é necessário especificar a formatação desejada.

 

SELECT CONVERT( VARCHAR, GETDATE(),111) as ‘111’

SELECT CONVERT( VARCHAR, GETDATE(),113) as ‘113’

SELECT CONVERT( VARCHAR, GETDATE(),108) as ‘108’

 

image

 

Lista de estilos disponíveis para campos do tipo datetime.

 

image

 

Saiba mais

CAST e CONVERT (Transact-SQL)

 

[]’s

Andressa Martins

Anúncios

Usando o comando MERGE

 

Um dos novos comandos do SQL Server 2008 é o merge, com esse comando podemos comparar registros entre duas fontes de dados (origem e destino). Você pode utilizar as ações de Insert, Update e Delete baseados no resultado da comparação entre as duas fontes.

 

Sintaxe:

INTO <target_table>Define a tabela ou view que será a fonte de destino.

AS – Define um alias.
 
USING <table_source> – Define a tabela ou view que será fonte de origem, baseado na condicao de merge da tabela destino com a tabela de origem.
 
ON <merge_search_condition>Define as condições de “Join” da tabela destino com a tabela origem.
 
WHEN MATCHED THEN – Define a ação a ser realizada quando há linhas na fonte de destino origem que correspondem com a fonte de origem.
 
WHEN NOT MATCHED [ BY TARGET ] THEN – Define a ação a ser realizada quando não há linhas da fonte de origem que correspondem com a fonte de destino. 
 

WHEN NOT MATCHED BY SOURCE THEN Define a ação a ser realizada quando há linhas na fonte de destino, mas não há linhas correspondentes na fonte de origem.

 

Exemplo

CREATE TABLE #Tabela1 ( Nome VARCHAR (100)

                                       , Cadastro DATETIME

                                       , Alteracao DATETIME

                                       , Ativo BIT)

INSERT #Tabela1

VALUES (‘Andressa’, GETDATE(), NULL, 1),

            (‘Joao’, GETDATE(), NULL, 1),

            (‘Maria’, GETDATE(), NULL, 1),

            (‘Ana’, GETDATE(), NULL, 1)

CREATE TABLE #Tabela2 ( Nome VARCHAR(100)

                                       , Twitter VARCHAR(100))

INSERT #Tabela2

VALUES (‘Andressa’,‘@dre_martins’),

            (‘Marieta’,‘@Marieta’),

            (‘Joaquim’,‘@Joaquim’),

            (‘José’,‘@jose’)

SELECT * FROM #Tabela1

SELECT * FROM #Tabela2

 
 

image

Agora vamos ao comando merge:

 

MERGE #Tabela1 AS Destino

USING #Tabela2 AS Origem

ON Destino.Nome = Origem.Nome

— Há registro no destino e na origem

WHEN MATCHED

THEN UPDATE SET Ativo = 0, Alteracao = GETDATE()

–Quando não há registro no destino e há na origem

WHEN NOT MATCHED

THEN INSERT (Nome, Cadastro, Ativo) VALUES (Origem.Nome, Getdate(),1)

–Quando  há registro no destino mas não há na origem

–e que sejam diferente de João

 

WHEN NOT MATCHED BY SOURCE AND Nome <>‘Joao’

THEN UPDATE SET Ativo = NULL, Alteracao = GETDATE();

SELECT * FROM #Tabela1

SELECT * FROM #Tabela2

 

Resultado:

image

 

Saiba mais

MERGE (Transact-SQL)

 

[]’s

Andressa Martins

CheckList diário utilizando o Reporting Services

Há algum tempo atrás eu li um artigo do Fabricio Lima (Criando um CheckList Automático do Banco de Dados ), adaptei os scripts dele as minhas necessidades e montei um relatório no Reporting Services, segue passo a passo o relatório que recebo diariamente por e-mail.

Para desenvolver um relatório é necessário ter instalado o Business Intelligence Development Studio, caso já tenha crie um projeto do tipo “Report Server Project”

clip_image002

Agora precisamos das fontes de dados compartilhadas, na aba Solution explorer clique com o botão direito em cima da pasta “Shared Data Sources” e selecione a opção “Add New Data Source”.

clip_image003

Defina as propriedades da conexão com o banco de dados.

clip_image005

Agora é necessário criar os DataSet de acordo com a sua necessidades, segue abaixo alguns

exemplos:

Para obtermos o tamanho das bases de dados da instância utilizamos a procedure abaixo.

— ============================================= 
— Author:  <Andressa> 
— Create date: <18/02/2011> 
— Description: <retorna dados do espaço utilizado pelas bases> 
— ============================================= 
CREATE PROCEDURE [dbo].[usp_Relatório_DBCheckList] 
  
AS 
BEGIN  
DECLARE @tabela TABLE (Nome  NVARCHAR(128),Tamanho float,NaoAlocado float) 
DECLARE AllDatabases CURSOR FOR  
  SELECT name
  FROM sys.databases
  WHERE STATE = 0 
   
OPEN AllDatabases 
 
   DECLARE @DBNome NVARCHAR(128),@SQL NVARCHAR(MAX) 
 
    FETCH NEXT FROM AllDatabases INTO @DBNome 
     WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SET @SQL = N’USE ‘ + @DBNome +’ 
         declare @objname nvarchar(776), @id int   ,@type character(2)  ,@pages bigint    , 
         @dbname sysname    ,@dbsize bigint    ,@logsize bigint    ,@reservedpages  bigint    , 
         @usedpages  bigint    ,@rowCount bigint   
          
         SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)   
         if @id is null   
          begin   
            select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))   
             ,@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))   
            from dbo.sysfiles   
          end 
          select   @reservedpages = sum(a.total_pages),   
             @usedpages = sum(a.used_pages),   
             @pages = sum(   
            CASE   
             When it.internal_type IN (202,204) Then 0   
             When a.type <> 1 Then a.used_pages   
             When p.index_id < 2 Then a.data_pages   
             Else 0   
            END   
            )   
         from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id   
           left join sys.internal_tables it on p.object_id = it.object_id   
           select database_name = db_name(),   
           database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))    
           * 8192 / 1048576,15,2)),   
           ”unallocated space” = ltrim(str((case when @dbsize >= @reservedpages then   
           (convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))    
           * 8192 / 1048576 else 0 end),15,2) ) ‘  
        INSERT INTO @tabela 
        EXEC sp_executesql @SQL 
 
         FETCH NEXT FROM AllDatabases INTO @DBNome 
      END 
    SELECT Nome, Tamanho, NaoAlocado from @tabela 
  CLOSE AllDatabases 
  DEALLOCATE AllDatabases 
END

Agora referencie a procedure no dataset.

clip_image007

Tamanho das unidades de Disco do servidor (DataSet_Disco)

 
XP_FIXEDDRIVES
 

 

Tamanho dos arquivos de log de toda a instância (DataSet_LogSpace)

DBCC SQLPERF(LOGSPACE)
 

 

 

Listagem de Jobs que estão em execução (DataSet_JobsExecucao)

SELECT Name AS Nome

,run_Requested_Date AS DataExecucao

,DATEDIFF(mi,run_Requested_Date

,GETDATE()) AS Duracao_Minutos

FROM msdb..sysjobactivity A INNER JOIN

msdb..sysjobs B ON A.job_id = B.job_id

WHERE start_Execution_Date IS NOT NULL AND stop_execution_date IS NULL

Listagem dos Jobs que não foram executados com sucesso (DataSet_JobFailed)

DECLARE @Ontem INT

DECLARE @OntemHorario INT

SET @OntemHorario= CAST((RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(HOUR,DATEADD(HOUR,-1, GETDATE())))), 2) +RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(MINUTE,GETDATE()))), 2)+RIGHT(‘0’+CONVERT(VARCHAR(2), (DATEPART(SECOND,GETDATE()))), 2)) AS INT)

SET @Ontem = CAST(CONVERT (VARCHAR(8),(DATEADD (DAY, -1, GETDATE())),112) AS INT)

CREATE TABLE #HistoricoJobs( Cod INT identity(1,1)

,IDInstancia INT

,IDJob VARCHAR(255)

,NomeJob VARCHAR(255)

,IDStep INT

,NomeStep VARCHAR(255)

,IDSqlMensagem INT

,SqlSeverity INT

,SqlMensangem VARCHAR(3990)

,IDStatusExecucao INT

,DataExecucao VARCHAR(20)

,HorarioExecucao VARCHAR(20)

,DuracaoExecucao INT

,EmailOperador VARCHAR(100)

,NetSenderOperador VARCHAR(100)

,PagerOperador VARCHAR(100)

,TentativasRealizadAS INT

,NomeServidor VARCHAR(100))

INSERT INTO #HistoricoJobs

EXEC Msdb.dbo.SP_HELP_JOBHISTORY NULL,NULL,NULL,NULL,NULL,@Ontem,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’FULL’

SELECT NomeJob

,CASE

WHEN IDStatusExecucao = 0 THEN ‘Failed’

WHEN IDStatusExecucao = 1 THEN ‘Succeeded’

WHEN IDStatusExecucao = 2 THEN ‘Retry (step only)’

WHEN IDStatusExecucao = 3 THEN ‘Canceled’

WHEN IDStatusExecucao = 4 THEN ‘In-progress message’

WHEN IDStatusExecucao = 5 THEN ‘Unknown’ END Status

,CAST(DataExecucao + ‘ ‘ + RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-5),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-3),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(HorarioExecucao,(LEN(HorarioExecucao)-1),2) ,2) AS VARCHAR) Dt_Execucao

,RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-5),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-3),2) ,2)+ ‘:’ +

RIGHT(’00’ + SUBSTRING(cast(DuracaoExecucao AS VARCHAR),(LEN(DuracaoExecucao)-1),2) ,2) DuracaoExecucao

,SqlMensangem

FROM #HistoricoJobs

WHERE ((DataExecucao >=@Ontem AND HorarioExecucao >= @OntemHorario) OR DataExecucao >@Ontem)

AND IDStep = 0

AND IDStatusExecucao <> 1

ORDER BY Dt_Execucao

DROP TABLE #HistoricoJobs

Listagem dos últimos backups (DataSet_Backup)

SELECT database_name as NomeDataBase

, name as Nome

,Backup_start_date as DataInicio

, datediff(mi,Backup_start_date,Backup_finish_date) Duracao_Min

, server_name as NomeServidor

,recovery_model as RecoveryModel

, cast(backup_size/1024/1024 as numeric(15,2)) As Tamanho

,type as Tipo

FROM msdb.dbo.backupset B

INNER JOIN msdb.dbo.backupmediafamily BF ON

B.media_set_id = BF.media_set_id

WHERE Backup_start_date >= DATEADD(HH, 18 ,@Dt_Referencia – 1 ) –backups realizados a partir das 18h de ontem

and Backup_start_date < DATEADD (DAY, 1, @Dt_Referencia)

order by database_name, Backup_start_date

/

Após criar os DataSets você terá uma lista semelhante a imagem abaixo:

clip_image018

Para criar um relatório clique com o botão direito em cima da pasta report e “New Item”, selecione “report”, nomeie o arquivo e clique em “Add”. Feito isso você precisa  criar os DataSources/DataSets utilizando o DataSource/DataSet reference correspondente.

image

Com a parte de acesso a dados já finalizada, precisamos criar a parte gráfica do relatório, montei umas tabelas simples e sem formatação apenas para o melhor entendimento.

Para adicionar um campo à uma tabela é simples, somente arrastar o campo do dataset para a tabela desejada.

clip_image020

Abaixo eu apliquei uma formatação simples apenas para diferenciar os dados do nosso relatório (job, bkp, log…)

Utilizei um recurso opcional de alterar a cor da célula de acordo com o valor da linha, assim quando um banco de dados estiver com pouco espaço alocado eu posso visualizar de maneira mais rápida facilitando a tomada de decisão, segue o comando que eu montei.

image

=IIF(
((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.9,
    "#f28989",
    IIF((((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value)>0.8
            and ((Fields!Tamanho.Value-Fields!NaoAlocado.Value)/Fields!Tamanho.Value) < 0.9),
    "#ffff80",
    "#c0ffc0"))

Você pode adaptar a expressão acima para toda as tabelas.

Interpretação  do código:

Quando o espaço alocado do meu banco de dados for…

  • maior que 90% a linha fica vermelha.
  • maior que 80% e menor que 90% a linha fica amarela.
  • menor que 80% a linha fica verde.

Vejam como ficou o relatório depois de renderizado.

image



 
O projeto de relatórios está disponível pra download.
 
Espero que tenham gostado da dica.
 
Andressa

Tipo de dados numéricos, qual usar?

O SQL Server nos oferece muitas opções de dados dos tipos numéricos mas que raramente são utilizados. Quando projetamos um banco de dados devemos nos atentar ao tamanho dos campos pois isso certamente irá causar um crescimento desnecessário na base de dados.

 

 INT, SMALLINT, TINYINT, BIGINT

 

Tipo de dados

Range

Tamanho

BIGINT

-9.223.372.036.854.775.808 a 9.223.372.036.854.775.808

8 bytes

INT

-2.147.483.648 a 2.147.483.647

4 bytes

SMALLINT

-32,768 a 32,367

2 bytes

TINYINT

0 a 255

1 byte

 

MONEY,SMALLMONEY

Tipo de dados

Range

Tamanho

MONEY

-922.337.203.685.477,5808 a 922.337.203.685.477,5807

8 bytes

SMALLMONEY

– 214.748,3648 to 214.748,3649

4 bytes

FLOAT, REAL

Tipo de dados

Range

Tamanho

FLOAT (N)

– 1,79E+308 a -2,23E-308, 0 e 2,23E-308 a 1,79E+308

Depende do valor de n

SMALLMONEY

– 3,40E + 38 a -1,18E – 38, 0 e 1,18E – 38 a 3,40E + 38

4 bytes

Valor n

Precisão

Tamanho

1-24

7 dígitos

4 bytes

25-53

15 dígitos

8 bytes

NUMERIC, DECIMAL

Tipo de dados

Range

Tamanho

NUMERIC

– 10^38 +1 a 10^38 – 1

8 bytes

DECIMAL

– 10^38 +1 a 10^38 – 1

4 bytes

Precisão

Tamanho

1-9 dígitos

5 bytes

10-19 dígitos

9 bytes

20-28 dígitos

13 bytes

29-38 dígitos

17 bytes

Saiba mais em:

Numeric Types