Short Scripts – Maio 2020

Neste segundo post de 2020 da sessão Short Scripts, você vai encontrar 7 exemplos diferentes de como podemos utilizar as Extended Stored Procedures (XPs), algumas não documentadas no Microsoft SQL Server.


Olá boa tarde, como vai?

Seja bem-vindo, em mais um post da sessão Short Scripts, o segundo post desta sessão em 2020 e o de número 41 em seu total.

Espero que você e seus familiares se encontrem bem neste momento terrível que estamos vivendo em todo Mundo.

Mantendo a tradição estou retornando com mais um conjunto dos “curtos ou 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, vou pagar a minha mais uma vez hoje, feita no encerramento do post anterior publicado no mês de fevereira em 2010, se por acaso você ainda não acessou o mesmo, calma, fique tranquilo, tudo vai dar certo, no encerramento deste post você terá a sua disposição a relação das últimas cinco publicações relacionadas aos Short Scripts.

Vamos caminhar mais um pouco então……..

O post de hoje

Para este post, não vou exatamente compartilhar um conjunto variado de scripts que foram catalogados nos últimos meses, ao contrário, vou disponibilizar um conteúdo que foi elaborado hoje, dedicado de forma única e exclusiva a demonstrar como podemos utilizar as chamadas Extended Stored Procedures no Microsoft SQL Server.

Em conjunto com o uso de forma de execução da Extended Stored Procedure, os exemplos de código aqui compartilhados se relacionam com os seguintes assuntos:

● Cláusula From;
● Cláusula Where;
● Comando Alter Table;
● Comando Begin;
● Comando Begin;
● Comando Create Procedure;
● Comando Create Table;
● Comando Declare;
● Comando Exec;
● Comando Execute;
● Comando Go;
● Comando Insert;
● Comando Reconfigure;
● Comando Select;
● Comando Set;
● Comando Union;
● Comando Update;
● Comando Use;
● DataType Char();
● DataType Datetime;
● DataType Decimal();
● DataType Varchar();
● Diretiva Set;
● Extended Stored Procedure XP_CMDShell;
● Extended Stored Procedure XP_ExecResult;
● Extended Stored Procedure XP_FixedDrives;
● Extended Stored Procedure XP_ReadErrorLog;
● Extended XP_Delete_File;
● Função Convert();
● Função DataLength();
● Função DateAdd();
● Função GetDate();
● System Stored Procedure SP_Configure;
● System Stored Procedure SP_Help; e
● Variáveis.
 

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Maio 2020. 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

— Exemplo 1 — Identificando a porta Padrão do Microsoft SQL Server através da XP_ReadErrorLog —
Use Master
Go

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

— Exemplo 2 — Habilitando a Extended Stored Procedure XP_CMDShell -utilizand a Sigla DSH —

— Ativando a exibição das configurações avançadas —
EXECUTE sys.sp_configure @configname = ‘show advanced options’,@configvalue = 1
Go

— Aplicando a alteração —
Reconfigure
Go

— Ativando para uso da XP_CMDShell —
EXECUTE sys.sp_configure @configname = ‘dsh’,@configvalue = 1
Go

— Aplicando a alteração —
Reconfigure
Go

Observação: Como você deve ter percebido, para se fazer uso da Extended Stored Procedure XP_CMDShell, que por padrão vem desabilitada nas configurações internas do SQL Server, temos a necessidade de ativar a exibição das configurações avançadas da nossa instância ou servidor, utilizando a System Stored Procedure SP_Configure, e posteriormente ativar da XP_ CMDShell.

Caso queira saber mais sobre este recurso e demais configurações existentes, acesse:

— Exemplo 3 — Obtendo a quantidade de Megabytes livres em disco através da XP_FixedDrives
Exec xp_fixeddrives
Go

— Exemplo 4 — Excluíndo arquivos específicos através da XP_DeleteFile
Declare @DateString CHAR(10)
Set @DateString = CONVERT(CHAR(10), DATEADD(d, -2, GETDATE()), 103)

Execute master.dbo.xp_delete_file 0, N’E:\MSSQL2012-Backup\BaanModelEA’,N’bak’, @DateString, 1
Go

— Exemplo 5 — Importando o conteúdo de arquivos .csv para dentro de uma tabela através da XP_CMDShell
CREATE TABLE Test
(Empid varchar(6),
Empname varchar(100),
BirthDate datetime,
Salary decimal(12,2),
Nome Varchar(2000))
Go

INSERT INTO Test
SELECT ‘EMP001′,’Suresh’,’19910619′,3000,’Pedro Galvão’
UNION ALL
SELECT ‘EMP002′,’Ramesh’,’19710103′,20000,’Teste’
UNION ALL
SELECT ‘EMP003′,’Nilesh’,’19800722′,4760,”
UNION ALL
SELECT ‘EMP004′,’Kumar’,’19680911′,42000,”
Go

Declare @Comando Varchar(500)
Set @Comando=’bcp Master..Test out c:\teste.csv -SServer -c -t, /CACP -T’

Exec xp_cmdshell @Comando
Go

— Exemplo 6 — Armazenando o Endereço de IP em uma tabela, através da execução de uma Stored Procedure, com base no resultado da XP_CMDShell —

— Exibindo as configurações avançadas —
EXEC sp_configure ‘show advanced options’, 1
Go

— Aplicando a alteração —
RECONFIGURE
Go

— Ativando o uso da XP_CMDShell —
EXEC sp_configure ‘xp_cmdshell’, 1
Go

— Aplicando a alteração —
RECONFIGURE
Go


Create Procedure P_RetornarIP @IP VarChar(25) Output
As
Begin

Set NoCount On

Declare @Auditoria2 Table
(Codigo Int Identity(1,1),
IP VarChar(2000))

Set RowCount 8

Insert Into @Auditoria2
exec master.dbo.xp_cmdshell ‘ipconfig’

Select @IP=(Select Replace(Substring(IP,CharIndex(‘:’,IP,1),25),’:’,”) from @Auditoria2
Where Codigo = 8)

End

Create Table Auditoria
(Codigo Int Identity(1,1),
HostName VarChar(20) Null)

Declare @IP VarChar(25)

Exec P_RetornarIP @IP OutPut
Insert Into Auditoria Values (@IP)

Select * from Auditoria
Go

— Exemplo 7 — Executando uma Instrução Transact-SQL através da XP_ExecResultSets —
Exec Master..XP_ExecResultSet ‘Select ”SP_help ””produtos”””’, ‘BancoDeDados’
Go

Exec Master..XP_ExecResultSet ‘Select ”Select * From produtos”’, ‘BancoDeDados’
Go


Muito bem, missão mais que cumprida!

Uma nova relação de short scripts acaba de ser compartilhada, mesmo sendo denominados shorts entre aspas “curtos ou 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/2020/02/05/8295/

https://pedrogalvaojunior.wordpress.com/2019/11/15/short-scripts-novembro-2019/

https://pedrogalvaojunior.wordpress.com/2019/09/11/short-scripts-setembro-2019/

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

https://pedrogalvaojunior.wordpress.com/2019/03/15/short-scripts-marco-2019/

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 agosto em 2020.

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

Até mais.

Material de Apoio – Abril 2020

Material de Apoio número 167….


Bom dia, bom dia, bom dia…

Seja bem-vindo a mais um post da sessão Material de Apoio.

Como tem passou estes dias desde o nosso último post? Tenho a noção que não esta sendo fácil aguentar esta quarentena, não é mesmo! Todavia é o melhor que podemos fazer, ficar em casa nos cuidando e principalmente evitando que esta doença possa se espalhar ainda mais.

Pensando neste atual cenário, estou mais uma vez aqui no meu blog, neste sábado dia 18/04, o qual representa o meu 34º (Trigéssimo quarto) dia de quarentena, com o objetivo de empenhar-se para encontrar uma maneira de lhe auxiliar a passar o tempo, em adicional propor uma forma de renovar o seu conhecer.

Introdução

Você vai encontrar em cada post desta sessão uma imagem que representa não exatamente o conteúdo do post, mas sim a época, período ou estação do ano em que o mesmo foi publicado, uma forma diferente e até mesmo divertida que encontrei para tentar te ajudar a se lembrar do que você acessou em mais esta visita.

Essa não é uma sugestão minha, tenho sempre recebido e-mails de visitantes que estavam gostando do conteúdo do post, mas as vezes não conseguiam se lembrar o que havia sido acessado, foi ai que pensei em adicionar uma imagem, figura ou símbolo que traga uma certa lembrança mais rápido e fácil ao visitante.

Para o post de hoje, nada mais coerente do que adicionar uma que representa a quarentena que estamos vivendo, a qual seria o símbolo que identificada o risco de morte,  denominada Figura 1 apresentada a seguir:

Image result for quarentenaFigura 1 – Simbolo que representa conteúdo compartilhado no post 167.

O post de hoje

Representa o primeiro post desta sessão no ano de 2020 e de número 167 no total da mesma.

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.

Todos os arquivos compartilhados neste post, 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, foram concebidos com base nas atividades realizadas mediantes aos meus estudos e consultorias aplicadas ao Microsoft SQL Server em seus mais diversos recursos e funcionalidades.

Destaco que os arquivos a seguir, não necessariamente representam a melhor solução, na verdade os respectivos códigos e scripts satisfazem um possível entendimento da minha parte de acordo com o cenário ao qual o mesmo foi analisado e implementado.

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

° Anexar arquivos;
° Banco de Dados;
° Cálculo de Juros;
° Cláusula From;
° Cláusula Order By;
° Cláusula Where;
° Comando Begin;
° Comando Begin Try;
° Comando Close;
° Comando Create Procedure;
° Comando Deallocate;
° Comando Declare;
° Comando Delete;
° Comando End Try;
° Comando Exec;
° Comando Fetch Next;
° Comando Insert;
° Comando Print;
° Comando Return;
° Comando Select;
° Comando Top;
° Comando Update;
° Comando Use;
° Cursor;
° Dias de Atraso;
° Diretiva Set NoCount;
° Extended Stored Procedure;
° Information_Schema;
° INFORMATION_SCHEMA.TABLES;
° Junções Inner Join;
° Nome de Colunas;
° Nome de Tabelas;
° Operador Lógico Condicional And;
° Operador Lógico Condicional IF;
° Operador Lógico Condicional Or;
° Operador Lógico Condicional While;
° Parcelas;
° SP_ExecuteSQL;
° System Function Concat();
° System Function DatabasePropertyEx();
° System Function IsNull;
° System Function Lower();
° System Function RTrim();
° System Stored Procedure;
° System Stored Procedure SP_Attach_Single_File_DB;
° Tipo de Dados NVarchar();
° Variáveis;
° Visão de Sistema Sys.Databases; e
° XP_CMDShell.

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 .docx 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 ou abrir em algum editor de texto similar ao Notepad.

Material de Apoio

1 – Material de Apoio – Abril 2020 – Simulando a Somatória entre Data e Hora entre Compatibility_Levels diferentes.sql

2 – Material de Apoio – Abril 2020 – Identificando o horário de inicialização do Sistema Operacional.sql

3 – Material de Apoio – Abril 2020 – Simulando movimentação de estoque de acordo com o código e indicador de movimentação.sql

4 – Material de Apoio – Abril 2020 – Trazendo o nome da tabela armazenada na Information_Schema + Colunas.sql

5 – Material de Apoio – Abril 2020 – Anexando diversos arquivos de bancos de dados via linha de comando.sql

6 – Material de Apoio – Abril 2020 – Calculando o Juros Percentual com base na quantidade de dias de atraso.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/2019/12/20/material-de-apoio-dezembro-2019/

https://pedrogalvaojunior.wordpress.com/2019/10/16/material-de-apoio-outubro-2019/

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

https://pedrogalvaojunior.wordpress.com/2019/06/25/material-de-apoio-junho-2019/


Feedback

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação, preenchendo o formulário abaixo:


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 de 2020.

Fique em casa, neste momento é o melhor que podemos fazer, continue aproveitando cada momento da sua vida, desfrutando com muita sabedoria, galeria e esperança.

Até mais.

Material de Apoio – Dezembro 2019

Material de Apoio número 166….


Muito boa tarde…..

Seja bem-vindo a mais um post da sessão Material de Apoio.

Como tem passou estes dias desde o nosso último post? Eu acredito que esteja tudo bem, pois não faz tanto tempo assim desde o nosso último encontro aqui no meu blog.

Introdução

Você vai encontrar em cada post desta sessão uma imagem que representa não exatamente o conteúdo do post, mas sim a época, período ou estação do ano em que o mesmo foi publicado, uma forma diferente e até mesmo divertida que encontrei para tentar te ajudar a se lembrar do que você acessou em mais esta visita.

Essa não é uma sugestão minha, tenho sempre recebido e-mails de visitantes que estavam gostando do conteúdo do post, mas as vezes não conseguiam se lembrar o que havia sido acessado, foi ai que pensei em adicionar uma imagem, figura ou símbolo que traga uma certa lembrança mais rápido e fácil ao visitante.

Para o post de hoje, nada mais coerente que do adicionar a imagem de uma árvore como símbolo, a qual seria um Pinheiro, denominada Figura 1 apresentada a seguir:

Image result for árvore pinheiroFigura 1 – Simbolo que representa conteúdo compartilhado no post 166.

O post de hoje

Representa o que encerra esta sessão no ano de 2019 e de número 166 no total da mesma.

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.

Todos os arquivos compartilhados neste post, 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, foram concebidos com base nas atividades realizadas mediantes aos meus estudos e consultorias aplicadas ao Microsoft SQL Server em seus mais diversos recursos e funcionalidades.

Destaco que os arquivos a seguir, não necessariamente representam a melhor solução, na verdade os respectivos códigos e scripts satisfazem um possível entendimento da minha parte de acordo com o cenário ao qual o mesmo foi analisado e implementado.

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

◦ Backup Database;
◦ Comando Alter Database;
◦ Comando Begin Transaction;
◦ Comando Checkpoint;
◦ Comando Commit Transaction;
◦ Comando Create Clustered Index;
◦ Comando Create Table;
◦ Comando DBCC CheckDB;
◦ Comando Declare;
◦ Comando Insert;
◦ Comando RollBack Transaction;
◦ Comando Select;
◦ Comando ShutDown;
◦ Comando Update;
◦ Comando Use;
◦ Comando While;
◦ Corromper Páginas de Dados;
◦ Criar Banco de Dados;
◦ Database Recovery Model;
◦ Database Status Emergency;
◦ Database UserAcess;
◦ Database Version;
◦ DataType BigInt;
◦ DataType Varchar();
◦ Diretiva Set NoCount;
◦ Falha em Ambiente de Banco de Dados;
◦ Index Clustered;
◦ Restaurar Páginas de Dados;
◦ Restore Database;
◦ System Function DATABASEPROPERTYEX();
◦ System Stored Procedure SP_Attach_DB;
◦ System Stored Procedure SP_DBOption; e
◦ System Stored Procedure SP_Detach_DB.

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 .docx 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 ou abrir em algum editor de texto similar ao Notepad.

Material de Apoio

1 – Material de Apoio – Dezembro 2019 – Simulando como Corromper e Restaurar Páginas de Dados.sql

2 – Material de Apoio – Dezembro 2019 – Recuperação de Dados – Criando o Banco de Dados.sql

3 – Material de Apoio – Dezembro 2019 – Recuperação de Dados – Restauração de Backups e Recuperação do Banco de Dados.sql

4 – Material de Apoio – Dezembro 2019 – Recuperação de Dados – Simulando a Falha no Ambiente – Database – Status – Emergency.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/2019/10/16/material-de-apoio-outubro-2019/

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

https://pedrogalvaojunior.wordpress.com/2019/06/25/material-de-apoio-junho-2019/

https://pedrogalvaojunior.wordpress.com/2019/04/17/material-de-apoio-abril-2019/


Feedback

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a torná-lo ainda melhor no decorrer do tempo com a sua participação, preenchendo o formulário abaixo:


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 fevereiro de 2020.  Até lá, continue aproveitando cada momento da sua vida, desfrutando com muita sabedoria os momentos de galeria e também os desafios que são colocados ao seu redor.

Um forte abraço, Feliz Natal e Próspero Ano Novo.

Obrigado.

Short Scripts – Novembro 2019


Olá bom dia, como vai?

Seja bem-vindo, em mais um post da sessão Short Scripts, estamos no mês de novembro, chegamos ao final de 2019!

Você pode estar estranhando o horário que este post foi compartilhado, é realmente nestes últimos dias minha vida esta bastante corrida devido aos afazeres profissionais e acadêmicos, todavia, temos que conseguir se organizar para cumprir os objetivos e alcançar nossas metas.

Mesmo com este horário meio fora do comum, pois são exatamente 01:10, isso mesmo, uma da madrugada deste dia 15/11 feriado nacional, que o post de número 39 desta tradicional sessão começa a sair do forno.

Mantendo a tradição estou retornando com mais um conjunto dos “curtos ou 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, vou pagar a minha mais uma vez neste madrugada, feita no encerramento do post anterior publicado no mês de setembro, se por acaso você ainda não acessou o mesmo, calma, fique tranquilo, tudo vai dar certo, no encerramento deste post você terá a sua disposição a relação das últimas cinco publicações relacionadas aos Short Scripts.

Vamos caminhar mais um pouco então……..

Continue Lendo “Short Scripts – Novembro 2019”

Short Scripts – Setembro 2019


Olá bom dia, como vai?

Seja bem-vindo, em mais um post da sessão Short Scripts, estamos no mês de setembro, 2019 está começando a apontar para sua reta final, olha vou te dizer uma coisa, “como o tempo passa, não é mesmo (kkkkk)”.

Deixando o tempo de lado, caminhando para mais um dia de muito trabalho e estudos, que hora poder encontra-lo aqui meu dileto visitante, no post de número 38 desta tradicional sessão.

Mantendo a tradição estou retornando com mais um conjunto dos “curtos ou 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, vou pagar a minha hoje, feita no encerramento do post anterior publicado no mês de junho, se por acaso você ainda não acessou o mesmo, calma, fique tranquilo, tudo vai dar certo, no encerramento deste post você terá a sua disposição a relação das últimas cinco publicações relacionadas aos Short Scripts.

Vamos caminhar mais um pouco então……..

Continue Lendo “Short Scripts – Setembro 2019”

Short Scripts – Junho 2019


Olá boa tarde, tudo bem?

Junho de 2019 chegou, estamos quase na metade do ano, para muitos um período importante, pois as férias de meio de ano estão chegando.

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

Mantendo a tradição estou retornando com mais um conjunto de “curtos ou 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:

  • Cláusula Over();
  • Cláusula Where;
  • Comando Create Table;
  • Comando IF;
  • Comando Insert;
  • Comando Print;
  • Comando Select;
  • Comando Update;
  • Comando While;
  • Condições;
  • CTE;
  • DataType Char;
  • DataType DateTime;
  • DataType Int;
  • DataType SmalltInt;
  • DataType TinyInt;
  • DataType UniqueIdentifier;
  • DataType Varchar;
  • Declaração de Variáveis;
  • Diretiva Set Ansi_Warnings;
  • Diretiva Set ArithAbort; 
  • Diretiva Set; 
  • DMFsys.dm_db_index_physical_stats();
  • Erros em agregação de múltiplas colunas;
  • Fragmentação de dados;
  • Fragmentação de índices;
  • Função Cast;
  • Função Coalesce;
  • Função Convert();
  • Função IsNull;
  • Função NewID();
  • Função Object_Id();
  • Função Object_Name();
  • Funções de Agregação;
  • Índices;
  • Operadores;
  • Rebuild Index;
  • Taxas de Fragmentação; e
  • Visão de sistema sys.indexeses.

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Junho 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  – Contornando o erro Multiple columns are specified in an aggregated expression containing an outer reference —

Create Table Car
(N_state varchar(20),
City varchar (20),
Cars int)
Go
Insert Into Car
values(‘California’, ‘Los Angeles’,1000),
(‘Ohio’, ‘Columbus’, 300), (‘Texas’, ‘Austin’,400),
(‘Florida’, ‘Miami’,800), (‘Florida’, ‘Orlando’,200)
Go
— Estourando o erro, quantidade de colunas retornadas não atende a quantidade de colunas declaradas —
Select *,
(Select sum(case
when c1.N_state=c2.N_state Then cars
else 0 end)
from car c2) as bb
from car c1
Go
— Exemplo 1 – Utilizando CTE – Contornando o erro —
;with CTE
As
(
Select *, (Select sum(cars) from car) as bb
from car)
Select *, cars, bb, cars/bb as cc from cte
Go
— Exemplo 2 – Select com a cláusula Over() –
Select *, sum(cars) over (partition by N_state) as bb
from car c1
Go

— Short Script 2  – Comparativo entre as função IsNull() e Coalesce() —

CREATE TABLE Strings
(String1 varchar(5),
String2 varchar(10),
String3 varchar(5),
String4 varchar(10))
Go
INSERT INTO dbo.Strings (String1, String2, String3, string4)
VALUES(‘Hello’,NULL,NULL,’Goodbye’)
Go
SELECT ISNULL(String1, String2) AS Expr1,
COALESCE(String1, String2) AS Expr2,
ISNULL(String3, String4) AS Expr3,
COALESCE(String3, String4) AS Expr4
FROM Strings
Go
— Short Script 3  – Obtendo taxas de fragmentação de tabelas antes e depois de um Rebuild —
Select object_name(ddips.object_id) As ‘Tabela’,
si.name As ‘Índice’,
convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) As ‘% Média de Fragmentação’,
ddips.page_count As ‘Páginas’,
ddips.compressed_page_count As ‘Páginas compactadas’,
ddips.record_count As ‘Registros’,
ddips.ghost_record_count As ‘Registros Fantasmas’
From sys.dm_db_index_physical_stats(db_id(), object_id(‘queimadas2018’),null, null, ‘detailed’) ddips Inner Join sys.indexes si
on si.object_id = ddips.object_id
Where ddips.avg_fragmentation_in_percent > 0
Go
— Short Script 4  – Utilizando as diretivas Set ArithAbort, Set Ansi_Warnings —
SET ARITHABORT Off
SET ANSI_WARNINGS Off
GOPRINT ‘Setting ARITHIGNORE ON’
GO
— SET ARITHIGNORE ON and testing. 
SET ARITHIGNORE On;
GO
SELECT 1 / 0 AS DivideByZero
GO
SELECT CAST(256 AS TINYINT) AS Overflow
GOPRINT ‘Setting ARITHIGNORE OFF’
GO
— SET ARITHIGNORE OFF and testing. 
SET ARITHIGNORE OFF
GO
SELECT 1 / 0 AS DivideByZero
GO
SELECT CAST(256 AS TINYINT) AS Overflow
GO
SET ARITHIGNORE On
SET ARITHABORT Off
SET ANSI_WARNINGS Off
SELECT 1/0 AS test
Go
— Short Script 5  – Utilizando DataType UniqueIdentifier em conjunto com a função NewID() —
— Exemplo 1 —
Create Table T1
(Codigo UniqueIdentifier Primary Key,
Descricao varchar(100))
Go
Insert Into T1 (Codigo, Descricao)
Values (NewId(), ‘Oi’),
(NewId(), ‘Testando’)
Go
Select * From T1
Go
— Exemplo 2 —
Create Table T2
(Codigo UniqueIdentifier Primary Key Default NewSequentialID(),
Descricao varchar(100))
Go
Insert Into T2 (Descricao)
Values ( ‘Oi’),
(‘Testando’)
Go
Select * From T2
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 “curtos ou 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/2019/03/15/short-scripts-marco-2019/

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

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

Até mais.

Dica do Mês – Analisando o comportamento do comando DBCC CleanTable.


Olá, bom dia. Tudo bem?

O frio já chegou na sua região? Aqui em São Roque, interior de São Paulo, hoje esta manhã esta sendo considerada até o presente momento a mais fria do ano (eu particularmente adoro o frio).

Fico extremamente contente e honrado com a sua visita ao meu blog, mesmo com todo este frio ter a sua presença aqui é muito importante, ainda mais neste post da sessão Dica do Mês, a qual foi criada á alguns anos com objetivo de compartilhar algo que possa ser considerada como uma dica ou melhores práticas para se trabalhar na área de banco de dados, mais especificamente falando relacionadas ao Microsoft SQL Server.

Posso dizer que o post de hoje é algo bastante simples, vamos conhecer um pouco mais sobre um dos mais tradicioais comandos pertencentes a categoria DBCC – Database Command Console existente no Microsoft SQL Server desde suas versões iniciais.

Estou me referindo ao comando DBCC CleanTable, considerado por muitos DBAs o “Veja, aquele produto de limpeza que utilizamos para tirar a gordura dos fogões e panelas”, ele faz basicamente isso em nossas tabelas. No decorrer deste post vou tentar mostrar como Podemos fazer isso.

Sendo assim, sem mais delongas, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Seja bem-vindo ao post – Dica do Mês – Analisando o comportamento do comando DBCC Clean Table.


Introdução

O Microsoft SQL Server apresenta em sua galeria de comandos um conjunto muito particular e exclusivo denominado DBCC – Database Command Console em uma possível tradução para o Português “Comandos de Console de Banco de Dados“, pois bem, este conjunto com categoria de comandos é composta por uma relação bem interessante e diversificada que nos permite fazer uso em diversos cenários, dentre eles por exemplos os comandos: DBCC CheckDB ou DBCC ShrinkFile, ambos muito conhecidos pelos DBAs e Administradores de Banco de Dados, caso você ainda não conheça, com certeza em algum momento terá a oportunidade.

Como já destaquei nesta relação exclusiva de comandos, podemos se deparar com um deles que nos permite aplicar sem qualquer tipo de alteração de configuração, ou mudanças drásticas na estrutura de nosso banco de dados, o que chamamos de remover a gordura de nossas tabelas, talvez você não consiga ou não esteja entendendo o que eu estou definindo como “gordura”, na verdade me refiro por exemplo a aquelas colunas do tipo de dados VarChar, criadas inicialmente em nossas tabelas, mas que ao longo do tempo se tornam colunas praticamente consideradoras espúrias (algo sem sentido ou desnecessário), é ai que entra o nosso amigo DBCC CleanTable, ele tem um papel único e específico justamente para este tipo de cenário, que daqui a pouco eu vou demonstrar, mas antes vamos conhecer um pouquinho sobre este comando.

DBCC CleanTable

Adicionado ao Microsoft SQL Server a partir da edição 2008, o comando DBCC CleanTable possui como papel principal a capacidade de recuperar e liberar o espaço ocupado por colunas existentes em uma tabela consideradas colunas com comprimento variável quando utilizam os seguintes tipos de dados:

  • Varchar;
  • Nvarchar;
  • Varchar(max);
  • Nvarchar(max);
  • Varbinary;
  • Varbinary(max);
  • Text;
  • Ntext;
  • Image;
  • Sql_variant; e
  • XML.

Ele recupera espaço anteriormente ocupado por um destes tipos de dados, depois que uma coluna de comprimento variável é descartada, mas não recupera espaço depois que uma coluna de comprimento fixo é descartada.

Mesmo não tem a capacidade de recuperar o espaço ocupado em disco logo após este limpeza na estrutura da tabela, o comando DBCC CleanTable, pode ajudar a melhorar ou até mesmo zerar taxas de fragmentação que possam estar sendo apresentadas justamente em uma tabela devido a utilização destas colunas com comprimentos variáveis.

Considerações

  • As colunas descartadas forem armazenadas em linha, DBCC CLEANTABLE recuperará espaço da unidade de alocação IN_ROW_DATA da tabela.
  • Quando as colunas forem armazenadas fora de linha, o espaço será recuperado da unidade de alocação LOB_DATA ou ROW_OVERFLOW_DATA, dependendo do tipo de dados da coluna descartada.Se o espaço recuperado de uma página ROW_OVERFLOW_DATA ou LOB_DATA resultar em uma página vazia, DBCC CLEANTABLE removerá a página. DBCC CLEANTABLE executa como uma ou mais transações.
  • O espaço recuperado de uma página ROW_OVERFLOW_DATA ou LOB_DATA resultar em uma página vazia, DBCC CLEANTABLE removerá a página. DBCC CLEANTABLE executa como uma -ou mais transações.
  • Caso não especificado um tamanho de lote, o comando processará a tabela inteira em uma transação e a tabela será bloqueada exclusivamente durante a operação. Para algumas tabelas grandes, o comprimento da única transação e o espaço do log requeridos podem ser muito grandes. Se um tamanho de lote for especificado, o comando executará em uma série de transações, cada qual incluindo o número especificado de linhas.
  • O comando DBCC CLEANTABLE não pode ser executado como uma transação dentro de outra transação. Essa operação é totalmente registrada. Não há suporte para DBCC CLEANTABLE para uso em tabelas do sistema, tabelas temporárias ou a parte do índice columnstore xVelocity de memória otimizada de uma tabela.

Evite utilizar

O comando DBCC CLEANTABLE não deve ser executado como uma tarefa de manutenção de rotina. Ao invés disso, utilize o DBCC CLEANTABLE depois de fazer mudanças significativas em colunas de comprimento variável em uma tabela ou exibição indexada e necessita recuperar o espaço sem-uso (considerada como área não alocada) prontamente.

Alternativas para o uso do DBCC CleanTable

Uma das possíveis alternativas quando desejamos recuperar o espaço ocupado por colunas de comprimemto variável aplica-se a reconstrução de índices em tabelas ou visões indexadas, mas este recurso pode ser considerado custoso no que se relaciona ao tempo de processamento ou até mesmo alocação de recursos durante sua execução.

Pois bem, agora que já conhecemos um pouco sobre este comando, suas considerações, o quando usar e não usar, vamos avançar um pouco este post, dando início a nosso cenário de estudos afim de analisarmos de uma forma bem simples e artificial como o DBCC CleanTable pode nos ser útil.


NOSSO AMBIENTE

Como de costume vamos utilizar um ambiente isolado dos demais bancos de dados que você possa conter, desta maneira nosso cenário será constituído dos seguintes elementos:

Observações

  1. Estaremos fazendo uso de um arquivo texto, o qual é parte importante para nossa análise. O mesmo possui os dados que serão importados para o Microsoft SQL Server, contendo com conjunto real de valores coletados através do portal do INPEInstituto Nacional de Pesquisas Especiais, através de seu Banco de Dados de análise de queimadas ocorridas no Brasil ao longo dos últimos 70 anos, sendo considerada uma das mais importantes fontes de dados abertos do Brasil, disponível para qualquer tipo de análise. Particularmente falando, sou um grande admirador do trabalho realizado pelo INPE, o qual eu comecei em 2017 a estudar para um dos meus projetos no mestrado e diretamente relacionados com a minha nova área de estudos: Data Warehouse, Data Mining e BI.
  2. A estrutura apresentada no arquivo texto, não tem por finalidade ou regra ser considerada uma estrutura padronizada, como também, algo que atenda as regras da Normalização, longe disso, todo conjunto de dados, tipos de dados, nomes das colunas e sua composição física e lógica foi definida e criada para atender as regras de negócio muito específicas de um trabalho de mestrado acadêmico, o qual não faz parte deste estudo ou análise.
  3. O objetivo deste post não se realiza a apresentar, demonstrar ou orientar como realizar o procedimento de importação de dados para o Microsoft SQL Server, sendo assim, caso você tenha dúvidas ou dificuldades para realizar este procedimento, acesse: Import and Export Data with the SQL Server Import and Export Wizard.
  4. Logo após a execução do processo de importação dos dados, você notará que nossa tabela e sua estrutura foi criada sem respeitar uma análise de uso de tipos de dados, criação de chaves primárias ou outras considerações que podemos definir como melhores práticas de modelagem de banco de dados. Não foi fique preocupado, pois estaremos realizando toda esta reestruturação logo na sequência.

Avançando mais um pouco, vamos criar nosso banco de dados e logo na sequência começaremos nossa análise, para tais procedimentos, utilizaremos o Bloco de Código 1 abaixo:

— Bloco de Código 1 – Criando nosso cenário —

— Criando o Banco de Dados —

Create Database TesteDBCCCleanTable
Go

— Acessando o Banco de Dados —

Use TesteDBCCCleanTable
Go

Presumo que neste momento você já tenha feito download do arquivo QueimadasTableCleanTable.txt, como também, já tenha realizado a importação dos dados e criação da tabela,.

Ótimo, espero que todo processo de importação de dados tenha ocorrido corretamente, agora com a estrutura criada e acessível, teremos a possibilidade de começar a realizar nossa análise, nosso próximo passo será reestrutura a tabela QueimadasCleanTable, definindo sua chave primária, alterando tipos de dados em determinadas colunas, removendo outras, enfim colocando um pouco de ordem na casa.

Desta forma, vamos utilizar o Bloco de Código 2 a seguir:

— Bloco de Código 2 – Reestruturando a Tabela QueimadasCleanTable —

— Remover a Anulabilidade da coluna CodigoQueimada —
Alter Table QueimadasCleanTable
Alter Column CodigoQueimada Int Not Null
Go
— Adicionar a coluna chave primária na Tabela QueimadasCleanTable —
Alter Table QueimadasCleanTable
Add Constraint [PK_QueimadasCleanTable_Codigo]
Primary Key (CodigoQueimada)
Go
— Alterando o Tamanho e Tipo de Dados da Coluna Pais —
Alter Table QueimadasCleanTable
Alter Column Pais Char(6) Not Null
Go
— Alterando os tipos de dados e tamanho da coluna Satelite —
Alter Table QueimadasCleanTable
Alter Column Satelite Varchar(10) Not Null
Go
— Alterando os tipos de dados e tamanho das colunas —
Alter Table QueimadasCleanTable
Alter Column Municipio Varchar(40) Not Null
Go
Alter Table QueimadasCleanTable
Alter Column Estado Varchar(20) Not Null
Go
Alter Table QueimadasCleanTable
Alter Column Bioma Varchar(15) Not Null
Go
— Alterando o formato do dado armazenado na coluna DataHora —
Update QueimadasCleanTable
Set DataHora=Convert(DateTime, DataHora, 102)
Go
— Alterando a Coluna DataHora —
Alter Table QueimadasCleanTable
Alter Column DataHora DateTime Not Null
Go
— Alterando a Coluna Longitude —
Alter Table QueimadasCleanTable
Alter Column Longitude Numeric(10,5) Not Null
Go
— Alterando a Coluna Latitude —
Alter Table QueimadasCleanTable
Alter Column Latitude Numeric(10,5) Not Null
Go

Ufa, após este longo caminho percorrido, nossa tabela QueimadasCleanTable, deve estar apresentando uma estrutura similar a Figura 1:


Figura 1 – Banco de Dados TesteDBCCCleanTable e Tabela QueimadasCleanTable criados.

Sensacional, agora a brincadeira vai começar, devemos ter basicamente 752.252 (Setecentas e cinquenta e duas mil, duzentas e cinquenta e duas) linhas de registros lógicos inseridas nesta tabela, uma massa de dados interessante e bem diversificada para nosso estudo, afim de confirmarmos nossas massa de dados, vamos executar o Bloco de Código 3 a seguir, para retornarmos em tela uma pequena porção de dados:

— Bloco de Código 3 – Validando uma porção de dados da Tabela QueimadasCleanTable —

Select Top 1000 DataHora,
Satelite,
Pais,
Estado,
Municipio
From QueimadasCleanTable
Go

Após a execução do Bloco de Código 3, você deverá ter obtido em tela, um resultado similar ao apresentado abaixo pelo Figura 2:

Figura 2 – Dados coletados e apresentados em tela após a execução do Bloco de código 3.

Por enquanto nenhuma novidade, nada em especial foi apresentado, não é mesmo? Eu acredito que sim. Agora que toda estrutura da tabela foi refeita, nossos dados foram validados, o que pode estar faltando para fazermos uso do comando DBCC CleanTable?

A resposta é simples, falta identificar quais são as colunas de comprimento variável existentes em nossa tabela que poderemos utilizar para entender o comportamento deste comando, sendo assim, nosso próximo passo será identificar quais seriam as colunas e seus respectivos tipos de dados que formam a estrutura da tabela QueimadasCleanTable, através da execução do Bloco de Código 4 apresentado abaixo:

— Bloco de Código 4 – Identificando as colunas de comprimento variável —

Select st.name As ‘TableName’,
sc.name As ‘ColumnName’,
sc.column_id As ‘ColumnID’,
sty.name As ‘DataType’,
sc.max_length As ‘MaxLength’
from sys.tables st Inner Join sys.columns sc
on st.object_id = sc.object_id
Inner Join sys.systypes sty
on sc.system_type_id = sty.xtype
Where st.name = ‘QueimadasCleanTable’
And sty.name = ‘VarChar’
Order By st.Name Asc, sc.column_id Asc
Go

Note que estamos fazendo uso das conhecidas e tradicionais tabelas de sistema:

  • sys.tables;
  • sys.columns; e
  • sys.systypes.

A execução do Bloco de Código 4 é simples e rápida, a Figura 3 abaixo, deve ilustrar o resultado obtido após sua execução:

Figura 3 – Relação de colunas que utilizam o tipo de dados Varchar() com tamanho variáveis.

Estamos quase lá, já sabemos da existência de 7(sete) colunas que neste momento fazem parte da estrutura da nossa tabela QueimadasCleanTable que nos possibilitam serem utilizadas.

Como eu destaquei anteriormente o comando DBCC CleanTable tem como papel principal recuperar o espaço ocupado por estas colunas, quando as mesmas venham a ser removidas ou sofram alterações em seus tamanhos.

Para que possamos entender de forma clara e didática como o DBCC CleanTable trabalha, temos a necessidade de identificar os espaços ocupados neste momento por nossa tabela, e obrigatoriamente as taxas de alocação e fragmentação de dados, vamos então executar o Bloco de Código 5, o qual vai nos ajudar a identificar o espaçamento ocupado por nossa tabela:

— Bloco de Código 5 – Identificando o espaço e áreas de alocação ocupadas pela Tabela QueimadasCleanTable —

— Identificando os espaços ocupados —
sp_spaceused ‘QueimadasCleanTable’
Go
— Identificando as taxas de alocação, fragmentação e distribuição de registros —
Select object_name(ddips.object_id) As ‘Tabela’,
si.name As ‘Índice’,
convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) As ‘% Média de Fragmentação’,
convert(decimal(5,2),isnull(ddips.avg_page_space_used_in_percent,0)) As ‘% Média de Espaço utilizado’,
ddips.page_count As ‘Páginas’,
ddips.compressed_page_count As ‘Páginas compactadas’,
ddips.record_count As ‘Registros’,
ddips.ghost_record_count As ‘Registros Fantasmas’
From sys.dm_db_index_physical_stats(db_id(), object_id(‘QueimadasCleanTable’),null, null, ‘detailed’) ddips Inner Join sys.indexes si
on si.object_id = ddips.object_id
Go
Acredito que você deve ter observado que o Bloco de Código 5 foi dividido em duas partes, a primeira fazendo uso da System Stored Procedure: SP_SpaceUsed, e a segunda, através da DMF – Dynamic Management Function – sys.dm_db_index_physical_stats.
Como uma forma de ajudar a identificar e entender os dados coletados após a execução deste bloco de código, apresenta abaixo a Tabela 1 com os dados coletados através SP_SpaceUsed e Tabela 2 com os dados coletados através sys.dm_db_index_physical_stats.
Tabela 1 – SP_SpaceUsed – Espaços Ocupados
name rows reserved data index_size unused
QueimadasCleanTable 752252 81736 KB 81272 KB 312 KB 152 KB
Ao realizarmos uma breve análise, podemos observar através dos resultados apresentados na Tabela 1, que nosso tabela QueimadasCleanTable, neste momento esta ocupando uma área em disco de quase 82Mbs (Megabytes), sendo 81.2Mbs para dados e 312Kbs (Kilobytes) para índices, com uma área não alocada de 152Kbs.
Tabela 2 – Sys.dm_db_index_physical_stats – Taxas de Fragmentação, Distribuição de Páginas de Dados e Registros
Tabela Índice % Média de Fragmentação % Média de Espaço utilizado Páginas Páginas compactadas Registros Registros Fantasmas
QueimadasCleanTable PK_CodigoQueimada_Queimadas2018_CleanTable 0.01 99.34 10159 0 752252 0
QueimadasCleanTable PK_CodigoQueimada_Queimadas2018_CleanTable 0.00 44.07 37 0 10159 0
QueimadasCleanTable PK_CodigoQueimada_Queimadas2018_CleanTable 0.00 5.92 1 0 37 0
Já os dados apresentados pela Tabela 2, mostram uma pequena taxa de fragmentação de 0,01 % para nosso índice chave primária: PK_CodigoQueimada_Queimadas2018_CleanTable, em sua área de alocação de dados, composta por 10.159 páginas de dados.
Estamos próximos da hora da verdade, com todo esta conjunto de dados coletados, poderemos comprovar como o DBCC CleanTable pode nos ajudar, através do Bloco de Codigo 6, realizaremos a exclusão de 4 (Municipio, Bioma, AreaIndu e FRP) das 7 colunas listadas anteriormente, logo na sequência vamos repetir a execução do Bloco de Código 5 para comprovar que as áreas e espaços ocupados continuam apresentando os mesmos valores, e nosso ultimo passo será executar o DBCC Clean Table.
— Bloco de Código 6 – Removendo as colunas Municipio, Bioma, AreaIndu e FRP —
Alter Table QueimadasCleanTable
Drop Column Municipio, Bioma, AreaIndu, FRP
Go
As colunas foram removidas corretamente, agora vamos repetidar a execução do Bloco de Código 5 na sequência.
— Executar novamente o Bloco de Código 5 —
— Identificando os espaços ocupados —
sp_spaceused ‘QueimadasCleanTable’
Go
— Identificando as taxas de alocação, fragmentação e distribuição de registros —
Select object_name(ddips.object_id) As ‘Tabela’,
si.name As ‘Índice’,
convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) As ‘% Média de Fragmentação’,
convert(decimal(5,2),isnull(ddips.avg_page_space_used_in_percent,0)) As ‘% Média de Espaço utilizado’,
ddips.page_count As ‘Páginas’,
ddips.compressed_page_count As ‘Páginas compactadas’,
ddips.record_count As ‘Registros’,
ddips.ghost_record_count As ‘Registros Fantasmas’
From sys.dm_db_index_physical_stats(db_id(), object_id(‘QueimadasCleanTable’),null, null, ‘detailed’) ddips Inner Join sys.indexes si
on si.object_id = ddips.object_id
Go
A Figura 4, vai ilustrar e comprovar que os valores apresentados após a nova execução do Bloco de Código 5, são os mesmos obtidos em sua primeira execução:
Figura 4 – Valores obtidos após a segunda execução do Bloco de Código 5.
E agora chegou o grande momento, vamos executar o Bloco de Código 7, o qual terá a responsabilidade de executar o comando DBCC CleanTable, logo na sequência vamos executar novamente o Bloco de Código 5, e ai sim teremos uma surpresa:
— Bloco de Código 7 – Executando o comando DBCC CleanTable —
Dbcc CleanTable(TesteDBCCCleanTable,’dbo.QueimadasCleanTable’)
Go
Por padrão como boa parte dos comandos DBCCs, o CleanTable, vai retornar na guia de mensagens a seguinte frase:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.”
Agora repita novamente a execução do Bloco de Código 5, e observe que teremos um novo conjunto de valores apresentados.
— Executar novamente o Bloco de Código 5 —
— Identificando os espaços ocupados —
sp_spaceused ‘QueimadasCleanTable’
Go
— Identificando as taxas de alocação, fragmentação e distribuição de registros —
Select object_name(ddips.object_id) As ‘Tabela’,
si.name As ‘Índice’,
convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) As ‘% Média de Fragmentação’,
convert(decimal(5,2),isnull(ddips.avg_page_space_used_in_percent,0)) As ‘% Média de Espaço utilizado’,
ddips.page_count As ‘Páginas’,
ddips.compressed_page_count As ‘Páginas compactadas’,
ddips.record_count As ‘Registros’,
ddips.ghost_record_count As ‘Registros Fantasmas’
From sys.dm_db_index_physical_stats(db_id(), object_id(‘QueimadasCleanTable’),null, null, ‘detailed’) ddips Inner Join sys.indexes si
on si.object_id = ddips.object_id
Go
As tabelas 3 e 4 apresentadas na sequência, vamos nos ajudar a identificar estes novos valores apresentados após a execução do Bloco de Código 7:
Tabela 3 – SP_SpaceUsed – Espaços Ocupados
name rows reserved data index_size unused
QueimadasCleanTable 752252 81736 KB 81272 KB 312 KB 152 KB
Ao analisarmos os valores apresentados na Tabela 3, inicialmente podemos ficar surpresos por não ocorreram mudanças, na verdade não vai ocorrer mesmo, pois como destacado no início deste post o DBCC CleanTable não tem a função de liberar o espaço físico e lógico ocupado pela tabela e suas estruturas.
Tabela 4 – Sys.dm_db_index_physical_stats – Taxas de Fragmentação, Distribuição de Páginas de Dados e Registros
Tabela Índice % Média de Fragmentação % Média de Espaço utilizado Páginas Páginas compactadas Registros Registros Fantasmas
QueimadasCleanTable PK_CodigoQueimada_Queimadas2018_CleanTable 0.01 80.99 10159 0 752252 0
QueimadasCleanTable PK_CodigoQueimada_Queimadas2018_CleanTable 0.00 44.07 37 0 10159 0
QueimadasCleanTable PK_CodigoQueimada_Queimadas2018_CleanTable 0.00 5.92 1 0 37 0
Por outro lado, a Tabela 4 nos apresenta uma pequena mudança na coluna % Média de Espaço Utilizado que agora é de 80,99 % e antes era de 99,34%, ou seja, ao realizarmos a execução das colunas: Municipio, Bioma, AreaIndu e FRP o DBCC CleanTable realizou uma pequena recuperação de espaço que estava sendo ocupados por estas colunas em suas respectivas linhas de registro lógicos.
Isso não é algo fora do comum, conseguir reaproveitar as áreas que estavam sendo ocupadas anteriormente sem precisar realizar qualquer tipo de reconstrução ou mudanças de configuração. Eu acredito que sim.
Seguindo a tradição dos posts desta sessão, antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:
Quero propor um desafio
Elabore um cenário similar ao apresentado aqui, e utilize os comandos Delete e Truncate Table em conjunto com o DBCC CleanTable, faça uma análise comparativa, tenho a certeza que este desafio vai lhe ajudar a entender de forma simples e objetiva as diferenças entre o Delete e o Truncate, sendo esta, uma das dúvidas mais recorrentes que podemos encontrar na internet.
Com isso chegamos ao final de mais um post da sessão Dica do Mês, espero que você tenha gostado, eu como de costume gostei muito. 

REFERÊNCIAS

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql

https://docs.microsoft.com/pt-br/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql

https://docs.microsoft.com/pt-br/sql/relational-databases/system-stored-procedures/sp-columns-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-systypes-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql

POSTS ANTERIORES

CONCLUSÃO

Como de costume, tenho um imenso prazer em poder compartilhar um pouco das minhas experiências, estudos e conhecimentos que estou diariamente formando sobre este fantástico Sistema Gerenciador de Banco de Dados chamado Microsoft SQL Server.

Neste post, tivemos a possibilidade de relembrar um pouco sobre os comandos DBCCs, mais especificamente o DBCC CleanTable, conhecido como o “Veja, desengordurante, das estruturas de tabelas e visões índexadas” existente no Microsoft SQL Server.

O uso desta comando pode ser aplicado, como uma ferramenta de apoio quando temos a necessidade de reaproveitar o espaço antes ocupado por áreas que pertenciam a uma ou mais colunas que venham a utilizar tipos de dados com tamanhos variáveis.

O comando DBCC CLEANTABLE não deve ser executado como uma tarefa de manutenção de rotina, mas sim, como um recurso aplicado em momento específicos e não de uso contínuo.

Através do cenário aqui apresentado, foi possível observar, como este comando é capaz de resdistribuir e aproveitar o espaço ocupado internamente, sem nos forçar a realizar alterações drásticas em nossas tabelas, bem como, mudanças nas configurações do nosso banco de dados ou SQL Server.

Este é o fantástico Microsoft SQL Server, produto tão fascinante que a cada dia eu não consigo deixar de querer estudar e conhecer mais ainda.

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique à vontade para enviar suas críticas, sugestões, observações e comentários.

Nos encontramos no próximo post da sessão Dica do Mês a ser publicado em breve.

Um forte abraço.

Até mais.