Material de Apoio – Junho 2018


Olá, boa tarde.

Tudo bem? E ai esta curtindo a Copa do Mundo de Futebol da Rússia? Posso dizer tranquilamente que eu estou curtindo muito todos os jogos e informações possíveis de serem acompanhadas.

Estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a cada vez mais melhorar ainda.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o terceiro do ano de 2018 e de número 157 no total desta sessão.

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

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

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

  • Acessos de Usuário;
  • Backup;
  • CheckList;
  • Comando Create Procedure;
  • Comando Declare;
  • Database Backup;
  • Disponibilidade;
  • DMV sys.dm_db_index_usage_stats;
  • DMV Sys.dm_os_sys_info;
  • Dymanic Management View;
  • Free Disk Space;
  • Função DateAdd;
  • Função Month;
  • Heap Table;
  • Índices Clustered; Reinicialização de Servidores;
  • Índices;
  • Junção de Tabelas;
  • Leitura e Escrita; System Stored Procedure;
  • Logins;
  • Set DateFirst;
  • Set Language;
  • Set NoCount;
  • System Stored Procedure SP_MSForeachTable;
  • System Table sys.allocation_units; System Table sys.indexes;
  • System Table sys.partitions;
  • System Table sys.schemas;
  • System Table sys.tables;
  • Usuários; e
  • Variáveis.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

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

1 – Material de Apoio – Junho – 2018 – Determine Free Space Prior to SQL Server Backup.sql

2 – Material de Apoio – Junho – 2018 – DMV – Sys.dm_os_sys_info.sql – Identificando o último restart realizado na instância ou servidor.sql

3 – Material de Apoio – Junho – 2018 – Identificando a relação de Heap Tables.sql

4 – Material de Apoio – Junho – 2018 – Identificando a última segunda – feira e o último dia do mês.sql

5 – Material de Apoio – Junho – 2018 – Identificando o último usuário que acesso a tabela.sql

6 – Material de Apoio – Junho – 2018 – Obtendo a relação de últimos acessos de leitura e escrita por banco de dados.sql

7 – Material de Apoio – Junho – 2018 – SP_msforeachtable – Criando índices clustered em todas as tabelas através de uma coluna específica.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

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

Links

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

https://pedrogalvaojunior.wordpress.com/2018/04/05/material-de-apoio-abril-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/13/material-de-apoio-fevereiro-2018/

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de agosto, até lá continue curtindo sua vida, aproveite este grande momento de confraternização mundial que a Copa do Mundo de Futebol nos proporciona a cada quatro anos.

Um forte abraço, muita saúde, sucesso e vamos em frente…

Anúncios

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…

Material de Apoio – Abril 2018


Muito, mas, muito bom dia!

São exatamente 08:00 horas da manhã e você já esta dando uma passadinha por aqui, que legal, obrigado por mais esta visita.

E ai tudo bem com você? Espero que sim. Estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a cada vez mais melhorar ainda.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o segundo do ano de 2018 e de número 156 no total desta sessão.

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

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

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

  • @@TranCount;
  • Claúsula Where;
  • Comando Declare;
  • Comando Kill;
  • Comando Order By;
  • Comando Rollback;
  • Comando While;
  • Conversão implícita de dados;
  • Cursor;
  • Database Level Events;
  • Dynamic Management Function sys.dm_exec_query_plan;
  • Dynamic Management Function sys.dm_exec_sql_text;
  • Dynamic Management View sys.dm_exec_query_stats;
  • Dynamic Management View sys.dm_os_tasks;
  • Dynamic Management View sys.dm_os_threads;
  • Error Code 3609;
  • Função Cast;
  • Função EventData();
  • Função Raiserror();
  • Função Top();
  • Loop de processamento infinito;
  • Opção Recompile;
  • Operador Cross Apply;
  • OS Threads;
  • Processos em execução;
  • Sessions;
  • Set RowCount;
  • Stored Procedure Exec;
  • System Table sys.sysprocesses;
  • Tratamento de Erros;
  • Trigger DDL; e
  • Variáveis.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

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

1 – Material de Apoio – Abril 2018 – Encerramento processos que apresentam várias horas ou longo tempo de execução.sql

2 – Material de Apoio – Abril 2018 – Trigger DDL DATABASE LEVEL EVENTS – Bloqueando a criação de tabelas com um determinado nome.sql

3 – Material de Apoio – Abril 2018 – Identificando a SessionID e suas respectivas OS Threads.sql

4 – Material de Apoio – Abril 2018 – Relação de Querys – Apresentam conversão implícita de dados.sql

5 – Material de Apoio – Abril 2018 – Criando um loop infinito utilizando SET ROWCOUNT com Variable Table.sql

6 – Material de Apoio – Abril 2018 – Utilizando Rollback Transaction dentro de Trigger com tratamento de erro 3609.sql

7 – Material de Apoio – Abril 2018 – Utilizando Rollback Transaction dentro de Trigger decrementando o valor de @@Trancount.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

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

Links

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

https://pedrogalvaojunior.wordpress.com/2018/02/13/material-de-apoio-fevereiro-2018/

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

https://pedrogalvaojunior.wordpress.com/2017/05/09/material-de-apoio-maio-2017/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

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

Um forte abraço, muita saúde, sucesso e vamos em frente…

Script Challenge – 13 – A resposta….


Salve pessoal, bom dia.

Tudo bem?  Seja mais uma vez muito bem vindo ao meu blog, mais especificamente ao post que apresenta a resposta para o Script Challenge – 2017, publicado em outubro de 2017 destacando o retorno da sessão Script Challenge (Script Desafiador ou Desafio do Script) como queiram traduzir.

Espero que você já tenha ouvido falar desta sessão ou acessado alguns dos posts publicados na mesma, caso ainda não tenha feito, fique tranquilo você vai encontrar no final deste post uma pequena relação contendo os últimos desafios lançados e seus respostas.

Vamos então falar um pouco mais sobre o último desafio, estou me referindo ao Script Challenge 13, sendo assim, seja bem vindo a mais um post da sessão Script Challenge.


Script Challenge 13

Falando do desafio de número 13, o mesmo foi publicado no mês de outubro de 2017, período de data que apresenta uma das comemorações mais importantes que ocorrem anualmente em quase todos os países do mundo, mas que especialmente no Estados Unidos da América.

E ai já matou a charada? Eu acredito que sim! Mas para te ajudar mais um pouco vou apresentar a Figura 1 que contem todo código Transact-SQL utilizado neste desafio, contendo trechos ou partes de código ocultas, procedimento que realizei no post que contempla o lançamento deste desafio como forma de aumentar o nível de dificuldade:

Figura 1 – Código Transact-SQL apresentado no Script Challenge 13.

Bom chegou a hora de revelar o que exatamente este pequeno bloco de código esta fazendo, chego o momento de revelar e desvendar este desafio, a seguir apresento a resposta para o Script Challenge 13 e o trecho de código disponível para você utilizar em seus ambientes de trabalho ou estudos.

A resposta

Tanto no post de lançamento do desafio, bem como, neste post de apresenta a resposta para o mesmo, eu deixei algumas pequenas dicas para tentar ajudar a identificar a resposta, dentre as quais a relação do script com uma das datas comemorativas mais tradicionais dos Estados Unidos, neste caso o Halloween(conhecido tradicionalmente como dia das bruxas).

Mesmo assim você pode estar se perguntando, o que Script Challenge 13 tem haver com dia das bruxas, ué tudo haver, pois quando falamos de bruxas, temos também em mente a relação com magia, fantasias, medo, terror e propriamente a fantasmas, isso mesmos fantasmas, algo que também pode acontecer em nossas tabelas com o passar do tempo conforme vamos realizando as manipulações de dados, torna-se possível se deparar com a ocorrência de possíveis dados fantasmas.

Então a resposta para o Script Challenge 13 é justamente a possibilidade que o script apresenta em identificar uma possível ocorrência de dados fantasmas em nossas tabelas e bancos de dados.

Isso mesmo, esta é a resposta e o script original que apresenta esta funcionalidade apresenta abaixo:

— Script Challenge 13 – A resposta – Identificando a ocorrência de dados fantasmas —

SELECT db_name(database_id),
                object_name(object_id),
                ghost_record_count,
                version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
                                                                            OBJECT_ID(N’GhostTable’),
                                                                            NULL,
                                                                            NULL ,
                                                                            ‘DETAILED’)
GO

Que coisa de louco isso Galvão! Sim realmente parece ser coisa de outro mundo, mas podem acontecer tranquilamente a qualquer momento, o complicado é imaginar, que nossos bancos de dados, podem ser vítimas deste tipo de situação.

Mas isso não é o fim do mundo, muito ao contrário, para este cenário temos uma grande vantagem. Você pode estar se perguntando qual, a resposta é simples, não precisamos chamar os Casas Fantasmas, nós mesmos podemos resolver facilmente isso. Foi pensando justamente nas possibilidades da ocorrência deste tipo de situação, que além da compartilhar a resposta para este desafio, vou deixar também um cenário de simulação de como é possível ocorrência a existência de dados fantasmas, como também a possibilidade de excluir estes “dados”, a seguir:

— Simulando a ocorrência de dados fantasmas —

— Criando o Banco de Dados – GhostDB —
Create Database GhostDB
Go

— Acessando o Banco de Dados —
Use GhostDB
Go

— Criando a Tabela GhostTable —
Create Table GhostTable
(GhostRecord Int)
Go

— Criando um índice clusterizado —
Create Clustered Index Ind_GhostTable_GhostRecord On GhostTable(GhostRecord)
Go

— Inserindo Dados na Tabela GhostTable —
Insert Into GhostTable
Select 100
Go

— Obtendo informações sobre as estatísticas de alocação de dados —
Select object_id,
index_id,
index_depth,
index_level
From sys.dm_db_index_physical_stats(db_id(),
object_id(‘GhostTable’),
object_id(‘Ind_GhostTable_GhostRecord’),
null,
null)
Go

— Obtendo informações sobre o Índice IND_GhostTable_GhostRecord —
Select id, name, root, first
from sys.sysindexes
where id=565577053 — Aqui você vai colocar o ID identificado do índice apresentado na sua máquina —
Go

— Identificando a página de dados que contem os dados inseridos na GhostTable —
SELECT first_page,
(convert(varchar(2), (convert(int, substring(first_page, 6, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 5, 1)))) + ‘:’ + convert(varchar(11),
(convert(int, substring(first_page, 4, 1)) * power(2, 24)) +
(convert(int, substring(first_page, 3, 1)) * power(2, 16)) +
(convert(int, substring(first_page, 2, 1)) * power(2, 8)) +
(convert(int, substring(first_page, 1, 1))))) As Page
FROM SYS.SYSTEM_INTERNALS_ALLOCATION_UNITS
Where first_page = 0x180100000100 — Valor obtido no bloco de código anterior através da coluna root —
Go

— Habilitando a Trace Flag 3604 para apresentar informações sobre as páginas de dados —
DBCC TRACEON (3604)
GO

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,1)
Go

Após obter o resultado do DBCC Page procure pela coluna m_ghostRecCnt, neste momento ela deve esta apresentando o valor m_ghostRecCnt = 0.

— Excluíndo os registros em GhostTable —
Delete from GhostTable
Where GhostRecord=100
Go

— Consultando informações sobre as páginas de dados relacionadas o índice Ind_GhostTable_GhostRecord —
DBCC PAGE(GhostDB,1,280,3)
Go

Agora verifique novamente a coluna m_ghostRecCnt que neste momento deverá apresentar o valor igual á m_ghostRecCnt = 1, este é o indicador da ocorrência de um dado fantasma em nossa tabela.

— Confirmando a existência de um registro fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’), OBJECT_ID(N’GhostTable’), NULL, NULL , ‘DETAILED’)
GO

— Simulando a eliminação de dados fantasmas —
Alter Table GhostTable
Rebuild — Utilize este tipo de procedimento em uma tabela com poucos registros —
Go

— Confirmando a existência de um registros fantasmas —
SELECT db_name(database_id),
object_name(object_id),
ghost_record_count,
version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(N’GhostDB’),
OBJECT_ID(N’GhostTable’),
NULL,
NULL ,
‘DETAILED’)
GO

— Liberando espaço alocado anteriormente em disco pelos registros fantasmas —
Exec sp_clean_db_free_space @dbname=N’GhostDB’
Go

Meu deus, conseguimos, chegamos ao final, esta é a resposta para o Script Challenge 13, sinceramente falando achei que não iria conseguir compartilhar este conteúdo com vocês.

Espero que tenham gostado desta da volta desta sessão, como também, a nova maneira que pretendo apresentar os desafios e seus respostas.


Sua Participação

No post de lançamento deste desafio, contei com a participação através de uma enquete contendo algumas opções de respostas que poderiam estar relacionadas com o Script Challenge 13. A seguir apresento o resultado desta enquete:

Referências

Agradecimentos

Obrigado por sua visita, espero que o retorno desta sessão e o conteúdo aqui apresentado como um possível “desafio” possam ser úteis e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em junho de 2018 em mais um post da sessão Script Challenge.

Até a próxima…

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….

Material de Apoio – Fevereiro 2018


Boa tarde.

Tudo bem? E ai esta curtindo o carnaval?

Espero que sim, eu estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post dedicado exclusivamente ao meu blog. Fico feliz em encontrar você aqui fazendo mais uma visita ao meu blog, neste feriado, espero que tenha gostado do conteúdo aqui encontrado.

O post de hoje

 

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o primeiro do ano de 2018 e de número 155 no total desta sessão.

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

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

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

  • Armazenamento de arquivos;
  • Cláusula Values;
  • Comando Select;
    Comando Top;
  • Endereço de e-mails;
  • Excel;
  • Grant All Permissions;
  • Impactos na Ordenação de dados;
  • Índices;
  • Ordenação de Colunas;
  • Passwords;
  • Performance;
  • Random Character;
  • Tabelas;
  • User Defined Function; e
  • Validação de dados.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Material de Apoio

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

1 – Material de Apoio – Fevereiro 2018 – Realizando a validação de endereços de e-mail.sql

2 – Material de Apoio – Fevereiro 2018 – Impacto na Ordenação de Colunas em Índice – Comandos DML.sql

3 – Material de Apoio – Fevereiro 2018 – Generating A Password in SQL Server with T-SQL from Random Characters.sql

4 – Material de Apoio – Fevereiro 2018 – Simple SQL Server Function to Generate Random 8 Character Password.sql

5 – Material de Apoio – Fevereiro 2018 – Comando Select em conjunto com comando Top e cláusula Values.sql

6 – Material de Apoio – Fevereiro 2018 – Atribuindo Grant All para todas as tabelas.sql

7 – Material de Apoio – Fevereiro 2018 – Como armazenar arquivos do Excel diretamente no SQL Server.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

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

Links

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

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

https://pedrogalvaojunior.wordpress.com/2017/05/09/material-de-apoio-maio-2017/

Agradecimento

Quero agradecer imensamente a sua visita, ainda mais hoje neste feriadão prolongado de carnaval.

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

Até a próxima, 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.

Dica do Mês – Microsoft SQL Server 2017 – Relação de Novas colunas internas


Olá pessoal, bom dia.

Tudo bem? Preparado para um mês que esta se iniciando hoje? Se você me perguntar eu ainda não sei te responder pois acordei a pouco(kkkkkk).

Estou iniciando mais um post da sessão Dica do Mês, hoje algo um pouco diferente, não vou apresentar linhas de código, funcionalidades novas ou algo que conheci, muito menos ferramentas. Caso você venha a me perguntar. “Mas Galvão o que exatamente você esta planejando para hoje?”

A minha resposta é simples, vou compartilhar o que descobri de novo no Microsoft SQL Server 2017 que poderá lhe ajudar em momentos de análise de seus dados, bancos de dados e até mesmo do próprio servidor SQL Server. Parece ser algo de outro mundo mas não é, muito pelo contrário, algo que você facilmente poderá encontrar sem muito esforço.

Mas antes de falar do post, vamos destacar um pouco sobre o Microsoft SQL Server 2017. Acredito que você deva saber que no último mês de outubro, a Microsoft realizou mais um lançamento de uma nova versão do Microsoft SQL Server, estou me referindo a versão 2017. Por acaso você estão utilizando esta nova versão? Caso ainda não tenha feito, aproveite e faça agora mesmo acessando o link: https://www.microsoft.com/en-us/sql-server/sql-server-2017.

Se você, assim como eu realizou o download no mesmo dia do lançamento, ou seja, dia 02/10, pode ter um certo tempo para notar que a cada nova versão, o produto esta evoluindo, tanto no seu processo de instalação que realmente é fantástico e muito prático, como também, na quantidade de recursos, funcionalidades e componentes internos apresentados a partir desta da versão 2017.

Foi pensando neste cenário que o post da sessão Dica do Mês foi concebido, apresentar um pouco das novidades internas que o Microsoft SQL Server 2017 possuí em comparação com seus antecessores.

Sendo assim, seja bem vindo ao post – Dica do Mês – Microsoft SQL Server 2017 – Relação de Novas colunas internas.

Você vai descobrir o conjunto de novos dados técnicos catalogados pelo SQL Server 2017 seremos capazes de obter em real-time……


Introdução

Afim de mostrar o que de novo foi adicionado a partir da versão 2017, decidi identificar o que internamente existe de diferente que não tínhamos na versão 2016 ou anteriores, desta forma, escolhi fazer um comparativo entre a versão 2017 e a versão 2016 SP1, lançado no último mês de abril.

Foi idealizando este cenário, que fiz a simples escolha de utilizar uma velha conhecida system view denominada sys.all_columns presente a partir do Microsoft SQL Server desde a versão 2005. Tenho quase a certeza que você conhece esta system view! Ainda não, poxa vida, isso não é um pecado, mas é algo que talvez possa lhe ser útil.

Basicamente a sys.all_columns, como boa parte das visões de sistemas, apresentam um conjunto de dados técnicos catalogados pelo Microsoft SQL Server afim de ajudar internamente o funcionamento lógico e relacional do produto, mas também, como um guia para nós profissionais de bancos de dados, quanto temos a necessidade de analisar ou identificar dados técnicos, objetos, recursos ou funcionalidades oriundas do SQL Server.

Através desta DMV, temos a capacidade de obter a relação completa de colunas que formam nossos objetos e também os próprios objetos criados pelo SQL Server no processo da instalação ou durante nossas atividades, o que me permitiu identificar os novas colunas internas adicionadas na versão 2017 categorizadas da seguinte maneira:

  • Internal Tables;
  • InLine Table-Valued Function;
  • System Tables;
  • System Views; e
  • User Tables.

A seguir apresento a relação completa das novas colunas internadas adicionadas a partir do Microsoft SQL Server 2017:

Internal Tables

  • _trusted_assemblies.create_date
  • _trusted_assemblies.created_by
  • _trusted_assemblies.description
  • _trusted_assemblies.hash
  • plan_persist_query.query_flags
  • plan_persist_query_hints.batch_sql_handle
  • plan_persist_query_hints.comment
  • plan_persist_query_hints.context_settings_id
  • plan_persist_query_hints.last_query_hint_failure_reason
  • plan_persist_query_hints.object_id
  • plan_persist_query_hints.query_hash
  • plan_persist_query_hints.query_hint_failure_count
  • plan_persist_query_hints.query_hint_id
  • plan_persist_query_hints.query_hints_flags
  • plan_persist_query_hints.query_hints
  • plan_persist_query_hints.query_id
  • plan_persist_query_hints.query_param_type
  • plan_persist_query_hints.statement_sql_handle
  • plan_persist_query_template_parameterization.comment
  • plan_persist_query_template_parameterization.last_parameterization_failure_reason
  • plan_persist_query_template_parameterization.parameterization_failure_count
  • plan_persist_query_template_parameterization.query_param_type
  • plan_persist_query_template_parameterization.query_template_flags
  • plan_persist_query_template_parameterization.query_template_hash
  • plan_persist_query_template_parameterization.query_template_id
  • plan_persist_query_template_parameterization.query_template
  • plan_persist_query_text.query_template_hash
  • plan_persist_wait_stats.count_executions
  • plan_persist_wait_stats.execution_type
  • plan_persist_wait_stats.last_query_wait_time_ms
  • plan_persist_wait_stats.max_query_wait_time_ms
  • plan_persist_wait_stats.min_query_wait_time_ms
  • plan_persist_wait_stats.plan_id
  • plan_persist_wait_stats.runtime_stats_interval_id
  • plan_persist_wait_stats.sumsquare_query_wait_time_ms
  • plan_persist_wait_stats.total_query_wait_time_ms
  • plan_persist_wait_stats.wait_category
  • plan_persist_wait_stats.wait_stats_id

Inline table-valued functions

  • dm_db_log_info.database_id
  • dm_db_log_info.file_id
  • dm_db_log_info.vlf_active
  • dm_db_log_info.vlf_begin_offset
  • dm_db_log_info.vlf_create_lsn
  • dm_db_log_info.vlf_first_lsn
  • dm_db_log_info.vlf_parity
  • dm_db_log_info.vlf_sequence_number
  • dm_db_log_info.vlf_size_mb
  • dm_db_log_info.vlf_status
  • dm_db_log_stats.active_log_size_mb
  • dm_db_log_stats.active_vlf_count
  • dm_db_log_stats.current_vlf_sequence_number
  • dm_db_log_stats.current_vlf_size_mb
  • dm_db_log_stats.database_id
  • dm_db_log_stats.log_backup_lsn
  • dm_db_log_stats.log_backup_time
  • dm_db_log_stats.log_checkpoint_lsn
  • dm_db_log_stats.log_end_lsn
  • dm_db_log_stats.log_min_lsn
  • dm_db_log_stats.log_recovery_lsn
  • dm_db_log_stats.log_recovery_size_mb
  • dm_db_log_stats.log_since_last_checkpoint_mb
  • dm_db_log_stats.log_since_last_log_backup_mb
  • dm_db_log_stats.log_truncation_holdup_reason
  • dm_db_log_stats.recovery_model
  • dm_db_log_stats.recovery_vlf_count
  • dm_db_log_stats.total_log_size_mb
  • dm_db_log_stats.total_vlf_count
  • dm_os_enumerate_filesystem.creation_time
  • dm_os_enumerate_filesystem.file_or_directory_name
  • dm_os_enumerate_filesystem.full_filesystem_path
  • dm_os_enumerate_filesystem.has_integrity_stream
  • dm_os_enumerate_filesystem.is_directory
  • dm_os_enumerate_filesystem.is_hidden
  • dm_os_enumerate_filesystem.is_read_only
  • dm_os_enumerate_filesystem.is_sparse
  • dm_os_enumerate_filesystem.is_system
  • dm_os_enumerate_filesystem.is_temporary
  • dm_os_enumerate_filesystem.last_access_time
  • dm_os_enumerate_filesystem.last_write_time
  • dm_os_enumerate_filesystem.level
  • dm_os_enumerate_filesystem.parent_directory
  • dm_os_enumerate_filesystem.size_in_bytes
  • dm_os_file_exists.file_exists
  • dm_os_file_exists.file_is_a_directory
  • dm_os_file_exists.parent_directory_exists
  • dm_sql_referenced_entities.is_incomplete
  • fn_full_dblog.AllocUnitId
  • fn_full_dblog.AllocUnitName
  • fn_full_dblog.Article ID
  • fn_full_dblog.Begin Time
  • fn_full_dblog.Beginlog Status
  • fn_full_dblog.Bulk allocated extent count
  • fn_full_dblog.Bulk allocated extent ids
  • fn_full_dblog.Bulk allocation first IAM Page ID
  • fn_full_dblog.Bulk AllocUnitId
  • fn_full_dblog.Bulk RowsetId
  • fn_full_dblog.Byte Offset
  • fn_full_dblog.Bytes Freed
  • fn_full_dblog.Checkpoint Begin
  • fn_full_dblog.Checkpoint End
  • fn_full_dblog.CHKPT Begin DB Version
  • fn_full_dblog.CHKPT End DB Version
  • fn_full_dblog.CI Index Id
  • fn_full_dblog.CI Table Id
  • fn_full_dblog.Column Offset
  • fn_full_dblog.Command Type
  • fn_full_dblog.Command
  • fn_full_dblog.Compression Info
  • fn_full_dblog.Compression Log Type
  • fn_full_dblog.Context
  • fn_full_dblog.CopyVerionInfo Source Page Id
  • fn_full_dblog.CopyVerionInfo Source Page LSN
  • fn_full_dblog.CopyVerionInfo Source Slot Count
  • fn_full_dblog.CopyVerionInfo Source Slot Id
  • fn_full_dblog.Current LSN
  • fn_full_dblog.Database Name
  • fn_full_dblog.Description
  • fn_full_dblog.Dirty Pages
  • fn_full_dblog.End AGE
  • fn_full_dblog.End Time
  • fn_full_dblog.File ID
  • fn_full_dblog.File Status
  • fn_full_dblog.FileGroup ID
  • fn_full_dblog.Flag Bits
  • fn_full_dblog.Flags
  • fn_full_dblog.Format LSN
  • fn_full_dblog.InvalidateCache Id
  • fn_full_dblog.InvalidateCache keys
  • fn_full_dblog.Last Distributed Backup End LSN
  • fn_full_dblog.Last Distributed End LSN
  • fn_full_dblog.Lock Information
  • fn_full_dblog.Log Record Fixed Length
  • fn_full_dblog.Log Record Length
  • fn_full_dblog.Log Record
  • fn_full_dblog.Log Reserve
  • fn_full_dblog.LogBlockGeneration
  • fn_full_dblog.Logical Name
  • fn_full_dblog.LSN before writes
  • fn_full_dblog.Mark Name
  • fn_full_dblog.Master DBID
  • fn_full_dblog.Master XDESID
  • fn_full_dblog.Max XDESID
  • fn_full_dblog.Meta Status
  • fn_full_dblog.Minimum LSN
  • fn_full_dblog.Modify Size
  • fn_full_dblog.New Size
  • fn_full_dblog.New Split Page
  • fn_full_dblog.New Value
  • fn_full_dblog.NewAllocUnitId
  • fn_full_dblog.Next Replicated End LSN
  • fn_full_dblog.Num Elements
  • fn_full_dblog.Num Transactions
  • fn_full_dblog.Number of Locks
  • fn_full_dblog.Offset in Row
  • fn_full_dblog.Offset
  • fn_full_dblog.Old Size
  • fn_full_dblog.Old Value
  • fn_full_dblog.Oldest Active LSN
  • fn_full_dblog.Oldest Active Transaction ID
  • fn_full_dblog.Oldest Replicated Begin LSN
  • fn_full_dblog.Operation
  • fn_full_dblog.Page ID
  • fn_full_dblog.PageFormat FormatOption
  • fn_full_dblog.PageFormat PageFlags
  • fn_full_dblog.PageFormat PageLevel
  • fn_full_dblog.PageFormat PageStat
  • fn_full_dblog.PageFormat PageType
  • fn_full_dblog.Pages Written
  • fn_full_dblog.Parent Transaction ID
  • fn_full_dblog.Partial Status
  • fn_full_dblog.PartitionId
  • fn_full_dblog.Physical Name
  • fn_full_dblog.Prepare Time
  • fn_full_dblog.Preplog Begin LSN
  • fn_full_dblog.Previous LSN
  • fn_full_dblog.Previous Page LSN
  • fn_full_dblog.Previous Savepoint
  • fn_full_dblog.Publication ID
  • fn_full_dblog.Repl CSN
  • fn_full_dblog.Repl Epoch
  • fn_full_dblog.Repl Flags
  • fn_full_dblog.Repl Min Hold LSN
  • fn_full_dblog.Repl Msg
  • fn_full_dblog.Repl Partition ID
  • fn_full_dblog.Repl Source Commit Time
  • fn_full_dblog.Replicated Records
  • fn_full_dblog.Rowbits Bit Count
  • fn_full_dblog.Rowbits Bit Value
  • fn_full_dblog.Rowbits First Bit
  • fn_full_dblog.RowFlags
  • fn_full_dblog.RowLog Contents 0
  • fn_full_dblog.RowLog Contents 1
  • fn_full_dblog.RowLog Contents 2
  • fn_full_dblog.RowLog Contents 3
  • fn_full_dblog.RowLog Contents 4
  • fn_full_dblog.RowLog Contents 5
  • fn_full_dblog.Rows Deleted
  • fn_full_dblog.RowsetId
  • fn_full_dblog.Savepoint Name
  • fn_full_dblog.Server Name
  • fn_full_dblog.Server UID
  • fn_full_dblog.Slot ID
  • fn_full_dblog.SPID
  • fn_full_dblog.Tag Bits
  • fn_full_dblog.Text Size
  • fn_full_dblog.TextPtr
  • fn_full_dblog.Transaction Begin
  • fn_full_dblog.Transaction ID
  • fn_full_dblog.Transaction Name
  • fn_full_dblog.Transaction SID
  • fn_full_dblog.Virtual Clock
  • fn_full_dblog.VLFs added
  • fn_full_dblog.Xact ID
  • fn_full_dblog.Xact Node ID
  • fn_full_dblog.Xact Node Local ID
  • fn_full_dblog.Xact Type
  • fn_get_audit_file.affected_rows
  • fn_get_audit_file.application_name
  • fn_get_audit_file.client_ip
  • fn_get_audit_file.duration_milliseconds
  • fn_get_audit_file.response_rows
  • fn_xe_file_target_read_file.timestamp_utc

System Tables

  • syscscolsegments.container_id
  • syscsdictionaries.container_id

User Tables

  • MSreplication_options.install_failures
  • MSreplication_options.major_version
  • MSreplication_options.minor_version
  • MSreplication_options.optname
  • MSreplication_options.revision
  • MSreplication_options.value
  • spt_monitor.connections
  • spt_monitor.cpu_busy
  • spt_monitor.idle
  • spt_monitor.io_busy
  • spt_monitor.lastrun
  • spt_monitor.pack_errors
  • spt_monitor.pack_received
  • spt_monitor.pack_sent
  • spt_monitor.total_errors
  • spt_monitor.total_read
  • spt_monitor.total_write

Views

  • all_columns.graph_type_desc
  • all_columns.graph_type
  • availability_groups.cluster_type_desc
  • availability_groups.cluster_type
  • availability_groups.required_synchronized_secondaries_to_commit
  • availability_groups.sequence_number
  • columns.graph_type_desc
  • columns.graph_type
  • computed_columns.graph_type_desc
  • computed_columns.graph_type
  • database_automatic_tuning_mode.actual_state_desc
  • database_automatic_tuning_mode.actual_state
  • database_automatic_tuning_mode.desired_state_desc
  • database_automatic_tuning_mode.desired_state
  • database_automatic_tuning_options.actual_state_desc
  • database_automatic_tuning_options.actual_state
  • database_automatic_tuning_options.desired_state_desc
  • database_automatic_tuning_options.desired_state
  • database_automatic_tuning_options.name
  • database_automatic_tuning_options.reason_desc
  • database_automatic_tuning_options.reason
  • database_credentials.principal_id
  • database_files.is_persistent_log_buffer
  • database_query_store_options.wait_stats_capture_mode_desc
  • database_query_store_options.wait_stats_capture_mode
  • database_scoped_configurations.is_value_default
  • database_scoped_credentials.principal_id
  • databases.is_temporal_history_retention_enabled
  • dm_db_column_store_row_group_physical_stats.closed_time
  • dm_db_column_store_row_group_physical_stats.created_time
  • dm_db_column_store_row_group_physical_stats.deleted_rows
  • dm_db_column_store_row_group_physical_stats.delta_store_hobt_id
  • dm_db_column_store_row_group_physical_stats.generation
  • dm_db_column_store_row_group_physical_stats.has_vertipaq_optimization
  • dm_db_column_store_row_group_physical_stats.index_id
  • dm_db_column_store_row_group_physical_stats.object_id
  • dm_db_column_store_row_group_physical_stats.partition_number
  • dm_db_column_store_row_group_physical_stats.row_group_id
  • dm_db_column_store_row_group_physical_stats.size_in_bytes
  • dm_db_column_store_row_group_physical_stats.state_desc
  • dm_db_column_store_row_group_physical_stats.state
  • dm_db_column_store_row_group_physical_stats.total_rows
  • dm_db_column_store_row_group_physical_stats.transition_to_compressed_state_desc
  • dm_db_column_store_row_group_physical_stats.transition_to_compressed_state
  • dm_db_column_store_row_group_physical_stats.trim_reason_desc
  • dm_db_column_store_row_group_physical_stats.trim_reason
  • dm_db_file_space_usage.modified_extent_page_count
  • dm_db_mirroring_connections.address
  • dm_db_tuning_recommendations.details
  • dm_db_tuning_recommendations.execute_action_duration
  • dm_db_tuning_recommendations.execute_action_initiated_by
  • dm_db_tuning_recommendations.execute_action_initiated_time
  • dm_db_tuning_recommendations.execute_action_start_time
  • dm_db_tuning_recommendations.is_executable_action
  • dm_db_tuning_recommendations.is_revertable_action
  • dm_db_tuning_recommendations.last_refresh
  • dm_db_tuning_recommendations.name
  • dm_db_tuning_recommendations.reason
  • dm_db_tuning_recommendations.revert_action_duration
  • dm_db_tuning_recommendations.revert_action_initiated_by
  • dm_db_tuning_recommendations.revert_action_initiated_time
  • dm_db_tuning_recommendations.revert_action_start_time
  • dm_db_tuning_recommendations.score
  • dm_db_tuning_recommendations.state
  • dm_db_tuning_recommendations.type
  • dm_db_tuning_recommendations.valid_since
  • dm_db_xtp_checkpoint_internals.checkpoint_id
  • dm_db_xtp_checkpoint_internals.checkpoint_timestamp
  • dm_db_xtp_checkpoint_internals.is_synchronized
  • dm_db_xtp_checkpoint_internals.last_segment_lsn
  • dm_db_xtp_checkpoint_internals.recovery_lsn
  • dm_exec_query_stats.last_columnstore_segment_reads
  • dm_exec_query_stats.last_columnstore_segment_skips
  • dm_exec_query_stats.max_columnstore_segment_reads
  • dm_exec_query_stats.max_columnstore_segment_skips
  • dm_exec_query_stats.min_columnstore_segment_reads
  • dm_exec_query_stats.min_columnstore_segment_skips
  • dm_exec_query_stats.total_columnstore_segment_reads
  • dm_exec_query_stats.total_columnstore_segment_skips
  • dm_exec_requests.is_resumable
  • dm_os_enumerate_fixed_drives.drive_type_desc
  • dm_os_enumerate_fixed_drives.drive_type
  • dm_os_enumerate_fixed_drives.fixed_drive_path
  • dm_os_enumerate_fixed_drives.free_space_in_bytes
  • dm_os_host_info.host_distribution
  • dm_os_host_info.host_platform
  • dm_os_host_info.host_release
  • dm_os_host_info.host_service_pack_level
  • dm_os_host_info.host_sku
  • dm_os_host_info.os_language_version
  • dm_os_memory_objects.partition_type_desc
  • dm_os_nodes.cpu_count
  • dm_os_sys_info.cores_per_socket
  • dm_os_sys_info.numa_node_count
  • dm_os_sys_info.process_physical_affinity
  • dm_os_sys_info.socket_count
  • dm_os_worker_local_storage.gq_address
  • dm_tran_global_transactions.max_csn
  • dm_tran_global_transactions_enlistments.snapshot_timestamp
  • dm_tran_global_transactions_log.commit_timestamp
  • dm_tran_version_store_space_usage.database_id
  • dm_tran_version_store_space_usage.reserved_page_count
  • dm_tran_version_store_space_usage.reserved_space_kb
  • dm_xe_session_targets.bytes_written
  • dm_xe_sessions.buffer_full_count
  • dm_xe_sessions.buffer_processed_count
  • dm_xe_sessions.total_bytes_generated
  • external_libraries.external_library_id
  • external_libraries.language
  • external_libraries.name
  • external_libraries.principal_id
  • external_libraries.scope_desc
  • external_libraries.scope
  • external_library_files.content
  • external_library_files.external_library_id
  • external_library_files.platform_desc
  • external_library_files.platform
  • hash_indexes.auto_created
  • hash_indexes.is_ignored_in_optimization
  • identity_columns.graph_type_desc
  • identity_columns.graph_type
  • index_resumable_operations.index_id
  • index_resumable_operations.last_max_dop_used
  • index_resumable_operations.last_pause_time
  • index_resumable_operations.name
  • index_resumable_operations.object_id
  • index_resumable_operations.page_count
  • index_resumable_operations.partition_number
  • index_resumable_operations.percent_complete
  • index_resumable_operations.sql_text
  • index_resumable_operations.start_time
  • index_resumable_operations.state_desc
  • index_resumable_operations.state
  • index_resumable_operations.total_execution_time
  • indexes.auto_created
  • indexes.is_ignored_in_optimization
  • indexes.suppress_dup_key_messages
  • key_constraints.is_enforced
  • masked_columns.graph_type_desc
  • masked_columns.graph_type
  • master_files.is_persistent_log_buffer
  • query_store_plan.plan_forcing_type_desc
  • query_store_plan.plan_forcing_type
  • query_store_runtime_stats.avg_log_bytes_used
  • query_store_runtime_stats.avg_num_physical_io_reads
  • query_store_runtime_stats.avg_tempdb_space_used
  • query_store_runtime_stats.last_log_bytes_used
  • query_store_runtime_stats.last_num_physical_io_reads
  • query_store_runtime_stats.last_tempdb_space_used
  • query_store_runtime_stats.max_log_bytes_used
  • query_store_runtime_stats.max_num_physical_io_reads
  • query_store_runtime_stats.max_tempdb_space_used
  • query_store_runtime_stats.min_log_bytes_used
  • query_store_runtime_stats.min_num_physical_io_reads
  • query_store_runtime_stats.min_tempdb_space_used
  • query_store_runtime_stats.stdev_log_bytes_used
  • query_store_runtime_stats.stdev_num_physical_io_reads
  • query_store_runtime_stats.stdev_tempdb_space_used
  • query_store_wait_stats.avg_query_wait_time_ms
  • query_store_wait_stats.execution_type_desc
  • query_store_wait_stats.execution_type
  • query_store_wait_stats.last_query_wait_time_ms
  • query_store_wait_stats.max_query_wait_time_ms
  • query_store_wait_stats.min_query_wait_time_ms
  • query_store_wait_stats.plan_id
  • query_store_wait_stats.runtime_stats_interval_id
  • query_store_wait_stats.stdev_query_wait_time_ms
  • query_store_wait_stats.total_query_wait_time_ms
  • query_store_wait_stats.wait_category_desc
  • query_store_wait_stats.wait_category
  • query_store_wait_stats.wait_stats_id
  • sequences.last_used_value
  • spatial_indexes.auto_created
  • spatial_indexes.is_ignored_in_optimization
  • spt_values.high
  • spt_values.low
  • spt_values.name
  • spt_values.number
  • spt_values.status
  • spt_values.type
  • syscscontainers.blob_container_id
  • syscscontainers.blob_container_type
  • syscscontainers.blob_container_url
  • system_columns.graph_type_desc
  • system_columns.graph_type
  • tables.history_retention_period_unit_desc
  • tables.history_retention_period_unit
  • tables.history_retention_period
  • tables.is_edge
  • tables.is_node
  • trusted_assemblies.create_date
  • trusted_assemblies.created_by
  • trusted_assemblies.description
  • trusted_assemblies.hash
  • xml_indexes.auto_created
  • xml_indexes.is_ignored_in_optimization

Importante: Vale ressaltar que estas novas colunas estão compondo o conjunto atual de recursos internos já existentes no Microsoft SQL Server, não estou me referindo a novas Internal Tables, DMVs ou Views adicionadas ao produto na versão 2017.

Com isso chegamos ao final de mais um post da sessão Dica do Mês, espero que você tenha gostado, então do meu tradicional encerramento, quero destacar em algumas destas novas colunas serão destaco em posts futuros do meu blog.


Referências

https://technet.microsoft.com/pt-br/library/ms178596(v=sql.110).aspx

https://technet.microsoft.com/pt-br/library/ms188021(v=sql.110).aspx

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-all-columns-transact-sql

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2017/08/26/dica-do-mes-simulando-a-insercao-de-uma-massa-de-dados-aleatoria/

https://pedrogalvaojunior.wordpress.com/2017/05/23/conhecendo-e-aplicando-o-uso-de-atualizacao-de-estatisticas-incrementais/

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

Conclusão

A cada versão do Microsoft SQL Server evolui, a gama de dados técnicos catalogados que nos permitem analisar e ajudar em possíveis cenários de administração ou tomadas de decisão cresce de forma exponencial, com isso, temos um conjunto inimaginável de possibilidades e alternativas que nos possibilitam superior nossos desafios.

Esse é o Microsoft SQL Server ainda mais poderoso na versão 2017.

 

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos no próximo post a ser publicado no mês de janeiro de 2018.

Vai SQL Server, Vai SQL Server…..

Abraços.

Script Challenge – 2017 – O retorno….


Olá bom dia, comunidade e amantes de bancos de dados.

Tudo bem? E ai esta chovendo neste momento na sua cidade? Aqui em São Roque estávamos desde o último dia 12/10/2017 sem chuva, mas nesta última madrugado do dia 25/10 para o dia 26/10 ela retornou com grande força.

Introdução

Você pode estar se perguntando o que o retorno da chuva tem haver com o post desta sessão, na minha opinião tem tudo haver, estou justamente hoje aproveitando a madrugada de trabalho para retornar ao meu blog um das sessões que durante 2 anos me ajudou em muito a manter o número de visitantes em alta.

Se você não conhecia, vai ter a possibilidade de a partir de agora a cada 4 meses por desfrutar da sessão Script Challenge(Script Desafio ou Desafio do Script), pois bem, a melhor forma de traduzir eu deixo para você.

Um pouco de História

Esta é uma sessão criado no final do ano de 2010 e mantida mensalmente até meados de 2012, desde então não me dediquei mais a mesma devido principalmente em não conseguir identificar possíveis scripts dentro da minha biblioteca que poderiam ser apresentados como um scripts considerados como desafio para os profissionais da área de banco de dados.

Mas acredito que nos últimos anos com o crescimento do Microsoft SQL Server, seu nível de evolução, amadurecimento e recursos adicionais, como também, meu nível de experiência profissional e acadêmica também evolui muito, sinto-me preparado para retornar com esta sessão e poder de forma bastante técnica, didática e conceitual poder lançar um “desafio” e responder o mesmo com muito segurança.

Esta é uma sessão que foi criada, pensando em apresentar pequenos blocos de códigos oriundos da linguagem Transact-SQL que possam apresentar um nível de dificuldade e conhecimento técnico um pouco mais elevado, exigindo assim uma análise mais profunda e até mesmo a criação um pequeno cenário de teste para seu entendimento. Vamos então conhecer o bloco de código selecionado para ser o Script Challenge deste post.


Script Challenge – 13

O bloco de código Transact-SQL selecionado para fazer parte do Script Challenge que representa o retorno desta sessão, esta totalmente relacionado com uma tradicional comemoração norte americana realizada no mês de Outubro.

A seguir apresento o bloco de código:


Figura 1 – Short Script 13.

Muito bem, nosso Script Challenge esta apresentado, mas não tudo será como antes.

Gostaria então de destacar duas mudanças iniciais implementadas na sessão Script Challenge a partir deste post:

  1. Como maneira ou forma de dificultar ainda mais o nível de complexidade do código, o bloco de código que representa o Script Challenge selecionado para compor o post será  apresentado no formato de imagem ou figura; e
  2. Algumas partes das linhas de código, trechos, ou partes de código serão omitidas, justamente como forma de tornar este código ainda mais desafiador.

E ai preparado para o desafio? Tenho a certeza que sim!


Sua participação

Legal, agora quero conter com a sua participação, me ajudando a responder este post, outra grande novidade que estou adicionando aos post da sessão Script Challenge será uma pequena enquete com possíveis respostas para identificar qual é a correta relacionada com o desafio apresentado através do bloco de código selecionado para este post, desta forma, apresento a seguir a Enquete – Script Challenge – 13 e suas alternativas de resposta:

Seguindo em frente, já conhecemos o Script Challenge selecionado, sua enquete e relação de opções de resposta, fica faltando somente a sua participação, algo que eu tenho a certeza que vai acontecer rapidamente.

Durante quanto tempo posso participar…

Outra grande mudança na sessão Script Challenge é seu tempo de publicação “validade”, anteriormente os posts desta sessão eram publicados mensalmente ou bimestralmente, agora serão publicados em um período mais longo, ou seja, a cada 4 meses estarei compartilhando com vocês novos posts dedicados exclusivamente para esta sessão.

Desta forma, você terá mais tempo para participar e me ajudar e encontrar a resposta correta para este desafio, enviando suas possíveis sugestões, críticas e até mesmo alternativas de resposta para minha enquete.


Agradecimentos

Obrigado por sua visita, espero que o retorno desta sessão e o conteúdo aqui apresentado como um possível “desafio” possam ser úteis e ao mesmo tempo prover conhecimento, aprendizado ou mostrar recursos e problemas existentes no Microsoft SQL Server que as vezes parecem não ter uma resposta.

Um forte abraço nos encontramos em breve nas demais sessões e especialmente em fevereiro de 2018 em mais um post da sessão Script Challenge.

Sucesso e até mais.

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.