Material de Apoio – Agosto 2017


Boa tarde pessoal!

Salve, salve amantes de banco de dados, Tudo bem?

Este é mais um post da sessão Material de Apoio, sendo o terceiro no decorrer de 2017 e de número 153 no total desta sessão.

Já passamos da metade de 2017, que loucura isso, como a rotina do dia a dia não nos deixa perceber o quanto o tempo na para de correr. Falando justamente da correria da nossas vida, a relação de arquivos compartilhadas neste post poderá justamente lhe ajudar a economizar muito do seu tempo.

O post de hoje

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts  catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA, e hoje principalmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Cláusula Values;
  • Comando Distinct;
  • Comando Select;
  • Comando Top;
  • CTE Recursiva para geração de sequência de datas;
  • CTE Recursiva para geração de sequência numérica de CEPs;
  • Extended Events;
  • Função Format;
  • Função PARSE;
  • Funções de Ranking – Row_Number;
  • Monitoramento de senhas;
  • Operador Cross Apply;
  • Operador Outer Appy;
  • Recursos bloqueados;
  • SPDIDs de Conexões;
  • SPIDs de usuários; e
  • User Defined Function para cálculo de anos em colunas computadas.

Tenho a certeza que este conteúdo poderá lhe ajudar muito em seus atividades profissionais e acadêmicas, fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos.

Material de Apoio

A seguir apresento a relação de scripts selecionados:

 

 

 

 

 

 

 

 

 

Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

Agradecimento

Quero agradecer imensamente a sua visita, espero lhe encontrar muitas vezes no decorrer deste ano em meu blog.

Não deixe de acessar os outros posts das demais sessões, e o próximo post desta sessão será publicado no mês de Novembro, até lá continue curtindo sua vida e compartilhando suas experiência.

Um forte abraço.

Microsoft anuncia o SQL Server 2012 Service Pack 4


A Microsoft anunciou no blog SQL Server Release Services que está planejando lançar o SQL Server 2012 Service Pack 4 ainda este ano.

O Service Pack 3 (ou SP3) foi lançado em novembro de 2015 e trouxe novidades como:

• Aprimoramentos de escalabilidade e desempenho para o mecanismo de armazenamento.

• Melhorias no desempenho da verificação de consistência.

• Dicas de consulta para fornecer controle granular enquanto estiver usando o Resource Governor.

• Adicionais recursos por meio de melhorias na DMV, Extended Events e planos de consulta de monitoramento.

• Melhorias no desempenho ao abrir e ler arquivos XEL usando leitor XEvent Linq.

Microsoft anuncia o SQL Server 2012 Service Pack 4

SQL Server 2012 Service Pack 4

De acordo com a Microsoft, o SQL Server 2012 Service Pack 4 será o último Service Pack para a versão 2012 do SQL Server e além de ser uma coleção de hotfixes, ele também trará mais de 20 melhorias com foco na performance, escalabilidade e diagnóstico com base no feedback de clientes e da comunidade.

Mais especificamente, o Service Pack 4 incluirá:

– Todas as correções e atualizações cumulativas para o SQL Server 2012.

– Melhorias na performance e escalabilidade.

– Recursos de monitoramento adicionais através de melhorias no DMV, Extended Events e Query Plans.

– Melhorias com base no feedback da comunidade.

– Melhorias introduzidas originalmente pelo SQL Server 2014 SP2 e SQL Server 2016 SP1.

A Microsoft confirmou que o SQL Server 2012 Service Pack 4 será lançado em setembro deste ano e um anúncio com mais informações sobre a atualização será publicado na época do lançamento.

Fontes e Direitos Autorais: SQL Server Release Services – SourabhAgarwal https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/

Short Script – Dezembro – 2015


Bom dia, Comunidade. Good Morning Everyone!!!!!

Como diria aquele apresentador “O louco meu…..”, 2015 esta chegando ao seu final parece que foi ontem que publiquei o primeiro post deste ano, realmente o tempo esta passando de forma assustadora e não estamos dando conta do quanto estamos vivendo, ou melhor sobrevivendo as loucuras do mundo.

Mantendo a escrita e tradição não poderia deixar passar a oportunidade de compartilhar com vocês os mais novos Short Scripts que adicionei a minha biblioteca de Scripts dedicados ao Microsoft SQL Server desde a versão 2000. Neste momento minha modesta biblioteca de arquivos esta composta por 1.121 scripts organizados em 66 pastas, sub-dividos em 4 categorias:

  • Comuns;
  • Básicos;
  • Intermediários; e
  • Avançados.

Pois bem, no post de hoje destaco os seguintes recursos ou funcionalidades:

  • Arquivo de ErrorLog e Logs Management;
  • Contagem de linhas existentes em tabelas;
  • CTE Recursiva;
  • Diferença entre datas desconsiderando sábado e domingo;
  • Extended Events Target;
  • Função para formatar a primeira letra de cada palavra em maiúscula;
  • Geração de combinação de letras;
  • Multiple Server in Query Windows;
  • Stored Procedure para gerar CNPJ e CPF;
  • SQLCMD Mode; e
  • Variável Table.

Fique a vontade para compartilhar, sugerir melhoras, críticas ou questionamentos sobre estes exemplos.

Segue abaixo a relação de short scripts:

1 – Exemplo – Utilizando Extended Events Target para separar e contar loings e logouts:

CREATE EVENT SESSION [CounterTest] ON SERVER

ADD EVENT sqlserver.login,

ADD EVENT sqlserver.logout(

ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.username))

ADD TARGET package0.event_counter

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO
— login and logout a few times

GO

— query for data

— Query the Target

SELECT

n.value(‘@name[1]’, ‘varchar(50)’) AS Event

, n.value(‘@count[1]’, ‘int’) AS EventCounts

FROM

( SELECT

CAST(t.target_data AS XML) AS target_data

FROM

sys.dm_xe_sessions AS s

JOIN

sys.dm_xe_session_targets AS t

ON

t.event_session_address = s.address

WHERE

s.name = ‘CounterTest’

AND t.target_name = ‘event_counter’

) AS tab

CROSS APPLY target_data.nodes(‘CounterTarget/Packages/Package/Event’) AS q ( n )

GO

 

2 – Exemplo – Stored Procedure para gerar CNPJ e CPF:

CREATE PROCEDURE dbo.stpGerador_CPF_CNPJ (
@Quantidade INT = 1,
@Fl_Tipo BIT = 1
)
AS BEGIN

IF (OBJECT_ID(‘tempdb..#Tabela_Final’) IS NOT NULL) DROP TABLE #Tabela_Final
CREATE TABLE #Tabela_Final (
Nr_Documento VARCHAR(18)
)

DECLARE
@n INT,
@n1 INT,
@n2 INT,
@n3 INT,
@n4 INT,
@n5 INT,
@n6 INT,
@n7 INT,
@n8 INT,
@n9 INT,
@n10 INT,
@n11 INT,
@n12 INT,

@d1 INT,
@d2 INT

— CPF
IF (@Fl_Tipo = 0)
BEGIN

WHILE (@Quantidade > 0)
BEGIN

SET @n = 9
SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @d1 = @n9 * 2 + @n8 * 3 + @n7 * 4 + @n6 * 5 + @n5 * 6 + @n4 * 7 + @n3 * 8 + @n2 * 9 + @n1 * 10
SET @d1 = 11 – ( @d1 % 11 )

IF ( @d1 >= 10 )
SET @d1 = 0

SET @d2 = @d1 * 2 + @n9 * 3 + @n8 * 4 + @n7 * 5 + @n6 * 6 + @n5 * 7 + @n4 * 8 + @n3 * 9 + @n2 * 10 + @n1 * 11
SET @d2 = 11 – ( @d2 % 11 )

IF ( @d2 >= 10 )
SET @d2 = 0

INSERT INTO #Tabela_Final
SELECT CAST(@n1 AS VARCHAR) + CAST(@n2 AS VARCHAR) + CAST(@n3 AS VARCHAR) + ‘.’ + CAST(@n4 AS VARCHAR) + CAST(@n5 AS VARCHAR) + CAST(@n6 AS VARCHAR) + ‘.’ + CAST(@n7 AS VARCHAR) + CAST(@n8 AS VARCHAR) + CAST(@n9 AS VARCHAR) + ‘-‘ + CAST(@d1 AS VARCHAR) + CAST(@d2 AS VARCHAR)

SET @Quantidade = @Quantidade – 1

END

END

— CNPJ
ELSE BEGIN

WHILE (@Quantidade > 0)
BEGIN

SET @n = 9
SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = 0
SET @n10 = 0
SET @n11 = 0
SET @n12 = 1

SET @d1 = @n12 * 2 + @n11 * 3 + @n10 * 4 + @n9 * 5 + @n8 * 6 + @n7 * 7 + @n6 * 8 + @n5 * 9 + @n4 * 2 + @n3 * 3 + @n2 * 4 + @n1 * 5
SET @d1 = 11 – ( @d1 % 11 )

IF (@d1 >= 10)
SET @d1 = 0

SET @d2 = @d1 * 2 + @n12 * 3 + @n11 * 4 + @n10 * 5 + @n9 * 6 + @n8 * 7 + @n7 * 8 + @n6 * 9 + @n5 * 2 + @n4 * 3 + @n3 * 4 + @n2 * 5 + @n1 * 6
SET @d2 = 11 – ( @d2 % 11 )

IF (@d2 >= 10)
SET @d2 = 0

INSERT INTO #Tabela_Final
SELECT ” + CAST(@n1 AS VARCHAR) + CAST (@n2 AS VARCHAR) + ‘.’ + CAST (@n3 AS VARCHAR) + CAST (@n4 AS VARCHAR) + CAST (@n5 AS VARCHAR) + ‘.’ + CAST (@n6 AS VARCHAR) + CAST (@n7 AS VARCHAR) + CAST (@n8 AS VARCHAR) + ‘/’ + CAST (@n9 AS VARCHAR) + CAST (@n10 AS VARCHAR) + CAST (@n11 AS VARCHAR) + CAST (@n12 AS VARCHAR) + ‘-‘ + CAST (@d1 AS VARCHAR) + CAST (@d2 AS VARCHAR);

SET @Quantidade = @Quantidade – 1

END

END

SELECT * FROM #Tabela_Final

END

 

3 – Exemplo – Reciclando o arquivo de ErrorLog e Logs Management:

EXEC sp_cycle_errorlog
GO

 

4 – Exemplo – Função – Formatar a primeira letra de cada palavra em maiúscula:

CREATE FUNCTION dbo.udfNomeProprio (
@Nome varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @Pos tinyint = 1
DECLARE @Ret varchar(250) = ”

WHILE (@Pos < LEN(@Nome) + 1)
BEGIN
IF @Pos = 1
BEGIN
–FORMATA 1.LETRA DA “FRASE”
SET @Ret += UPPER(SUBSTRING(@Nome, @Pos, 1))
END
ELSE IF (SUBSTRING(@Nome, (@Pos-1), 1) = ‘ ‘
AND SUBSTRING(@Nome, (@Pos+2), 1) <> ‘ ‘) AND (@Pos+1) <> LEN(@Nome)
BEGIN
–FORMATA 1.LETRA DE “CADA INTERVALO””
SET @Ret += UPPER(SUBSTRING(@Nome, @Pos, 1))
END
ELSE
BEGIN
–FORMATA CADA LETRA RESTANTE
SET @Ret += LOWER(SUBSTRING(@Nome,@Pos, 1))
END

SET @Pos += 1
END

RETURN @Ret
END
GO

SELECT dbo.udfNomeProprio(‘pedro antonio galvão junior’)
GO

 

5 – Exemplo – Utilizando Multiple Server in Query Windows using SQLCMD Mode:

— Sem o GO o SQLCMD Mode não entende o final do bloco —
:connect saom4276
select @@SERVERNAME

:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME

— Utilizando o comando Go para encerrar o bloco —
:connect saom4276
select @@SERVERNAME
Go

:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME
Go

 

6 – Exemplo – Gerando combinação de letras:

create table #t (string varchar (2))

declare @a1 varchar(1), @a2 varchar(1)
set @a1=’A’

while @a1 <= ‘Z’
begin
set @a2=’A’

while @a2 <= ‘Z’
begin
insert into #t select @a1 + @a2
set @a2 = char (ascii(@a2) + 1)
end

set @a1 = char (ascii(@a1) + 1)
end

select string from #t
where string like ‘_’ /*single underscore*/

Go

 

7 – Exemplo – Calculando diferença entre datas desconsiderando sábado e domingo:

declare @Data datetime set @Data = ‘2015/10/01’

–Calcula a quantidade de dias entre a data inicial e a data atual, excluindo sbados e domingos soma 1 no fim pois no conta o proprio dia

WITH AllDates AS

(   SELECT  TOP (DATEDIFF(DAY, @Data, GETDATE()))

D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @Data)

FROM    sys.all_objects a

CROSS JOIN sys.all_objects b

)

SELECT  WeekDays = COUNT(*) +1 –

–Clcula a quantidade de feriados entre as datas

(select count(*) from FTAFE(NOLOCK)

where convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112) between @Data and GETDATE()

and FTAFE.CD_CIDADE in(0)

and DATEPART(weekday, convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112)  ) not in(6,7))

FROM    AllDates

WHERE   DATEPART(WEEKDAY, D) NOT IN(6, 7)

Go
8 – Exemplo – Variável Table + CTE Recursiva para gerar sequência de letras:

declare @Tabela table
( COL1 INT,  COL2 VARCHAR(1));

insert into @Tabela values
(75, NULL),
(78, ‘C’),
(12, ‘B’),
(24, ‘D’)

;with CTE_Rec (COL1, COL2) as
(
select
COL1,
COL2
from @Tabela

union all

select
COL1,
case when ASCII(COL2) > 66 then CAST(CHAR(ASCII(COL2) – 1) AS VARCHAR(1))  end
from CTE_Rec as c
where
COL2 is not null
)

select
COL1,
COL2
from CTE_Rec
order by
COL1,
COL2

Go

 

9 – Exemplo – Quantidade de Linhas – Todas as Tables:

— Exemplo 1 —
SELECT t.[name], p.[rows]
FROM sys.schemas s INNER JOIN sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
ON p.[object_id] = t.[object_id]
AND p.[index_id] = i.[index_id]
Go

— Exemplo 2 —
;With Contador (name, rows)
As
(
SELECT t.[name], p.[rows]
FROM sys.schemas s INNER JOIN sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
ON p.[object_id] = t.[object_id]
AND p.[index_id] = i.[index_id]
)
Select name, rows,  ” as soma from Contador
Union all
Select ‘Total’ , Null, convert(varchar(10),sum(rows)) as soma from contador

Go

Caso você queria acessar os demais Short Scripts publicados em 2015, segue abaixo os links:

https://pedrogalvaojunior.wordpress.com/2015/10/21/short-scripts-outubro-2015/

https://pedrogalvaojunior.wordpress.com/2015/08/26/short-scripts-agosto-2015/

https://pedrogalvaojunior.wordpress.com/2015/06/03/short-script-junho-2015/

https://pedrogalvaojunior.wordpress.com/2015/05/04/short-script-maio-2015/

Mais uma vez obrigado por sua visita, espero que estes material posso ser útil e venha a colaborar em seu aprendizado.

Nos encontramos em breve…..

Short Scripts – Agosto 2015


Boa tarde, Comunidade!!!!

Tudo em paz? Mas que loucura este tempo aqui em São Roque e Sorocaba, hoje ás 6:30hrs da manhã estava 8º graus, agora ás 13hrs sensação térmica de 20º graus e subindo……

Nos últimos meses acabei deixando de publicar alguns posts relacionadas a minha sessão Short Scripts devido a correria da vida profissional e acadêmica, mas hoje vou pagar este dívida, compartilhando com vocês alguns dos meus novos short scripts.

Muitos tem me perguntado o porque acabou guardando tantos scripts ou códigos de exemplo, a resposta é bem simples e direta, sempre existirá alguém ou algo no mundo que poderá necessitar deste recurso e isso é que me mantem nesta jornada em cultivar minha biblioteca de scripts a cada dia mais atualização e completa(tarefa muito, mas muito complicada de se realizar).

Os Short Scripts apresentados hoje, estão relacionandos com os seguintes assuntos:

  • Block Process;
  • CTE;
  • Claúsula Output;
  • Comando Select;
  • Comando SET;
  • Concatenação de Valores;
  • Extended Events;
  • Índices;
  • Junção de Tabelas
  • Fatorial;
  • Funções; e
  • Tipos de Dados.

É isso galera, a seguir você vai poder encontrar os blocos de código que representam os Short Scripts, fique a vontade para copiar, compartilhar, sugerir melhorias e fazer suas críticas também.

 

— Short Script 1 –  Uitlizando claúsula Output em Delete com Inner Join + Select —

DECLARE @t TABLE

(nDex INT IDENTITY(1,1),

valu VARCHAR(9),

keey UNIQUEIDENTIFIER)

INSERT @t

VALUES (‘a’,NEWID()) , (‘b’,NEWID()),

(‘c’,NEWID()) , (‘d’,NEWID()),

(‘e’,NEWID()) , (‘f’,NEWID()),

(‘g’,NEWID()) , (‘h’,NEWID()),

(‘i’,NEWID()) , (‘j’,NEWID()),

(‘k’,NEWID())

DELETE t

OUTPUT DELETED.*

FROM @t AS t INNER JOIN (SELECT TOP 9 nDex FROM @t ORDER BY NEWID()) AS b

ON b.ndex = t.nDex

GO

 

 

— Short Script 2 – Uitilizando – CTE para separar palavras —

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,ji)

FROM split

 

— Short Script 3 – Simulando – Cenários de utilização de índices —

USE TempDB;

— Criando a Tabela Funcionário —

CREATE TABLE Funcionario

(ID int primary key,

NomeFunc varchar(200),

DataNasc date,

DataADM date);

Go

— Consulta 1 – Fazendo uso do Operador Clustered Index Scan —

SELECT * from Funcionario

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

— Criando um novo índice chamado Ind_NC_Funcionario_DataADM —

CREATE Nonclustered Index Ind_NC_Funcionario_DataADM On Funcionario (DataADM);

Go

— Consulta 2 – Fazendo uso do Operador Clustered Index Scan —

SELECT * from Funcionario

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

— Consulta 3 – Forçando o uso do índice IND_NC_Funcionario_DataADM, gerando Index Seek, Key Lookup e Nested Loops —

SELECT * from Funcionario with (index=Ind_NC_Funcionario_DataADM)

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

— Consulta 4 – Utilizando realmente o índice IND_NC_Funcionario_DataADM —

SELECT ID, DataADM

From Funcionario

Where DataADM between Convert(date, ‘1/1/2012’, 103) and Convert(date, ’31/1/2012′, 103)

Go

 

— Short Script 4 – Função – Concatenar valores em ordem decrescente —

CREATE FUNCTION dbo.ConcatenaValores (@C1 int, @C2 int, @C3 int, @C4 int, @C5 int)

returns int as

begin

declare @Concatenado varchar(5);

set @Concatenado= space(0);

SELECT @Concatenado+= Cast(N as char(1))

from (values (@C1), (@C2), (@C3), (@C4), (@C5)) as Numeros(N)

order by N desc;

return Cast(@Concatenado as int);

end;

go

Select Valor= dbo.ConcatenaValores(1, 2, 3, 4, 5);

 

— Short Script 5 – Criando tipo de dados NotNull mas Null sobrepondo Not Null —

CREATE TYPE NotNullType FROM VARCHAR(10) NOT NULL;

GO

— table create

CREATE TABLE Test(TestId INT, NullTest NotNullType NULL);

GO

— insert

INSERT INTO Test(TestId) VALUES(1);

SELECT NullTest FROM Test;

 

— Short Script 6 – Comparando comportamento entre os comandos SET e Select —

DECLARE @a INT= 0;

DECLARE @b INT= 0;

 

CREATE TABLE #tmpPrice ( Value INT );

SET @a = ( SELECT Value FROM #tmpPrice);

SELECT @b = Value FROM #tmpPrice;

SELECT @a AS, @b AS b

 

— Short Script 7 – Função para Calcular Fatorial com CTE encapsulada —

CREATE FUNCTION fatorial

(

@n INT

)

RETURNS INT

AS

BEGIN

DECLARE @val INT;

WITH fat(f, n) AS

(

SELECT CAST (1 as bigint) as f, 0 as n

UNION ALL

SELECT CAST (1 as bigint) as f, 1 as n

UNION ALL

SELECT f * (n + 1), n +1

FROM fat

WHERE n < 20 AND n <> 0

)

SELECT @val = f

FROM fat

WHERE n = @n

RETURN @val

END

GO

— Testando a função

SELECT dbo.fatorial(3);

SELECT dbo.fatorial(4);

SELECT dbo.fatorial(7);

 

— Short Script 8 –  CTE – Calculando fatorial —

WITH fat(f, n) AS

(

SELECT CAST (1 as bigint) as f, 0 as n — fat de 0 é 1

UNION ALL

SELECT CAST (1 as bigint) as f, 1 as n — fat de 1 é 1

UNION ALL

SELECT f * (n + 1), n +1

FROM fat

WHERE n < 20 AND n <> 0

— 20 é o limite neste caso, pois o fatorial de 21

— não cabe em um tipo bigint. O <> 0 é para cortar a recursão

— do primeiro âncora, senão repetiria tudo, faça o teste.

)

SELECT f

FROM fat

WHERE n = 12

 

— Short Script 9 – Capturar Blocked Process com Extended Events —

CREATE EVENT SESSION [Blocked] ON SERVER

ADD EVENT sqlserver.blocked_process_report

ADD TARGET package0.event_file

(SET filename=N’C:\xel\blocked.xel’)

GO

ALTER EVENT SESSION [Blocked]

ON SERVER

STATE = start;

GO

 

–Agora precisamos ler os arquivos .XEL gerados pela sessão e extrair os dados do XML para identificarmos as causas dos blocked process:

select theNodes.event_data.value(‘(//blocked-process/process)[1]/@spid’,‘int’) as blocking_process,

theNodes.event_data.value(‘(//blocked-process/process/inputbuf)[1]’,‘varchar(max)’) as blocking_text,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@clientapp’,‘varchar(100)’) as blocking_app,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@loginname’,‘varchar(50)’) as blocking_login,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@isolationlevel’,‘varchar(50)’) as blocking_isolation,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@hostname’,‘varchar(50)’) as blocking_host,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@spid’,‘int’) as blocked_process,

theNodes.event_data.value(‘(//blocking-process/process/inputbuf)[1]’,‘varchar(max)’) as blocked_text,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@clientapp’,‘varchar(100)’) as blocked_app,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@loginname’,‘varchar(50)’) as blocked_login,

theNodes.event_data.value(‘(//blocked-process/process)[1]/@isolationlevel’,‘varchar(50)’) as blocked_isolation,

theNodes.event_data.value(‘(//blocking-process/process)[1]/@hostname’,‘varchar(50)’) as blocked_host

from

(select convert(xml,event_data) event_data

from

sys.fn_xe_file_target_read_file(‘c:\xel\blocked*.xel’, NULL, NULL, NULL)) theData

cross apply theData.event_data.nodes(‘//event’) theNodes(event_data)

 

Chegamos ao fim de mais um Short Scripts, espero que você tenha gostado destes códigos!!!

Nos encontramos em breve.

Até a próxima…..