#08 – Para que serve


Boa noite comunidade, boa noite amantes do SQL Server e Banco de Dados…..

Hoje dia 03 de Setembro começo de noite de mais um sabádão, estamos vivendo os primeiros dias de mais um mês e se aproximando do final de 2016. Antes de começar a falar sobre o post de hoje, gostaria de compartilhar mais algumas conquistas obtidas neste últimos dias, bem como uma outra que esta por vir.

Há primeira conquista se refere aos meus alunos do Curso de Sistemas de Informação para Internet da Fatec São Roque, tive o imenso prazer em poder lecionar para esta fantástica turma nos últimos 4 semestre, desejo a vocês tudo de bom, felicidades e muitas conquistas.

A próxima ainda mais importante é o aniversário do meu filho mais velho Eduardo Galvão que no próximo dia 06 de Setembro estará completando 15 anos de vida, com certeza um dos momentos mais felizes e esplêndidos da minha vida. Desejo a você meu pequeno grande menino um futuro fantástico, muito saúde, felicidades e alegrias.

Vamos em frente, voltando ao post de hoje da sessão Para que serve, conforme prometido vou dar continuidade ao post anterior onde começamos a conhecer um pouco sobre o conceito de Índice Hipotéticos. Caso você não tenha acessado o post anterior não perca tempo clique no link acima e conheça um pouco sobre este conceito tão surpreende quando a sua forma de uso.


Começa agora o #08 – Para que serve – Índices Hipotéticos – Parte II.

No #08 – Para que serve, daremos início ao processo de criação do nosso ambiente de banco de dados com objetivo de construir um estrutura específica para conhecermos e simularmos o uso dos índices hipotéticos. É isso siga-me os bons, mãos no teclado, acompanhe a sequência de passos apresentandos abaixo:

— Passo 1 – Criando o Banco de Dados HypotheticalDB —

CREATE DATABASE [HypotheticalDB]
ON  PRIMARY
(NAME = ‘HypotheticalDB-Data’,
 FILENAME = ‘C:\SQLServer2016\Data\HypotheticalDB_Data.mdf’ ,
 SIZE = 4MB ,
 MAXSIZE = 4096MB,
 FILEGROWTH = 2MB )
LOG ON
(NAME = ‘HypotheticalDB-Log’,
 FILENAME = ‘C:\SQLServer2016\Log\HypotheticalDB_Log.ldf’ ,
 SIZE = 8MB ,
 MAXSIZE = 2GB ,
 FILEGROWTH = 4MB)
GO

— Passo 2 – Acessando o Banco de Dados —
Use HypotheticalDB
Go
— Passo 3 – Criando as Tabelas —
Drop Table If Exists dbo.Clientes
CREATE TABLE Clientes
(Codigo  INT Identity(1,1) NOT NULL Primary Key Clustered,
 CodigoCategoria TinyInt NOT NULL,
 Nome  VARCHAR(60) NOT NULL,
 Endereco VARCHAR(80) NOT NULL,
 Estado  CHAR(2) NOT NULL,
 DataUltimaCompra  DATETIME)
Go
Drop Table If Exists dbo.ClientesCategorias
CREATE TABLE ClientesCategorias
(Codigo TinyInt NOT NULL,
 Descricao VARCHAR(20) NOT NULL)
Go

Nota: Observe que no passo 3 estamos utilizando uma nova instrução introduzida no Microsoft SQL Server 2016, estou me referindo ao Drop If Exists uma das mais esperadas melhorias a serem adicionados ao Microsoft SQL Server que por muitos anos estava sendo aguardada.

— Passo 4 – Inserindo dados na Tabela ClientesCategorias —
INSERT Into ClientesCategorias (Codigo, Descricao)
 Values (1, ‘Premier’),
             (2, ‘Advanced’),
             (3, ‘Special’)
Go
— Passo 5 – Inserindo dados na Tabela Clientes —
Insert Into Clientes (CodigoCategoria, Nome, Endereco, Estado, DataUltimaCompra)
Values (3, ‘José Bonito’,’Rua A’,’SP’,GETDATE()-30),
            (1, ‘Dassaev Silva’,’Rua B’,’SP’,GETDATE()-120),
            (3, ‘Viewer Partes’,’Rua 123′,’RJ’,GETDATE()-720),
            (1, ‘Dino Silva Sauros’,’Avenida Parque dos Dinassauros’,’AM’,GETDATE()-240),
            (2, ‘Fernandino Campos Boyd’,’Estrada Velha’,’MG’,GETDATE()-5),
            (1, ‘Katrina Tornado’,’Rua Storm’,’RG’,GETDATE()-300),
            (2, ‘Washington Wizard’,’Place 1′,’PR’,GETDATE()-1024),
            (3, ‘Chicago Bulls’,’Place 2′,’PR’,GETDATE()-89),
            (2, ‘Denver Nuggets’,’Place 3′,’PR’,GETDATE()-289),
            (2, ‘Los Angeles Lakers’,’Place 4′,’PR’,GETDATE()-390)
Go
— Passo 6 – Consultando os dados —
Select Codigo, Descricao From ClientesCategorias
Go
Select Codigo, CodigoCategoria, Nome, Endereco, Estado, DataUltimaCompra From Clientes
Go
Até aqui nada muito diferente do que normalmente utilizamos em nosso dia-á-dia, talvez o uso da Drop If Exists possa ser um diferencial. Dando continuidade os dois próximos passos serão de extrema importância para nosso ambiente, estaremos justamente realizando a criação de três índices:
  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas;
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas; e
  • IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas.

Observe que serão criados dois índices não-clusterizados e um índice clusterizado, todos vinculados a tabela ClientesCategorias para coluna Codigo, onde você vai poder notar que dois índices devem ser criados sem estatísticas o que indica para o SQL Server que este será um índice hipotético existindo somente de maneira lógica e não terá nenhum tipo de vínculo ou estrutura física criada. Então siga em frente, mãos no teclado, a seguir os passos 7 e 8:

— Passo 7 – Criando índices hipotéticos não-clusterizado na tabela ClientesCategorias —
CREATE INDEX IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = 0
CREATE INDEX IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = -1
Go
— Passo 8 – Criando índices hipotéticos clusterizado na tabela ClientesCategorias —
CREATE CLUSTERED INDEX IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas
 ON ClientesCategorias (Codigo) With Statistics_Only = -1
Go
Antes de continuarmos vou apresentar a Figura 1 que ilustra a criação destes índices dentro da estrutura da tabela ClientesCategorias, você vai poder notar na figura que somente existe estatísticas para estes objetos as guias Keys e Indexes estão vazias:
HypotheticalIndex

Figura 1 – Estrutura da Tabela ClientesCategorias.

Vamos que vamos estamos quase lá, falta pouco, agora vamos executar o passo 9 em duas etapas a primeira será a execução do system stored procedure sp_helpindex responsável em apresentar a estrutura de índices existente em uma determinada tabela, neste caso estou me referindo a tabela ClientesCategorias, sendo assim, vamos realizar esta execução:

 

— Passo 9 – Obtendo informações sobre os índices —
Exec sp_helpindex ClientesCategorias
Go

Após a execução o Management Studio vai retornar nossos três índices criados anteriormente, podemos observar a existência de uma coluna chamada index_description, verifique que todos os índices apresentam a seguinte informação: nonclustered, hypothetical

Você pode estar se perguntando, mas nos não criamos um índice clusterizado? A resposta seria sim, criamos ele continua sendo clusterizado, mas como este não apresenta um estrutura física o mesmo é reconhecido e tratado pelo SQL Server como índice não-clusterizado neste momento, a Figura 2 apresentada este resultado:

HypotheticalIndex2

Figura 2 – Relação de índices hipotéticos pertencentes a table ClientesCategorias.

O próximo passo e realizar a segunda parte do passo 9,  onde faremos a execução do comando DBCC Show_Statistics responsável em apresentar informações sobre as estruturas físicas e lógicas vinculadas a estatísticas de um índice, no nosso caso vamos utilizar os índices:

  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas; e
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

Vamos então executar o bloco de código abaixo:

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas)

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas)
Go

Observando  a Figura 3 a seguir fica mais fácil  entender que o índice: IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas não apresenta nenhuma informação relacionada a estatísticas, ao contrário do índice:

IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas, onde o SQL Server após o procedimento de crição do mesmo já estabeleceu alguns dados estatísticos que posteriormente será utilizados no processamento das querys de acordo com sua necessidade. Segui a seguir a Figura 3:

HypotheticalIndex3

Figura 3 – Comparativo entre as estruturas dos índices IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas  e IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

Falta pouco, pouco mesmo, prometo que estamos no final, vamos executar o passo de número 10, onde estaremos obtendo as informações sobre nossos índices através da catalog view sys.sysindexes, onde nosso índice IND_ClientesCategorias_Clusterizado_CodigoComEstatisticas agora será apresentado como um índice clusterizado, isso nos faz entender que em alguns momentos o SQL Server acaba mudando um pouco o seu comportamento de acordo com a forma de obtenção de informações sobre as estruturas de nossos índices, sinceramente não saberia dizer se isso pode ser considerado uma falha ou até mesmo um possível bug.

Pisando fundo em nosso “acelerador” execute o passo 10 apresenta logo a seguir:

— Passo 10 – Obtendo informações sobre a relação de índices —

SELECT object_id,
             OBJECT_NAME(object_id) AS ‘Tabelas’ ,
             name As ‘Nome do Índice’,
             type_desc,
             is_hypothetical As ‘Índice Hipotético = 1 Não-Hipotético=0’
FROM sys.indexes
WHERE object_id in (object_id(‘ClientesCategorias’), object_id(‘Clientes’))
Go

E agora o tão esperado momento, vamos realmente fazer uso de nossos índices hipotéticos através da execução dos passos 11 e 12 teremos a capacidade técnica de entender o comportamento do SQL Server, principalmente através da comparação dos planos de execução gerados para cada query processada, sendo assim, vamos começar executando o passo 11 a seguir:

— Passo 11 – Executando o Select de maneira clássica sem a diretiva SET AUTOPILOT —
SET SHOWPLAN_XML ON
Go
Select C.Codigo,
          Cc.Codigo As ‘Categoria do Cliente’,
    C.Nome,
    C.Endereco,
    C.Estado,
    C.DataUltimaCompra
From Clientes C Inner Join ClientesCategorias CC
                           On C.CodigoCategoria = CC.Codigo
Where C.Estado = ‘SP’
GO
SET SHOWPLAN_XML OFF
Go
Observe que solicitamos ao Management Studio para realizar o plano de execução da nossa query através da diretiva SET SHOWPLAN_XML, onde o mesmo deverá ser gerado no formato XML, recomendo que você salve este plano de execução para que possamos fazer uso do mesmo no último. Agora execute o passo 12, salve o plano de execução gerado em xml e apresentado de forma gráfica.
— Passo 12 – Executando o Select de maneira personalizada ativando a diretiva SET AUTOPILOT  —
SET AUTOPILOT ON — Ativando a diretiva —
Go
Select C.Codigo,
          Cc.Codigo As ‘Categoria do Cliente’,
    C.Nome,
    C.Endereco,
    C.Estado,
    C.DataUltimaCompra
From Clientes C Inner Join ClientesCategorias CC
                           On C.CodigoCategoria = CC.Codigo
Where C.Estado = ‘SP’
Go
SET AUTOPILOT OFF — Desativando a diretiva —
GO
Muito bem, conseguimos executar nossos dois selects, espero que você tenha salvado os respectivos planos de execução, agora após a execução do passo 12 você deverá esta visualizando o plano de execução deste select, clique com o botão da direita na parte branca e escolha a opção Comparative ShowPlan, selecione o arquivo que representa o plano de execução criado após a execução do passo 11 em seguida o mesmo deverá ser aberto, conforme a Figura 4 a seguir apresenta:
HypotheticalIndex4
Figura 4 – Comparação entre os planos de execução gerados durante a execução dos passos 11 e 12.
Podemos observar que os dois planos de execução são praticamentes idênticos de maneira geral, mas se realmente analisarmos cada um dos operadores, será possível notar um uma pequena diferença no operador Select, onde a instrução CompileMemory nos mostra uma diferença de 8(oito) compilações a menos realizada no passo 11 em comparação com o passo 12, conforme ilustra a Figura 5 abaixo:
HypotheticalIndex5
Figura 5 – Comparativo de resultados apresentados na instrução CompileMemory.
Poxa vida, analisando friamente esta é uma diferença tão pequena que talvez não seja necessário se preocupar ou até mesmo querer entender o que pode ter acontecido. Mas DBA que é DBA de verdade não gosta de se deparar com estas situações em seu ambiente, menos ficar sem uma possível resposta, por mais que muitas vezes ela até mesmo não exista.
Então se você quiser realmente saber o que pode ter influenciado o SQL Server mais especificamente o Database Engine e seus componentes dentre eles o Query Optimizer e o Execution Plan a apresentar este resultado não deixe de acessar o próximo post dedicado aos índices hipotéticos, então nos vemos no #09 Para que serve.
Até lá………..

É isso ai galera, chegamos ao final de mais post da sessão Para que serve!

Espero que você tenha gostado, que as informações compartilhadas aqui possam lhe ajudar a se tornar cada vez um profissional de banco de dados reconhecido e valorizado, um dos papéis na área de tecnologia mais importantes para qualquer empresa.

Reconher o verdadeiro papel de um DBA dentro de sua estrutura, é reconhecer o verdadeiro valor de seus dados e como eles podem se tornar uma infomação valiosa para sua tomada de decisão.

Caso deseje acessar os posts anteriores desta sessão, utilize os links listados abaixo:

Anúncios

Short Scripts – Março 2016


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

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

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

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

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

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

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

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

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

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

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

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

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

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

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

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

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

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

create table #customers (

id int,

customer varchar(50)

)

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

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

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

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

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

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

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

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

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

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

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

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

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

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

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

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

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

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

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

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

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

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

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

Exec sp_helpdb ‘Master’

Go

 

 

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

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

go

declare @txt varchar(255)

set @txt = ‘Question’

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

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

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

select @txt

Go

 

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

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

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

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

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

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

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

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

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

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

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

— The after update Select Statement

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

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

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

GO

 

Exec [QOD_Test_1]

Go

 

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

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

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

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

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

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

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

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

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

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

Go

 

 


 

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

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

Valeu!!!

Short Script – Dezembro – 2015


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

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

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

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

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

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

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

Segue abaixo a relação de short scripts:

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

CREATE EVENT SESSION [CounterTest] ON SERVER

ADD EVENT sqlserver.login,

ADD EVENT sqlserver.logout(

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

ADD TARGET package0.event_counter

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

GO
— login and logout a few times

GO

— query for data

— Query the Target

SELECT

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

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

FROM

( SELECT

CAST(t.target_data AS XML) AS target_data

FROM

sys.dm_xe_sessions AS s

JOIN

sys.dm_xe_session_targets AS t

ON

t.event_session_address = s.address

WHERE

s.name = ‘CounterTest’

AND t.target_name = ‘event_counter’

) AS tab

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

GO

 

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

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

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

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

@d1 INT,
@d2 INT

— CPF
IF (@Fl_Tipo = 0)
BEGIN

WHILE (@Quantidade > 0)
BEGIN

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

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

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

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

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

SET @Quantidade = @Quantidade – 1

END

END

— CNPJ
ELSE BEGIN

WHILE (@Quantidade > 0)
BEGIN

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

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

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

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

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

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

SET @Quantidade = @Quantidade – 1

END

END

SELECT * FROM #Tabela_Final

END

 

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

EXEC sp_cycle_errorlog
GO

 

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

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

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

SET @Pos += 1
END

RETURN @Ret
END
GO

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

 

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

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

:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME

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

:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME
Go

 

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

create table #t (string varchar (2))

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

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

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

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

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

Go

 

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

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

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

WITH AllDates AS

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

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

FROM    sys.all_objects a

CROSS JOIN sys.all_objects b

)

SELECT  WeekDays = COUNT(*) +1 –

–Clcula a quantidade de feriados entre as datas

(select count(*) from FTAFE(NOLOCK)

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

and FTAFE.CD_CIDADE in(0)

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

FROM    AllDates

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

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

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

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

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

union all

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

select
COL1,
COL2
from CTE_Rec
order by
COL1,
COL2

Go

 

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

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

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

Go

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

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

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

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

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

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

Nos encontramos em breve…..

Short Script – Agosto 2013


Galera, bom dia.

Tudo em paz? Semana passando rapidamente, hoje quinta – feira.

Estou voltando com mais um relação de Short Scripts para vocês, com alguns códigos voltados para atividades em Sistemas de Gestão Empresarial os chamados ERP, como por exemplo, atualização de Saldos, Movimentação de Estoque, entre outros.

Segue abaixo a relação:

Exemplo – 1 – Script – User Role List and Login Type:

SELECT

CASE

WHEN SSPs2.name IS NULL THEN ‘Public’

ELSE SSPs2.name

END AS ‘Role Name’,

SSPs.name AS ‘Login Name’,

Case SSPs.is_disabled

When 0 Then ‘0 – Habilitado’

When 1 Then ‘1 – Desabilitado’

End AS ‘Login Status’,

SSPs.type_desc AS ‘Login Type’

FROM sys.server_principals SSPs LEFT JOIN sys.server_role_members SSRM

ON SSPs.principal_id  = SSRM.member_principal_id

LEFT JOIN sys.server_principals SSPs2

ON SSRM.role_principal_id = SSPs2.principal_id

WHERE SSPs2.name IS NOT NULL

OR SSPs.type_desc <> ‘CERTIFICATE_MAPPED_LOGIN’

AND SSPs.type_desc <> ‘SERVER_ROLE’

AND SSPs2.name IS NULL

ORDER BY SSPs2.name DESC, SSPs.name

Exemplo – 2 – Lista Databases online:

SELECT name FROM master..sysdatabases
WHERE dbid > 4 AND (512 & status) <> 512

 

Exemplo – 3 – Script – Database Login and User Role List:

 

SELECT SDPs.name as Role,

m.login,

m.[User]

FROM [Master].sys.database_principals SDPs Inner JOIN (SELECT SDPs2.name AS role,

SDPs1.name AS  [User],

p2.principal_id,

SL.name AS login

FROM [Master].sys.database_principals SDPs1 Inner JOIN [Master].sys.syslogins SL

ON SDPs1.sid = SL.sid

Inner JOIN [Master].sys.database_role_members SRM

ON SRM.member_principal_id = SDPs1.principal_id

Inner JOIN [Master].sys.database_principals SDPs2

ON SRM.role_principal_id = SDPs2.principal_id

AND SDPs2.type IN (‘R’)

WHERE SDPs1.type IN (‘S’,’U’,’G’)

)  m  ON SDPs.principal_id = m.principal_id

WHERE SDPs.type IN (‘R’)

ORDER BY role, login

Go

 

— CTE —

With Roles (Role, Login, [User])

As

(SELECT SDPs2.name AS role,

SDPs1.name AS  [User],

SL.name AS login

FROM [Master].sys.database_principals SDPs1 Inner JOIN [Master].sys.syslogins SL

ON SDPs1.sid = SL.sid

Inner JOIN [Master].sys.database_role_members SRM

ON SRM.member_principal_id = SDPs1.principal_id

Inner JOIN [Master].sys.database_principals SDPs2

On SRM.role_principal_id = SDPs2.principal_id

AND SDPs2.type IN (‘R’)

WHERE SDPs1.type IN (‘S’,’U’,’G’))

 

Select * from Roles

ORDER BY role, login

 

Exemplo – 4 – Script – User Role Permissions – Grantor, Object Permissions and Permissions Type:

SELECT SDPs1.name AS [User],

SDBPs.permission_name AS [Permissions],

ISNULL(SDBPs.class_desc,”) COLLATE latin1_general_cs_as + ISNULL(‘:’+SO.name,”) COLLATE latin1_general_cs_as + ISNULL(‘:’+SC.name,”) COLLATE latin1_general_cs_as As PermissionObjetct,

SDPs.name as Grantor,

SDBPs.state_desc AS PermissionType

FROM [Master].sys.database_permissions SDBPs INNER JOIN  [Master].sys.database_principals  SDPs

on SDBPs.grantor_principal_id=SDPs.principal_id

INNER JOIN  [Master].sys.database_principals SDPs1

on SDBPs.grantee_principal_id=SDPs1.principal_id

LEFT OUTER JOIN [Master].sys.sysobjects SO

on SDBPs.major_id=SO.id and SDBPs.class =1

LEFT OUTER JOIN [Master].sys.schemas  SC

on SDBPs.major_id=SC.[schema_id]

WHERE SDPs1.name IN (‘public’)

And SDBPs.permission_name NOT IN(‘CONNECT’)

ORDER BY User, Permissions, PermissionObjetct

 

Exemplo – 5 – Ordenação Numérica:

DECLARE @NUMREQUISICAO INT,
@LOTEINI INT

SET @NUMREQUISICAO=1
SET @LOTEINI=(SELECT MIN(LOTEPRODUCAO) FROM MOVIMENTACAO_LOTEPRODUCAO)

WHILE @LOTEINI <=(SELECT MAX(LOTEPRODUCAO) FROM MOVIMENTACAO_LOTEPRODUCAO)
Begin

IF @LOTEINI >(select max(loteproducao) from movimentacao_loteproducao)
Break
Else
Begin
BEGIN TRANSACTION

UPDATE MOVIMENTACAO_LOTEPRODUCAO
SET NUMREQUISICAO=@NUMREQUISICAO
WHERE LOTEPRODUCAO=@LOTEINI

COMMIT

SET @NUMREQUISICAO=@NUMREQUISICAO+1
End

SET @LOTEINI=(SELECT TOP 1 LOTEPRODUCAO FROM MOVIMENTACAO_LOTEPRODUCAO WHERE LOTEPRODUCAO >@LOTEINI ORDER BY LOTEPRODUCAO)
End
/****************************************************/
SELECT * FROM MOVIMENTACAO_LOTEPRODUCAO
ORDER BY NUMREQUISICAO

 

Exemplo – 6 – Stored Procedure para reorganização de Números:

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_ReorganizarNumTeste]’) and OBJECTPROPERTY(id, N’IsView’) = 1)

Drop View [dbo].[V_ReorganizarNumTeste]

 

Create View V_ReorganizarNumTeste

As

Select * From CTBaloes

Where DatePart(Month,DataTeste)=Month(GetDate())

And    DatePart(Year,DataTeste)=Year(GetDate())

/********************************************************/

Alter Procedure dbo.ReorganizarNumTeste

As

Declare @NumTeste Int, @Contador Int

Set @NumTeste=1

 

Select @Contador=Count(NumTeste) From CTBaloes

Where DatePart(Month,DataTeste)=Month(GetDate())

And    DatePart(Year,DataTeste)=Year(GetDate())

 

While @NumTeste <= @Contador

Begin

Set @NumTeste=@NumTeste+1

 

Begin Transaction

Update CTBaloes

Set NumTeste=@NumTeste

Where DatePart(Month,DataTeste)=Month(GetDate())

If @@ERROR <> 0

Begin

ROLLBACK  TRANSACTION

Break

RETURN

End

End

 

Commit Transaction

 

/********************************************************/

Exec reorganizarnumteste

/********************************************************/

Select * From CTBaloes

Where    DatePart(Year,DataTeste)=Year(GetDate())

ORDER BY NUMTESTE

 

Declare @NumTeste Int, @Contador Int

Set @NumTeste=1

 

Select @Contador=Count(NumTeste) From CTBaloes

Where DatePart(Month,DataTeste)=Month(GetDate())

And    DatePart(Year,DataTeste)=Year(GetDate())

PRINT @Contador

/********************************************************/

 

Create View V_Teste

As

Select * From CTBaloes CT

Where DatePart(Month,CT.DataTeste)=Month(GetDate())

And    DatePart(Year,CT.DataTeste)=Year(GetDate())

 

Select * From CTBaloes CT Inner Join V_Teste VT On CT.CodProduto = VT.CodProduto

Where DatePart(Month,CT.DataTeste)=Month(GetDate())

And    DatePart(Year,CT.DataTeste)=Year(GetDate())

And    CT.DataTeste=VT.DataTeste

And    CT.Maquina=VT.Maquina

/********************************************************/

Drop View V_Teste

/********************************************************/

 

SELECT * FROM V_REORGANIZARNUMTESTE

 

Exemplo – 7 – Retornando – Linhas – Claúsula – Percent:

Select top 80 percent * from produtos

 

Exemplo – 8 – Trigger para Atualização de Saldo:

 

CREATE  Trigger T_Atualizar_Saldos

On EstFisic

After Insert, Update

As

 

Set NoCount Off

 

Declare @TipoMov Char(1),

@Codigo Char(10),

@Quantidade Float(8),

@Data_Mov DateTime

 

Select @TipoMov=Status, @Quantidade=Quantidade, @Codigo=Codigo, @Data_Mov=Data From Inserted

 

Begin

 

If @TipoMov = ‘E’

Begin

Update Produtos

Set Saldo= Saldo+@Quantidade, Data=@Data_Mov

Where Codigo = @Codigo

End

 

If @TipoMov=’S’

Begin

Update Produtos

Set Saldo= Saldo-@Quantidade, Data=@Data_Mov

Where Codigo = @Codigo

End

End

 

Exemplo – 9 – Trigger para Controle e Movimentação de Saldo:

Create Table NovosProdutos
(Codigo Int Identity(1,1),
Descricao VarChar(20),
Saldo Int)
Go

Insert Into NovosProdutos Values(‘Produto -‘+Convert(VarChar(3),@@Identity),0)
Go 100

Create Table Movimentacao
(Codigo Int Identity(1,1),
CodProduto Int,
TipoMovimentacao Char(1),
Valor Int)
Go

Create Trigger T_MovimentacaoSaldo
On Movimentacao
After Insert, Update
As
Declare @TipoMovimentacao Char(1)

Select @TipoMovimentacao=TipoMovimentacao from Inserted

If @TipoMovimentacao = ‘E’
Begin
Update NovosProdutos
Set Saldo = Saldo + I.Valor
from NovosProdutos NP Inner Join Inserted I
On NP.Codigo = I.CodProduto
End

If @TipoMovimentacao = ‘S’
Begin
Update NovosProdutos
Set Saldo = Saldo – I.Valor
from NovosProdutos NP Inner Join Inserted I
On NP.Codigo = I.CodProduto
End

–Fazendo lançamentos de entrada
Insert Into Movimentacao Values(2,’E’,10)
Insert Into Movimentacao Values(2,’E’,15)
Insert Into Movimentacao Values(2,’E’,5)
Insert Into Movimentacao Values(2,’E’,22)
Insert Into Movimentacao Values(2,’E’,10)

–Fazendo lançamentos de saída
Insert Into Movimentacao Values(2,’S’,8)
Insert Into Movimentacao Values(2,’S’,5)
Insert Into Movimentacao Values(2,’S’,3)
Insert Into Movimentacao Values(2,’S’,2)
Insert Into Movimentacao Values(2,’S’,1)

Insert Into Movimentacao Values(8,’E’,10)
Insert Into Movimentacao Values(9,’E’,15)
Insert Into Movimentacao Values(1,’E’,5)
Insert Into Movimentacao Values(3,’E’,22)
Insert Into Movimentacao Values(22,’E’,10)

–Fazendo lançamentos de saída
Insert Into Movimentacao Values(8,’S’,8)
Insert Into Movimentacao Values(9,’S’,5)
Insert Into Movimentacao Values(1,’S’,3)
Insert Into Movimentacao Values(3,’S’,2)
Insert Into Movimentacao Values(22,’S’,1)

Select * from NovosProdutos

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

Agradeço a sua visita.

Até mais.

Simulando – Desastre e Recuperação de Bancos de Dados – Microsoft SQL Server 2008 R2 e 2012 – Parte IV.


Galera, bom dia.

Tudo bem?

Como prometido estou retornando com mais um post da série referente à Simulação de Desastre e Recuperação de Banco de Dados no Microsoft SQL Server 2008, R2 e 2012.

Neste post, vou destacar como podemos recuperar a estrutura física e lógica da tabela Clientes, que no post anterior acabou tendo a sua estrutura corrompida.

Você vai aprender e entender como podemos realizar este tipo de procedimento, identificando as páginas de dados que estão danificadas e quais os procedimentos devemos realizar para contornarmos este problema.

Vale ressaltar que este tipo de procedimento poderá apresentar riscos de perda de dados, sendo assim, recomendo a criação de uma ambiente de teste, com base, nos posts anteriores.

Para começar, vamos relembrar um pouco da estrutura do nosso banco de dados chamado MyDatabaseDesastre, sendo este banco, composto por uma tabela chamada Clientes, conforme apresenta a Figura 1 apresentada abaixo:

Figura1

Figura 1 – Estrutura do Banco de Dados MyDatabaseDesastre.

 

Agora que já relembramos a simples estrutura que estamos utilizando nesta série, podemos evoluir na identificação dos danos que foram causados.

 

Para começar, vou executar o Código 1, apresentado a seguir:

 

— Código 1 – Realizando um simples select na Tabela Clientes –

 

Select Codigo, Nome, Email from Clientes

 

Você poderá observar que o tempo de processamento deste simples código esta bem fora do que normalmente é executado, isso já pode ser considerado um sinal que existe algo de errado ou diferente em nosso ambiente.

 

Após alguns segundos o Management Studio conclui a execução deste bloco de código e para nosso espanto não temos o retorno dos dados, mas sim uma mensagem de erro é apresentada, conforme apresenta a Figura 2.

Recuperar-Parte4-Figura 2

 

Figura 2 – Mensagem de erro retornada pelo Management Studio.

Observe que a mensagem apresenta o código de erro 824 e nível de severidade 24, onde erros  com este nível de severidade normalmente nos indica que podemos estar se deparando com falhas de hardware relacioanadas com a mídia(disco rígido) utilizada para alocar os arquivos de nosso banco de dados, como também, podem ser considerados erros fatais, que impedem o funcionamento normal do SQL Server.

A Tabela 1 a seguir lista e descreve os níveis de severidade dos erros gerados pelo Mecanismo de banco de dados do SQL Server.

Nível de severidade Descrição
0-9 Mensagens informativas que retornam informações de status ou reportam erros que não sejam severos. O Mecanismo de Banco de Dados não gera erros de sistema com severidades de 0 a 9.
10 Mensagens informativas que retornam informações de status ou reportam erros que não sejam severos. Por razões de compatibilidade, o Mecanismo de Banco de Dados converte a severidade 10 em severidade 0 antes de retornar as informações de erro ao aplicativo de chamada.
11-16 Indica erros que podem ser corrigidos pelo usuário.
11 Indica que um determinado objeto ou entidade não existe.
12 Severidade especial para consultas que não usam bloqueio por causa de dicas de consulta especiais. Em alguns casos, operações de leitura executadas por essas instruções podem resultar em dados inconsistentes, pois os bloqueios não são usados para garantir a consistência.
13 Indica erros de deadlock de transação.
14 Indica erros relacionados à segurança, como uma permissão negada.
15 Indica erros de sintaxe no comando Transact-SQL.
16 Indica erros gerais que podem ser corrigidos pelo usuário.
17-19 Indica erros de software que não podem ser corrigidos pelo usuário. O usuário deve informar o problema ao seu administrador de sistema.
17 Indica que a instrução fez o SQL Server ficar sem recursos (como memória, bloqueios ou espaço em disco para o banco de dados) ou exceder algum limite definido pelo administrador de sistema.
18 Indica um problema no software Mecanismo de Banco de Dados, mas a instrução conclui a execução e a conexão com a instância do Mecanismo de Banco de Dados é mantida. O administrador de sistema deve ser informado sempre que uma mensagem com nível de severidade 18 ocorrer.
19 Indica que um limite do Mecanismo de Banco de Dados não configurável foi excedido e que o processo em lotes atual foi encerrado. Mensagens de erro com nível de severidade 19 ou maior pararam a execução do lote atual. Erros de severidade 19 são raros e devem ser corrigidos pelo administrador de sistema ou por seu principal provedor de suporte. Contate seu administrador de sistema quando uma mensagem com severidade de nível 19 ocorrer. Mensagens de erro com nível de severidade de 19 a 25 são gravadas no log de erros.
20-25 Indique problemas de sistema que são erros fatais, ou seja, a tarefa do Mecanismo de Banco de Dados que está executando uma instrução ou um lote que não está mais em execução. A tarefa registra informações sobre o que aconteceu e, depois, é encerrada. Na maioria dos casos, a conexão do aplicativo com a instância do Mecanismo de Banco de Dados também pode ser encerrada. Se isso acontecer, dependendo do problema, é possível que o aplicativo não consiga se reconectar.

Mensagens de erro nesse intervalo podem afetar todos os processos que acessam dados no mesmo banco de dados e indicar que um banco de dados ou objeto está danificado. Mensagens de erro com nível de severidade de 19 a 25 são gravadas no log de erros.

20 Indica que uma instrução encontrou um problema. Como o problema afetou apenas a tarefa atual, é improvável que o banco de dados tenha sido danificado.
21 Indica que foi encontrado um problema que afeta todas as tarefas no banco de dados atual, mas é improvável que o banco de dados tenha sido danificado.
22 Indica que a tabela ou o índice especificado na mensagem foi danificado por um problema de software ou hardware.

Erros de severidade de nível 22 raramente ocorrem. Se acontecer, execute o DBCC CHECKDB para determinar se outros objetos no banco de dados também foram danificados. O problema pode ser apenas no cache do buffer e não no próprio disco. Nesse caso, reiniciar a instância do Mecanismo de Banco de Dados corrige o problema. Para continuar trabalhando, você deve reconectar-se à instância do Mecanismo de Banco de Dados; caso contrário, use o DBCC para corrigir o problema. Em alguns casos, pode ser necessário restaurar o banco de dados.

Se a reinicialização da instância do Mecanismo de Banco de Dados não corrigir o problema, é porque o problema está no disco. Às vezes, destruir o objeto especificado na mensagem de erro pode resolver o problema. Por exemplo, se a mensagem informar que a instância do Mecanismo de Banco de Dados encontrou uma linha com comprimento 0 em um índice não-clusterizado, exclua o índice e crie-o novamente.

23 Indica que a integridade do banco de dados inteiro está em risco por um problema de software ou hardware.

Erros de severidade de nível 23 raramente ocorrem. Se um acontecer, execute o DBCC CHECKDB para determinar a extensão do dano. O problema pode ser apenas no cache e não no próprio disco. Nesse caso, reiniciar a instância do Mecanismo de Banco de Dados corrige o problema. Para continuar trabalhando, você deve reconectar-se à instância do Mecanismo de Banco de Dados; caso contrário, use o DBCC para corrigir o problema. Em alguns casos, pode ser necessário restaurar o banco de dados.

24 Indica uma falha de mídia. O administrador de sistema pode ter que restaurar o banco de dados. Também pode ser necessário contatar o seu fornecedor de hardware.

Tabela 1 – Relação de Níveis de Severidade existentes no Microsoft SQL Server.

Observações

  • Níveis de severidade de 19 a 25 enviam uma mensagem do SQL Server ao log de aplicativos do Microsoft Windows e disparam um alerta.
  • Eventos com níveis de severidade inferiores a 19 irão disparar alertas apenas se você tiver usado sp_altermessage, RAISERROR WITH LOG ou xp_logevent para obrigá-los a serem gravados no log de aplicativos do Windows.
  • Vale ressaltar que o Database Engine é responsável em identificar o tipo de erro e seu nível de severidade de acordo com a tabela de níveis existente no Microsoft SQL Server.

Se observarmos além do código de erro e nível de severidade, o Database Engine, nos retornou outra linha contendo mais informações técnicas identificadas pelo Query Processor, durante a execução deste bloco de código.

A seguir apresento a mensagem de erro:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x940faf8d; actual: 0x3c942ff4). It occurred during a read of page (1:153) in database ID 9 at offset 0x00000000132000 in file ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’.  Additional messages in the SQL Server error log or system event log may provide more detail.

 

This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

Olha só que legal, como o Microsoft SQL Server é uma ferramenta fantástica, moderna e inteligente e que não nos deixa na mão. Analisando de forma rápida a mensagem de erro, podemos ter um pouco mais de certeza do que esta acontecendo com nossa tabela Clientes. Para facilitar o entendimento desta mensagem e apresentar os pontos que devemos sempre analisar, vou dividir esta mensagem em algumas partes, sendo elas:

1)      SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x940faf8d; actual: 0x3c942ff4): Nesta parte da mensagem o Database Engine nos informa que estamos se deparando com uma falha lógica de consistência, que esta sendo gerada durante o processo de Input e Output.

 

2)      It occurred during a read of page (1:153) in database ID 9 at offset 0x00000000132000 in file ‘C:\Bancos\MyDatabaseDesastre\MyDatabaseDesastre.mdf’: Nesta parte da mensagem, podemos entender que o erro que ocorrendo durante a tentativa de leitura da página de dados 1:153, no offset 0x00000000132000, sendo este, offset justamente o qual foi alterado anteriormente.

 

3)      This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB): Neste trecho a mensagem informa que este tipo de erro poderá representar um risco para integridade dos nossos dados e deverá ser corrigido o quanto antes possível.

 

4)      This error can be caused by many factors; for more information, see SQL Server Books Online: Nesta última parte, o Database Engine, nos informa que esta falha pode ser ocasionada por diversas fatores. Além disso, indica que através do Books Online podemos obter mais informações sobre esta falha.

 

Muito bem, já temos um possível caminho para percorrer a fim de encontramos uma possível solução para este erro, nosso próximo passo é definirmos qual estratégia será seguida.

Como em nosso cenário temos especificamente uma única tabela que esta apresentando falhas em sua estrutura física e lógica, a alternativa inicial neste momento será utilizar o comando DBCC CheckTable.

 

Usando o comando DBCC CheckTable

Basicamente o comando DBCC CheckTable tem como finalidade avaliar e identificar a estrutura física de uma tabela ou visão indexada. Por padrão, após executar este comando, o Database Engine deverá apresentar quais são as unidades de alocação que formam as páginas de dados estão apresentando falhas.

Sendo assim, vamos fazer uso deste comando e verificar as partições que estão apresentando falhas, para isso utilizaremos o Código 2.

 

— Código 2 – Verificando as estruturas de alocação da tabela Clientes –

DBCC CheckTable (‘Clientes’) With All_Errormsgs

Após a execução do comando DBCC CheckTable, o Database Engine nos retornar o resultado do processamento realizado, com base, análise da estrutura física e lógica da nossa tabela, neste caso, teremos como resultado, informações referentes as Partition(Partições) e Alloc Unit(Unidades de Alocação) que estão apresentando falhas.

A seguir apresento o resultado gerado pelo DBCC CheckTable:

Msg 8928, Level 16, State 1, Line 1

Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data): Page (1:153) could not be processed.  See other errors for details.

 

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data), page (1:153). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

 

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data). Index node page (1:155), slot 0 refers to child page (1:153) and previous child (0:0), but they were not encountered.

 

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1061578820, index ID 1, partition ID 72057594039894016, alloc unit ID 72057594040811520 (type In-row data). Page (1:156) is missing a reference from previous page (1:153). Possible chain linkage problem.

There are 25 rows in 1 pages for object “Clientes”.

 

CHECKTABLE found 0 allocation errors and 4 consistency errors in table ‘Clientes’ (object ID 1061578820).

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (MyDatabaseDesastre.dbo.Clientes).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Observe que foram retornadas quarto códigos de mensagens de erros: 8928, 8939, 8980 e 8978, em cada uma destas mensagens são apresentadas as informações:

  • Object ID: 1061578820
  • Partition ID: 72057594039894016
  • Allocation Unit ID: 72057594040811520

Note que através destes dados teremos a capacidade de obter mais informações sobre os componentes internos que compõem e armazenam tanto a nossa tabela Clientes, como também, seus dados e índice.

 

Desta forma, para confrontar que estes elementos representam realmente a estrutura física e lógica da nossa tabela, vamos executar o código 3 apresentado abaixo.

— Código 3 – Confirmando os componentes que formam a estrutura da tabela Clientes —

Select SP.partition_id,

OBJECT_NAME(SP.object_id) As ObjectName,

SAU.type_desc,

SAU.total_pages,

SAU.data_pages,

SAU.used_pages,

SDS.name,

sds.type_desc,

SP.rows

from sys.partitions SP Inner Join sys.allocation_units SAU

On sp.partition_id = sau.container_id

Inner Join sys.data_spaces SDS

On sau.data_space_id = sds.data_space_id

Where  sp.partition_id = 72057594039894016 –> Código da PartitionID

 

Neste código estamos utilizando três visões de sistemas existentes desde a versão 2005 do Microsoft SQL Server, sendo elas:

ü  Sys.partitions: Contem uma linha para cada partição de cada tabela e índice existente no banco de dados acessado.

 

ü  Sys.Allocation_Units: Contem uma linha para cada unidade de alocação que compõem o banco de dados acessado.

 

ü  Sys.Data_Spaces: Contem uma linha para cada data space utilizado por Filegroup, Partition Schema ou FileStream que formam o banco de dados acessado.

 

Podemos observar e identificar com base resultado apresentado que nossa tabela clientes possui neste momento a seguinte estrutura:

ü  Total de Páginas da Tabela: 4;

ü  Total de Página de Dados: 2;

ü  Páginas de dados utilizadas: 4; e

ü  Quantidade de linhas da tabela: 150 (anote este valor).

Perfeito, já temos todas as confirmações, mas uma pergunta ainda esta sem resposta.

Como podemos resolver a estrutura da nossa tabela Clientes?

Se observamos as mensagens apresentadas após a execução do comando DBCC CheckTable, podemos observar que o Database Engine, através do seu mecanismo de identificação de erro nos da uma possível sugestão: “repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (MyDatabaseDesastre.dbo.Clientes).”

Show de bola, o Database Engine, realmente é fantástico, ele esta dando uma possível sugestão, utilizando o comando DBCC CheckTable com a opção Repair_Allow_Data_Loss, como uma alternativa básica para corrigir a estrutura física e lógica da nossa tabela Clientes, para isso vamos executar o Código 4.

 

Importante: Para podermos utilizar a opção Repair_Allow_Data_Loss é necessário que nosso banco de dados tenha o seu acesso restrito, por isso, se faz necessário alterar a opção Restrict Access para Single_User.

 

— Código 4 – Corrigindo a estrutura física e lógica da tabela Clientes –

Alter Database MyDatabaseDesastre

Set Single_User

Go

 

DBCC CheckTable (‘Clientes’, Repair_Allow_Data_Loss)

Go

Importante: Ao executar a opção Repair_Allow_Data_Loss, estamos dizendo para o SQL Server que aceitamos correr o risco de perder dados durante o processo de reconstrução da estrutura física e lógica da tabela Clientes.

 

Execumos o comando DBCC CheckTable, e o Database Engine nos apresentou o seguinte resultado:

DBCC results for ‘Clientes’.

There are 25 rows in 1 pages for object “Clientes”.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nossa tabela teve sua estrutura reconstruída de forma correta e agora já podmeos fazer uso da mesma, observe que a mensagem apresentada acima informa que nossa tabela possui 25 linhas de registros em uma página de dados, valor muito diferente do que tinhamos antes de executar o comando DBCC CheckTable. Para concluirmos vamos novamente executar o Código 3 apresentando anteriormente:

 

Select SP.partition_id,

OBJECT_NAME(SP.object_id) As ObjectName,

SAU.type_desc,

SAU.total_pages,

SAU.data_pages,

SAU.used_pages,

SDS.name,

sds.type_desc,

SP.rows

from sys.partitions SP Inner Join sys.allocation_units SAU

On sp.partition_id = sau.container_id

Inner Join sys.data_spaces SDS

On sau.data_space_id = sds.data_space_id

Where  sp.partition_id = 72057594039894016 –> Código da PartitionID

 

Observe que o Database Engine não encontrar nenhuma informação após a execução do nosso código.

 

Mas porque será que isso aconteceu?

 

A resposta é muito simples, durante a execução do comando DBCC CheckTable todas as unidades de alocação, partições e páginas de dados foram reconstruídas ou até mesmo, novas áres de alocação física e lógica foram criadas e atribuídas para nossa tabela.

Com isso, torna-se possível acessar a tabela Clientes, como também, a sua contagem de linhas de registro agora esta correta, para confirmarmos este novo cenário, vamos executar o Código 5 apresentado na sequência:

 

— Código 5 – Confirmando os componentes que formam a nova estrutura da tabela Clientes —

Select SP.partition_id,

OBJECT_NAME(SP.object_id) As ObjectName,

SAU.type_desc,

SAU.total_pages,

SAU.data_pages,

SAU.used_pages,

SDS.name,

sds.type_desc,

SP.rows

from sys.partitions SP Inner Join sys.allocation_units SAU

On sp.partition_id = sau.container_id

Inner Join sys.data_spaces SDS

On sau.data_space_id = sds.data_space_id

Where  sp.object_id = OBJECT_ID(‘Clientes’)

Agora sim, o Database Engine conseguiu identificar e retornar os dados sobre a nova estrutura de componentes que estão neste momento formando a nossa tabela Clientes, conforme apresenta a Figura 3.

Recuperar-Parte4-Figura 3

Figura 3 – Nova estrutura física e lógica que forma a tabela Clientes.

 

 

 

Agora esta tudo resolvido, nossa tabela esta corrigida, integra e pronta para ser utilizada, sendo assim, já podemos alterar a opção Restrict Access do nosso banco de dados, para Multi_User, para isso execute o Código 6 apresentado na sequência:

 

 

 

— Código 6 – Alterando o Restrict Acess para Multi_User —

 

 

 

Alter Database MyDatabaseDesastre

 

Set Multi_User

 

Go

 

 

 

Com isso, vou concluir mais este artigo e começar a pensar na última parte, dedicada exclusivamente ao arquivo de log do nosso banco de dados, como podemos corromper esta estrutura física e realizar o seu processo de restauração de dados.

 

Espero que você tenha gostado, estamos na reta final desta série, retorno em breve.

 

Mais uma vez obrigado por sua atenção e visita.

 

Até mais.