Short Scripts – Março 2019


Olá boa tarde, estamos no mês de março, primeiro trimestre de 2019 esta quase se encerrando.

Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 36 posts publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a alguns meses no final do último post desta sessão, publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.

O post de hoje

Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Cálculo de Idade;
  • Cláusula Values;
  • Cláusula Where;
  • Comando Create Procedure;
  • Comando Declare;
  • Comando Exec;
  • Comando Group By;
  • Comando Insert;
  • Comando Order By;
  • Comando Pivot;
  • Comando Select;
  • Common Table Expression;
  • Diretiva Set;
  • DMV sys.dm_os_ring_buffers;
  • DMV sys.dm_os_virtual_address_dump;
  • Filegroup;
  • Função Concat();
  • Função DateDiff();
  • Função de Agregação SUM;
  • Função Month();
  • Função Year();
  • Gerenciamento de Memória;
  • Grupo de Arquivos;
  • Inner Joins;
  • Left Joins;
  • Operador Lógico Condicional Case;
  • Operador Lógico Condicional While;
  • Stored Procedure;
  • Variáveis;
  • Variável do Tipo Tabela;
  • Visão;
  • Visão de Sistema sys.all_objects;
  • Visão de Sistema sys.filegroups;
  • Visão de Sistema sys.indexes;
  • Visão de Sistema sys.syslanguages;
  • Visão de Sistema SYSCACHEOBJECTS; e
  • Visão de Sistemas sys.messages.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2019. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique à vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Alterando a sequência numérica com base em uma condição —

— Criando a Tabela Valores —
Create Table Valores
(Contador Int Primary Key Identity(1,1),
CodigoCaracter Char(4),
SequenciaDeControle Int)
Go

 

— Inserindo os registros com CódigoCaracter 0001 —
Insert Into Valores Values (‘0001’,1),(‘0001’,2),(‘0001’,3),(‘0001’,4)
Go

— Inserindo os registros com CódigoCaracter 0002 —
Insert Into Valores Values (‘0002’,5),(‘0002’,6),(‘0002’,7),(‘0002’,8)
Go

— Inserindo os registros com CódigoCaracter 0003 —
Insert Into Valores Values (‘0003’,9),(‘0003’,10)
Go

 

— Consultando os registros inseridos —
Select Contador, CodigoCaracter, SequenciaDeControle from Valores
Go

 

— Declarando um bloco de execução para alterar os registros de acordo com uma condição —
Declare @ContadorRegistros Int,
@ContadorSequenciaDeControle Int,
@ValorColunaCodigoCaracter Char(4)

Set @ContadorRegistros=1
Set @ContadorSequenciaDeControle=1

While @ContadorRegistros <= (Select Count(CodigoCaracter) from Valores)
Begin

Set @ValorColunaCodigoCaracter=(Select CodigoCaracter From Valores Where Contador = @ContadorRegistros)

If (@ValorColunaCodigoCaracter = (Select CodigoCaracter From Valores Where Contador = @ContadorRegistros) And @ContadorRegistros > 1)
Set @ContadorSequenciaDeControle=@ContadorSequenciaDeControle + 1
Else
Set @ContadorSequenciaDeControle=0

Update Valores
Set SequenciaDeControle = @ContadorSequenciaDeControle
Where Contador = @ContadorRegistros

Set @ContadorRegistros += 1

End

 

— Consultando os registros após a alteração —
Select Contador, CodigoCaracter, SequenciaDeControle from Valores
Go

 

— Short Script 2 – Utilizando Extended Events – Ring Buffer através da sessão system_health para obter detalhes sobre conexões abertas —

Declare @LanguageID int

Select @LanguageID = lcid
From sys.syslanguages
Where name = @@Language

WITH RingBufferXML
As (SELECT CAST(record as xml) AS RecordXML
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type= ‘RING_BUFFER_CONNECTIVITY’),
RingBufferConnectivity
As (SELECT x.y.value(‘(/Record/@id)[1]’, ‘int’) AS [RecordID],
x.y.value(‘(/Record/ConnectivityTraceRecord/RecordType)[1]’, ‘varchar(max)’) AS RecordType,
x.y.value(‘(/Record/ConnectivityTraceRecord/RecordTime)[1]’, ‘datetime’) AS RecordTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/SniConsumerError)[1]’, ‘int’) AS Error,
x.y.value(‘(/Record/ConnectivityTraceRecord/State)[1]’, ‘int’) AS State,
x.y.value(‘(/Record/ConnectivityTraceRecord/Spid)[1]’, ‘int’) AS SPID,
x.y.value(‘(/Record/ConnectivityTraceRecord/RemoteHost)[1]’, ‘varchar(max)’) AS RemoteHost,
x.y.value(‘(/Record/ConnectivityTraceRecord/RemotePort)[1]’, ‘varchar(max)’) AS RemotePort,
x.y.value(‘(/Record/ConnectivityTraceRecord/LocalHost)[1]’, ‘varchar(max)’) AS LocalHost,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TotalTime)[1]’, ‘int’) AS TotalTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/EnqueueTime)[1]’, ‘int’) AS EnqueueTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetWritesTime)[1]’, ‘int’) AS NetWritesTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetReadsTime)[1]’, ‘int’) AS NetReadsTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/TotalTime)[1]’, ‘int’) AS SslTotalTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/TotalTime)[1]’, ‘int’) AS SspiTotalTime,
x.y.value(‘(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime)[1]’, ‘int’) AS TriggerAndResGovTime
FROM RingBufferXML
CROSS APPLY RecordXML.nodes(‘//Record’) AS x(y))

SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
RBC.Error = M.message_id AND M.language_id = @LanguageID
WHERE RBC.RecordType IN (‘Error’, ‘LoginTimers’)
ORDER BY RBC.RecordTime DESC
Go

 

— Short Script 3 – Stored Procedure – Realizando o versionamento de código fonte  —

CREATE PROCEDURE dbo.GetOne
AS
SELECT 1
GO

CREATE PROCEDURE dbo.GetOne;2 —Versionamento, recurso antigo
AS
SELECT 2
GO

 

Exec dbo.GetOne;2 — Executando a procedure versão 2
go

 

 

— Short Script 4 – Gerenciamento de Memória  — Como encontrar quem está usandoo espaço de endereço virtual no seu SQL Server —

 

SELECT convert(varchar,getdate(),120) as [Timestamp], max(region_size_in_bytes)/1024 [Total max contiguous block size in KB]

from sys.dm_os_virtual_address_dump

where region_state = 0x00010000 — MEM_FREE
Go

 

Select SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb

from sys.dm_os_memory_clerks

Where type not like ‘%bufferpool%’
Go

With VASummary(Size,Reserved,Free) AS
(SELECT
Size = VaDump.Size,
Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)
WHEN 0 THEN 1 ELSE 0 END)
FROM
(
SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))
AS Size, region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address

UNION

SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address  = 0x0
)
AS VaDump
GROUP BY Size)
SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail Mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
Go

 

SELECT SUM(PAGESUSED)*8/1024 ‘MB of MemToLeave memory consumed by procedures’ FROM MASTER.DBO.SYSCACHEOBJECTS

WHERE PAGESUSED >1
Go

 

— Short Script 5 – Criando um Pivot em conjunto com Visão com Sumarização —

Create Table Fornos
(Id Int Primary Key,
Defeito Varchar(20) Not Null,
Forno Varchar(20) Not Null,
Equipe Varchar(20) Not Null)
Go

 

Insert Into Fornos
Values (1,’DEFEITO A’,’FORNO 3′,’AZUL’),
(2,’DEFEITO A’,’FORNO 2′,’VERDE’),
(3,’DEFEITO B’,’FORNO 1′,’AZUL’),
(4,’DEFEITO A’,’FORNO 1′,’PRETO’),
(5,’DEFEITO B’,’FORNO 2′,’VERDE’),
(6,’DEFEITO B’,’FORNO 2′,’AZUL’),
(7,’DEFEITO A’,’FORNO 1′,’PRETO’),
(8,’DEFEITO A’,’FORNO 2′,’AZUL’)
Go

Select * From Fornos
Go

 

— Gerando o Pivot —
Select * from
(Select CONCAT(Defeito,’ – ‘, Equipe) As DefeitosPorEquipe,
CONCAT(Defeito,’ – ‘, Equipe) As ‘Defeitos Agrupados Por Equipes’,
Equipe,
Forno As ‘Fornos’
From Fornos) As F
Pivot (Count(DefeitosPorEquipe) For Equipe In ([Azul],[Preto],[Verde])) as Pvt
Go

 

— Adicionando os Totais —
Select * from
(
Select CONCAT(Defeito,’ – ‘, Equipe) As DefeitosPorEquipe,
CONCAT(Defeito,’ – ‘, Equipe) As ‘Defeitos Agrupados Por Equipes’,
Equipe,
Forno As ‘Fornos’
From Fornos
) As F
Pivot (Count(DefeitosPorEquipe) For Equipe In ([Azul],[Preto],[Verde])) as Pvt

Union All

Select ‘Totais….’, ‘—->’,
Sum(Azul) As SomaAzul,
Sum(Preto) As SomaPreto,
Sum(Verde) As SomaVerde
From
(
Select CONCAT(Defeito,’ – ‘, Equipe) As DefeitosPorEquipe,
Equipe
From Fornos
) As F
Pivot (Count(DefeitosPorEquipe) For Equipe In ([Azul],[Preto],[Verde])) as Pvt
Go

 

— Short Script 6 – Identificando o filegroup vinculado para cada tabela de usuário —

SELECT o.[name],
o.[type],
i.[name],
i.[index_id],
f.[name]
FROM sys.indexes i INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = ‘U’ — User Created Tables
GO

 

— Short Script 7 – Realizando o cálculo da idade —

— Exemplo 1 —
Declare @Hoje date

Set @Hoje= cast (current_timestamp as date)

 

SELECT CPF, DATA_NASC,
case when month(@Hoje) > month(DATA_NASC)
then datediff (year, DATA_NASC, @Hoje)
when month(@Hoje) = month(DATA_NASC) and day(@Hoje) >= day(DATA_NASC)
then datediff (year, DATA_NASC, @Hoje)
else datediff (year, DATA_NASC, @Hoje) -1
end as Idade
from tb_idade_dez
Go

 

— Exemplo 2 —
Select CPF, DATA_NASC, (datediff (month, DATA_NASC, @Hoje) / 12) as Idade

from tb_idade_dez
Go

 

— Exemplo 3 —
Select CPF, DATA_NASC, datediff(day,DATA_NASC,getdate())/365.15 as idade

from TB_IDADE_JANEIRO
Go

 

— Exemplo 4 —
Declare @DATAS table (DataNasc date)

INSERT into @DATAS values
(‘19880101’), (‘19880111’), (‘19880112’), (‘19880113’),
(‘19880213’), (‘19880313’), (‘19880413’), (‘19880513’),
(‘19880613’), (‘19880713’), (‘19880813’), (‘19880913’),
(‘19881013’), (‘19881113’), (‘19881213’)

 

Declare @Hoje date
Set @Hoje= ‘20190112’

 

SELECT DataNasc, @Hoje as Hoje,
datediff(day, DataNasc, @Hoje)/365.15 as idade,
case when month(@Hoje) > month(DataNasc) then datediff (year, DataNasc, @Hoje)
when month(@Hoje) = month(DataNasc) and day(@Hoje) >= day(DataNasc) then datediff (year, DataNasc, @Hoje)
else
datediff (year, DataNasc, @Hoje) -1
end as Anos
from @DATAS
Go

Muito bem, missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/11/20/short-scripts-novembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/09/18/short-scripts-setembro-2018/

https://pedrogalvaojunior.wordpress.com/2018/05/10/short-scripts-maio-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post a ser publicado no mês de junho.

Um forte abraço, saúde e paz.

Até mais.

Short Scripts – Maio 2018


Olá Mundo, bom dia, comunidade….

Tudo bem? E ai como passaram os últimos meses?

Pergunto isso, devido ao post anterior desta sessão ter sido publicado no mês de fevereiro, posso imaginar que neste intervalo de tempo nossas vidas apresentaram inúmeras situações, espero que todas possam ter sido dentro do possível boas, assim como a minha, e se caso venha a existir algo mais espinhoso, que você possa ter superado.

Seguindo em frente, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 33 posts publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados atualmente na minha biblioteca particular de códigos e exemplos.

O post de hoje

Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Acesso de usuário a tabelas;
  • Acesso e Leitura a Banco de dados;
  • Contas de Execução;
  • Descrição de Colunas;
  • DMV – Sys.dm_os_memory_clerks;
  • DMV – Sys.dm_server_services;
  • Ident_Current;
  • Leitura de Log;
  • Memória;
  • Memory Cache Entries;
  • Memory Clerks;
  • Multipage Allocators Statistic
  • OS Threads;
  • Processos em execução;
  • Scope_Ident;
  • Serviços do SQL Server;
  • SessioID;
  • System Identity Variable; e
  • XP_ReadErrorLog.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Maio 2018 . Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Encerramento processos que apresentam várias horas ou longo tempo de execução  —
SET NOCOUNT ON
DECLARE @spid SMALLINT, @spidString VARCHAR(12)

DECLARE spidCursor CURSOR
FOR SELECT spid
FROM master.sys.sysprocesses
WHERE last_batch < DATEADD(hh, -8, GETDATE()) AND spid > 50 — Change 8 to any other value for hours AND spid > 50 — for user spid
FOR READ ONLY
OPEN spidCursor
FETCH NEXT FROM spidCursor INTO @spid

SELECT ‘Killed spid(s) – ‘
WHILE (@@fetch_status = 0)
AND (@@error = 0)
BEGIN
SELECT @spidString = CONVERT(VARCHAR(12), @spid)
EXEC (‘kill ‘ + @spidString)
SELECT @spid
FETCH NEXT FROM spidCursor INTO @spid
END

CLOSE spidCursor
DEALLOCATE spidCursor
SET NOCOUNT OFF

— Short Script 2 –  Identificando – Memory Cache Entries —
WITH memory_cache_entries
AS
(
SELECT
name AS entry_name,
[type],
in_use_count,
pages_allocated_count,
CAST(entry_data AS XML) AS entry_data
FROM sys.dm_os_memory_cache_entries(nolock)
WHERE type = ‘USERSTORE_TOKENPERM’
),
memory_cache_entries_details
AS
(
SELECT
entry_data.value(‘(/entry/@class)[1]’, ‘bigint’) AS class,
entry_data.value(‘(/entry/@subclass)[1]’, ‘int’) AS subclass,
entry_data.value(‘(/entry/@name)[1]’, ‘varchar(100)’) AS token_name,
pages_allocated_count,
in_use_count
FROM memory_cache_entries
)
SELECT
class,
subclass,
token_name,
COUNT(*) AS nb_entries
FROM memory_cache_entries_details
GROUP BY token_name, class, subclass
ORDER BY nb_entries DESC;

— Short Script 3 – Obtendo a relação de últimos acessos de leitura e escrita por banco de dados —

;WITH myCTE AS
(
SELECT
DB_NAME(database_id) AS TheDatabase,
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM sys.dm_db_index_usage_stats
)
SELECT
ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases where name=’tempdb’),
x.TheDatabase,
MAX(x.last_read) AS last_read,
MAX(x.last_write) AS last_write
FROM
(
SELECT TheDatabase,last_user_seek AS last_read, NULL AS last_write FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_scan, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,last_user_lookup, NULL FROM myCTE
UNION ALL
SELECT TheDatabase,NULL, last_user_update FROM myCTE
) AS x

GROUP BY TheDatabase
ORDER BY TheDatabase

— Short Script 4 – Identificando e analisando as diferenças entre Ident_Current, Scope_Identity e Variável de Sistema Identity —
USE AdventureWorks2016;
Go

CREATE TABLE t6(id int IDENTITY);
CREATE TABLE t7(id int IDENTITY(100,1));
Go

CREATE TRIGGER t6ins ON t6 FOR INSERT
AS
BEGIN
INSERT t7 DEFAULT VALUES
END;
Go

SELECT id FROM t6;
SELECT id FROM t7;

— Do the following in Session 1.
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT(‘t7’);
/* Returns value inserted into t7, that is in the trigger.*/

SELECT IDENT_CURRENT(‘t6’);
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/

— Do the following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL because there has been no INSERT action
up to this point in this session.*/

SELECT SCOPE_IDENTITY();
/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT(‘t7’);
/* Returns the last value inserted into t7.*/

— Short Script 5 – Identificando a SessionID e suas respectivas OS Threads —
SELECT osTask.session_id,
osThreads.os_thread_id,
osTask.scheduler_id,
osTask.task_state
FROM sys.dm_os_tasks AS osTask INNER JOIN sys.dm_os_threads AS osThreads
ON osTask.worker_address = osThreads.worker_address
WHERE osTask.session_id IS NOT NULL
ORDER BY osTask.session_id;
Go

— Short Script 6 – Identificando o último usuário que acesso a tabela —
SELECT DB_NAME(ius.[database_id]) AS [Database],
                 OBJECT_NAME(ius.[object_id]) AS [TableName],
                 MAX(ius.[last_user_lookup]) AS [last_user_lookup],
                 MAX(ius.[last_user_scan]) AS [last_user_scan],
                 MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID(‘[PlayerAllData]’)
GROUP BY ius.[database_id], ius.[object_id]
Go

— Short Script 7 – Obtendo informações sobre a descrição de uma coluna —
declare @ColumnName nvarchar(300),
@TableName nvarchar(300);

Set @ColumnName= N’…’;
Set @TableName= N’…’;

Select st.name [Table],
sc.name [Column],
sep.value [Description]
from sys.tables st inner join sys.columns sc
on st.object_id = sc.object_id
left join sys.extended_properties sep
on st.object_id = sep.major_id
and sc.column_id = sep.minor_id
and sep.name = ‘MS_Description’
Where st.name = @TableName
And sc.name = @ColumnName
Go

— Short Script 8 – XP_ReadErrorLog – Identificando a porta padrão do SQL Server —
USE MASTER
Go

XP_readerrorlog 0, 1, N’Server is listening on’
Go

— Short Script 9 –  Utilizando – sys.dm_os_memory_clerks – Multipage Allocators Statistic —
Select name AS clerk_name, memory_node_id,
                 sum(single_pages_kb) / 1024 as single_page_total_size_mb,
                 sum(multi_pages_kb) / 1024 as multi_page_total_size_mb,
                 sum(awe_allocated_kb) / 1024 as awe_allocaed_size_MB
From sys.dm_os_memory_clerks(nolock)
Where memory_node_id = 64
Group by memory_node_id, name
Having SUM(multi_pages_kb) > 0
Order BY sum(single_pages_kb) + sum(multi_pages_kb) +
sum(awe_allocated_kb) DESC
Go

— Short Script 10 –  Utilizando – sys.dm_server_services – Identificando a conta que esta executando os serviços do SQL Server —
Select  servicename,
startup_type_desc,
status_desc,
               last_startup_time,
               service_account,
               is_clustered,
               cluster_nodename,
               filename,
               startup_type,
               status,
               process_id
From sys.dm_server_services
Go

Missão mais que cumprida! Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2018/02/19/short-scripts-fevereiro-2018-transaction-log/

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de agosto de 2018.

Abraços…

Short Scripts – Fevereiro 2018 – Transaction Log


Olá, bom dia, mais uma semana começando….

E você já esta aqui acessando o meu blog, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 32 posts, sendo estes publicados trimestralmente.

Mantendo a tradição estou retornando com mais um conjunto de “pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Mas como este é o primeiro post desta sessão em 2018, farei algo um pouco diferente, você terá uma pequena surpresa.

Desejo que o conteúdo aqui compartilhado possa lhe ser útil, como também sirvo de referência e sugestões para novas formar de resolução de problemas e aprendizado.

Vamos então conhecer um pouco mais sobre este novo post….

O post de hoje

Como já destacado no início do post, ao invés de compartilhar os últimos scripts adicionados a  minha biblioteca, quero dividir com você um conteúdo dedicado especificamente a um assunto muito importante quando nos referimos ao Microsoft SQL Server, mais especificamente ao Transaction Log (Log de Transações), funcionalidade presente em todos os bancos de dados criados em qualquer versão e edição do SQL Server.

E ai que você achou desta surpresa, gostou? Eu gostei, não é fácil você conseguir encontrar em um único local um conteúdo focado exclusivamente a este assunto tão importante, que muitos profissionais que trabalham com banco de dados até hoje não conseguem entender o conceito e forma de atuação do Transaction-Log.

Seguindo em frente, a seguir apresento os códigos e exemplos selecionados para o Short Script – Fevereiro 2018 – Transaction Log. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Short Scripts

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos:

— Short Script 1 – Log Record —

— Altera o Recovery Model para SIMPLE
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Truncar o Transaction Log —
CHECKPOINT
Go

— Conteúdo do log – todas as colunas —
USE AdventureWorks2016
Select * from ::fn_dblog(null, null)
Go

–update
Begin Transaction

UPDATE dbo.Pessoa
SET nome = ‘XUXA’
Where ID=3

Rollback
Go

— Conteúdo armazenado no Log File —
Select [Current LSN],
Operation,
Context,
[Transaction ID],
[Log Record Length],
[Previous LSN],
AllocUnitName,
[Page ID],
[Slot ID],
[Checkpoint Begin],
[Checkpoint End],
[Minimum LSN],
SPID,
[Begin Time],
[Transaction Name],
[Parent Transaction ID],
[Lock Information],
Description,
[RowLog Contents 0],
[RowLog Contents 1],
[Log Record]
From ::fn_dblog(null, null)
Go

— DBCC SQLPERF —
DBCC SQLPERF(LOGSPACE)
Go

— Short Script 2 – CheckPoint —

— Criando a Base de Dados —
CREATE DATABASE DemoCheckpoint
ON PRIMARY
(NAME = ‘DemoCheckpoint_data’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_data.mdf’)
LOG ON
(Name = ‘DemoCheckpoint_Log’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_log.ldf’,
SIZE = 100MB,
FILEGROWTH = 10MB)
GO

— Alterando o Recovery Model —
ALTER DATABASE DemoCheckpoint
SET RECOVERY SIMPLE
Go

— Criando a Tabela Teste —
USE DemoCheckpoint
GO

CREATE TABLE Teste
(C1 varchar(50) NOT NULL,
C2 varchar(50) NOT NULL)
GO

— Forçando o Checkpoint —
CHECKPOINT
Go

— Abrir o Perfmon com os contadores

— em outra sessão
USE DemoCheckpoint
GO

WHILE 1=1
BEGIN

INSERT INTO dbo.teste
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Short Script 3 – Log Chain Simple —

— Iniciar nova sessão do Perfmon —
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Alterar Recovery Model para Simple —
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Abrir nova Query —
USE AdventureWorks2016
Go

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Alterar Recovery Model para Full —
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
GO

— Realizar Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backupcompress.bak’
WITH COMPRESSION,
DIFFERENTIAL
GO

— Short Script 4 – DBCC LogInfo —

— Criando uma nova base de dados —
CREATE DATABASE TestDB
ON PRIMARY
(NAME = ‘TestDB_data’,
FILENAME = ‘D:\MSSQL\TestDB_data.mdf’)
LOG ON
(Name = ‘TestDB_Log’,
FILENAME = ‘D:\MSSQL\TestDB_log.ldf’,
SIZE = 10MB,
FILEGROWTH = 10MB)
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

–Forçando o crescimento do Transact-Log manualmente em 20MB —
ALTER DATABASE TestDB
MODIFY FILE
(NAME = ‘TestDB_Log’,
SIZE = 20MB);
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

— Short Script 5 – Natureza Circular —

— Alterando Recovery Model FULL —
ALTER DATABASE TestDB
SET RECOVERY FULL;
Go

— Realizando Backup Database —
BACKUP DATABASE TestDB
TO DISK = ‘D:\TestDB.bak’
Go

— Forçando o encolhimento do Transaction – Log —
DBCC LOGINFO(TestDB)
Go

BACKUP LOG TestDB
TO DISK = ‘bkplogTestDB.trn’
Go

USE TestDB
Go

DBCC SHRINKFILE (TestDB_Log,1)
Go

DBCC LOGINFO(TestDB)
Go

— Criando uma nova Tabela —
USE TestDB
GO

CREATE TABLE dbo.pessoa
(ID int identity PRIMARY KEY NOT NULL,
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL,
Nascimento date NOT NULL,
Cargo varchar(50))
GO

— Abrir nova query —
USE TestDB
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘Junior’, ‘Galvão’, ‘19800428’, ‘Database Administrator’)
END

— Monitorar o crescimento do log em tempo de execução —
DBCC LOGINFO(TestDB)

CHECKPOINT

SELECT name,
Log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘TestDB’
Go

— Realizar Backup do Arquivo de Log —
BACKUP LOG TestDB TO DISK = ‘d:\log.trn’
Go

— Alterando Recovery Model para Full
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
Go

— Realizando novo Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
WITH COMPRESSION
Go

— Short Script 6 – Backup and Transaction Log —

— Preparando a base – 1m10s se não preparada na demo 5
ALTER DATABASE AdventureWorks2016 SET RECOVERY FULL
GO
BACKUP DATABASE AdventureWorks2016 TO DISK = ‘d:\backup.bak’ WITH COMPRESSION
GO

— Realizando Backup do Arquivo de Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Encolhendo o Transaction Log —
USE AdventureWorks2016
Go

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Ajustando o tamanho do Transaction Log —
USE AdventureWorks2016
Go

— Encolhendo o Transaction Log —
DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Modificando o arquivo de Log —
ALTER DATABASE AdventureWorks2016
MODIFY FILE
(NAME = AdventureWorks2016_Log,
SIZE = 4MB)
Go

— Encolhendo o Transaction Log —
DBCC LOGINFO(AdventureWorks2016)

— Abrir nova query —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
Go

— Abrir nova query —
USE AdventureWorks2016
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END
Go

— Forçando o Truncate do Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtedo informações do arquivo de log —
DBCC LOGINFO(AdventureWorks2016)
CHECKPOINT
SELECT name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Ajustando o tamanho do TLog
USE AdventureWorks2016
GO

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
GO

DBCC LOGINFO(AdventureWorks2016)
Go

— Short Script 7 – File Growth —

— Habilitando Trace Flags para evidênciar mudanças no Log —
DBCC TRACEON (3004, 3605, -1);
Go

— Limpar o log do SQL Server —
sp_cycle_errorlog
Go

— Criar uma nova Base de Dados —
CREATE DATABASE TransactionLog
ON PRIMARY
(NAME = ‘TransactionLog_data’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_data.mdf’,
SIZE = 10240MB)
LOG ON
(Name = ‘TransactionLog_Log’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_log.ldf’,
SIZE = 1024MB,
FILEGROWTH = 1024MB)
GO

— Identificar o tempo decorrido para processamento relacionado somente ao Log —
xp_readerrorlog
Go

— Desabilitando Trace Flags —
DBCC TRACEOFF (3004, 3605, -1);
Go

— Short Script 8 – Open Transaction in Tracking —

— Identificando as Transações abertas e seu respectivo consumo do Transaction Log —
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions [s_tdt] Inner JOIN sys.dm_tran_session_transactions [s_tst]
ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
Inner JOIN sys.[dm_exec_sessions] [s_es]
ON [s_es].[session_id] = [s_tst].[session_id]
Inner JOIN sys.dm_exec_connections [s_ec]
ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er]
ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [Begin Time] ASC;
Go

É isso ai, missão cumprida! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.

Quero agradecer ao amigo Luiz Mercante que colaborou com a criação e uso destes scripts em algumas palestras realizadas nos últimos anos no evento SQL Saturday.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

https://pedrogalvaojunior.wordpress.com/2017/12/09/short-scripts-dezembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/

https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de maio de 2018.

Sucesso….

Short Scripts – Dezembro 2017


Hello World, muito, mas muito bom dia….

Mais um sábado começando, justamente hoje que excepcionalmente eu não vou trabalhar e deveria estar dormindo estou aqui aproveitando um pouco do meu tempo para me dedicar a outra enorme paixão, meu blog e meu seguidores que estão me ajudando desde o começo deste ano a aumentar de forma considerável a audiência dos meus posts e sessões aqui compartilhadas.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados a minha biblioteca particular de códigos e exemplos nos últimos meses.

O post de hoje

Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Armazenamento de arquivos;
  • Construção de Frases;
  • Conversão de caracteres;
  • Data Type Money;
  • Excel;
  • Grant All Permissions;
  • Operador Cube;
  • Operador Rollup;
  • Select Recursivo;
  • Sequência Numérica;
  • Views;
  • XP_DirTree;
  • XML Path.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Dezembro 2017. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Armazenando arquivos do Excel diretamente no SQL Server —
Set Nocount On
Go

Create Table FileList
(id int identity(1,1) primary key clustered,
FileName varchar(max))
Go

Create Table #TempTable
(id int identity(1,1) primary key clustered,
FileName varchar(max),
FileDepth int,
FileID int)
Go

Create Table dbo.TestBlob
(tbId int IDENTITY(1,1) NOT NULL,
tbName varchar (50) NULL,
tbDesc varchar (100) NULL,
tbBin varbinary (max) NULL)
Go

Insert Into #TempTable
EXEC master.sys.xp_dirtree ‘E:\ExcelOutput’,0,1;
Go

Select * from #TempTable
Go

Declare @I int=0, @FileName varchar(max), @Count int

Select * into #TempFileList from FileList
Set @Count=(Select count(*) from #TempFileList)

Declare @SQLText nvarchar(max)
While (@i<@Count)
 Begin

Set @FileName=(select top 1 FileName from #TempFileList)
  Set @SQLText=’Insert TestBlob(tbName, tbDesc, tbBin) Select ”’+@FileName+”’,”Files”,
  BulkColumn from Openrowset( Bulk ”’+@FileName+”’, Single_Blob) as tb’

  Print @SQLText
  Delete from #TempFileList where FileName=@FileName
  Set @I=@I+1
 End

Select tbID as ID,
tbName as ‘File Name’,
tbBin as ‘Converted file’
from TestBlob
Go

Drop Table #TempFileList
Go

— Short Script 2  – Convertendo caracteres diretamente para o formato e data type Money —
SELECT ‘1,,,,,,’+$0
Go

— Short Script 3  – Criando uma frase através de um Select Recursivo —
Create Table myWords
(RowID Int,
Word Varchar(20))
Go

Insert Into myWords Values(1, ‘This’),(2, ‘is’),(3, ‘an’),(4, ‘interesting’),
(5,’table’)

Declare @Sentence as varchar(8000)
SET @Sentence = ”

SELECT @Sentence = @Sentence + word + ‘ ‘
FROM myWords
ORDER BY RowID

PRINT @Sentence
Go

— Short Script 4  – Utilizando XML Path para gerar uma sequência numérica —
Declare @MyTable Table
(MyID Int)

Insert Into @MyTable Values(1),(2),(3),(4),(5)
Go

— Execução 1 —
Select MyID as “text()”, ‘;’ as “text()” from @MyTable for xml path(”)
Go

— Execução 2 —
Select MyID + ‘;’ as “text()” from @MyTable for xml path(”)
Go

— Short Script 5  – Atribuindo Grant All para todas tabelas em um Banco de Dados —

USE MyDatabase
Go

If EXISTS (Select [name] FROM master..sysdatabases WHERE [name] = ‘MyDatabase’)
Begin

Print ‘Updating Permissions for MyDatabase’
Print ‘ ‘

Declare @tablename varchar(255), @tablename_header varchar(255)

Declare tnames_cursor CURSOR FOR
Select name FROM MyDatabase..sysobjects
WHERE type = ‘U’

Open tnames_cursor
Fetch Next From tnames_cursor INTO @tablename

While (@@Fetch_status <> -1)
Begin

If (@@Fetch_status <> -2)
Begin
Select @tablename_header = ‘Updating ‘ + ‘MyDatabase..’ + RTrim(UPPER(@tablename) )
Print @tablename_header
Exec (‘Grant All on ‘ + @tablename +’ to shanewiso’)
End

Fetch Next From tnames_cursor INTO @tablename
End

Deallocate tnames_cursor
End
Go

— Short Script 6  – Diferença entre os operadores Rollup e Cube —
Create Table Funcionarios
(Codigo Int Primary Key,
Nome VarChar(50) Not Null,
Sexo VarChar(10) Not Null,
Salario Int Not Null,
Departamento VarChar(50) Not Null)
Go

Insert Into Funcionarios
Values
(1, ‘David’, ‘Masculino’, 5000, ‘Sales’),
(2, ‘Jim’, ‘Feminino’, 6000, ‘HR’),
(3, ‘Kate’, ‘Feminino’, 7500, ‘IT’),
(4, ‘Will’, ‘Masculino’, 6500, ‘Marketing’),
(5, ‘Shane’, ‘Feminino’, 5500, ‘Finance’),
(6, ‘Shed’, ‘Masculino’, 8000, ‘Sales’),
(7, ‘Vik’, ‘Masculino’, 7200, ‘HR’),
(8, ‘Vince’, ‘Feminino’, 6600, ‘IT’),
(9, ‘Jane’, ‘Feminino’, 5400, ‘Marketing’),
(10, ‘Laura’, ‘Feminino’, 6300, ‘Finance’),
(11, ‘Mac’, ‘Masculino’, 5700, ‘Sales’),
(12, ‘Pat’, ‘Masculino’, 7000, ‘HR’),
(13, ‘Julie’, ‘Feminino’, 7100, ‘IT’),
(14, ‘Elice’, ‘Feminino’, 6800,’Marketing’),
(15, ‘Wayne’, ‘Masculino’, 5000, ‘Finance’)
Go

— Agrupamento simples através de Group By através da coluna Departamento —
Select Departamento,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Departamento
Go

— Utilizando operador Rollup para gerar totais e subtotais com base na coluna Departamento —
Select Coalesce (Departamento, ‘Departamentos’) As Departamento,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Rollup (Departamento)
Go

— Procurando subtotais através do operador Rollup para colunas Departamento e Sexo —
Select Coalesce (Departamento, ‘Departamentos’) As Departamento,
Coalesce (Sexo,’Sexos’) As Sexo,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Rollup (Departamento, Sexo)
Go

— Utilizando o Operador Cube para gerar todas as possíveis combinações de agrupamentos de dados através das colunas Departamento e Sexo —
Select Coalesce (Departamento, ‘Departamentos’) As Departamento,
Coalesce (Sexo,’Sexos’) As Sexo,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Cube (Departamento, Sexo)
Go

— Short Script 7  – Identificando a relação de colunas utilizadas em uma view —
— Exemplo 1: Utilizando Information_Schema —

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns
WHERE UsedColumns.VIEW_NAME=’NameofView’
Go

— Exemplo 2: Utilizando DMVs —
SELECT
v.name AS ViewName,
c.name AS ColumnName,
columnTypes.name AS DataType,
aliases.name AS Alias
FROM sys.views v INNER JOIN sys.sql_dependencies d
ON d.object_id = v.object_id
INNER JOIN .sys.objects t
ON t.object_id = d.referenced_major_id
INNER JOIN sys.columns c
ON c.object_id = d.referenced_major_id
INNER JOIN sys.types AS columnTypes
ON c.user_type_id=columnTypes.user_type_id
AND c.column_id = d.referenced_minor_id
INNER JOIN sys.columns AS aliases
On c.column_id=aliases.column_id
AND aliases.object_id = object_id(‘[SchemaName].[ViewName]’)
WHERE v.name = ‘ViewName’;
Go

Show, missão cumprida! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de fevereiro de 2018.

Um forte abraço, feliz natal e próspero ano novo.

Short Scripts – Setembro 2017


Muito boa tarde!!! Olá galera….

Após mais um sábado de aulas na Fatec São Roque, chegou a hora de se dedicar a comunidade de tecnologia, mais especificamente aos amantes de bancos de dados.

Como promessa é dívida e deve ser cumprida “ou melhor” paga, estou pagando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados a minha biblioteca particular de códigos e exemplos nos últimos meses.

O post de hoje

Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Cálculo de Datas;
  • Comando Select;
  • Computed Column;
  • CTE Recursiva;
  • Formatação de Plano de Contas;
  • Função Format();
  • Função Parse();
  • Operador Cross Apply;
  • Operador Outer Apply;
  • Scalar User Defined Function;
  • Sequência Fibonacci; e
  • Sequência numérica de CEPs.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Setembro 2017. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Calculando o Ano e Mês posterior através do ano e mês  —

Declare @AnoMes int = 201712

Declare @AnoMesPosteriorDate date = DATEADD(MONTH, @AnoMes % 100, DATEADD(YEAR, @AnoMes / 100 – 1900, 0))

Declare @AnoMesPosterior int = YEAR(@AnoMesPosteriorDate) * 100 + Month(@AnoMesPosteriorDate)

Select @AnoMes, @AnoMesPosterior
Go

— Short Script 2  –  Comando Select especificando o nome da coluna e o valor através do sinal de igual —

Select ‘1/0’ = ‘Hello’, ‘2…’ = ‘teste’
Go

— Short Script 3  – Formatando a apresentação de um Plano de Contas —

DECLARE @tableDados TABLE ( Codigo VARCHAR(100) )

INSERT  INTO @tableDados
        SELECT  ‘1.0.0.0.0.00.00’
        UNION  ALL
        SELECT  ‘1.1.0.0.0.00.00’
        UNION ALL
        SELECT  ‘1.1.1.1.2.00.00’
        UNION ALL
        SELECT  ‘2.3.7.1.1.01.00’
        UNION ALL
        SELECT  ‘2.3.2.0.5.00.00’

SELECT  Codigo FROM @tableDados

SELECT TD.Codigo,  LEFT(TD.CODIGO, LEN(TD.CODIGO) – PATINDEX(‘%[1-9]%’, REVERSE(TD.CODIGO)) + 1)
FROM @tableDados AS TD
Go

— Short Script 4  –  Formatando a apresentação de dados através das funções Parse() e Format() —

Declare @Valor Varchar(10)
Set @Valor=’1,540.20′

Select @Valor As Antes, PARSE(@Valor As money Using ‘en-US’) As Depois

Select FORMAT(Cast(@Valor As Money),’C’,’pt-BR’)
Go

— Short Script 5  – Analisando as diferentes de comportamente entre os operadores Cross Apply e Outer Apply —

Declare @Tabela1 Table
(Codigo Int,
Valor Int)

Declare @Tabela2 Table
(Codigo Int,
Valor Int)

Insert Into @Tabela1 Values (1,1),(2,2),(Null, Null)
Insert Into @Tabela2 Values (1,1),(2,2),(3,3),(4,4),(5,5), (Null, Null)

— Utilizando operador Outer Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Outer Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2

— Utilizando operador Cross Apply —
Select T.Codigo,
T.Valor
From @Tabela1 T Cross Apply (Select Codigo From @Tabela2
Where Codigo = T.Codigo) As T2

— Short Script 6  – Criando a sequência Fibonacci de valores —

Create Table Sequence
(Code Int Primary Key Identity(1,1),
Number BigInt Not Null)
Go

Declare @Counter Int = 1, @String Varchar(Max)

While @Counter <=50
Begin

Insert Into Sequence (Number)
Select IsNull(Sum(Number),1) from Sequence
Where Code < @Counter -1

Set @String = (Select Concat(@String,’,’,Number) from Sequence Where Code = @Counter)

Set @Counter +=1

End

Update Sequence
Set Number = 0
Where Code = 1

Select Number ‘Number List’ From Sequence

Select ‘0’+@String As ‘Sequence Finobacci’
Go

— Short Script 7 –  Criando uma Computed Column para uso de uma Scalar User Defined Function —

— Criando a Function F_CalcularDiferencaAnos —
Create Function F_CalcularDiferencaAnos (@DataNascimento Date)
Returns Int
As
Begin
Return (Select DATEDIFF(Year, @DataNascimento, GetDate()))
End
Go

— Criando a Tabela1 para Teste —
Create Table Tabela1
(Codigo Int,
DataNascimento Date,
DiferencaComputada As (dbo.F_CalcularDiferencaAnos(DataNascimento))) — Criando uma coluna computada com a function —
Go

— Inserindo os dados —
Insert Into Tabela1 (Codigo, DataNascimento)
Values (1,’1980-04-28′), (2,’1981-01-28′)
Go

— Validando o resultado —
Select * from Tabela1
Go

— Short Script 8  –  Criando uma sequência de valores de CEPs através de uma CTE Recursiva —

Declare @Tabela table
(Codigo int, Cidade varchar(40), Inicio char(9), Fim char(9));

insert into @Tabela values (1, ‘Belo Horizonte’, ‘30000-000’, ‘35000-000’)

;With CTE_Rec as
(
Select Cidade,
            Cast(left(Fim, 5) + right(Fim, 3) as int) as Fim,
            Cast(left(Inicio, 5) + right(Inicio, 3) as int) as Cep
From @Tabela

Union all

Select Cidade, Fim, Cep + 1
from CTE_rec
where Cep < Fim
)

Select Cidade, cast(Cep / 1000 as char(5)) + ‘-‘ + right(’00’ + cast(Cep % 1000 as varchar), 3) as Cep,
(Cep / 1000) as Div,
(Cep % 1000) as Div2
from CTE_Rec
OPTION (MAXRECURSION 0)
Go

 

Ufa, conseguimos! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de dezembro.

Um forte abraço, até mais.

Short Scripts – Junho 2017


Boa noite galera, olá comunidade de banco de dados.

Tudo bem? Graças a deus mais um final de semana se aproximando, finalzinho de noite de quinta – feira, acredito que neste momento minha linda esposa esta curtindo mais um episódio de uma das suas séries favoritas, ou lendo mais um dos seus intermináveis livros (kkkkkk), isso não é um crítica, muito ao contrário, a leitura faz bem para a alma e principalmente para a mente, mas posso dizer que minha pequena Fernanda é uma degustadora insaciável de livros.

Mudando de assunto, este é o segundo post deste ano dedicado exclusivamente a sessão Short Scripts, sessão criada a alguns anos que  esta atraindo um número interessante de visitantes, principalmente de profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Para este post, selecionei como de costume os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Check Constraint;
  • Common Table Expression;
  • Índices;
  • Information_schema.columns;
  • Monitoramento de Processos;
  • Network Protocol;
  • Operador Outer Apply;
  • Requisição de Disco;
  • Sys.dm_server_registry;
  • Sys.dm_tcp_listener_states;
  • sys.dm_exec_query_plan;
  • sys.dm_exec_sql_text;
  • sys.dm_exec_query_stats;
  • sys.types;
  • sys.tables;
  • sys.dm_db_index_usage_stats;
  • Sys.Identity_Columns; e
  • User Defined Function.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Junho 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Utilizando Check Constraint para cálculo de datas —

Create Table Alunos
(Codigo Int)
Go

— Adicionando a coluna e constraint —
Alter Table Alunos
Add DataNascimento DateTime
Constraint CK_Alunos_DataNascimento Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Adicionando somente a constraint —
Alter Table Alunos
Add Constraint CK_Alunos_DataNascimento
Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Default – Estado —
Alter Table Alunos
Add Constraint [DF_Estado] Default ‘SP’ for Estado
Go

— Short Script 2  – Aplicando o uso de Common Table Expression para inserir registros com valores de um registro anterior —

DECLARE @Metas TABLE ( Data DATE, Meta INT );

INSERT INTO @Metas
( Data, Meta )
VALUES ( DATEFROMPARTS(2017, 03, 29), 50 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 13), 50 );

DECLARE @Producao TABLE
(
Data DATE ,
Quantidade INT
);

INSERT INTO @Producao
( Data, Quantidade )
VALUES ( DATEFROMPARTS(2017, 04, 10), 49 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 12), 36 ),
( DATEFROMPARTS(2017, 04, 13), 50 ),
( DATEFROMPARTS(2017, 04, 14), 50 );

WITH DadosProduzidos
AS ( SELECT P.Data ,
Quantidade = SUM(P.Quantidade)
FROM @Producao AS P
GROUP BY P.Data
)
SELECT D.Data ,
[Produzido] = D.Quantidade ,
Meta = ( SELECT TOP 1 M.Meta
FROM @Metas AS M
WHERE M.Data <= D.Data
ORDER BY M.Data DESC
)
FROM DadosProduzidos D;

— Short Script 3  – Identificando a relação de todos os índices existentes em um banco de dados —

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc,
i.name,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t
ON t.OBJECT_ID = i.OBJECT_ID
GO

— Short Script 4 – Obtendo informações de network protocols e device através da sys.dm_server_registry —

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpPort%’
AND CONVERT(float,value_data) > 0
Go

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpDynamicPort%’
AND CONVERT(float,value_data) > 0
Go

— Short Script 5 – Obtendo informações de network protocols e device através da sys.dm_tcp_listener_states —

SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states
WHERE is_ipv4 = 1
AND [type] = 0
AND ip_address <> ‘127.0.0.1’
Go

— Short Script 6 – Criando uma User Defined Function com operador Outer Apply —

CREATE FUNCTION AttributesOfTable (@tableToSearch nvarchar(500))
returns table
return SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @tableToSearch;
go

Declare @tableToSearch table (nome_tabela varchar(50));
INSERT into @tableToSearch values (‘Customer’), (‘Order’), (‘Papagaio’);

SELECT T1.nome_tabela as [nome da tabela],
T2.TABLE_SCHEMA as [nome do esquema],
T2.COLUMN_NAME as [nome da coluna]
from @tableToSearch as T1
outer apply dbo.AttributesOfTable(T1.nome_tabela) as T2;

— Short Script 7 – Utilizando a DMV sys.identity_columns para identificar o valor identity de uma determinada coluna —

SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns
INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 8 – Consultando informações sobre o SQL Server armazenadas no Registro do Windows —

— A. Display the SQL Server services —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%ControlSet%’
Go

— B. Display the SQL Server Agent registry key values —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%SQLAgent%’
Go

— C. Display the current version of the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name = N’CurrentVersion’
Go

— D. Display the parameters passed to the instance of SQL Server during startup —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%Parameters’
Go

— E. Return network configuration information for the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE keyname LIKE N’%SuperSocketNetLib%’
Go

— Short Script 9 – Identificando a relação de querys que apresentam o maior consumo de disco durante seu período de processamento – 

SELECT TOP 20 SUBSTRING(qt.text,
(qs.statement_start_offset/2)+1,
((CASE
qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset
END –
qs.statement_start_offset)/2)+1),
qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads,
qs.max_logical_reads,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY
qs.total_logical_reads DESC

Legal, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são bastante conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Agradeço sua visita, com certeza é imenso prazer saber que você teve interesse em acessar este post.

Nos encontramos novamente no mês de setembro, para o próximo post dedicado a sessão Short Scripts.

Um grande abraço, boa noite, bom descanso.

Valeu.

Short Scripts – Março 2017


Hoje é sexta – feira, sexta – feira…

Salve, salve comunidade e amantes de bancos de dados e SQL Server, Tudo bem? Mais uma final de semana próximo após uma longa semana de muito trabalho, se eu for falar de muito trabalho, sinceramente  esta semana foi complicada, repleta de novidades e muita troca de conhecimento.

Conforme o prometido no final de 2016, estou retornando com o primeiro post da sessão Short Scripts, sessão criada a alguns anos no meu blog que lentamente esta conseguindo ajudar diversos profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Como já destaquei acima, este é o primeiro post de 2017 dedicado exclusivamente a sessão Short Scripts, na relação de scritps selecionados para hoje, você vai poder encontrar códigos relacionados com os seguintes assuntos:

  • Auditoria,
  • Comando Intersect,
  • Comando OpenQuery,
  • Comando Order By,
  • Datatype Char,
  • Datatype Int,
  • DMF Sys.dm_exec_sessions,
  • DMV Sys.system_internals_partitions,
  • DMV Sys.system_internals_allocation_units,
  • Função Substring,
  • Índices,
  • Informações sobre conexão de usuário,
  • Páginas de Dados,
  • Trigger, e
  • Variáveis.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados antes de serem publicados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas. Fique a vontade para compartilhar, comentar, melhorar cada um destes códigos.

Short Scripts

— Short Script 1 – Realizando Auditoria in Live —

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END –
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              — Ignore system spids.
AND session_Id NOT IN (@@SPID)     — Ignore this current statement.
ORDER BY 1, 2
Go

— Short Script 2 – Utilizando comando Intersect com Datatypes Char e Int —

Create Table #A (x Char(2));
Go
Insert Into #A Values (‘1’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘2’);
Insert Into #A Values (‘3’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘9’);
Go
Create Table #B (M BigInt);
Go
Insert Into #B Values(5);
Insert Into #B Values(5);
Insert Into #B Values(6);
Insert Into #B Values(7);
Insert Into #B Values(7);
Go
— (Select #1)
SELECT x AS ‘Select #1’ FROM #A
INTERSECT SELECT M FROM #B
Go
— (Select #2)
SELECT DISTINCT(x) AS ‘Select #2’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #3)
SELECT DISTINCT(x) AS ‘Select #3’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #4)
SELECT DISTINCT(x) AS ‘Select #4’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— (Select #5)
SELECT x AS ‘Select #5’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— Short Script 3 – Utilizando comando OpenQuery com variáveis —
— Valores Básicos —
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = ‘teste’
SELECT  @TSQL = ‘SELECT * FROM OPENQUERY(MeuLinkedServer,”SELECT * FROM MinhaTabela WHERE User = ””’ + @VAR + ”””’)’
EXEC (@TSQL)
Go
— Query Complexa —
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = ‘MyLinkedServer’
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(‘+ @LinkedServer + ‘,”’
SET @TSQL = ‘SELECT au_lname, au_id FROM pubs..authors”)’
EXEC (@OPENQUERY+@TSQL)
— Use o Sp_executesql procedimento armazenado —
DECLARE @VAR char(2)
SELECT  @VAR = ‘CA’
EXEC MyLinkedServer.master.dbo.sp_executesql
N’SELECT * FROM pubs.dbo.authors WHERE state = @state’,
N’@state char(2)’,
@VAR
Go
— Short Script 4 – Realizando order by com base na função SubString —
Declare @Tabela Table
(Codigo VarChar(15))
Insert Into @Tabela Values(‘191-XXX-003’)
Insert Into @Tabela Values(‘192-XXX-003’)
Insert Into @Tabela Values(‘193-XXX-003’)
Insert Into @Tabela Values(‘194-XXX-003’)
Insert Into @Tabela Values(‘195-XXX-003’)
Insert Into @Tabela Values(‘191-XXX-001’)
Insert Into @Tabela Values(‘192-XXX-001’)
Insert Into @Tabela Values(‘193-XXX-001’)
Insert Into @Tabela Values(‘194-XXX-001’)
Insert Into @Tabela Values(‘195-XXX-001’)
Insert Into @Tabela Values(‘191-XXX-002’)
Insert Into @Tabela Values(‘192-XXX-002’)
Insert Into @Tabela Values(‘193-XXX-002’)
Insert Into @Tabela Values(‘194-XXX-002’)
Insert Into @Tabela Values(‘195-XXX-002’)
Select * from @Tabela
Order By SubString(codigo,Len(Codigo)-2,3) Asc
Go
— Short Script 5 – Monitorando querys em execução —
SELECT
DES.SESSION_ID,
DES.CPU_TIME,
DES.READS,
DES.WRITES,
DES.LOGICAL_READS,
DES.ROW_COUNT,
DER.SESSION_ID,
DES.STATUS,
DES.HOST_NAME,
DES.PROGRAM_NAME,
DES.LOGIN_NAME,
DES.ORIGINAL_LOGIN_NAME,
DEC.CLIENT_NET_ADDRESS,
DEC.AUTH_SCHEME,
DEC.NET_TRANSPORT,
SUBSTRING(T.[TEXT], DER.[STATEMENT_START_OFFSET] / 2,
COALESCE(NULLIF(DER.[STATEMENT_END_OFFSET], – 1) / 2, 2147483647)) AS COMANDO
FROM
SYS.DM_EXEC_SESSIONS AS DES
INNER JOIN SYS.DM_EXEC_REQUESTS DER
ON DER.BLOCKING_SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_CONNECTIONS DEC
ON DEC.SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_REQUESTS DER2
ON DER2.SESSION_ID = DES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DER.[SQL_HANDLE]) AS T
GO
— Short Script 6 – Identificando tabelas e suas respectivas páginas de dados —
— Exemplo 1: —
SELECT P.partition_id,
OBJECT_NAME(P.object_id) As ObjectName,
U.allocation_unit_id,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.Partitions As P INNER JOIN Sys.Allocation_Units As U
ON P.hobt_id = U.container_id
Inner Join Sys.system_internals_allocation_units SU
On u.allocation_unit_id = su.allocation_unit_id
Go
— Exemplo 2: —
SELECT SIP.partition_id,
OBJECT_NAME(SIP.object_id) As ObjectName,
sip.rows,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.system_internals_partitions As SIP Inner Join Sys.system_internals_allocation_units SU
On sip.partition_id = su.allocation_unit_id
Go
— Short Script 7 – Identificando índices com Escrita Excessiva —
— Quantidade de Índices com Escrita Excessiva em comparação a leitura —
SELECT  OBJECT_NAME(s.object_id),
i.name,
i.type_desc
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK ) Inner JOIN sys.indexes i WITH (NOLOCK)
ON s.index_id = i.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Quantidade de Índices com Escrita Excessiva —
SELECT  COUNT(*)
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )
WHERE   OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Short Script 8 – Identificando o espaço ocupado por conexões em uso —
SELECT A.session_id,
B.host_name, B.Login_Name ,
(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,
D.Text
FROM sys.dm_db_session_space_usage A Inner JOIN sys.dm_exec_sessions B
ON A.session_id = B.session_id
Inner JOIN sys.dm_exec_connections C
ON C.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D
WHERE A.session_id > 50
and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB
ORDER BY totalalocadoMB desc
COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)
Go
— Short Script 9 – Obtendo a diferença de datas entre registro anterior e o próximo registro —
Create Table Datas
(ID Int Identity(1,1),
DataInicio Date,
DataFinal Date)
Go
Insert Into Datas Values (GETDATE(), GETDATE()+30)
Insert Into Datas Values (GETDATE()+1, GETDATE()+20)
Insert Into Datas Values (GETDATE()+2, GETDATE()+10)
Insert Into Datas Values (GETDATE(), GETDATE()+5)
Go
— Exemplo 1 —
Select I.ID, I.DataInicio, F.DataFinal,
DateDiff(D, I.DataInicio, F.DataFinal) As Intervalo
From Datas I Left Join Datas F
On I.ID = F.ID + 1

Go

— Exemplo 2 —
SELECT
[current].Id,
[current].Time CurrentValue,
[next].Time          NextValue
FROM #temp AS [current] LEFT JOIN #temp AS [next]
ON [next].Id = (SELECT MIN(Id) FROM #temp

                                 WHERE Id > [current].Id)
Go
— Short Script 10 – Criando um trigger condicional —
–Criando a Table de Novos Produtos–
Create Table NovosProdutos
(Codigo Int Identity(1,1),
Descricao VarChar(10))
–Criando a Table de Histórico Novos Produtos–
Create Table HistoricoNovosProdutos
(Codigo Int,
Descricao VarChar(10))
Go
–Inserindo valores —
Insert Into Novosprodutos Values(‘Arroz’)
Insert Into Novosprodutos Values(‘Arroz1’)
Insert Into Novosprodutos Values(‘Arroz2’)
Insert Into Novosprodutos Values(‘Arroz3’)
Go
–Criando a Trigger para controle de histórico–
Create TRIGGER T_Historico
ON NovosProdutos
for update
AS
IF (Select Descricao from Inserted) <> (Select Descricao from Deleted)
BEGIN
INSERT Into HistoricoNovosProdutos (Codigo, Descricao)
SELECT Codigo, Descricao FROM INSERTED
END
Go
–Fazendo os teste —
Update NovosProdutos
Set Descricao=’Arroz 4′
Where Codigo = 1
Go
Update NovosProdutos
Set Descricao=’Arroz1′
Where Codigo = 2
Go
Select * from NovosProdutos
Go

Muito bem, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância e apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são bastante conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço…

Nos encontramos em breve nos próximos posts desta e outras sessões do meu blog, valeu.

Short Scripts – Dezembro 2016


Buenas tardes….. Comunidade.

Hoje um dia mais que especial na minha vida e da minha família, meu pai Pedro Galvão esta completando 60 anos de vida, meu deus, como o tempo nos supreende com a sua velocidade.

Quero aproveitar este post para agradeço por este privilégio de poder conviver com uma pessoa tão integra, humilde, trabalhadora, honesta, enfim existem milhares de adjetivos para definir o que meu pai e sua excência em pessoa representa.

O post de hoje

Falando do post de hoje, este é um último post dedicado a sessão Short Scripts no ano de 2016, mas com certeza em 2017 vai estar retornando com toda a força.

Como de costume, e não poderia ser diferente, estamos no final do ano, próximos ao Natal, momento de festa e troca de presentes, lógicamente vou deixar o meu presentinho para vocês, compartilhando os últimos scripts adquiridos neste ano, relacionados aos seguintes assuntos:

  • Calendário anual de feriados;
  • Conversão de Binários para String;
  • CTE e CTE Recursiva;
  • Funções;
  • Extended Stored Procedure XP_ServiceControl;
  • MaxRecursion;
  • Union e Union All; e
  • Monitoramento de Serviços.

Short Scripts

A seguir apresento a relação de Short Scripts de hoje:

— Short Script 1 – Monitorando os serviços do SQL Server através da Extended Stored Procedure – XP_ServiceControl

Set NoCount On
CREATE TABLE #ServicesStatus
(
myid int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100),
Status varchar(50),
checkdatetime datetime default (getdate())
)
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’MSSQLServer’
update #ServicesStatus set serviceName = ‘MSSQLServer’ where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’SQLServerAGENT’
update #ServicesStatus set serviceName = ‘SQLServerAGENT’ where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’msdtc’;
update #ServicesStatus set serviceName = ‘msdtc’ where myid = @@identity;
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’sqlbrowser’
update #ServicesStatus set serviceName = ‘sqlbrowser’ where myid = @@identity
Select * from #ServicesStatus
— Short Script 2 – Realizando a conversão de Binário para String e String para Binário —
— Declarando uma chave legível —
DECLARE @chave  VARCHAR(MAX) =’quechavemalfeita’
— Convertendo para Binário —
DECLARE @dadosBinary VARBINARY(MAX) = CONVERT(VARBINARY(MAX),@chave,0)
SELECT @chave,@dadosBinary
— Observando as diferenças —
SELECT CONVERT(VARCHAR(MAX),@dadosBinary,0) –o Tipo 0 tranforma em texto legivelSELECT CONVERT(VARCHAR(MAX),@dadosBinary,1) — transforma o valor BINARY EM VALOR VARCHAR DEIXANDO O MESMO CONTEUDO

SELECT CONVERT(VARCHAR(MAX),@dadosBinary,2)– transforma o valor BINARY EM VALOR VARCHAR RETIRANDO O 0x NO INICIO
Go

— Short Script 3 – Criando um calendário anual para feriados —
CREATE SCHEMA Calendar
Go
CREATE FUNCTION Calendar.Computus (@Y INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @a INT, @b INT, @c INT, @d INT, @e INT, @f INT, @g INT, @h INT, @i INT, @k INT, @L INT, @m INT
SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b – @f + 1) / 3
SET @h = (19 * @a + @b – @d – @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i – @h – @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L – 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L – 7 * @m + 114) % 31))
END
GO
CREATE TABLE Calendar.[Calendar] (
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO
ALTER TABLE Calendar.[Calendar]
ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN (‘HOLIDAY’, ‘SATURDAY’, ‘SUNDAY’, ‘BANKDAY’)))
GO
SET DATEFIRST 1;
WITH Dates(Date)
AS
(
SELECT cast(‘1999’ AS DateTime) Date
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast(‘2021’ AS DateTime) -1
),
DatesAndThursdayInWeek(Date, Thursday)
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date – 1
WHEN 6 THEN Date – 2
WHEN 7 THEN Date – 3
END AS Thursday
FROM Dates
),
Weeks(Week, Thursday)
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week,
Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,
CASE
WHEN (DATEPART(DayOfYear, d.Date) = 1)          — New Year’s Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  — Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  — Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  — Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date)))    — Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) — Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) — Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) — Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      — Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     — Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    — Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    — Boxing day
THEN ‘HOLIDAY’
WHEN DATEPART(Weekday, d.Date) = 6 THEN ‘SATURDAY’
WHEN DATEPART(Weekday, d.Date) = 7 THEN ‘SUNDAY’
ELSE ‘BANKDAY’
END KindOfDay,
CASE
WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN ‘New Year”s Day’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN ‘Palm Sunday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN ‘Maundy Thursday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN ‘Good Friday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN ‘Easter Sunday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN ‘Ascension Day’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN ‘Pentecost’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN ‘Whitmonday’
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN ‘Labour day’
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN ‘Constitution day’
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN ‘Cristmas day’
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN ‘Boxing day’
END Description
FROM DatesAndThursdayInWeek d
inner join Weeks w
on d.Thursday = w.Thursday
OPTION(MAXRECURSION 0)
GO
CREATE FUNCTION Auxiliary.Numbers (@AFrom INT, @ATo INT, @AIncrement INT)
RETURNS @RetNumbers TABLE
([Number] int PRIMARY KEY NOT NULL)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO
CREATE FUNCTION Auxiliary.iNumbers( @AFrom INT, @ATo INT, @AIncrement INT)
RETURNS TABLE
AS
RETURN( WITH Numbers(n)
AS
(SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo)
SELECT n AS Number from Numbers
)
GO

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço…

Feliz Natal com muita saúde, paz, alegria e esperança. Que 2017 você possa brilhar ainda mais e estar junto com a comunidade.

Short Scripts – Março 2016


Olá comunidade, boa tarde amantes do SQL Server e Banco de dados!!!!

Após alguns meses fora do ar, mais uma sessão do meu blog esta retornando com muita força e disposição, estou me referindo a Short Scripts, sessão que durante os últimos anos tem conseguido alavancar a visitação do meu blog e principalmente ajudar a todos da comunidade na utilização de pequenos scripts dos mais variados possíveis assuntos relacionandos com o banco de dados mas principalmente ao Microsoft SQL Server.

Caso você não se lembre esta é uma sessão publicada a cada bimestre, onde durante o intervalo entre a publicação do post anterior e o novo post, vou adicionando novos arquivos “scripts” a minha biblioteca de scripts e posteriormente selecione aqueles que aparentemente podem ser considerados pequenos e de fácil uso e entendimento, mas que na verdade podem ajudar a resolver diversas situações ou até mesmo servir como recurso para implementação na busca da solução de grandes obstáculos que os profissionais de banco de dados e desenvolvimento possam estar se deparando.

Muitos dos scripts disponibilizados nesta sessão são resultado de pesquisas, posts publicados nos fóruns MSDN e também de respostas encontradas em outros websites dedicados ao SQL Server. Caso você não tenha acessado os últimos posts, não perca tempo, utilize os links publicados a seguir:

https://pedrogalvaojunior.wordpress.com/2015/12/14/short-script-dezembro-2015/

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/

Na relação de short scripts disponibilizados neste post, destaco os seguintes assuntos, conceitos ou funcionalidades:

  • Comando Like;
  • Comando Try…Catch;
  • Comando Print;
  • Concatenação de colunas e linhas;
  • Conversão de dados;
  • Dados Hexadecimais;
  • Database Owner;
  • Diretiva Set Ansi_Defaults;
  • Função Hashbytes;
  • Operador Bitwise;
  • Session Settings;
  • SET Implicit_Transactions;
  • String; e
  • Tipos de dados XML.

A seguir, apresento a relação de short scripts:

— Short Script 1 – Brincando com concatenação de linhas e colunas —

Select 1.8 E,1.8E,1.Eight,3+8E8Eight,

3+8 E8E,‘Six’+‘Seven’ ‘Eight’,‘Six’+‘Seven’+‘Eight’,

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

— Short Script 2 – Utilizando operador Bitwise para análise lógica de dados —

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

— Short Script 3 – Trabalhando com XML – Apresentando os mesmos dados de forma diferente —

create table #customers (

id int,

customer varchar(50)

)

insert into #customers values (1,‘John’)

insert into #customers values (2,‘Lyss’)

insert into #customers values (3,‘Jack’)

insert into #customers values (4,‘David’)

insert into #customers values (5,‘Anne’)

insert into #customers values (6,‘Victoria’)

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

ORDER BY [customers!2!customer] DESC,parent

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

FOR XML PATH(),ROOT(‘customers’)

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

INNER JOIN #customers AS customersb ON customersb.id=customersa.id

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

FOR XML RAW(‘customer’),ROOT(‘customers’),TYPE

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

return <customers id=”{$e/@id}”>

<customers customer=”{$e/@customer}”>

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

— Short Script 4 – Descobrindo e Alterando o Database Owner —

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

— Obtendo informações de um banco específico —

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

— Obtendo informações de um banco específico após alteração —

Exec sp_helpdb ‘Master’

Go

 

 

— Short Script 5 – Concatenação de string diretamente no select —

create table xxx (i int identity, a varchar(3))

go

declare @txt varchar(255)

set @txt = ‘Question’

select @txt = isnull (a, ‘?’) from xxx order by i

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

select @txt = @txt + ‘ ‘ + a from xxx order by i

select @txt

Go

 

— Short Script 6 – Utilizando comando Like na junção de tabelas —

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

insert into #table2 values (‘[_]1’)

insert into #table2 values (‘[%]1’)

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

— Short Script 7 – Session Settings – Utilizando ANSI_Defaults forçando SQL Server trabalhar com SET IMPLICIT_TRANSACTIONS —

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

Insert Into QOD_Customers Values (‘A’,‘teste’)

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

SELECT COUNT(CompanyName) AS ‘Before rollback’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

UPDATE Dbo.QOD_Customers SET Region = ‘XXX’ WHERE dbo.QOD_Customers.region IS NULL

— The after update Select Statement

SELECT COUNT(CompanyName) AS ‘After update’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

SELECT COUNT(CompanyName) AS ‘After Rollback’ FROM [dbo].[QOD_Customers] WHERE [dbo].[QOD_Customers].[Region] IS NULL

GO

 

Exec [QOD_Test_1]

Go

 

— Short Script 8 – Utilizando Try..Catch + Transaction + Print para apresentar mensagem —

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

— Short Script 9 – Trabalhando com a função Hashbytes para conversão de dados string para hexadecimal —

Select HASHBYTES(‘MD2’,‘January 1, 2016’)

Select HASHBYTES(‘MD4’,‘January 1, 2016’)

Select HASHBYTES(‘MD5’,‘January 1, 2016’)

Select HASHBYTES(‘SHA’,‘January 1, 2016’)

Select HASHBYTES(‘SHA1’,‘January 1, 2016’)

Select HASHBYTES(‘SHA2_256’,‘January 1, 2016’)

Select HASHBYTES(‘SHA2_512’,‘January 1, 2016’)

Go

 

 


 

Como de costume fique a vontade para compartilhar este conteúdo com seus contatos e redes sociais, contribua também enviando dicas, sugestões, dúvidas, críticas, enfim participe e ajude a melhorar cada vez mais esta sessão.

Mais uma vez obrigado por sua visita, nos encontramos em breve em mais uma post da sessão Short Scripts.

Valeu!!!

Short Script – Junho – 2015


Bom dia, Comunidade! Hello Everybody!!!

Estamos começando mais um mês e como diz alguns dos cronistas esportivos durante suas transmissões “O tempo passa….”, realmente passa e temos que procurar aproveitar.

Como já destaquei anteriormente procure diariamente estudar e conhecer cada vez mais sobre o mundo de Banco de Dados, Carreira de DBA, SQL Server e outros elementos que se relacionam com a minha função de Administrador de Banco de Dados, que exerço a muito tempo. Na verdade faço isso por que gosto e principalmente para me manter atualizado neste concorrido mercado de trabalho, seja ele profissional ou acadêmico, neste sentido em muitos momentos algumas informações, dicas, truques e até mesmo códigos de exemplo acabam fazendo parte da minha “Biblioteca de Scripts” que venho nos últimos anos compartilhando com todos vocês.

Seguindo esta escrita e mantendo a tradição, como de costume todo inicio ou final de mês estou compartilhando com vocês novos Shorts Scripts ou Materiais de Apoio, e hoje não será diferente.

Neste Short Script, você vai encontrar pequenos blocos de código ou exemplos relacionados aos seguintes itens:

  • Criação de Jobs e Steps por linha de comando utilizando a opção @subsytem;
  • Criação de função para manipulação de String;
  • Criação de uma User Function Scalar para trabalhar com a Tabela Price;
  • Simulando um Select “*” que não retorna todas as colunas;
  • Trabalhando com XML em conjunto com SP_XML_PrepareDocument;
  • Utilizando a função Count_Big em conjunto com Funções de Agregação;
  • Utilizando o comando Intersect com valores Char e Int; e
  • Utilizando Sparse Columns e Sparse Columns Sets.

Fique a vontade para copiar e compartilhar os Short Scripts apresentados abaixo:

 

— Short Script 1 – Criando Job para substituir o uso do XP_CMDShell –

Create PROCEDURE dbo.usp_ExecCmdShellProcess

AS

BEGIN

DECLARE @job NVARCHAR(100), @BulkCMD Varchar(1000)

SET @job = ‘xp_cmdshell replacement’+Convert(Varchar(10),GetDate()) ;

 

SET @BulkCMD = ‘BULK INSERT ListFiles FROM ‘+”’C:\TEMP\LISTFILES.TXT” ‘+

‘WITH (FIELDTERMINATOR = ”;”, ROWTERMINATOR = ”’+ CHAR(10) +”’, CODEPAGE = ”ACP”)’

 

EXEC msdb..sp_add_job @job_name = @job,

@description = ‘Automated job to execute command shell script’,

@owner_login_name = ‘pedro’, @delete_level = 1 ;

 

EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1,

@step_name = ‘Command Shell Execution’, @subsystem = ‘CMDEXEC’,

@command =’dir /b > C:\TEMP\LISTFILES.TXT’, @on_success_action = 1;

 

EXEC msdb..sp_add_jobserver @job_name = @job ;

 

EXEC msdb..sp_start_job @job_name = @job ;

 

Exec(@Bulkcmd)

 

END ;

GO

 

— Short Script 2 – Criando User Function para cortar Strings em partes —

CREATE FUNCTION fncCortaString(@txt VARCHAR(50), @txt_ini VARCHAR(50), @txt_fim VARCHAR(50))

RETURNS VARCHAR(50)

BEGIN

RETURN

(

SUBSTRING(@txt, CHARINDEX(@txt_ini, @txt) + LEN(@txt_ini), (CHARINDEX(@txt_fim, @txt) – CHARINDEX(@txt_ini, @txt)) – LEN(@txt_ini))

)

END

 

Select dbo.fncCortaString(‘SQL Server’, ‘SQL ‘, ‘ver’)

 

— Short Script 3 – Utilizando a função Count_Big para criar índice único em view com função de agregação —

Create Table MySampleTable

(Id1 Int,

Id2 Int,

SomeData Varchar(100))

 

Create View SampleView

With SchemaBinding

As

Select COUNT(*) TableCount,

Id2

From dbo.MySampleTable

Group By Id2

 

— Erro ao criar —

Create Unique Clustered Index [IX_ViewSample]

On [dbo].[SampleView]

(Id2 Asc)

Go

 

— Alterando o tipo de contagem de dados —

Create View SampleView

With SchemaBinding

As

Select COUNT_BIG(*) TableCount,

Id2

From dbo.MySampleTable

Group By Id2

 

— Criando o Índice —

Create Unique Clustered Index [IX_ViewSample]

On [dbo].[SampleView]

(Id2 Asc)

Go

 

— Short Script 4 – Utilizando o comando Intersect com valores Char e Int —

Create Table #A (x Char(2));

 

Insert Into #A Values (‘1’);

Insert Into #A Values (‘6’);

Insert Into #A Values (‘2’);

Insert Into #A Values (‘3’);

Insert Into #A Values (‘5’);

Insert Into #A Values (‘5’);

Insert Into #A Values (‘6’);

Insert Into #A Values (‘9’);

Go

 

Create Table #B (M BigInt);

 

Insert Into #B Values(5);

Insert Into #B Values(5);

Insert Into #B Values(6);

Insert Into #B Values(7);

Insert Into #B Values(7);

Go

 

SELECT x AS ‘Select #1’ FROM #A

INTERSECT SELECT M FROM #B;

 

— (Select #2)

SELECT DISTINCT(x) AS ‘Select #2’

FROM #A

LEFT OUTER JOIN #B

ON #A.x = #B.M

 

— (Select #3)

SELECT DISTINCT(x) AS ‘Select #3’

FROM #A

LEFT OUTER JOIN #B

ON #A.x = #B.M

 

— (Select #4)

SELECT DISTINCT(x) AS ‘Select #4’

FROM #A

INNER JOIN #B

ON #A.x = #B.M

 

— (Select #5)

SELECT x AS ‘Select #5’

FROM #A

INNER JOIN #B

ON #A.x = #B.M

 

— Short Script 5 – Criando User Function com base na Tabela Price —

CREATE FUNCTION CalcPrest (@C money, @i numeric(9,5), @n int)

returns table as return

SELECT Cast(@C *

(Power((1 + (@i / 100)), @n) * (@i / 100)) /

(Power((1 + (@i / 100)), @n) -1)

as money) as Prestação;

go

 

— Short Script 6 – Select “*” não retornando todas as colunas de uma tabela —

— Exemplo 1 —

DROP TABLE #temp;

 

CREATE TABLE #temp (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

Name VARCHAR(25),

Column1 INT SPARSE,

Column2 INT SPARSE,

Column3 INT SPARSE,

Column4 INT SPARSE,

ColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 

INSERT INTO #temp (Name, Column1) VALUES (‘Row1’, 1);

INSERT INTO #temp (Name, Column1, Column2) VALUES (‘Row2’, 2, 2);

INSERT INTO #temp (Name, Column1, Column2, Column3) VALUES (‘Row3’, 3, 3, 3);

INSERT INTO #temp (Name, Column1, Column2, Column3, Column4) VALUES (‘Row4’, 4, 4, 4, 4);

INSERT INTO #temp (Name, Column1, Column3) VALUES (‘Row5’, 5, 5);

INSERT INTO #temp (Name, Column3, Column4) VALUES (‘Row6’, 6, 6);

 

SELECT * FROM #temp;

— Exemplo 2 —

DROP TABLE #temp;

 

CREATE TABLE #temp (

RowID INT IDENTITY PRIMARY KEY CLUSTERED,

Name VARCHAR(25),

Column1 INT SPARSE,

Column2 INT SPARSE,

Column3 INT SPARSE,

Column4 INT SPARSE);

 

INSERT INTO #temp (Name, Column1) VALUES (‘Row1’, 1);

INSERT INTO #temp (Name, Column1, Column2) VALUES (‘Row2’, 2, 2);

INSERT INTO #temp (Name, Column1, Column2, Column3) VALUES (‘Row3’, 3, 3, 3);

INSERT INTO #temp (Name, Column1, Column2, Column3, Column4) VALUES (‘Row4’, 4, 4, 4, 4);

INSERT INTO #temp (Name, Column1, Column3) VALUES (‘Row5’, 5, 5);

INSERT INTO #temp (Name, Column3, Column4) VALUES (‘Row6’, 6, 6);

 

SELECT * FROM #temp;

 

— Short Script 7 – Trabalhando com XML em conjunto com SP_XML_PrepareDocument –

Declare @Table As Table (SINo Int, Name Varchar(500), Salary Money)

Declare @XMLIntLog As XML

Declare @IDoc Int

 

Set @XMLIntLog = Null

Insert Into @Table Values(1,’SES’,10000)

Insert Into @Table Values(2,’SRS’,40000)

Insert Into @Table Values(3,’SS’,50000)

Set @XMLIntLog = (Select SINo, Name, Salary from @Table Tab For XML Auto, Root(‘Root’), elements)

 

Exec sp_xml_preparedocument @IDoc Output, @XMLIntLog

 

Select SINo, Name, Salary from OpenXML(@iDoc, ‘/Root/Tab’,7)

With(SINo Int, Name Varchar(500), Salary Money)

 

Exec sp_xml_removedocument @IDoc

 

— Short Script 8 – Utilizando Sparse Columns e Sparse Columns Sets —

CREATE TABLE dbo.Table1 (

RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED,

DateTimeStamp DATETIME DEFAULT GETDATE(),

Col1 INTEGER SPARSE,

Col2 INTEGER SPARSE,

Col3 INTEGER SPARSE,

TblColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

 

INSERT INTO dbo.Table1 (Col1) VALUES (1), (2);

INSERT INTO dbo.Table1 (Col2) VALUES (3), (4);

INSERT INTO dbo.Table1 (Col3) VALUES (5), (6);

INSERT INTO dbo.Table1 (TblColumnSet) VALUES (‘<Col1>1</Col1><Col2>2</Col2><Col3>3</Col3>’);

 

SELECT RowID, DateTimeStamp, Col1, Col2, Col3, TblColumnSet

FROM dbo.Table1;

 

Agradeço a sua visita, espero que este material posso te ajudar, qualquer dúvida, sugestão ou crítica post o seu comentário.

Até mais.

Short Script – Maio – 2015


Bom dia, pessoal, mais uma semana começando.

Mantendo a tradição e escrita, estou começando mais um mês no meu blog, compartilhamento com todos vocês a minha relação de Scripts que atualmente esta crescendo muito graças ao SQLServerCentral.com.

Venho diariamente estudando e respondendo as questões diárias que são publicadas no SQLServerCentral.com, pelos principais nomes e especialistas em SQL Server do Mundo, estou seguindo nesta caminhada desde o final do mês de fevereiro. Posso dizer que esta sendo uma oportunidade fantástica de relembrar, conhecer, aprender e também descobrir muitos segredos, novidades e principalmente informações sobre o Microsoft SQL Server em todas as suas versões.

Aproveito a oportunidade para indicar e recomendar sua visita ao SQLServerCentral.com, principalmente a sessão de Question of the Day: http://www.sqlservercentral.com/Questions

Pois bem, na relação de Short Scripts que estou disponibilizando hoje, boa parte destes códigos são resultado das questões que eu respondi no SQLServerCentral.com e identifiquei como questões importantes ou relevantes para meus estudos, bem como, para repassar para meus alunos.

Nesta relação, você terá a oportunidade de conhecer e aprender um pouco mais sobre:

·         Análise e Lógica Condicional com operadores Not, And e Or;
·         Colunas Computadas;
·         Complexidade Lógica;
·         CTE Recursiva;
·         Declaração de variáveis do Tipo XML em conjunto com Select;
·         Função Coalesce em conjunto com Left Join e valores nulos; e
·         Funções Char(), NChar(), Unicode() e ASCII();
·         Identificando ausência de índices em tabelas;
·         Índices Clusterizados e Índices Chaves;
·         Índices com colunas duplicadas;
·         Operador Intersect;
·         Taxas de Fragmentação de Tabelas antes e depois do Rebuild;
·         Transações aninhadas;
·         Utilitário BCP para criação de arquivos XML.

 

Legal, a seguir você poderá encontrar os Short Scripts separados por seus respectivos conteúdos:

  • Análise, Precedência e Lógica Condicional com operadores Not, And e Or    VALUES ( 1, ‘Baseball Hat’ ),           ( 3, ‘Snowboard’ ),           ( 5, ‘Shows’ )SELECT ID   WHERE Product = ‘Bicycle’ OR Product = ‘Snowboard’ AND ID = 4

 

  1.    FROM @Logic
  2.            ( 4, ‘Goggles’ ),
  3.          ( 2, ‘Bicycle’ ),
  4. INSERT INTO @Logic
  5. DECLARE @Logic TABLE ( ID INT, Product VARCHAR(50) )

 

  • Colunas Computadas   (   , col1Computed AS LEFT(col1, 4)   , col2Computed AS LEFT(col2, 4)   , col3Computed AS LEFT(col3, 4)   , col4Computed AS LEFT(col4, 4) 

 

  1.    );
  2.    , col4 CHAR(8) NOT NULL
  3.    , col3 CHAR(8) NULL
  4.    , col2 VARCHAR(8) NOT NULL
  5.      col1 VARCHAR(8) NULL
  6. CREATE TABLE t
  7. Complexidade Lógica

DECLARE @AA VARCHAR(12) = ‘AAA’

,@XXX           VARCHAR(12) = ‘AAA’

,@YYY           VARCHAR(12) = ‘BBB’

,@ZZZ           VARCHAR(12) = ‘CCC’

,@AAXXX         VARCHAR(12) = ‘XXAAA’

,@AAYYY         VARCHAR(12) = ‘XXBBB’

,@AAZZZ         VARCHAR(12) = ‘XXCCC’

,@XXXCount       INT = 1

,@YYYCount       INT = 1

,@ZZZCount       INT = 1

,@AAXXXCount     INT = 1

,@AAYYYCount     INT = 1

,@AAZZZCount     INT = 1

,@Result       INT;

 

— Much to your chagrin, you encounter the following logic in a SQL SP.

— You may assume that each local variable is DECLAREd and @AA,

— @XXX, @YYY, @ZZZ, @AAXXX, @AAYYY, @AAZZZ each have a value assigned,

— with only @AA required to be NOT NULL. Each @xxxCount variable

— is also assigned an integer value.

SET @Result = 0;

 

IF @AA = @XXX

BEGIN

IF @XXXCount > @AAXXXCount SET @Result = 2

END

ELSE IF @AA = @YYY

BEGIN

IF @YYYCount > @AAYYYCount SET @Result = 2

END

ELSE IF @AA = @ZZZ

BEGIN

IF @ZZZCount > @AAZZZCount SET @Result = 2

END

ELSE IF @AA = @AAXXX

BEGIN

IF @XXXCount = @AAXXXCount SET @Result = 3

END

ELSE IF @AA = @AAYYY

BEGIN

IF @YYYCount = @AAYYYCount SET @Result = 3

END

ELSE IF @AA = @AAZZZ

BEGIN

IF @ZZZCount = @AAZZZCount SET @Result = 3

END

 

— Identify the equivalent logic from the options shown:

— Option 1:

SET @Result = 0;

 

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

 

SELECT [Option 1:]=@Result

SELECT @Result = NULL;

 

 

— Option 2:

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

ELSE SET @Result = 0;

 

SELECT [Option 2:]=@Result

SELECT @Result = NULL;

 

— Option 3:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

END;

 

SELECT [Option 3:]=@Result          — Not identical

SELECT @Result = NULL;

 

— Option 4:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

ELSE 0 END;

 

SELECT [Option 4:]=@Result

SELECT @Result = NULL;

 

— Option 5:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 END

ELSE 0 END;

 

SELECT [Option 5:]=@Result   — Not identical

 

  • CTE Recursiva set @DataFim = ‘20150430’;with CTE_Datas as   select @DataIni as Data, @DataFim as DataFim   union all   select DATEADD(DAY, 1, Data), DataFim   where Data < DataFim 

 

  1. select Data from CTE_Datas
  2. )
  3.    from CTE_Datas
  4. (
  5. set @DataIni = ‘20150401’;
  6. declare @DataIni date, DataFim date;

 

  • Declaração de variáveis do Tipo XML em conjunto com Select

 

DECLARE @Xml XML = N’

<products>

<product id=”1″ name=”Adjustable Race” />

<product id=”879″ name=”All-Purpose Bike Stand” />

<product id=”712″ name=”AWC Logo Cap” />

<product id=”19910″ name=”Cozonac” />

</products>’;

 

SELECT

xt.xc.value(‘@id’, ‘INT’) AS ProductID,

xt.xc.value(‘@name’,’NVARCHAR(50)’) AS Name

FROM

@Xml.nodes(‘/products/product’) AS xt(xc);

 

  • Função Coalesce em conjunto com Left Join e valores nulos(a int null, c int null)(x int null, z int null)

 

  1. y int null,
  2. Create table #xyz
  3. b int null,
  4. Create table #abc

 

 

Insert Into #abc values(null, null, 3), (1,null,3),(1,null,null),(null,2,null)

 

Insert Into #xyz values(null,2,3),(null,null,1),(null,null,2)

 

— Q1

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON a.a = x.x

AND a.b = x.y;

 

— Q2

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON COALESCE(a.a, 9) = COALESCE(x.x, 9)

AND COALESCE(a.b, 9) = COALESCE(x.y, 9);

 

 

  • Funções Char(), NChar(), Unicode() e ASCII()SELECT ASCII(36)   SELECT NCHAR(10084)  

 

    1. SELECT UNICODE(10084)
    2. SELECT CHAR(36)
  • Identificando ausência de índices em tabelas             user_scans, ID.equality_columns, FROM sys.dm_db_missing_index_group_stats GS LEFT OUTER JOIN sys.dm_db_missing_index_groups IG                                                                                                                                                                                                                                LEFT OUTER JOIN sys.dm_db_missing_index_details ID Where ID.database_id = DB_ID(‘CycleCount’) 

 

    1. ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC
    2.                                                                                                                                                                                                                                On (ID.index_handle = IG.index_handle)
    3.                                                                                    On (IG.index_group_handle = GS.group_handle)
    4.              ID.inequality_columns, ID.included_columns, ID.statement
    5. SELECT avg_total_user_cost, avg_user_impact, user_seeks,
  • Índices Clusterizados e Índices Chaves

 

SELECT t.name, i.name, i.type_desc, c.name

FROM sys.tables t

INNER JOIN sys.indexes i

INNER JOIN sys.index_columns ic

INNER JOIN sys.columns c

ON ic.object_id = c.object_id AND ic.column_id = c.column_id

ON i.object_id = ic.object_id AND i.index_id = ic.index_id

ON t.object_id = i.object_id AND i.index_id = 1;

GO

 

  • Índices com colunas duplicadas   (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                      order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id    (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                     order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id        substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and          (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))

 

  1. Go
  2.        (t1.columnlist<>t2.columnlist or
  3. where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and
  4.                                    i.index_id=ic.index_id inner join
  5.        from sys.indexes i inner join
  6.                from sys.index_columns ic
  7.                                            ic1.index_id=i.index_id
  8.                                      where ic1.index_id = ic.index_id and
  9.                                            sys.columns c on ic1.object_id=c.object_id and
  10.              (select distinct stuff((select ‘, ‘ + c.name
  11.      where o.is_ms_shipped=0) t1 inner join
  12.                                    i.index_id=ic.index_id inner join
  13.        from sys.indexes i inner join
  14.                from sys.index_columns ic
  15.                                            ic1.index_id=i.index_id
  16.                                      where ic1.index_id = ic.index_id and
  17.                                            sys.columns c on ic1.object_id=c.object_id and
  18.              (select distinct stuff((select ‘, ‘ + c.name
  19. select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from

 

  • Operador Intersect

 

CREATE TABLE A ( x INT )

 

INSERT INTO A

VALUES ( 18 ),

( 20 ),

( 4 ),

( 1 ),

( 2 )

 

CREATE TABLE B ( Y INT )

 

INSERT INTO B

VALUES ( 1 ),

( 20 ),

( 2 ),

( 3 )

 

SELECT X AS ‘Intersecting’

FROM A

INTERSECT

SELECT Y

FROM B

 

  • Taxas de Fragmentação de Tabelas antes e depois do Rebuild

 

Select

object_name(ddips.object_id) [Table_name],

convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) [Ext_frag],

ddips.page_count [Pages],

ddips.compressed_page_count

From sys.dm_db_index_physical_stats(db_id(), object_id(‘produtos’), null, null, ‘detailed’) ddips

  • Transações aninhadas

 

CREATE Table TranTable(col varchar(3))

GO

 

BEGIN TRAN A

INSERT INTO TranTable (col) Values (‘abc’)

BEGIN TRAN B

INSERT INTO TranTable (col) Values (‘def’)

SAVE TRAN B

BEGIN TRAN C

INSERT INTO TranTable (col) Values (‘ghi’)

COMMIT TRAN C

ROLLBACK TRAN B

INSERT INTO TranTable (col) Values (‘xyz’)

COMMIT TRAN A

GO

 

Select col from TranTable

GO

 

Drop TABLE TranTable

GO

 

  • Utilitário BCP para criação de arquivos XML

 

— Exemplo 1 —

 

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

 

SET @FileName = ‘C:\Users\test.xml’

SET @bcpCommand = ‘bcp “SELECT

”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”, ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”, CompanyName AS ”Parameter/value”

FROM Sales.Customers

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

print(@bcpCommand)

 

EXEC master..xp_cmdshell @bcpCommand

 

 

 

— Exemplo 2 —

Declare @Contador Int,

@FileName varchar(50),

@bcpCommand varchar(2000)

 

Set @Contador = 1

 

While @Contador <=(Select Count(IDdaSuaTabela) from SuaTabela)

Begin

 

SET @bcpCommand = ‘bcp “SELECT ”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”,

ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”,

CompanyName AS ”Parameter/value”

FROM Sales.Customers

Where IDdaSuaTabela = @Contador

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @FileName = ‘C:\Users\test-arquivo-‘+Convert(Varchar(10),@Contador)+’.xml’

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

EXEC master..xp_cmdshell @bcpCommand

 

Set @Contador += 1

End

Mais uma vez obrigado por sua visita, fique à vontade para compartilhar este material com seus contatos. Dúvidas, comentários, sugestões e críticas, envie seu comentário ou se preferir um e-mail.

Até mais.

Short Scripts – Janeiro – 2015


Exemplo – Obtendo informações – Page Life ExpectancyBom dia, Comunidade….

Estamos na metade de Janeiro de 2015 é o tempo passa e temos que acompanhar esta grande aeronave chamada “Tempo” em sua viagem até o final de 2015.

Muito bem, dando continuidade nos posts de 2015 e mantendo a tradição quero compartilhar com vocês a primeira relação de Short Scripts de 2015. Como você deve saber, a sessão Short Scripts tem como objetivo apresentar pequenos Códigos, Linhas de Código, Trechos ou mais especificamente Scripts utilizados no SQL Server em todas as suas principais versões que podem nos ajudar em alguns cenários e ambientes.

Dentre os assuntos e conceitos, destaco:

  • Claúsula Where;
  • CTE e CTE Recursiva;
  • Check Constraint;
  • Memory Clerks;
  • Page Life Expectancy;
  • Server Memory; e
  • Trigger.

Então fique a vontade para copiar e compartilhar:

Exemplo – Obtendo informações – Utilizando sys.dm_os_memory_clerks

SELECT  type,
SUM(single_pages_kb)/1024. AS [SPA Mem, MB],
SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
FROM sys.dm_os_memory_clerks
GROUP BY type
HAVING  SUM(single_pages_kb) + sum(Multi_pages_kb)  > 40000 — Só os maiores consumidores de memória
ORDER BY SUM(single_pages_kb) DESC

— Total utilizado
SELECT  SUM(single_pages_kb)/1024. AS [SPA Mem, KB],
SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
FROM sys.dm_os_memory_clerks

Exemplo – Obtendo informações – Page Life Expectancy

SELECT cntr_value AS ‘Page Life Expectancy’
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Buffer Manager’
AND counter_name = ‘Page life expectancy’

Exemplo – Informações – Server Memory

select counter_name,
cntr_value,
cast((cntr_value/1024.0)/1024.0 as numeric(8,2)) as Gb
from sys.dm_os_performance_counters
where counter_name like ‘%server_memory%’;

Exemplo – Básico – Descobrindo se o Trigger é Insert, Update ou Delete

Create Database Teste
Go
Use Teste
Go

Create Table T1
(Codigo Int)

Create Table T2
(Codigo Int,
Comando Varchar(10))
Go

Create Trigger Trigger1
On T1
After Insert, Update, Delete
As
Begin

If Exists (Select * From Inserted) And Not Exists (Select * From Deleted)
Insert Into T2 (Comando) Values (‘Insert’)

If Exists (Select * From Inserted) And Exists (Select * From Deleted)
Insert Into T2 (Comando) Values (‘Update’)

If Not Exists (Select * From Inserted) And Exists (Select * From Deleted)
Insert Into T2 (Comando) Values (‘Delete’)

End

Insert Into T1 Values (1)
Insert Into T1 Values (2)
Insert Into T1 Values (3)
Insert Into T1 Values (4)
Insert Into T1 Values (5)

Select * from T2

Exemplo – Utilizando – CTE Composta + CTE Recursiva

create table #Atendimentos
(id_Status_Entrega int identity primary Key,
id_Aviso_Receb int,
Situacao int,
DataHoraRegistro datetime default getdate(),
Quem_Recebeu varchar(20))

insert into #Atendimentos
(id_Aviso_Receb, Situacao, DataHoraRegistro, Quem_Recebeu)
values (52505,0,’2014-12-29 11:36′, ‘julio.mallioti’),
(52505,1,’2014-12-29 13:05′, ‘julio.mallioti’),
(52505,2,’2014-12-29 14:05′, ‘julio.mallioti’)
;with cte
as(
select * ,
row_number()over(partition by id_Aviso_Receb order by Situacao) as rownum
from #Atendimentos
),
cte2
as(
select *, cast(NULL as datetime) Diferenca from cte where rownum = 1 –anchor
union all
select cte.*,
cast((cte.DataHoraRegistro- cte2.DataHoraRegistro) as Datetime) Diferenca
from cte join cte2
on cte.rownum = cte2.rownum+1
and cte.id_Aviso_Receb = cte2.id_Aviso_Receb)

select *, cast(diferenca as time) Diferenca_Time
from cte2
order by id_Aviso_Receb, Situação

Exemplo – Criando – Check Constraint + User Function vinculada com Check Constraint

CREATE DATABASE [Exemplo]
go

USE [Exemplo]
GO
— Cria a Tabela de Relacionamento a ser Verificada
CREATE TABLE [dbo].[Estados](
[id] [int] IDENTITY(1,1) NOT NULL Primary Key,
[Estado] [char](2) NULL)
GO

— Esse Insert deve funcionar
insert into Estados (Estado) values (‘RS’), (‘SP’)

— Cria Tabela
CREATE TABLE [dbo].[Clientes](
[Id] [int] IDENTITY(1,1) NOT NULL Primary Key,
[Nome] [varchar](50) NULL,
[idEstado] [int] NULL)
go

— Cria a Function para Verificação
create function [dbo].[VerificaEstado](@idEstado int)
returns int
as
begin
— VErifica se o estado existe na tabela de Estados
return (select id from Estados where id = @idEstado)
end
GO

— Implementa a Function
ALTER TABLE [dbo].[Clientes]  WITH CHECK
ADD  CONSTRAINT [chk_VerificaEstado]
CHECK  (([dbo].[VerificaEstado]([idEstado]) IS NOT NULL))
GO

ALTER TABLE [dbo].[Clientes]
CHECK CONSTRAINT [chk_VerificaEstado]
GO

— Esse Insert Deve Funcionar
insert into Clientes (Nome, idEstado) values (‘Roberto Fonseca’, 1)
— Esse Insert Tem que dar Erro de Constrant de Check Violada
insert into Clientes (Nome, idEstado) values (‘Tem que Dar Erro’, 3)

select * from Clientes

use master
go
drop database exemplo
go

—————————————————————————————————–

Mais uma vez obrigado por sua visita, espero que você tenha gostado.

Nos encontramos em breve.

Short Script – Outubro – 2014


Bom dia, Ufa, Sexta – Feira…. Graças a Deus.

Já estamos no final do mês de Outubro e para encerrar mais este mês, vou compartilhar com vocês alguns dos Short Scripts que utilizei nos últimos dias em minha atividades diárias, como também, na solução de dúvidas nos Fóruns MSDN dedicados ao SQL Server.

Com você deve saber, esta Sessão Short Script tem a finalidade de compartilhar pequenos códigos de exemplos, ou códigos que não ultrapassem 10 linhas em relação a sua estrutura de código fonte, apresentado como você Usuário, Profissional de TI, Desenvolvedor, DBA, Estudante ou simplesmente um amante da área de tecnologia, pode fazer uso deste códigos na solução ou simulação das suas necessidades.

Todos os códigos foram testados e estão funcionando, vale ressaltar que os mesmos estão compatíveis com o SQL Server a partir da versão 2005.

Na relação de hoje destaco os seguintes recursos:

  • Buffer Pool;
  • Conversão de dados;
  • Comando Merge;
  • Informações sobre a Instância;
  • Plan Cache; e
  • Mapeamento de SIDs entre SQL Server e Windows.

A seguir você poderá encontrar a relação de Short Scripts, espero que você goste, e se surpreenda com este material:

— Short Script – Convertendo BigInt para Time —

Declare @Tabela Table

(Codigo Int Identity(1,1) Primary Key,

ValorBigInt BigInt)

Insert Into @Tabela

Values (216000000000), (252000000000), (288000000000), (324000000000), (360000000000),

(396000000000), (432000000000), (468000000000), (504000000000), (540000000000),

(576000000000), (612000000000), (648000000000), (684000000000), (720000000000)

Select CONVERT(TIME, DATEADD(SECOND, ValorBigInt / 10000000, ‘19700101 00:00’), 114) From @Tabela

Select DATEADD(SECOND, ValorBigInt / 10000000, ‘19700101 00:00’) From @Tabela

Select DATEADD(SECOND, ValorBigInt / 10000000, ’00:00′) From @Tabela

— Short Script – Obtendo Informações sobre Buffer Pool – Sumarizado —

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SA.data_pages As ‘Páginas de Dados’,

SA.used_pages As ‘Páginas de Dados Utilizadas’,

SA.total_pages As ‘Total de Páginas de Dados’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Go

— Short Script – Obtendo Informações sobre Buffer Pool – Detalhada —

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SA.data_pages As ‘Páginas de Dados’,

SA.used_pages As ‘Páginas de Dados Utilizadas’,

SA.total_pages As ‘Total de Páginas de Dados’,

SB.Row_Count

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Inner Join sys.dm_os_buffer_descriptors SB

On SB.allocation_unit_id = SA.allocation_unit_id

Go

— Short Script – Obtendo Informações sobre Buffer Pool – Mais Detalhada —

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SB.free_space_in_bytes As ‘Espaço Livre em Bytes’,

SB.page_id As ‘Identificador da Página’,

SB.page_type As ‘Tipo da Página’,

SB.Row_Count As ‘Total de Linhas por Página’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Inner Join sys.dm_os_buffer_descriptors SB

On SB.allocation_unit_id = SA.allocation_unit_id

Go

— Short Script – Obtendo informações sobre o Plan Cache —

SELECT cp.refcounts,

cp.usecounts,

cp.objtype,

st.dbid,

st.objectid,

st.text,

qp.query_plan

FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp;

Go

— Short Script – Obtendo informaçõe sobre a Instância do SQL Server —

DECLARE @Local varchar(8000)

EXEC master.dbo.xp_instance_regread

N’HKEY_LOCAL_MACHINE’,

N’Software\Microsoft\MSSQLServer\Setup’,

N’SQLPath’,

@Local output

SELECT @Local

GO

— Short Script – Utilizando o Comando Merge de Forma Incremental —

CREATE TABLE TB_ALVO

(

CD_PESSOA int IDENTITY(1, 1),

NM_PESSOA varchar(30),

CONSTRAINT PK_ALVO PRIMARY KEY(CD_PESSOA)

);

CREATE TABLE TB_ORIGEM

(

CD_PESSOA int,

NM_PESSOA varchar(30),

CONSTRAINT PK_ORIGEM PRIMARY KEY(CD_PESSOA));

GO

INSERT TB_ALVO(NM_PESSOA) VALUES(‘Jose’);

INSERT TB_ALVO(NM_PESSOA) VALUES(‘João’);

INSERT TB_ALVO(NM_PESSOA) VALUES(‘Durval’);

GO

INSERT TB_ORIGEM(CD_PESSOA, NM_PESSOA) Values(103, ‘Miguel’);

INSERT TB_ORIGEM(CD_PESSOA, NM_PESSOA) Values(104, ‘Sandro’);

GO

SELECT * FROM TB_ALVO

SELECT * FROM TB_ORIGEM

MERGE TB_ALVO AS A

USING TB_ORIGEM AS O

ON (A.CD_PESSOA = O.CD_PESSOA)

WHEN NOT MATCHED BY TARGET AND O.NM_PESSOA LIKE ‘S%’

THEN INSERT(NM_PESSOA) VALUES(O.NM_PESSOA)

WHEN MATCHED

THEN UPDATE SET A.NM_PESSOA = O.NM_PESSOA

WHEN NOT MATCHED BY SOURCE AND A.NM_PESSOA LIKE ‘S%’

THEN DELETE;

GO

SELECT * FROM TB_ALVO

SELECT * FROM TB_ORIGEM

GO

DROP TABLE TB_ALVO

DROP TABLE TB_ORIGEM

GO

— Short Script – Mapeamento entre SQL Server SIDs e Windows SIDs —

— Criando Tabela para armazenar Numeração —

CREATE TABLE dbo.TinyNumbers(Number TINYINT PRIMARY KEY);

Go

— Inserindo numeração sequência de valores para mapear ID —

INSERT dbo.TinyNumbers(Number)

SELECT TOP (256) ROW_NUMBER() OVER (ORDER BY number)-1

FROM master.dbo.spt_values;

Go

— Criando a Função para Obter o Windows SIDs —

CREATE FUNCTION dbo.GetWindowsSID (@sid VARBINARY(85))

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(SELECT ADsid = STUFF((SELECT ‘-‘ + part FROM

(SELECT Number = 1, part = ‘S-‘

+ CONVERT(VARCHAR(30),CONVERT(TINYINT,CONVERT(VARBINARY(30),LEFT(@sid,1))))

+ ‘-‘

+ CONVERT(VARCHAR(30),CONVERT(INT,CONVERT(VARBINARY(30),SUBSTRING(@sid,3,6))))

UNION ALL

SELECT TOP ((LEN(@sid)-5)/4) Number,

part = CONVERT(VARCHAR(30),CONVERT(BIGINT,CONVERT(VARBINARY(30),

REVERSE(CONVERT(VARBINARY(30),SUBSTRING(@sid,9+Number*4,4))))))

FROM dbo.TinyNumbers

ORDER BY Number

) AS x ORDER BY Number

FOR XML PATH(), TYPE).value(N’.[1]’,‘nvarchar(max)’),1,1,)

);

Go

— Criando View para relacionar SQL Servers SIDs com Windows SIDs —

CREATE VIEW dbo.server_principal_sids

AS

SELECT sp.name, sp.[sid], ad.ADsid, sp.type_desc

FROM sys.server_principals AS sp

CROSS APPLY dbo.GetWindowsSID(sp.[sid]) AS ad

WHERE [type] IN (‘U’,‘G’)

AND LEN([sid]) % 4 = 0;

Go

— Retornando os SIDs mapeados —

SELECT name,

[sid],

ADSid,

type_desc

FROM dbo.server_principal_sids;

Go


Fique a vontade para compartilhar este conteúdo, fazer suas críticas e sugestões.

Agradeço a sua visita nos encontramos em breve.

Short Script – Março – 2014


Salve, galera!!!

Mais uma vez começando e como de costume vou complicar com vocês, mas alguns Short Scripts, específicos para o Microsoft SQL Server 2008, R2 e 2012.

Na relação de hoje, vocês vão poder encontrar Scripts sobre:

– History Auto Page Repair;

– Long Running SQL/CLR Tasks;

– Process Address Space Info;

– Information about Memory Amounts and State;

– Numa State;

– CPU Utilization History in the Last 30 minutes;

– Isolate Top Waits for Instance Since Last Server Restart; e

– Pending I/O Request By File.

Fique a vontade para compartilhar e melhorar estes códigos, a seguir você encontra-rá cada um deles:

Exemplo 1 – History Auto Page Repair:

SELECT  DB_NAME(database_id) AS [database_name] ,
        database_id ,
        file_id ,
        page_id ,
        error_type ,
        page_status ,
        modification_time
FROM    sys.dm_db_mirroring_auto_page_repair ;

Exemplo 2 –  Long Running SQL/CLR Tasks:

SELECT  os.task_address ,
        os.[state] ,
        os.last_wait_type ,
        clr.[state] ,
        clr.forced_yield_count
FROM    sys.dm_os_workers AS os
        INNER JOIN sys.dm_clr_tasks AS clr
                     ON ( os.task_address = clr.sos_task_address )
WHERE   clr.[type] = ‘E_TYPE_USER’ ;

Exemplo 3 – Process Address Space Info:

SELECT  physical_memory_in_use_kb,
        locked_page_allocations_kb,
        page_fault_count,
        memory_utilization_percentage,
        available_commit_limit_kb,
        process_physical_memory_low,
        process_virtual_memory_low
FROM    sys.dm_os_process_memory ;

Exemplo 4 – Information about Memory Amounts and State:

SELECT  total_physical_memory_kb ,
        available_physical_memory_kb ,
        total_page_file_kb ,
        available_page_file_kb ,
        system_memory_state_desc
FROM    sys.dm_os_sys_memory ;

Exemplo 5 – Numa State:

SELECT  CASE COUNT(DISTINCT parent_node_id)
          WHEN 1 THEN ‘NUMA disabled’
          ELSE ‘NUMA enabled’
        END
FROM    sys.dm_os_schedulers
WHERE   parent_node_id <> 32 ;

Exemplo 6 – CPU Utilization History in the Last 30 minutes:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info);

SELECT TOP(30)

SQLProcessUtilization AS [SQL Server Process CPU Utilization],

SystemIdle AS [System Idle Process],

100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],

DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Event Time]

FROM (

SELECT record.value(‘(./Record/@id)[1]’, ‘int’) AS record_id,

record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS [SystemIdle],

record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,’int’) AS [SQLProcessUtilization], [timestamp]

FROM (

SELECT [timestamp], CONVERT(xml, record) AS [record]

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’

AND record LIKE N’%<SystemHealth>%’) AS x

) AS y

ORDER BY record_id DESC;

Exemplo 7 – Isolate Top Waits for Instance Since Last Server Restart:

WITH    Waits

AS ( SELECT   wait_type ,

wait_time_ms / 1000. AS wait_time_s ,

100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,

ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn

FROM     sys.dm_os_wait_stats

WHERE    wait_type NOT IN ( ‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’,

‘RESOURCE_QUEUE’, ‘SLEEP_TASK’,

‘SLEEP_SYSTEMTASK’,

‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’,

‘LOGMGR_QUEUE’, ‘CHECKPOINT_QUEUE’,

‘REQUEST_FOR_DEADLOCK_SEARCH’,

‘XE_TIMER_EVENT’, ‘BROKER_TO_FLUSH’,

‘BROKER_TASK_STOP’,

‘CLR_MANUAL_EVENT’,

‘CLR_AUTO_EVENT’,

‘DISPATCHER_QUEUE_SEMAPHORE’,

‘FT_IFTS_SCHEDULER_IDLE_WAIT’,

‘XE_DISPATCHER_WAIT’,

‘XE_DISPATCHER_JOIN’ )

)

SELECT  W1.wait_type ,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s ,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct ,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM    Waits AS W1

INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn ,

W1.wait_type ,

W1.wait_time_s ,

W1.pct

HAVING  SUM(W2.pct) – W1.pct < 95 ; — percentage threshold

Exemplo 8 – Pending I/O Request By File:

SELECT  DB_NAME(mf.database_id) AS [Database] ,
mf.physical_name ,
r.io_pending ,
r.io_pending_ms_ticks ,
r.io_type ,
fs.num_of_reads ,
fs.num_of_writes
FROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
ON r.io_handle = fs.file_handle
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id
ORDER BY r.io_pending, r.io_pending_ms_ticks DESC;

Legal, mais uma relação de Short Scripts compartilhada com vocês, espero que este conteúdo possa ser útil no seu dia – á – dia.

Um grande abraço.

Até a próxima.

Short Scripts – Janeiro – 2014


Bom dia, Comunidade….

Salve, Salve galera que gosta de Banco de Dados, mais especificamente o Microsoft SQL Server!!!!

Estamos começando mais um ano de muita expectativa com o possível lançamento do Microsoft SQL Server 2014, mas enquanto esta nova versão no chega, vamos trabalhando com as atuais versões.

Revirando o meu baú de Scripts, encontrei alguns códigos que a muitos anos eu utilizei e para falar a verdade nem se lembrava mais.

Pois bem, na relação de hoje você vai encontrar Short Scripts sobre:

  • Acessos de Usuários;
  • Conexões de Usuários;
  • Bloco de Códigos com Múltiplas CTEs;
  • Cálculo de dias úteis;
  • Cláusula values no Select;
  • Função PWDCompare;
  • Função PWDEncrypt;
  • Operador Neested Loop;
  • Operadore Merge Join; e
  • Trigger DDL.

Fique a vontade para copiar os códigos de exemplo, fazer suas alterações, propor melhorias ou mudanças, como também, suas sugestões e críticas.

Segue abaixo a relação de Short Scripts:

1 –  Exemplo – Básico – Multiplas – CTEs – Bloco de Código

;With CTE1 (Codigo, Descricao) As (Select Codigo, Descricao from Tabela1), CTE2 (Valor1, Valor2) As (Select Valor1, Valor2 From Tabela2)

Select T1.*, T2.* From Tabela1 T1, Tabela2 T2

2 – Exemplo – Básico – Trigger DDL – Criação e Exclusão Tabelas

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You must disable Trigger “safety” to drop or alter tables!’
ROLLBACK ;

3 – Exemplo – Calcular Dias Úteis

Set DateFormat dmy

Declare @DiaFinal Int,            @ContarDias Int

Set @DiaFinal = (Select DatePart(Day,GetDate())) Set @ContarDias=0

Set Datefirst 1

While @DiaFinal >= (Select DatePart(Day,GetDate())) And @DiaFinal < 28  Begin   Set @ContarDias = @ContarDias + 1   Set @DiaFinal=(Select DatePart(Day,GetDate()+@ContarDias))  End

SELECT @@DATEFIRST AS ‘1st Day’,  DATEPART(dw,GetDate()+@ContarDias) AS ‘Today’, @ContarDias As “Total de Dias”

4 – Exemplo – Informações – Conexões e Acessos

— Quantidade de Conexões Simultâneas —

SELECT status,

client_net_address as [IP do cliente],

p.hostname as [Nome da máquina do cliente],

[text] as [Texto da consulta],

SPID,

DB_NAME(p.dbid) as [Nome do BD no qual foi executada a query],

p.[program_name] as [Programa solicitante]

FROM sys.dm_exec_connections c INNER JOIN sys.sysprocesses p

on c.session_id = p.spid

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST

where STATUS IN (‘RUNNABLE’, ‘RUNNING’, ‘SUSPENDED’)

ORDER BY STATUS

— Quantidade de Conexões por banco de dados —

SELECT db_name(dbid) as Banco_de_Dados,

count(dbid) as Qtd_Conexoes

FROM sys.sysprocesses

WHERE dbid > 50

and db_name(dbid) = ‘smartgp’

GROUP BY dbid, loginame

5 – Exemplo – Relação de Usuário conectados

Select SPID As ‘Id’,
Loginame As ‘Usuário – SQL Server’,
HostName As ‘Computador’,
NT_Domain As ‘Domínio’,
NT_UserName As ‘Usuário – Domínio’
From Master..SysProcesses
Where Loginame <> ‘sa’
Order By Loginame

6 – Exemplo – Trabalhando com Operadores Neested Loop + Merge Join

— NESTED LOOP —
SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID = 43659

— MERGE JOIN —
SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID BETWEEN 43659 AND 44000

7 – Exemplo – Utilizando – Claúsula – Values no Select

Select * from (Values (‘Pedro’, 1),(‘FIT’, 2)) As Teste (About, Valor)

Select * from (Values (‘Valor’)) As Tabela (“Teste”)

8 – Exemplo – Utilizando a Função – PWDCompare e PWDEncrypt

— Inserindo a senha criptografada —

INSERT INTO Usuarios_novos VALUES (CONVERT(VARBINARY(255), PWDENCRYPT(‘Senha_Teste’)))

— Comparando a senha digitada com a senha criptografada —

SELECT PWDCOMPARE(‘Senha_Teste’,CONVERT(VARBINARY(255), PWDENCRYPT(‘Senha_Teste’)), 0) AS RESULTADO

Mais uma vez obrigado por sua visita, espero que esta material possa ser útil.

Nos encontramos na próxima Short Script.

Até mais.