Dica do Mês – Comando Restore Database Page – Restaurando páginas de dados de uma tabela no Microsoft SQL Server


Olá boa tarde, que surpresa te encontrar mais uma vez no meu blog, caso esta seja a sua primeira vez, fico mais feliz ainda, seja muito bem vindo.

Este é mais um post da sessão Dica do Mês, sessão dedicada a compartilhar bimestralmente dicas, novidades, curiosidades e demais assuntos, conteúdos e informações relacionadas ao Microsoft SQL Server, Banco de Dados e Tecnologias de Banco de Dados.

No post de hoje, quero compartilhar com vocês uma das funcionalidades adicionadas ao Microsoft SQL Server a partir da versão 2016 e que recentemente acabei conhecendo com um pouco mais. 

Funcionalidade que trouxe um grande salto de qualidade ao produto, ainda mais se levarmos em consideração sua praticidade e simplicidade de uso.

Como você já pode notar no título deste post, estou me referindo a nova capacidade de recuperação de dados através do comando Restore Database em conjunto com a opção Page.

Pois bem, sem mais delongas, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Sendo assim, seja bem vindo ao post – Dica do Mês – Comando Restore Database Page – Restaurando páginas de dados de uma tabela no Microsoft SQL Server.


Introdução

Umas das tarefas mais ingratas para qualquer profissional de tecnologia, principalmente aqueles que estão diretamente relacionadas as tarefas de administração, retenção e armazenamento de dados se relaciona ao momento em que nossos ambientes começam apresentam comportamentos fora do comum ou até mesmo instabilidades. 

Quem nunca se deparou com este tipo de situação! Eu por diversas vezes passei por isso nesta minha longa estrada da vida na área de tecnologia da informação.

Mas não somente isso é importante, algo muito maior e mais preocupante podemos enfrentar, o tão temido momento de restauração de um banco de dados o chamado Restore Database, imagina então você ter que recuperar uma parte específica de uma tabela ou índice que de uma hora para outra começou a apresentar falhas e simplesmente tornou-se inacessível.

Foi justamente com base neste tipo de cenário, que o time de engenheiros da Microsoft dedicados no desenvolvimento do Microsoft SQL Server adicionaram no comando Restore Database e também no interface gráfica do Management Studio a capacidade de verificar a integridade física e lógica de uma ou mais páginas de dados, como também, a possibilidade de realizar sua restauração.

Até aqui tranquilo, nada de novidade, vamos então seguir em frente e conhecer a opção Page existente no comando Restore Database.

Tabelas e Índices

As tabelas são o coração do Microsoft SQL Server e do modelo relacional em geral, pois é onde o dado é armazenado. Cada instância de um dado na tabela representa uma entidade simples ou registro (formalmente chamado de tupla). A maioria das tabelas serão relacionadas entre si. Por exemplo: A tabela Clientes possuí um identificador único CodigoCliente que é usado como chave estrangeira no relacionamento com a tabela Pedido.

As tabelas devem ser modeladas de acordo com a teoria de banco de dados relacionais, respeitando as formas normais.

Ao criarmos nossas tabelas e índices, estamos criando internamente estrutura responsáveis em armazenar em tempo real nossos dados em áreas físicas das unidades de armazenamento de dados.

Não vou me aprofundar nos conceitos relacionados a páginas de dados, pois este não é objetivo deste post, mas sim de destacar como a Restore Database Page é importante, sua finalidade e forma de uso.

Restore Database Page

Seu objetivo é possibilitar a restauração de uma página de dados danificada sem restaurar todo o banco de dados, muito menos provocar qualquer tipo de impacto ou instabilidade no acesso aos dados após sua resturaçao.

Normalmente, as páginas que são candidatos para restauração foram marcadas como “suspeita” devido a um erro que é encontrado ao acessar a página.

As páginas suspeitas são identificadas na tabela suspect_pages no banco de dados msdb.  

Avançando mais um pouco, neste momento, já temos uma noção dos elementos básicos: Tabelas e Índices, sabemos também da estrutura que as compõem chamada de páginas de dados e de que forma estas estruturas são controladas e gerenciadas, agora vamos construir nosso cenário de testes que justamente vai nos permitir ter a visão completa de toda esta estrutura e como poderemos realizar os procedimentos de sobrescrever uma página de dados e posteriormente realizar sua restauração.

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:

  • Banco de Dados:  RestoreDatabasePage;
  • Database Recovery Model: Full;
  • Database Page_Verify: CheckSum;
  • Tabela: TabelaCorrompida; e
  • Índice Clusterizado: Ind_TabelaCorrompida_Codigo. 

Criando o ambiente

Através do Bloco de Código 1 apresentado abaixo, vamos realizar a criação dos respectivos elementos destacados anteriormente:

— Bloco de Código 1 – Criação do Ambiente —

— Criando o Banco de Dados —
Create Database RestoreDatabasePage
Go

— Acessando —
Use RestoreDatabasePage
Go

— Criando a TabelaCorrompida —
Create Table TabelaCorrompida
(Codigo Int Identity(0,2),
ValorGUID UniqueIdentifier,
ValorRandomico BigInt,
ColunaGrande Char(100) Default ‘TC’)
Go

— Criando o Índice Clusterizado na TabelaCorrompida —
Create Clustered Index Ind_TabelaCorrompida_Codigo On TabelaCorrompida(Codigo)
Go

Como nossa estrutura base pronta, chegou a hora de popular nossa tabela realizando o processo de inserção de uma aleatória massa de dados em nossa tabela, para tal, vamos utilizar o Bloco de Código 2 apresentado a seguir:

— Bloco de Código 2 – Populando a TabelaCorrompida —
— Desabilitando a contagem de linhas processadas —
Set NoCount On
Go

— Declarando a variável de controle @Contador —
Declare @Contador Int = 0

— Abrindo bloco de transação Trans1 —
Begin Transaction Trans1

While @Contador <= 132768
Begin

Insert Into TabelaCorrompida(ValorGUID, ValorRandomico)
Values (NewId(), ABS(CHECKSUM(Rand()* 200000000)))

Set @Contador += 2
End

— Confirmando e encerrando o bloco de transação Trans1 —
Commit Transaction Trans1
Go

Observação: Note que estou fazendo uso dos comandos Begin Transaction e Commit Transaction, como forma de controle e adoção de transações explícita, sendo assim, estou informando o Microsoft SQL Server quando a transação começa e deverá ser obrigatoriamente encerrada, além disso, estou evitando e isolando o processo de inserção de dados de qualquer possibilidade de bloqueio.

Neste momento, nossa tabela já esta populada “abastecida de dados”, com um total fixo de 66385 linhas de dados, denominados tecnicamente como registros lógicos.

Vamos caminhar mais um pouco, antes de realizarmos o processo de consultar a estrutura de nossas páginas de dados e posteriormente forçar sua reescrita, vamos realizar um procedimento de backup database de nosso banco de dados, procedimento importante para garantir e possibilitar a restauração das páginas, para tal utilizaremos o Bloco de Código 3 apresentado abaixo:

— Bloco de Código 3 – Backup Database —
Backup Database RestoreDatabasePage
To Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Full.bak’  — Troque para sua                                                                                                                                              unidade de disco
With Compression,
NoFormat,
Init,
Stats=10
Go

Pronto, nosso backup já esta realizado, estamos prontos e preparados para começar a brincadeira, nosso próximo passo será obter a relação das páginas de dados que forma nossa TabelaCorrompida, para isso, vamos utilizar a não documentada function sys.fn_PhysLocFormatter, solicitando ao Microsoft SQL Server a apresentação das 100 primeiras páginas de dados da nossa tabela, conforme apresenta o Bloco de Código 4:

— Bloco de Código 4 – Obtenção a relação das páginas de dados da TabelaCorrompida —
Select TOP 100 sys.fn_PhysLocFormatter(%%physloc%%) PageId,
*
FROM TabelaCorrompida
Go

A Figura 1 apresentada a seguir ilustra o resultado obtido após a execução do Bloco de Código 4:
Figura 1 – Relação das páginas de dados e seus respectivos dados.

Legal, esta ficando interessante esta brincadeira, por enquanto sem nenhum perigo!

Para que possamos realizar o processo de reescrita de uma ou mais páginas de dados, vou selecionar duas páginas (256 e 258) e seus valores para utilizar em nosso cenário, conforme a Tabela 1 apresentada abaixo:

PageID Codigo ValorGuid
(1:256:10) 20 6460AAB3-AD12-47BB-B179-8C1930B1A287
(1:258:1) 120 AEF17F9D-D838-4FEF-B723-CA3658D03319

Tabela 1 – Relação de páginas de dados e valores que iremos utilizar.

Já sabemos com quais estruturas vamos fazer o processo de reescrever suas estruturas, devemos então preparar nosso banco de dados para que nos possibilite a realização desta tarefa, desta forma, utilizaremos o Bloco de Código 5, apresentado abaixo:

— Bloco de Código 5 — Alterando a forma de acesso do banco de dados RestoreDatabasePage —

— Preparando-se para corromper a estrutura de páginas —
Use Master
Go

— Limitando a conexão do Banco de Dados para Single_User —
Alter Database RestoreDatabasePage
Set Single_User
With Rollback Immediate
Go

Ótimo, acabamos de limitar o acesso físico e lógico do nossa banco de dados para Single_User, desta forma, nenhuma outra conexão ou solicitação de acesso será permitida ao mesmo, neste momento temos acesso único e exclusivo.

O passo seguinte, consiste na consulta da estrutura da página de dados 256 e posteriormente na procura do valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 armazenado no Slot 10, vamos então executar o Bloco de Código 6, apresentado abaixo:

— Bloco de Código 6 — Obtendo as informações sobre a página de dados 256 e pesquisando valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 —

Para que possamos obter as informações de retorno apresentadas pelos comandos DBCC – Database Command Console, precisamos fazer uso do comando Dbcc TraceOn ativando a Trace Flag 3604 que orienta e informa ao Microsoft SQL Server que o mesmo deverá apresentar logo após a execução dos comandos DBCCs seus respectivos resultados.

— Obtendo informações sobre os slots de alocação de dados —
Dbcc TraceOn (3604)
Go

Seguindo nossa caminhada, vamos utilizar o comando DBCC Page, comando que vai nos possibilitar obter o conjunto de informações internas que formam a estrutura da nossa tabela, neste caso, vamos buscar toda estrutura da página de dados de número 256.

— Procurando valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 e guardar slots —
Dbcc Page (‘RestoreDatabasePage’, 1, 256, 3);
Go

A Figura 2 apresentada abaixo, ilustra uma parte da estrutura interna da página de dados 256, apresentando sua área de buffer e page hearder:
Figura 2 – Estrutura interna da página de dados 256.

Pois bem, precisamos agora procurar o valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 dentro da área de dados desta mesma página, afim de encontramos o refiro Slot 10 que armazena este dado.

Para que possamos encontrar o referido valor clique na guia de mensagens do Management Studio e preciso posteriormente a tecla de atalho CTRL + F, informando o valor na campo de busca.

A Figura 3 ilustra o 6460AAB3-AD12-47BB-B179-8C1930B1A287 localizado na estrutura interna da página de dados 256:
Figura 3 – Valor 6460AAB3-AD12-47BB-B179-8C1930B1A287 localizado.

O mesmo procedimento deverá ser feito para página 258 referente ao código 120 e ValorGuid AEF17F9D-D838-4FEF-B723-CA3658D03319.

Além disso, recomendo que você anote as informações referente OffSet e Length de dados valor pesquisado em sua referida página, pois ambos serão utilizado no procedimento de reescrita, mas como eu sou bonzinho, a Tabela 2 apresentada abaixo destaca estes valores:

Collumn Offset Length ValorGuid
2 0x8 16 6460aab3-ad12-47bb-b179-8c1930b1a287
2 0x8 16 AEF17F9D-D838-4FEF-B723-CA3658D03319

Tabela 2 – Informações sobre Offset e Length dos respectivos ValorGuid.

Agora chegou a tão esperada hora de suar o barraco (kkkk), não é bem assim, mas chegou o momento de reescrevermos a estrutura das páginas de dados: 256 e 258, através do comando DBCC WritePage declarado no Bloco de Código 7 apresentado na abaixo:

— Reescrevendo a página de dados 256 no OffSet 0x8 —
Dbcc WritePage (‘RestoreDatabasePage’, 1, 256, 8, 16, 0x00000000000000000000000000000001, 1)
Go

— Reescrevendo a página de dados 256 no OffSet 0x8 —
Dbcc WritePage (‘RestoreDatabasePage’, 1, 258, 8, 16, 0x00000000000000000000000000000001, 1)
Go

Se você conseguiu realizar o processamento destes dois comandos DBCC WritePage, isso significa que neste momento as páginas de dados 256 e 258 estão apresentando inconsistência em suas estruturas, algo que podemos comprovar através da execução do Bloco de Código 8, apresentado abaixo:

— Bloco de Código 8 – Verificando a Integridade da TabelaCorrompida —
— Alterando o acesso ao Banco de Dados para Multi_User —
Alter Database RestoreDatabasePage
Set Multi_User
Go

— Realizar testes de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

Select Count(Codigo) From TabelaCorrompida
Go

Ao realizarmos o comando Select Count() para tentarmos contar a quantidade de linhas de registros existentes na TabelaCorrompida, o Management Studio nos retorna a seguinte mensagem de erro:
Msg 824, Level 24, State 2, Line 162
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x4bd220eb; actual: 0xcb53a034). It occurred during a read of page (1:256) in database ID 11 at offset 0x00000000200000 in file ‘S:\MSSQL-2017\Data\RestoreDatabasePage.mdf’. Additional messages in the SQL Server error log or operating system error 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.

Vamos avançar mais ainda, estamos nos aproximando do final deste post, agora que nosso ambiente esta danificado podemos fazer uso da opção Page existente no comando Restore Database que vai nos permitir restaurar a estrutura física e lógica da nossa tabela, sendo assim, vamos utilizar o Bloco de Código 9, apresentado abaixo:

— Bloco de Código 9 – Iniciando o processo de restauração e recuperação das páginas de dados —
— Realizando a Restauração das Páginas de Dados —
Use Master
Go

— Restore Database Page —
Restore Database RestoreDatabasePage
PAGE=’1:256, 1:258′ — Informando os números de páginas
From Disk = N’S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Full.bak’
With File = 1, — Especificando o arquivo de dados
NoRecovery, — Não liberando o banco para acesso
Stats = 10
Go

 

Perfeito, realizamos o procedimento se restauração das páginas de dados 256 e 258 sem restaurar toda estrutura do nosso banco, agora podemos realizar um novo teste e verificar se a a estrutura da nossa TabelaCorrompida encontra-se funcional, conforme apresenta o Bloco de Código 10 a seguir:

— Bloco de Código 10 — Realizando um novo teste de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

Select Count(Codigo) From TabelaCorrompida
Where Codigo Not Between 20 And 120
Go

E para nossa surpresa o Management Studio retornou mais uma vez outra mensagem de erro:
Msg 829, Level 21, State 1, Line 186
Database ID 11, Page (1:256) is marked RestorePending, which may indicate disk corruption. To recover from this state, perform a restore.

Esta mensagem nos informa que não podemos realizar o acesso a TabelaCorrompida pois neste momento a página 256 esta marcado como pendente de restauração, este é um comportamento normal apresentado pelo SQL Server, pois o mesmo depende da realização de um backup de log e posteriormente da restauração (conhecido como Tail Log) para realizar a limpeza e desmarcar esta página de dados como pendente.

Para tal procedimento, utilizaremos o Bloco de Código 11, apresentado abaixo:

— Bloco de Código 11 — Realizando Backup Log e Restore Log (Tail Log) —
— Backupear o Log e Restaura para Liberar páginas marcadas como pendentes —
Use Master
Go

Backup Log RestoreDatabasePage
To Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Log.bak’
With NoFormat,
Init,
Name = N’RestoreDatabasePage-Backup-Log’,
Stats=10
Go

— Restaurar Log —
Restore Log RestoreDatabasePage
From Disk = ‘S:\MSSQL-2017\Backup\RestoreDatabasePage-Backup-Log.bak’
With Recovery,
Replace,
Stats = 10
Go

Acredito que o procedimento de Backup Log e Restore Log tenha ocorrido normalmente, basta agora realizar o último teste de acesso a TabelaCorrompida para poder consultar todos os dados armazenados na mesma, conforme apresenta o Bloco de Código 12:

— Bloco de Código 12 — Realizar último teste de integridade consultando dados na TabelaCorrompida —
Use RestoreDatabasePage
Go

A Figura 4 apresentada abaixo ilustra a massa de dados existente na TabelaCorrompida, após o procedimento de restauração e recuperação das páginas de dados: 256 e 258.
Figura 4 – Relação de dados existentes na TabelaCorrompida, recuperados após o procedimento de Restore Database Page.

— Obtendo a quantidade de registros armazenados na TabelaCorrompida —
Select Parcial=(Select Count(Codigo) From TabelaCorrompida Where Codigo Not In (20,120)),
Geral=(Select Count(Codigo) From TabelaCorrompida)
Go

Show de bola, muito bom, conseguimos, seguimos todos os passos desde a criação do nosso ambiente, inserção de dados, identificação das páginas e suas estrutura, reescrita na estrutura das páginas e o tão esperado procedimento de restauração.

Com isso chegamos ao final de mais um post da sessão Dica do Mês, antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:


Referências

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-pages-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

https://www.mssqltips.com/sqlservertip/1925/how-to-use-the-sql-server-sysfnphyslocformatter-undocumented-function/

https://blogs.msdn.microsoft.com/fcatae/2016/04/12/dbcc-page/

https://docs.microsoft.com/pt-br/sql/t-sql/database-console-commands/dbcc-transact-sql

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-New-%28undocumented%29-physical-row-locator-function.aspx

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/12/13/more-undocumented-fun-dbcc-ind-dbcc-page-and-off-row-columns/

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

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2018/07/26/dica-do-mes-ocultando-uma-instancia-em-execucao-do-microsoft-sql-server/

https://pedrogalvaojunior.wordpress.com/2018/04/25/dica-do-mes-sql-operations-studio-view-as-chart/

https://pedrogalvaojunior.wordpress.com/2018/03/14/dica-do-mes-microsoft-sql-server-2017-sql-graph-databases/

https://pedrogalvaojunior.wordpress.com/2018/01/24/dicadomes-sqlservertoolsuiteintroduction/

Conclusão

Como já destaquei em outros posts, a cada nova versão, atualização e correção a Microsoft transforma o SQL Server em um produto surpreende, ainda mais na sua capacidade e versatilidade de permitir aos profissionais de tecnologia, administradores de bancos de dados, programadores, entre outros, utilizar recursos nativo e também os não documentados oficialmente como um elemento capaz de se superar e sobreviver a  inúmeras falhas ou situações de perdas de dados.

No post de hoje, mais uma vez este foi constatado, a possibilidade através do comando DBCC Page de se obter informações sobre as páginas de dados, o comando DBCC WritePage (muito cuidado com ele) sensacional na sua funcionalidade em permitir uma reescrita de dados na estrutura das páginas que formam uma tabela, e principalmente a não documentada function sys.fn_physLocFormatter que de forma simples, fácil e confiável nos apresenta a distribuição de páginas de dados que compõem nossas tabelas em conjunto com os respectivos slots que armazenam nosso dados.

Acredito que você tenha conseguido entender e observar como consultamos a estrutura de páginas, a forma que alteramos seu conteúdo forçando uma reescrita de dados e depois como conseguimos através do comando Restore Database Page recuperar estas áreas.

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 a 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 no mês de dezembro.

Um forte abraço, sucesso, até mais…

Anúncios

Short Scripts – Setembro 2018


Buenos dias, bom dia, comunidade….

E ai como passaram os últimos meses?

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

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

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

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

O post de hoje

Normalmente compartilho os principais scripts armazenados nos últimos meses, no post de hoje vou fazer um pouco diferente, recentemente tive a necessidade de realizar alguns atividades relacionadas as Split de dados e Collation, sendo assim, decide então compartilhar alguns códigos que estejam envolvidos com estes assuntos e que também se vinculem com outros, dentre os quais destaco:

  • Collate Column,
  • Collate,
  • Collation,
  • Database Collate,
  • Database Collation,
  • Instâncias Microsoft SQL Server,
  • Junção de Tabelas,
  • Page Split,
  • Server Collation,
  • Servidores Microsoft SQL Server,
  • String Split,
  • User Defined Function, e
  • Valores separados por vírgulas.

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

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

Short Scripts

— Short Script 1  – Identificando Page Split – Método 1  —
SELECT cntr_value
FROM sys.sysperfinfo
WHERE counter_name =’Page Splits/sec’ AND
OBJECT_NAME LIKE’%Access methods%’
Go

— Short Script 2  – Identificando Page Split – Método 2  —
SELECT object_name,
counter_name,
instance_name,
cntr_value, cntr_type

FROM sys.dm_os_performance_counters
WHERE counter_name =’Page Splits/sec’ AND
OBJECT_NAME LIKE’%Access methods%’
Go

— Short Script 3  – Criando um User Defined Function para separação de Strings por vírgula  – Método 1 —
CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(@TEXT1 varchar(8000), @COLUMN tinyint, @SEPARATOR char(1))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @POS_START int = 1
DECLARE @POS_END int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

WHILE (@COLUMN >1 AND @POS_END> 0)
BEGIN
SET @POS_START = @POS_END + 1
SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
SET @COLUMN = @COLUMN – 1
END

IF @COLUMN > 1 SET @POS_START = LEN(@TEXT) + 1
IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1

RETURN SUBSTRING (@TEXT, @POS_START, @POS_END – @POS_START)
END
Go

SELECT
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 1, ‘-‘) AS PREFIX,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 2, ‘-‘) AS REGISTRATION_GROUP,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 3, ‘-‘) AS REGISTRANT,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 4, ‘-‘) AS PUBLICATION,
dbo.UFN_SEPARATES_COLUMNS(@ISBN, 5, ‘-‘) AS [CHECK]
Go

— Short Script 4  – Criando um User Defined Function para separação de Strings por vírgula – Método 2  —

Create FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end – @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)

END
RETURN
END

Select * from dbo.fnSplitString(‘Querying SQL Server’,”)
Go

— Short Script 5  – Informando o Collate de uma coluna na cláusula Where  —
S
elect campo
From tabela
Where campo collate Latin_General_CI_AS like ‘%á%’
Go

— Short Script 6  – Informando o Collate na declaração de uma coluna e na cláusula Where  —
Select Campo collate Latin_General_CI_AS
From tabela
Where Campo collate Latin_General_CI_AS like ‘%á%’
Go

— Short Script 7  – Alterando o Collate de um Banco de Dados  —
Alter Database MosaicoClient
Collate SQL_Latin1_General_CP1_CI_AI
Go

— Short Script 8  – Alterando o Collate de uma Coluna  —
Alter Table Users
Alter Column [Password] Varchar(12) Collate SQL_Latin1_General_CP1_CS_AS
Go

— Short Script 9  – Identificando o Server Collation  – Método 1 —
Select SERVERPROPERTY (‘collation’)
Go

— Short Script 10 – Identificando o Server Collation  – Método 2 —
Exec sp_helpsort
Go

— Short Script 11 – Identificando o Database Collation —
Select DatabasePropertyEx(‘MRP’,’collation’)
Go

— Short Script 12 – Identificando o Column Collation – Método 1 —
Exec sp_help ‘Produtos’
Go

— Short Script 13 – Identificando o Column Collation – Método 2 —
Select * from Sys.columns
Where Name = ‘Produtos’
Go

— Short Script 14 – Identificando o Column Collation – Método 3 —
Select * from Information_schema.columns
Where Table_Name = ‘Produtos’
Go

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


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

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

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

Links

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

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

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

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

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

Agradecimento

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

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

Abraços…

Material de Apoio – Agosto 2017


Boa tarde pessoal!

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

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

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

O post de hoje

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

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

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

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

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

Material de Apoio

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

 

 

 

 

 

 

 

 

 

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

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

Links

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

Agradecimento

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

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

Um forte abraço.

#13 – Para que serve


Muito boa noite galera, tudo bem?

Noite de sábado, temperatura agradável, galera curtindo uma pizza, balada entre outras coisas e eu estou aqui para compartilhar com você mais um post da minha sessão Para que serve, hoje o post de número 13. Você esta pensando, post de número 13 não é nada muito “ospicioso” como diária um personagem de novela (kkkkk).

Que nada vamos em frente não se preocupe com este número, tenho a certeza que este post será muito legal e apresentará informações de alto astral relacionada ao novo Microsoft SQL Server 2016.

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar ou demonstrar como  códigos de exemplo, aplicativos, utilitários, enfim recursos relacionados diretamente á banco de dados ou gerenciadores de bancos de dados podem ser utilizados como uma possível solução de problemas, bem como, orientar na sua forma de utilização.

Após esta tradicional saudação, chegou a hora de falar sobre o #13 – Para que serve de hoje, tenho a certeza que você vai gostar.

No post de hoje, vou a destacar uma das mais aguardados melhorias relacionadas ao SQL Server, estou me referindo a capacidade de consultor os histogramas de estatísticas de processamento de forma programada, isso mesmo, agora a partir da nova atualização cumulativa do SQL Server 2016 SP1, conhecida como Cumulative Update 2, temos duas novas DMF – Dynamic Management Function – Função de Gerenciamento Dinâmico que nos permitem de forma direta através do uso do comando Select obter informações sobre os histogramas e dados estatísticos.

Vou fazer um pequeno suspense, não vou revelar o nome de ambas as DMFs, somente no decorrer deste post você vai conhece-las.

Muito bem, após deixar este gostinho de quero mais, chegou a hora de conhecer estas novas funcionalidades e ver como podemos aplicar isso no nosso ambiente.

Como aqui o #13 – Para que serve – Uma nova e mais fácil maneira de obter informações sobre o histograma de estatísticas no Microsoft SQL Server 2016 SP1 –

Introdução

Quando se referimos a estatísticas de bancos de dados, estatísticas de processamento ou estatísticas de consumo de operadores do plano de execução, estamos na verdade se referindo ao bom e velho conceito de estatísticas, o qual devemos voltar no tempo para entender melhor se realmente quisermos saber a importância deste assunto, para este post este não é o foco, na verdade o que eu quero é mostrar que a partir da nova atualização cumulativa aplicada para o Service Pack 1 do SQL Server 2016 os times de engenheiros e desenvolvedores do SQL Server introduziram no produto duas novas DMF denominadas sys.dm_db_stats_histogram e sys.dm_db_stats_properties, onde através do uso destas novas DMFs podemos obter todas as informações relacionadas as estatísticas de processamento de nossas querys e principalmente o histograma de maneira mais rápida, fácil e principalmente legível, pois particularmente falando ler o histograma através do comando DBCC Show_Statistics não era nada fácil(kkkkk).

Vamos conhecer um pouco mais sobre cada DMF para entender melhor seu funcionamento:

sys.dm_db_stats_histogram: Retorna o histograma de estatísticas para o objeto de banco de dados especificado (tabela ou exibição indexada) no atual SQL Server banco de dados. Semelhante ao DBCC SHOW_STATISTICS WITH HISTOGRAM.

Ao executar esta nova DMF o Microsoft SQL Server 2016 apresentará uma tabela de resultado contendo o seguinte conjunto de colunas, conforme a Tabela 1 ilustra:

Nome da coluna

Column name
Tipo de dados Description
object_id int ID do objeto (tabela ou exibição indexada) para o qual as propriedades do objeto de estatísticas serão retornadas.
stats_id int ID do objeto de estatísticas. É exclusiva na tabela ou exibição indexada. Para obter mais informações, veja sys.stats.
step_number int O número da etapa do histograma.
range_high_key sql_variant Valor da coluna associada superior de uma etapa do histograma. O valor da coluna também será denominado um valor de chave.
range_rows real Número estimado de linhas cujo valor de coluna fica dentro de uma etapa do histograma, excluindo-se o limite superior.
equal_rows real Número estimado de linhas cujo valor de coluna é igual ao limite superior da etapa do histograma.
distict_range_rows bigint Número estimado de linhas com um valor de coluna distinto dentro de uma etapa do histograma, excluindo-se o limite superior.
average_range_rows real Número médio de linhas com valores de colunas duplicados em uma etapa de histograma, exceto o limite superior (RANGE_ROWS / DISTINCT_RANGE_ROWS para DISTINCT_RANGE_ROWS > 0).

sys.dm_db_stats_properties: Retorna propriedades de estatísticas para o objeto de banco de dados especificado (tabela ou exibição indexada) no banco de dados do SQL Server atual. Para tabelas particionadas, consulte a DMF sys.dm_db_incremental_stats_properties.

Ao executar esta nova DMF o Microsoft SQL Server 2016 apresentará uma tabela de resultado contendo o seguinte conjunto de colunas, conforme a Tabela 2 ilustra:

Nome da coluna Tipo de dados Description
object_id int ID do objeto (tabela ou exibição indexada) para o qual as propriedades do objeto de estatísticas serão retornadas.
stats_id int ID do objeto de estatísticas. É exclusiva na tabela ou exibição indexada. Para obter mais informações, veja sys.stats.
last_updated datetime2 Data e hora da última atualização do objeto de estatísticas.
rows bigint O número total de linhas da tabela ou exibição indexada na última atualização das estatísticas. Se as estatísticas forem filtradas ou corresponderem a um índice filtrado, o número de linhas talvez seja menor do que o número de linhas na tabela.
rows_sampled bigint O número total de linhas amostradas para cálculos de estatísticas.
etapas int O número de etapas no histograma. Para obter mais informações, veja DBCC SHOW_STATISTICS.
unfiltered_rows bigint O número total de linhas da tabela antes da aplicação da expressão de filtro (para estatísticas filtradas). Se as estatísticas não forem filtradas, unfiltered_rows será igual ao valor retornado na coluna de linhas.
modification_counter bigint Número total de modificações da coluna de estatísticas principal (a coluna em que o histograma é criado) desde que as últimas estatísticas de tempo foram atualizadas.

Essa coluna não mantém informações para tabelas com otimização de memória.

Agora que o segredo foi revelado, podemos começar a pensar na maneira que estas novas DMFs podem ser utilizadas, para tal vamos fazer uso do banco de dados analítico: AdventureworksDW2016CTP3 disponível para download através do link: http://www.microsoft.com/en-us/download/details.aspx?id=49502

Utilizando as novas DMFs

Seguindo em frente vamos começar nossa prática, para tal a primeira coisa a fazer é executar o bloco de código 1 declarado abaixo, antes clique no botão Include Actual Execution Plan em seu Management Studio, pois vamos realizar uma análise após a execução.

— Bloco de Código 1 —

Figura 1 – Instrução select declarada para o bloco de código 1.

Após a execução deste bloco de código obtemos o seguinte conjunto de dados relacionados ao operador Clustered Index Scan, conforme a Figura 2 apresentada abaixo:

Figura 2 – Dados relacionadas ao operador Clustered Index Scan.

Note que estou destacando na figura os dados referentes aos seguintes elementos:

  • Number of Rows Read;
  • Actual Number of Rows;
  • Estimated Number of Rows; e
  • Estimated Number of Rows to be Read.

Você pode estar se perguntando, o porque o Junior Galvão acabou destacados estes valores na Figura 2? A resposta é muito simples, uma das maneiras para tentar entender o comportamento do SQL Server no processamento de seus operadores e procurar ter uma ideia de estatísticas de processamento é justamente através da leitura e entendimento destes quatro conjunto de dados, o que posso dizer que não é a melhor forma para se encontrar informações sobre processamento e estatísticas.

Agora imagine que todas as vezes que você desejar obter informações sobre as estatísticas de processamento e como elas estão armazenadas e seus status, pois bem, é justamente neste ponto que agora no novo SQL Server 2016 SP1 CU 2 você terá facilmente a capacidade de fazer isso acontecer, para tal vamos executar o bloco de código 2 fazendo uso da nova DMF, sys.dm_db_status_histogram.

— Bloco de Código 2 —

Figura 3 – Bloco de código 2.

Observe que estamos fazendo uso da nova DMF sys.dm_db_status_histogram e neste momento nosso Management Studio deverá ter retornado um conjunto de linhas conforme a Figura 4 abaixo ilustra:

Figura 4 – Conjunto de dados estatísticas referentes ao processamento do bloco de código 2.

Ao analisarmos a Figura 4 podemos notar facilmente o conjunto de linhas de retornado contendo todas as informações relacionadas ao histograma da estatísticas de número 2 para a tabela [dbo].[FactResellerSales]. Tenho a certeza que você tão surpreso quanto eu quando executei pela primeira vez este mesmo bloco de código, realmente é assustador a facilidade que temos agora em entender o histograma.

Sensacional, mas como o SQL Server consegui apresentar estes dados desta maneira? Como de costume a resposta é simples, através da capacidade de utilizar em tempo de execução uma Table Valued Function denominada DM_DB_STATS_HISTOGRAM, ou seja, uma função que armazena valores em uma determinada tabela utilizada especificamente para esta nova DMF, a comprovação disso esta na Figura 5 que ilustra o plano de execução utilizado para o processamento do bloco de código 2.

Figura 5 – Plano de execução gerado para o processamento do bloco de código 2.

Continuando nossa jornada, o próximo passo é fazer uso da outra DMF, no caso a sys.dm_db_stats_properties, onde a qual vamos nos permitir obter o mesmo conjunto de valores referente ao cabeçalho da estatística o mesmo realizado através do comando DBCC SHOW_STATISTICS com a opção WITH STATS_HEADER.

Vamos então executar o bloco de código 3 apresentado a seguir:

Figura 7 – Bloco de código 3.

E qual será o resultado obtido após o processamento do bloco de código 3? A resposta é apresentada na Figura 7 a seguir:

Figura 7 – Resultado do processamento do bloco de código 3.

Show de bola, temos exatamente o mesmo conjunto de dados retornados pela DMF sys.dm_db_stats_properties da mesma forma que teríamos se estivéssemos utilizando do bom e velho DBCC SHOW_STATISTICS, não é realmente fantástico, só de imaginar a capacidade de possibilidades que teremos de utilizar estes dados a partir de agora realmente é algo surreal.

Da mesma forma que o SQL Server 2016 SP1 CU2 utiliza uma Table Valued Function para armazenar e apresentar os consumidos e coletados pelo processamento da sys.dm_db_status_histogram, também é utilizada uma outra Table Valued Function para o processamento da sys.dm_db_stats_properties denominada DM_DB_STATS_PROPERTIES.

Para finalizar nossa brincadeira e mostrar como estas novas funcionalidades podem nos ajudar, vamos utilizar o bloco de código 4 para através dele conseguir especificar uma determinada range_key existe em nossas estatísticas. Poxa vida especificar em um comando select qual determinada faixa de valores estatísticas nós queremos obter dados realmente é acima do que estávamos pensando, por incrível que isso possa parecer, é totalmente possível de ser feito a partir de agora.

— Bloco de Código 4 —

Figura 8 – Retorno de dados referentes ao filtro da faixa de valores.

Putz, que coisa louco, meu deus, temos com base no bloco de código 4 a comprovação que podemos através do uso de outras DMFs inline retornado dados estatísticos com base em filtros ou predicados declarados na cláusula where existente na linha 26 onde, a coluna sh.range_high_key é justamente uma coluna pertencente a nova DMF sys.dm_db_stats_histogram.

Que loucura isso, fora de série esta nova capacidade do SQL Server, fantástico, inimaginável, fora do comum o que o time de engenheiros do SQL Server fizeram desta vez, show.

Referências

https://msdn.microsoft.com/library/mt794645.aspx

https://blogs.msdn.microsoft.com/sql_server_team/easy-way-to-get-statistics-histogram-programmatically/

https://support.microsoft.com/en-us/help/4013106/cumulative-update-2-for-sql-server-2016-sp1

http://msdn.microsoft.com/library/jj553546.aspx

http://msdn.microsoft.com/library/ms174384.aspx

https://msdn.microsoft.com/pt-br/library/mt761751.aspx

https://msdn.microsoft.com/pt-br/library/ms177623.aspx

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2017/01/23/12-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/12/16/11-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/11/15/10-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/10/08/09-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

A maneira como nossos dados estão constantemente sendo processados é algo que a cada dia um DBA ou profissional de banco de dados se pergunta. Saber em qual momento uma determinada query, transação ou simplesmente um comando select pode ocasionar algo tipo de impacto em nosso ambiente ainda é mais preocupante. Foi justamente pensando nisso que a Microsoft e seu time de profissionais que trabalham com o SQL Server buscaram responder a partir da disponibilidade das duas novas DMFs: sys.dm_db_stats_histogram e sys.dm_db_stats_properties recursos adicionados na versão 2016 SP1 e disponível também para próximas versão do SQL Server, dentre elas a SQL Server vNext.
Esta nova maneira de acessar e consultar os dados coletados e armazenados no histograma poderá ajudar em muito os profissionais de banco de dados e desenvolvedores a entender como seus estatísticas de processamento de dados estão sendo afetadas com base nos processos de manipulação.
Neste post você pode mais uma vez observar que o Microsoft SQL Server esta em constante evolução, um dos produtos mais prestigiados pela Microsoft, buscando sempre trazer melhorais e inovações, algo de extrema importância para qualquer profissional que trabalha com esta tecnologia.

Agradecimentos

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

Nos encontramos em breve, até lá…..

Short Scripts – Dezembro 2016


Buenas tardes….. Comunidade.

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

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

O post de hoje

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

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

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

Short Scripts

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

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

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

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

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

Links

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

Agradecimento

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

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

Microsoft SQL Server 2016 e String_Split(), agora ficou fácil dividir uma string.


Fala galera, boa tarde, segunda – feira, eita dia complicado, começo de semana é tenso, pois saber que mais um final de semana passou voando é triste. Vamos em frente e pensar que mais um final de semana está chegando, é bem melhor.

Seguindo a onda de informações, anúncios, posts, entre outras formas de divulgação sobre o Microsoft SQL Server 2016, hoje vou destacar mais um pouco no meu blog sobre esta nova versão e destacar mais uma das suas novidades, estou me referindo a nova Table Value Function String_Split(). Algo que realmente era muito pedido pelos desenvolvedores e que a Microsoft demorou um pouco para reconhecer a sua importância, mas na versão 2016 ela está presente e será muito útil.

 

A String_Split()

Pode-se dizer que é uma daquelas funções desejadas por todos os profissionais que trabalham com desenvolvimento e necessitam em algum momento realizar o chamado split de uma string. Se você não sabe ou conhece este termo, split pode ser entendido como fatiar, dividir, cortar, quebrar em pedaços.

Fazendo uma analogia, a string_split() vai fazer exatamente isso com uma string, transformando a mesma em pequenas outras strings (substrings ou partes de uma string).

Funcionalidade ou capacidade considerado por muitos como algo realmente complexo se der feito por um SGBD – Sistema Gerenciador de Banco de Dados, ainda mais para o SQL Server se pensarmos que até a versão 2014 tínhamos a necessidade de customizar este tipo de necessidade.

Mas que para nossa alegria a Microsoft introduziu este recurso na versão RC0 sinal que provavelmente e o que tudo indica a mesma vai fazer parte da versão final do SQL Server 2016.

 

Compatibilidade

Por se tratar de uma nova funcionalidade, até o presente momento a documentação oficial da Microsoft indica que esta função é compatível com a versões:

  • Microsoft SQL Server 2016; e
  • Azure Database.

As versões Azure SQL Data Warehouse e Parallel Data Warehouse até o momento não estão na lista de produtos compatíveis com a esta nova função.

Observação: Um detalhe muito importante é a necessidade do nível de compatibilidade do banco de dados estar definido na versão 130, sendo este o nível do SQL Server 2016.

 

Sua importância. O porquê ela pode ajudar

A String_Split() vem para preencher uma lacuna muito grande deixada pelo tipo de desenvolvimento e engenheiros da Microsoft desde a versão 2008 e 2012, onde novas funções para se trabalhar com string foram adicionadas no produto.

Sua importância vai muito além da capacidade técnica de permitir que uma string seja dividida em pequenas partes e posteriormente armazenada em uma tabela, variável ou função, ela vai com certeza flexibilidade e facilitar em muito a maneira com que os dados podem ser tratados e reconhecidos pelo SQL Server o que poderá permitir a criação de novos padrões de reconhecimento de caracteres.

Uma das grandes vantagens de se utilizar a String_Split() está relacionada com a uma simples sintaxe, onde requer basicamente dois parâmetros.

 

Como utilizar a String_Split()

Criada para ser utilizada de maneira rápida e simples, a String_split é composta pode dois parâmetros string compatíveis com os tipos de dados: (nvarcharvarcharnchar ou char) para a string que desejamos supostamente dividir em conjunto com o caractere reconhecido como “divisor” ou “separador”, que também deve ser informado em um tipo de string compatível com os tipos de dados: nvarchar(1)varchar(1)nchar(1) ou char(1)).

Outra característica muito interessante desta função é a maneira que os dados são apresentados e retornados para usuário, onde podemos ter o retorno do split da string em uma coluna com diversas linhas representando os fragmentos “pedaços” ou “partes” da string, como também um valor caractere no tipo de dados nchar ou nvarchar de acordo com o tipo de dado utilizado, respeitando o tamanho do dado identificado no momento da fragmentação da string. Caso não seja reconhecido o tipo de dados original da string, o resultado da sua fragmentação será retornado com o tipo de dados varchar.

Exemplos

A seguir você vai poder encontrar alguns exemplos de como podemos fazer uso da função String_Split(). Vale ressaltar que esta função se encontra disponível a partir da versão RC0, requerendo a configuração o nível de compatibilidade para o número 130.

Alguns dos exemplos apresentados aqui foram elaborados e executados no banco de dados de exemplo AdventureWorks2016 CTP3, caso venha tenha interesse em realizar o download desta sample database, utilize o link: https://www.microsoft.com/en-us/download/details.aspx?id=49502

Outro detalhe importante a ser destacado é o resultado apresentado para cada exemplo ilustrado a seguir, por padrão o SQL Server gera uma coluna chamada value contendo a lista de valores fragmentados após o processo de split da string.

 

 

— Exemplo 1 – Separando de forma simples uma string –

SELECT *

FROM STRING_SPLIT(‘Junior,Galvão,MVP,SQL Server’,’,’)

Go

 

Resultado

value
Junior
Galvão
MVP
SQL Server

 

— Exemplo 2 – Fazendo uso de variáveis como parâmetros de entrada de valores –

DECLARE @string VARCHAR(100) = ‘Microsoft,SQL Server,2016,RC0′,

@separador CHAR(1) =’,’

 

SELECT *

FROM STRING_SPLIT(@string,@separador)

Go

 

Resultado

value
Microsoft
SQL Server
2016
RC0

 

— Exemplo 3 – Armazenando o resultado da divisão de uma string em uma nova tabela –

DECLARE @string VARCHAR(100) = ‘Microsoft,SQL Server,2016,RC0′,

@separador CHAR(1) =’,’

 

SELECT * INTO #SplitTable

FROM STRING_SPLIT(@string,@separador)

GO

 

— Visualizando a estrutura da tabela —

sp_Columns #SplitTable

Go

 

— Consultando os dados da tabela —

Select * from #SplitTable

Go

 

Após executar a system stored procedure sp_columns podemos notar que o tamanho e tipo de dados da coluna value criada através do select…into foi definido como Varchar() sendo este o tipo de dados padrão utilizado pela String_Split() para garantir compatibilidade no armazenamento e apresentação de dados oriundos de uma outra tabela.

 

— Exemplo 4 – Apresentando a mensagem quando o separador de string for definido com mais de um caracter —

DECLARE @string VARCHAR(100) = ‘pedrogalvaojunior#@gmail#@com’,

@separador CHAR(2) =’#@’

 

SELECT * FROM STRING_SPLIT(@string,@separador)

Go

 

Como pode ser observado o Microsoft SQL Server 2016 vai lançar e apresentar uma mensagem de erro com o código 214 informando que a quantidade de caracteres ou melhor dizendo que o tamanho utilizado para o parâmetro separador foi definido acima de um caractere, sendo que, este parâmetro só identifica e reconhece um único caractere.

Resultado

Msg 214, Level 16, State 11, Line 3

Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

 

— Exemplo 5 – Apresentando o comportamento da String_Split() quando um parâmetro apresenta valor nulo –

SELECT * FROM STRING_SPLIT(‘pedrogalvaojunior,wordpress,com’,NULL)

Go

 

Para este exemplo 5 o comportamento do SQL Server 2016 é exatamente o mesmo do exemplo 4, onde será apresentanda uma mensagem de erro informando que o tamanho informado no parâmetro separado, foi definido acima de um caractere.

Resultado

Msg 214, Level 16, State 11, Line 3

Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

 

— Exemplo 6 – Realizando o split de uma string com base na junção de uma tabela com a função String_Split() –

— Criando a tabela Split —

Create Table Split

( SplitId INT IDENTITY (1,1) NOT NULL,

SplitValue1 NVARCHAR(50),

SplitValue2 NVARCHAR(50))

GO

 

— Inserindo linhas de registro —

INSERT INTO Split (SplitValue1, SplitValue2)

VALUES (‘Pedro’,’Galvão’),

(‘Junior’,’Galvão’),

(‘Antonio’,’Silva’),

(‘Chico’,’Bento’)

Go

 

— Realizando a Junção da Tabela Split com a função Split_String() —

Select SplitId, SplitValue1, SplitValue2, Value

From Split S Inner Join String_Split(‘Pedro,Antonio’,’,’) STS

On S.SplitValue1 = STS.Value

Go

 

Observe que o SQL Server realizou o split dos dados com base na junção e valores informados para a função e mesmo assim apresentou os dados dados de cada coluna que compõem a estrutura da tabela Split.

 

Resultado

SplitID SplitValue1 SplitValue2 Value
1 Pedro Galvão Pedro
3 Antonio Silva Antonio

 

— Exemplo 7 – Apresentando o resultado quando ambos os parâmetros vazios –

Select * from String_Split(‘ ‘,’,’)

Go

 

Após executa o select acima o SQL Server identificou que ambos os parâmetros encontra-se vazios, sem nenhum tipo de dado que permita fazer a identificação da string e do separador, sendo assim o resultado retornado será uma única linha de registro vazia.

 

Resultado

Value

 

— Exemplo 8 – Apresentando o comportamento da String_Split() quando o caracter do final da string é o mesmo utilizado como separador –

SELECT * FROM STRING_SPLIT(‘Conhecendo,SQL Server,2016,’,’,’)

Go

 

Este é um cenário bem interessante e pode ocorrer a qualquer momento, nesta situação o SQL Server 2016 vai trabalhar da mesma forma que os outros exemplos, analisando e identificando a string e posteriormente fazendo a fragmentação com base no caractere separador, mesmo que este seja um caractere utilizado no final da string.

Note que que criada 4 linhas de registro sendo que a última não apresentará valores por será reconhecida e tratada como uma linha nula ou em branco.

Resultado

value
Conhecendo
SQL Server
2016

 

Legal, legal, acredito que agora ficou ainda mais fácil de entender e compreender como a String_Split() é fácil de ser utilizado e principalmente a maneira que os dados são apresentando e retornado para usuário. Desta forma, vou encerrar mais este artigo por aqui, quero também aproveitar para disponibilizar abaixo uma relação de links sobre os principais posts referentes ao SQL Server 2016 que estou desde 2015 publicando.

 


 

 

Links

Caso você não tenha acessado os posts anteriores dedicados ao Microsoft SQL Server 2016, não perca tempo e a oportunidade de conhecer mais sobre esta nova versão acessando os links abaixo:

https://pedrogalvaojunior.wordpress.com/2015/12/28/microsoft-sql-server-2016-e-json-uma-combinacao-bem-interessante-final/

https://pedrogalvaojunior.wordpress.com/2015/11/09/microsoft-sql-server-2016-e-json-uma-combinacao-bem-interessante-parte-i/

https://pedrogalvaojunior.wordpress.com/2015/10/02/conhecendo-o-live-query-statistics-no-microsoft-sql-server-2016/

https://pedrogalvaojunior.wordpress.com/2015/07/10/microsoft-sql-server-2016-lista-de-novidades-parte-i/

https://pedrogalvaojunior.wordpress.com/2015/07/16/microsoft-sql-server-2016-lista-de-novidades-parte-ii/

https://pedrogalvaojunior.wordpress.com/2015/07/30/microsoft-sql-server-2016-lista-de-novidades-final

 

Conheça o SQL Server 2016 RC0: Faça download da versão RC0 disponibilizada a poucos dias acessando: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016. Acesse também da documentação oficial Microsoft sobre a String_Split(): https://msdn.microsoft.com/en-us/library/mt684588.aspx

 


 

Conclusão

Como você pode observar uma das mais esperadas funcionalidades foi adicionada ao SQL Server, a capacidade de fragmentar, dividir, fatiar uma string em pequenas partes ou substrings. Recurso mais que necessário e importante para qualquer desenvolver ou profissional de banco de dados que necessita analisar um texto “string” e através de um caractere denominado separador delimitar como pode ser gerada fragmentos deste texto.

A cada novo build liberado pela Microsoft podemos observar e notar o grande trabalho e esforço que esta sendo feito para transformar o Microsoft SQL Server 2016 na maior e principal versão do produto desde o grande salto dado em 2005 com o lançamento naquele momento da versão 2005.

Funções similares ao String_Split() estão sendo cada vez mais disponibilidades e adicionadas ao produto como forma de fazer com que o SQL Server se torne uma plataforma única de desenvolvimento, administração, armazenamento e gestão de banco de dados, isso representa uma grande evolução e atenção das equipes de desenvolvimento e engenheiros do produto em atender e satisfazer a comunidade técnica que se dedica a estudar e conhecer cada vez mais o SQL Server.

Acredito que os exemplos apresentados aqui conseguir mostrar a simplicidade de se trabalhar com esta nova funcionalidade, ilustrando sua simplicidade no uso e forma de obter os resultados.

Mais uma vez agradeço a sua atenção, seu interesse em visitar o meu blog, espero encontra-lo em outras oportunidades. Deixe seus comentários, críticas e sugestões.

Até a próxima.

Dica do Mês – Função Next Value For


Olá, galera, bom dia.

Estamos chegando ao final do primeiro mês de 2016, nossa janeiro já esta indo embora. Como eu havia prometido no início deste ano, dentro do possível vou tentar trazer novas sessões para o meu blog e hoje falo do lançamento ou melhor o retorno de uma delas que vou batizar de “Dica do Mês“, é isso mesmo, no final de cada vez vou trazer uma simples dica relacionada a algo que utililizei em minhas atividades profissionais e acadêmicas.

O objetivo desta sessão como seu próprio nome diz, é compartilhar com você leitor ou seguidor do meu blog, uma simples dica que poderá fazer toda diferença no seu dia-á-dia ou simplesmente mostrar como utilizar um determinado recurso, funcionalidade e produto existente no SQL Server.

Neste primeiro post, quero destacar uma função muito legal que foi introduzida no Microsoft SQL Server 2012 e que recentemente em diversas dúvidas nos fóruns brasileiros de SQL Server acabou sendo destaque como solução. Estou me referindo ao função Next Valeu For fazendo uma tradução ao pé da letra podemos dizer: Próximo Valor para.

 

Sobre Next Value For –  Função que possui a característica possibilitar a geração de uma sequência de números para uma objeto específico, neste caso, o objeto que refiro é Sequence Object, funcionalidade também introduzida a partir do Microsoft SQL Server 2012, em conjunto com diversas novidades relacionadas a geração de sequência de valores numéricos.

A Next Value For é reconhecida como uma função não-determinística, desta maneira o sua utilização é somente permitida em conjunto com o objeto sequence, sendo este responsável pela geração da sequência numérica repassada para a função.

 

Sintaxe – A função Next Value For possui uma forma de uso bastante simples, uma das suas particularidades é permitir o uso da função Over como opção para geração de valores dentro da sequência.

  • Exemplo da Sintaxe: NEXT VALUE FOR [ database_name . ] [ schema_name . ]  sequence_name
    [ OVER (<over_order_by_clause>) ]

 

Argumentos – Com base em sua sintaxe podemos identificar quatro argumentos que formam sua estrutura de código, sendo eles:

  • database_name – Representa o nome do banco de dados que possui o objeto sequence;
  • schema_name –  Representa o nome do schema que contém o objeto sequence;
  • sequence_name – Representa o nome do objeto sequence responsável em armazenar e garantir a sequência numérica
  • Over_order_by_clause – Determina a ordem de organização e apresentação dos valores das linhas dentro de uma faixa de valores gerados pelo objeto sequence.

 

Exemplos: Vou destacar algumas maneiras de como utilizar a função Next Value For:

— Exemplo 1- Utilizando a função Next Valeu For para mostrar dois valores em sequência —

CREATE SCHEMA Test;
GO

CREATE SEQUENCE Test.Contador
START WITH 1
INCREMENT BY 1 ;
GO

SELECT NEXT VALUE FOR Test.Contador AS FirstUse;
SELECT NEXT VALUE FOR Test.Contador AS SecondUse;

Go

 

— Exemplo 2 – Utilizando a função Next Valeu For em conjunto com variável —

DECLARE @var1 bigint = NEXT VALUE FOR Test.Contador
DECLARE @var2 bigint ;
DECLARE @var3 bigint ;

SET @var2 = NEXT VALUE FOR Test.Contador ;

SELECT @var3 = NEXT VALUE FOR Test.Contador;
SELECT @var1 AS myvar1, @var2 AS myvar2, @var3 AS myvar3 ;
GO

 

— Exemplo 3 – Utilizando a função Next Valeu For em conjunto com constraint Default —

CREATE TABLE Test.MyTable
(
IDColumn nvarchar(25) PRIMARY KEY,
name varchar(25) NOT NULL) ;
GO

CREATE SEQUENCE Test.CounterSeq
AS int
START WITH 1
INCREMENT BY 1 ;
GO

ALTER TABLE Test.MyTable
ADD DEFAULT N’AdvWorks_’ + CAST(NEXT VALUE FOR Test.CounterSeq AS NVARCHAR(20)) FOR IDColumn;
GO

INSERT Test.MyTable (name) VALUES (‘Junior’) ;
INSERT Test.MyTable (name) VALUES (‘Galvão’) ;

INSERT Test.MyTable (name) VALUES (‘SQL’) ;
INSERT Test.MyTable (name) VALUES (‘Server’) ;
SELECT * FROM Test.MyTable;
GO

 

— Exemplo 4 – Utilizando a função Next Value For com ranking windows function —

Use AdventureWorks2012

Go

SELECT NEXT VALUE FOR Test.Contador OVER (ORDER BY LastName) AS ListNumber,
FirstName, LastName
FROM Person.Contact ;
GO


 

Muito bem galera, é isso, este é o primeiro post da nova sessão Dica do Mês, simples, direto, rápido e prático, espero que você tenha gostado.

Não deixe de me visitar, amanhã vou retornar com mais uma novidade, uma nova sessão chamada “Para que serve“, por enquanto uma surpresa.

Mais uma vez obrigado, nos encontramos novamente nos próximos posts.

Até mais.

Microsoft SQL Server 2016 e JSON uma combinação bem interessante – Final


Bom dia, comunidade, segunda – feira, alias estamos na última semana de 2015, eita ano complicado, repleto de surpresas não tão boas, mas que esta se encerrando de forma razoável.

Para encerrar este ano, estou retornando com a parte final desta pequena série de posts dedicadas exclusivamente a esta novidade bastante útil e interessante que foi introduziada no Microsoft SQL Server 2016, como você pode observar no título estou me referindo ao suporte para uso do JSON em um ambiente rodando o SQL Server 2016.

Caso você não tenha acessado o post anterior sobre esta novidade ou outros posts dedicados ao Microsoft SQL Server 2016, não perca tempo e a oportunidade de conhecer mais sobre esta nova versão acessando os links abaixo:

https://pedrogalvaojunior.wordpress.com/2015/11/09/microsoft-sql-server-2016-e-json-uma-combinacao-bem-interessante-parte-i/

https://pedrogalvaojunior.wordpress.com/2015/07/10/microsoft-sql-server-2016-lista-de-novidades-parte-i/

https://pedrogalvaojunior.wordpress.com/2015/07/16/microsoft-sql-server-2016-lista-de-novidades-parte-ii/

https://pedrogalvaojunior.wordpress.com/2015/07/30/microsoft-sql-server-2016-lista-de-novidades-final/

 

Caso você já tenha acessado a primeira parte desta série, deverá ter observado que destaquei de forma bastante apronfudada como o JSON se tornou uma tecnologia tão presente e utilizada atualmente no desenvolvimento de software, além disso, apresentei um pouco da sua história e a visão do time de produto do SQL Server perante a comunidade.

Nesta segunda parte, conforme prometido vou destacar um pouco como podemos trabalhar com funções específicas do JSON e de que forma podemos criar uma indexação de dados existentes dentro do formato JSON.

Mais uma vez obrigado por sua visita, espero que este post possa ser útil e traga algum tipo de interesse e curiosidade em conhecer mais sobre o Microsoft SQL Server.

 

O JSON e suas funções

Como toda novidade, e isso também não poderia ser diferente para o JSON, os time de desenvolvimento e engenheiros do SQL Server, adicionaram neste nova versão um conjunto de funções internas exclusivas para se trabalhar com o JSON, sendo estas:

  • ISJSON: Definida e reconhecida como uma função JSON Text, a ISJOSON tem como finalidade verificar se o texto que esta sendo passado no formato JSON pode ser reconhecido como um tipo de dados NVARCHAR. Através desta função é possível podemos implementar restrições(constraints) em colunas com forma de verificação de dados. Importante destacar que esta função esta disponível a partir da versão SQL Server 2016 Community Technology Preview 3.2
  • JSON_Values: Definida e reconhecida como uma função JSON Text, a JSON_Value, possui um papel importante quando trabalhamos com dados JSON, que consiste basicamente na análise e extração de um possível valor escalar que representa um determinado caminho específico reconhecido pelo padrão JavaScript, como forma de obter e reconhecer todo caminho necessário para se obter um valor JSON. Para identificação deste caminnho, torna-se encessário utilizar algo similar a uma sintaxe JavaScript para fazer referência a propriedades em texto JSON. Alguns exemplos são:
  1. ‘$’ – faz referência a todo objeto JSON no texto de entrada;
  2. ‘$.property1’ – referências property1 no objeto JSON;
  3. ‘$[5]’ – referencia 5-th elemento na matriz JSON;
  4. $. property1.property2.array1 [5].property3.array2 [15] .property4 ‘ – referências complexa Propriedade aninhada no objeto JSON.
  • O sinal de cifrão ($) representa o objeto de entrada JSON (semelhante ao iniciar / em XPath). Você pode adicionar qualquer JavaScript como referências de propriedade/matriz após o item de contexto para fazer referência a qualquer propriedade de aninhados. Importante destacar que esta função esta disponível a partir da versão SQL Server 2016 Community Technology Preview 3.2
  • JSON_Query: A função JSON_Query também é uma função texto que possui a finalidade de reconhecer e extrair um determinado valor “objeto” existente no formato JSON, sendo este um único ou um conjunto de caracteres.
  • OPENJSON: Uma das mais importantes funções, a OPENJSON irá fornecer a capacidade de analisar o texto JSON e retornar propriedades como uma tabela relacional usando a função de valor de tabela interna OPENJSON. Através da utilização da OPENJSON podemos ter explicitamente definido o esquema de resultados ou esquema padrão será usada. OPENJSON fornece uma visão de conjunto de linhas sobre um documento JSON. Porque o OPENJSON é um provedor de conjunto de linhas, que pode ser usado em instruções Transact-SQL em qual conjunto de linhas de uma tabela, visão ou até mesmo em conjunto com funções similares ao OPENROWSET.

 

 

Como indexar documentos e dados JSON

Quando a Microsoft anuncio o suporte ao JSON no SQL Server, muitos se perguntavam e ainda devem estar se perguntando. “Como será possível realizar a indexação ou algum tipo de indexação de dados no formato JSON?”

Esta pode parecer uma pergunta complicado de se responder ou até mesmo imaginar que possa existir uma resposta, mas como grande surpresa e reconhecimento da capacidade profissional do time de desenvolvimento do SQL Server, existe sim um resposta, nada muito diferente do que já estamos acostumados a utilizar no SQL Server.

A capacidade de se trabalhar com índices de diferentes formas e tipos de dados é algo que de longe não existem em nenhum outro SGBD – Sistema Gerenciador de Banco de Dados, característica singular do SQL Server, desde o reconhecimento de forma nativa de índices XML adicionados ao Microsoft SQL Server 2005 e posteriormente os fantásticos índices de cobertura presentes no Microsoft SQL Server 2008 R2, sem se esquecer do ColumnStore Index criados a partir da versão 2012.

Para que possamos fazer uso de índices para nossos dados no formato JSON, inicialmente não precisamos criar nada de diferente em relação a outras situações, basta através do comando Create Index criar um novo índice especificando qual ou quais colunas deverão fazer parte deste índice. A principal diferença é que o uso do índice no momento em que nossa query for executada esta atrelado a declaração da coluna JSON_Value, sendo esta condição para que o query optimizer possa ser notificado e posteriormente venha a reconhecer o uso ou não deste elemento de pesquisa e ordenação de dados.

Observação: Vale ressaltar que todo e qualquer índice será utilizado em uma operação de busca de dados, principalmente se o query optimizer entender a sua necessidade. No caso de um índice com base em dados JSON, a função JSON_Valeu tem que reconhecer de forma idêntica a coluna e caminho no formato JSON para que seja possível obter o dado de uma maneira mais ágil e eficiente.

Agora que você já conhece um pouco sobre as funções específicas para uso do JSON e também a forma maneira que os índices para tipos de dados JSON podem ser utilizados, vamos seguir em frente e brincar um pouco com alguns exemplos apresentados abaixo.

Exemplos

Para estes exemplos, vou utilizar o mesmo cenário apresentado na primeira parte. Vou utilizar novamente minha Virtual Machine criada no Microsoft Azure, rodando o SQL Server 2016 CTP3 (versão lançada em 28/10/2015 – http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-everything-built-in.aspx) e AdventureWorks2016CTP3 disponível para o 2016, caso você queira montar o mesmo ambiente rodando stand-alone, faça download do SQL Server 2016 e AdventureWorks, ou saber mais sobre esta nova versão acesse:

  1. http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/28/sql-server-2016-everything-built-in.aspx
  2. https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016
  3. https://azure.microsoft.com/en-us/marketplace/partners/microsoft/sqlserver2016ctp3evaluationwindowsserver2012r2/
  4. http://www.microsoft.com/en-us/download/details.aspx?id=49502

Vamos por a mão na massa como eu sempre digo aos meus alunos: “Quem não pratica, não aprende”. Desta forma, let’s go:

— Exemplo 1 – Utilizando a função ISJSON –

CREATE TABLE MyTable(

Id int,

jsonCol1 varchar(MAX) CHECK (ISJSON(jsonCol1)>0),

jsonCol2 varchar(4000));

ALTER TABLE MyTable

ADD CONSTRAINT jsonCol2_is_json CHECK (ISJSON(jsonCol2) > 0);

GO

 

SELECT id, json_col

FROM tab1

WHERE ISJSON(json_col) > 0

GO

 

— Exemplo 2 – Utilizando a função JSON_VALUE –

Declare @VarJSON NVarchar(400)

Set @VarJSON =

N'{

“info”:{

“type”:1,

“address”:{

“town”:”Bristol”,

“county”:”Avon”,

“country”:”England”

},

“tags”:[“Sport”, “Water polo”]

},

“type”:”Basic”

}’

 

SELECT FirstName, LastName,

JSON_VALUE(jsonInfo, ‘$.info.address[0].town’) AS Town

FROM Person.Person

WHERE JSON_VALUE(jsonInfo, ‘$.info.address[0].state’) like ‘US%’

ORDER BY JSON_VALUE(jsonInfo, ‘$.info.address[0].town’)

Go

 

— Exemplo 3 – Utilizando a função JSON_Query –

Declare @jsoninfo nvarchar(400)

Set @jsoninfo =

N'{

“info”:{

“type”:1,

“address”:{

“town”:”Bristol”,

“county”:”Avon”,

“country”:”England”

},

“tags”:[“Sport”, “Water polo”]

},

“type”:”Basic”

}’

 

SELECT FirstName, LastName,

JSON_QUERY(@jsoninfo, ‘$.info.address’) AS Address

FROM Person.Person

ORDER BY LastName

Go

Figura-1-JSON

Figura 1 – Utilizando a função JSON_Query.

— Exemplo 4 – Utilizando a função OPENJSON –

SELECT * FROM OPENJSON(‘[“Brasil”,

“United Kingdom”,

“United States”,

“Índia”,

“Singapore”,

“Marrocos”,

“Suriname”]’)

Go

Figura-2-JSON

Figura 2 – Utilizando a função OPENJSON.

— Exemplo 5 – Utilizando a função OPENJSON para transformar JSON Texto em uma relational Table –

Declare @JSalestOrderDetails NVarchar(4000)

 

Set @JSalestOrderDetails =

‘{“OrdersArray”: [

{“Number”:1, “Date”: “8/10/2012”, “Customer”: “Adventure works”, “Quantity”: 1200},

{“Number”:4, “Date”: “5/11/2012”, “Customer”: “Adventure works”, “Quantity”: 100},

{“Number”:6, “Date”: “1/3/2012”, “Customer”: “Adventure works”, “Quantity”: 250},

{“Number”:8, “Date”: “12/7/2012”, “Customer”: “Adventure works”, “Quantity”: 2200}

]}’

 

SELECT Number, Customer, Date, Quantity_

FROM OPENJSON (@JSalestOrderDetails, ‘$.OrdersArray’)

WITH (

Number varchar(200),

Date datetime,

Customer varchar(200),

Quantity int

) AS OrdersArray

Go

Figura-3-JSON

Figura 3 – Transformando um JSON Text em tabela relacional.

— Exemplo 6 – Trabalhando com índices e dados JSON —

CREATE TABLE SalesOrderRecord

( Id int PRIMARY KEY IDENTITY,

OrderNumber NVARCHAR(25) NOT NULL,

OrderDate DATETIME NOT NULL,

JOrderDetails NVARCHAR(4000),

Quantity AS CAST(JSON_VALUE(JOrderDetails, ‘$.Order.Qty’) AS int),

Price AS JSON_VALUE(JOrderDetails, ‘$.Order.Price’))

GO

CREATE INDEX idxJson ON SalesOrderRecord(Quantity) INCLUDE (Price);

Go

 

Conclusão

O Microsoft SQL Server 2016 esta chegando e com ele uma série de novas funcionalidades, melhorias e inovações estão sendo apresentadas, entre estes itens o JSON é um dos mais aguardados.

A tecnologia JSON criada inicialmente como uma forma de se consumir dados de maneira mais simples em comparação ao formato XML, torna-se uma referência mundial e isso fez com que a Microsoft e seu tipo de engenheiros do SQL Server entende-se a necessidade de adicionar este recurso ou melhor dizendo funcionalidade ao produto.

Nesta parte final, você pode conhecer mais sobre o JSON, como é possível indexar um documento ou dados em formato JSON, o uso de funções próprias para se trabalhar com este tecnologia, bem como, o SQL Server consegui tratar, interpretar e apresentar estes dados.

Espero que você tenha gostado deste artigo, que as informações compartilhadas aqui possam lhe ajudar, mostrando como o Microsoft SQL Server esta a cada dia se tornando um ambiente completo de Gerenciamento de Banco de Dados totalmente compatível com diversas tecnologias e linguagens de programação.

 

Agradecimentos,

Agradeço a todos que estiveram visitando meu blog neste ano de 2015, com certeza a presença de vocês é de total importância, mais uma vez conseguir obter um número expressivo de visitantes e leitores, sem falar no número de seguidores que constantemente cresce. Espero em 2016 poder contar ainda mais com a sua presença.

Desejo um Feliz Ano Novo, repleto de Paz, Saúde, Alegria, Conquistas e Realizações.

Nos encontramos em breve.

Até a próxima.

Short Scripts – Agosto 2015


Boa tarde, Comunidade!!!!

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

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

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

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

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

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

 

— Short Script 1 –  Uitlizando cláusula Output em Delete com Inner Join + Select —

DECLARE @t TABLE

(nDex INT IDENTITY(1,1),

valu VARCHAR(9),

keey UNIQUEIDENTIFIER)

INSERT @t

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

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

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

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

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

(‘k’,NEWID())

DELETE t

OUTPUT DELETED.*

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

ON b.ndex = t.nDex

GO

 

 

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

DECLARE @s VARCHAR(8000), @d VARCHAR(10)

SET @s = ‘separar por espaço em branco’

SET @d = ‘ ‘

;WITH split(i,j) AS

(

SELECT i = 1, j = CHARINDEX(@d, @s + @d)

UNION ALL

SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split

WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0

)

SELECT SUBSTRING(@s,i,ji)

FROM split

 

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

USE TempDB;

— Criando a Tabela Funcionário —

CREATE TABLE Funcionario

(ID int primary key,

NomeFunc varchar(200),

DataNasc date,

DataADM date);

Go

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

SELECT * from Funcionario

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

Go

— Criando um novo índice chamado Ind_NC_Funcionario_DataADM —

CREATE Nonclustered Index Ind_NC_Funcionario_DataADM On Funcionario (DataADM);

Go

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

SELECT * from Funcionario

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

Go

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

SELECT * from Funcionario with (index=Ind_NC_Funcionario_DataADM)

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

Go

— Consulta 4 – Utilizando realmente o índice IND_NC_Funcionario_DataADM —

SELECT ID, DataADM

From Funcionario

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

Go

 

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

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

returns int as

begin

declare @Concatenado varchar(5);

set @Concatenado= space(0);

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

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

order by N desc;

return Cast(@Concatenado as int);

end;

go

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

 

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

CREATE TYPE NotNullType FROM VARCHAR(10) NOT NULL;

GO

— table create

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

GO

— insert

INSERT INTO Test(TestId) VALUES(1);

SELECT NullTest FROM Test;

 

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

DECLARE @a INT= 0;

DECLARE @b INT= 0;

 

CREATE TABLE #tmpPrice ( Value INT );

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

SELECT @b = Value FROM #tmpPrice;

SELECT @a AS, @b AS b

 

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

CREATE FUNCTION fatorial

(

@n INT

)

RETURNS INT

AS

BEGIN

DECLARE @val INT;

WITH fat(f, n) AS

(

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

UNION ALL

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

UNION ALL

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

FROM fat

WHERE n < 20 AND n <> 0

)

SELECT @val = f

FROM fat

WHERE n = @n

RETURN @val

END

GO

— Testando a função

SELECT dbo.fatorial(3);

SELECT dbo.fatorial(4);

SELECT dbo.fatorial(7);

 

— Short Script 8 –  CTE – Calculando fatorial —

WITH fat(f, n) AS

(

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

UNION ALL

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

UNION ALL

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

FROM fat

WHERE n < 20 AND n <> 0

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

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

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

)

SELECT f

FROM fat

WHERE n = 12

 

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

CREATE EVENT SESSION [Blocked] ON SERVER

ADD EVENT sqlserver.blocked_process_report

ADD TARGET package0.event_file

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

GO

ALTER EVENT SESSION [Blocked]

ON SERVER

STATE = start;

GO

 

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

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

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

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

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

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

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

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

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

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

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

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

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

from

(select convert(xml,event_data) event_data

from

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

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

 

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

Nos encontramos em breve.

Até a próxima…..

Short Script – Maio – 2015


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

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

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

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

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

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

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

 

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

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

 

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

 

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

 

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

DECLARE @AA VARCHAR(12) = ‘AAA’

,@XXX           VARCHAR(12) = ‘AAA’

,@YYY           VARCHAR(12) = ‘BBB’

,@ZZZ           VARCHAR(12) = ‘CCC’

,@AAXXX         VARCHAR(12) = ‘XXAAA’

,@AAYYY         VARCHAR(12) = ‘XXBBB’

,@AAZZZ         VARCHAR(12) = ‘XXCCC’

,@XXXCount       INT = 1

,@YYYCount       INT = 1

,@ZZZCount       INT = 1

,@AAXXXCount     INT = 1

,@AAYYYCount     INT = 1

,@AAZZZCount     INT = 1

,@Result       INT;

 

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

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

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

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

— is also assigned an integer value.

SET @Result = 0;

 

IF @AA = @XXX

BEGIN

IF @XXXCount > @AAXXXCount SET @Result = 2

END

ELSE IF @AA = @YYY

BEGIN

IF @YYYCount > @AAYYYCount SET @Result = 2

END

ELSE IF @AA = @ZZZ

BEGIN

IF @ZZZCount > @AAZZZCount SET @Result = 2

END

ELSE IF @AA = @AAXXX

BEGIN

IF @XXXCount = @AAXXXCount SET @Result = 3

END

ELSE IF @AA = @AAYYY

BEGIN

IF @YYYCount = @AAYYYCount SET @Result = 3

END

ELSE IF @AA = @AAZZZ

BEGIN

IF @ZZZCount = @AAZZZCount SET @Result = 3

END

 

— Identify the equivalent logic from the options shown:

— Option 1:

SET @Result = 0;

 

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

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

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

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

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

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

 

SELECT [Option 1:]=@Result

SELECT @Result = NULL;

 

 

— Option 2:

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

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

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

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

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

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

ELSE SET @Result = 0;

 

SELECT [Option 2:]=@Result

SELECT @Result = NULL;

 

— Option 3:

SELECT @Result = CASE @AA

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

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

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

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

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

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

END;

 

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

SELECT @Result = NULL;

 

— Option 4:

SELECT @Result = CASE @AA

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

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

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

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

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

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

ELSE 0 END;

 

SELECT [Option 4:]=@Result

SELECT @Result = NULL;

 

— Option 5:

SELECT @Result = CASE @AA

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

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

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

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

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

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

ELSE 0 END;

 

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

 

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

 

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

 

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

 

DECLARE @Xml XML = N’

<products>

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

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

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

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

</products>’;

 

SELECT

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

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

FROM

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

 

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

 

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

 

 

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

 

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

 

— Q1

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON a.a = x.x

AND a.b = x.y;

 

— Q2

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

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

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

 

 

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

 

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

 

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

 

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

FROM sys.tables t

INNER JOIN sys.indexes i

INNER JOIN sys.index_columns ic

INNER JOIN sys.columns c

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

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

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

GO

 

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

 

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

 

  • Operador Intersect

 

CREATE TABLE A ( x INT )

 

INSERT INTO A

VALUES ( 18 ),

( 20 ),

( 4 ),

( 1 ),

( 2 )

 

CREATE TABLE B ( Y INT )

 

INSERT INTO B

VALUES ( 1 ),

( 20 ),

( 2 ),

( 3 )

 

SELECT X AS ‘Intersecting’

FROM A

INTERSECT

SELECT Y

FROM B

 

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

 

Select

object_name(ddips.object_id) [Table_name],

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

ddips.page_count [Pages],

ddips.compressed_page_count

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

  • Transações aninhadas

 

CREATE Table TranTable(col varchar(3))

GO

 

BEGIN TRAN A

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

BEGIN TRAN B

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

SAVE TRAN B

BEGIN TRAN C

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

COMMIT TRAN C

ROLLBACK TRAN B

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

COMMIT TRAN A

GO

 

Select col from TranTable

GO

 

Drop TABLE TranTable

GO

 

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

 

— Exemplo 1 —

 

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

 

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

SET @bcpCommand = ‘bcp “SELECT

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

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

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

FROM Sales.Customers

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

 

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

 

print(@bcpCommand)

 

EXEC master..xp_cmdshell @bcpCommand

 

 

 

— Exemplo 2 —

Declare @Contador Int,

@FileName varchar(50),

@bcpCommand varchar(2000)

 

Set @Contador = 1

 

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

Begin

 

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

”ContactName” AS ”Parameter/name”,

ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”,

CompanyName AS ”Parameter/value”

FROM Sales.Customers

Where IDdaSuaTabela = @Contador

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

 

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

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

 

EXEC master..xp_cmdshell @bcpCommand

 

Set @Contador += 1

End

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

Até mais.

Material de Apoio – Outubro – 2014


Hello Community, boa tarde.

Estou retornando com mais um post para vocês, dedicado exclusivamente a compartilhar meus Scripts e Códigos de Exemplo que utilizo no meu dia á dia, bem como, nas minhas aulas e treinamentos.

Como de costume você vai pode compartilhar este material com seus contatos, como também, deixar seu comentário, dúvida, sugestão e crítica. O principal objetivo do meu blog é compartilhar o conhecimento entre todos aqueles que gostam de conhecer e aprender sobre Banco de Dados, além disso, mostrar que muitas vezes a solução para uma determinada dúvida ou problema pode ser obtida de forma fácil.

A relação de hoje apresenta os seguintes recursos:

  • Alter Authorization;
  • Conversão de Valores Decimais;
  • Criação e Exclusão de Foreing Keys;
  • Função Scalar;
  • Tipo de Dados no formato de Tabela;
  • Trigger.

Vale ressaltar que os arquivos estão renomeados com a extensão .docx por questões de compatibilidade com o WordPress, após realizar o download, basta remover a extensão e utilizar o Script em seu ambiente. Todos os scriptos e seus códigos de exemplo foram testados a partir do Microsoft SQL Server 2005, talvez alguns pequenos ajustes ou alterações possam ser necessárias.

A seguir você poderá apresento a relação de Scripts:

Agradeço a sua visita, espero que tenho gostado.

Nos encontramos em breve.

Criando Funções e Esquemas de Particionamento de Dados para oferecer possíveis ganhos desempenho e distribuição de dados no Microsoft SQL Server 2008 e Microsoft SQL Server 2008 R2.


Pessoal, bom dia.

Estou de volta, com mais um artigo. Hoje vou falar um pouco de Particionamento de Dados no Microsoft SQL Server 2008 e Microsoft SQL Server 2008 R2, através do Partition Function e Partition Scheme.

Recentemente venho recebendo de alguns contatos, questionamentos sobre como podemos tentar melhorar o desempenho de nossos servidores SQL Server, de uma forma, que não impacte na estrutura interna de aplicações que estão fazendo acesso aos dados.

A princípio veio a minha mente utilizar o particionamento do banco de dados em diversos Filegroups, algo que normalmente eu utilizo e consigo mensurar e observar melhoras de desempenho.

Por outro lado, também pensei em utilizar particionamento horizontal de tabelas, fazendo com o banco de dados, cresça no seu tamanho de tabelas, mas isso vai impactar diretamente na aplicação.

Então, me deparei com a possibilidade de utilizar o Particionamento de Dados através do uso de Funções e Esquemas, o que poderia aliar ainda o uso de outros Filegroups.

Muito bem, um dos cenários mais atraentes para se utilizar este tipo de técnica, consiste na identificação de tabelas que possuem um volume grande de manipulações de dados, como também, sua quantidade de registro cresce constantemente, passando de centenas de dados, milhares ou bilhões de linhas de registros em pouco tempo.

Como de costumo vou destacar um pouco das finalidades e funcionalidades destes recursos, inicialmente pela Função de Particionamento.

Partition Function (Funções de Particionamento): Cria uma função no banco de dados atual que mapeia as linhas de uma tabela ou índice em partições com base nos valores de uma coluna especificada. Usar a instrução CREATE PARTITION FUNCTION é a primeira etapa na criação de uma tabela particionada ou índice.

No momento da criação uma função de particionamento, temos a necessidade de especificar o lado, sendo ele: Left ou Right, que determinada à forma de controle para alocação do dado em uma partição.

Ao especificar qual lado de cada intervalo de valor de limite, esquerdo ou direito, os valores devem pertencer. Estamos definindo a forma com que os intervalos serão classificados pelo Mecanismo de Banco de Dados em ordem crescente da esquerda para a direita. Se não for especificado, LEFT é o padrão.

O escopo de uma função de partição está limitado ao banco de dados no qual ele for criado. No banco de dados, as funções de partição residem em um namespace separado das outras funções.

Quaisquer linhas cuja coluna de particionamento tenha valores nulos serão colocadas na partição mais à esquerda, a menos que NULL seja especificado como um valor de limite e RIGHT sejam indicados. Nesse caso, a partição mais à esquerda será uma partição vazia e os valores NULL serão colocados na partição seguinte.

Qualquer uma das permissões a seguir pode ser usada para executar CREATE PARTITION FUNCTION:

  • Permissão ALTER ANY DATASPACE. Essa permissão tem como padrão os membros da função de servidor fixa sysadmin e das funções de banco de dados fixas db_owner e db_ddladmin.
  • Permissão CONTROL ou ALTER no banco de dados no qual a função de partição está sendo criada.
  • Permissão CONTROL SERVER ou ALTER ANY DATABASE no servidor do banco de dados no qual a função de partição está sendo criada.

Partition Scheme (Schemas de Particionamento): Cria um esquema no banco de dados atual que mapeia as partições de uma tabela particionada ou índice para grupos de arquivos.

Uma função de partição deve primeiramente ser criada em uma instrução CREATE PARTITION FUNCTION antes de criar um esquema de partição.

Ao criarmos um Esquema de Particionamento, temos a obrigatoriedade de vincular as áreas de particionamento e alocação de dados, de acordo com os limites especificados, como faixa de valores na Função de Particionamento. Neste caso, se especificamos três limites de valores em nossa função, deveremos utilizar quatro áreas de alocação de dados em nosso esquema, para podermos armazenados todos os possíveis limites de dados.

Para criar um Esquema de particionamento, através do comando CREATE PARTITION SCHEME, devemos ter uma das seguintes permissões:

  • Permissão ALTER ANY DATASPACE. Esta permissão tem como padrão os membros da função de servidor fixa sysadmin e das funções de banco de dados fixas db_owner e db_ddladmin.
  • Permissão CONTROL ou ALTER no banco de dados no qual o esquema de partição está sendo criado.
  • Permissão CONTROL SERVER ou ALTER ANY DATABASE no servidor do banco de dados no qual o esquema de partição está sendo criado.

Agora que conhecemos um pouco do que uma Função de Particionamento e um Esquema de Particionamento devem realizar, vamos agora, começar a criar o nosso ambiente, para colocarmos em uso estes dois recursos.

Vale ressaltar que somente a Edição Enterprise do Microsoft SQL Server 2005 em diante tem a capacidade de trabalhar com Funções e Esquemas de Particionamento de Tabelas e Índices.

Começamos pela criação do nosso banco de dados, vou definir o nome deste Banco como Particionamento, conforme apresenta o Código 1.

— Código 1 – Criando o Banco de Dados –

Create Database Particionamento

ON PRIMARY

(NAME = Particionamento_Dados,

  FILENAME = N’C:\SQL\Teste_Dados.mdf’,

  SIZE = 2MB,

  MAXSIZE = Unlimited,

  FILEGROWTH = 10%)

LOG ON

(NAME = Particionamento_Log,

 FILENAME = N’C:\SQL\Particionamento_Log.ldf’,

 SIZE = 4MB,

 MAXSIZE = Unlimited,

 FILEGROWTH = 10%)

 

O próximo consiste no adicionamento de novos Filegroups (Agrupamento de Arquivos) e em seguida a criação de novos arquivos de dados, que serão utilizados em conjunto com a nossa função e esquema de particionamento. Na primeira parte do Código 2, vamos criar os dois novos Filegroups, denominados: ParticionamentoFG1 e ParticionamentoFG2.

— Código 2 – Parte I – Criação de novos Filegroups –

Alter Database Particionamento

Add FileGroup ParticionamentoFG1

Go

Alter Database Particionamento

Add FileGroup ParticionamentoFG2

Go

Nossos Filegroups já estão criados, falta agora vincular os nossos arquivos de dados e estes repositórios, conforme apresenta a segunda parte do Código 2.

 

— Código 2 – Parte II – Criação de novos arquivos de dados e vínculo com os Filegroups –

Alter Database Particionamento

Add File

 (Name = Particionamento_Dados_Segundo,

  FileName = ‘C:\SQL\Particionamento_Dados_Segundo.ndf’,

  Size = 2MB,

  MaxSize = Unlimited,

  Filegrowth = 10%)

To Filegroup ParticionamentoFG1

Go

Alter Database Particionamento

Add File

 (Name = Particionamento_Dados_Terceiro,

  FileName = ‘C:\SQL\Particionamento_Dados_Terceiro.ndf’,

  Size = 5MB,

  MaxSize = Unlimited,

  Filegrowth = 10%)

To Filegroup ParticionamentoFG2

Go

Pois bem, nossos novos arquivos de dados estão criados e vinculados com seus respectivos filegrous, vamos utilizar a Visão de Catálogo de Sistema, sys.FileGroups para obter a relação de agrupamento de arquivos definidos para o nosso Banco de Dados Particionamento. Para isso podemos executar o Código 3, conforme apresento abaixo:

 — Código 3 – Consultando a relação de Filegroups –

Select * from sys.FileGroups

Após executarmos este código recebemos o seguinte resultado:

Figura 1 – Relação de Filegroups configurados para o Banco de Dados – Particionamento.

Podemos constatar e comprovar a existência dos três Filegroups alocados para este banco de dados, o próximo passo consiste justamente na parte mais importante deste artigo, onde estaremos fazendo a criação da nossa Função de Particionamento e Esquema de Particionamento de Dados.

Para isso, vou utilizar o Código 4 e Código 5, respectivamente:

— Código 4 – Criando a Função de Particionamento PF_Valores –

CREATE PARTITION FUNCTION PF_Valores (Int)

AS RANGE Left FOR VALUES (1,4,8)

GO

É possível notar que a função de particionamento PF_Valores, possui como faixa de valores os Número 1, 4 e 8, além disso, o controle para delimitação dos valores esta definido ao Esquerda. Outro detalhe importante que estes valores serão tratados e reconhecidos como Inteiros.

Podemos confirmar a criação desta função, através da Visão de Catálogo de Sistema: Sys.Partition_Functions, através do código: Select * from sys.partition_functions

— Código 5 – Criando a Esquema de Particionamento PS_Valores –

CREATE PARTITION SCHEME PS_Valores

AS PARTITION PF_Valores

TO (ParticionamentoFG1, ParticionamentoFG1, ParticionamentoFG2, ParticionamentoFG2);

GO

Fazendo uma rápida análise, podemos observar que o esquema de particionamento PS_Valores, esta vinculado em sua opção Partition a função PF_Valores, para as seguintes áreas de alocação de dados:

  • ParticionamentoFG1 e ParticionamentoFG2

Sendo assim, como a função PF_Valores tem o controle de limite de valores, a partir da Esquerda, respeitando sua faixa de valores: 1, 4 e 8. O SQL Server vai criar a seguinte estrutura para armazenamento de dados, conforme apresenta a Tabela 1:

Partition

1

2

3

4

 Valores

Int <= 1

Int > 1 AND Int <= 4

Int > 4 AND col1 <=8

Int > 8

Tabela 1 – Estrutura para alocação de dados de acordo com a partição.

A partir do momento em que o vínculo entre o Esquema de Particionamento é definido com a Função de Particionamento, o SQL Server define a sua falta de distribuição de dados, conforme a Tabela 1, mas atrelando os Filegroups especificados no esquema. Com isto, teremos a distribuição de dados apresentada na Tabela 2:

Partition

1

2

3

4

 Valores

Int <= 1

Int > 1 AND col1 <= 4

Int   > 4   AND Int <=8

Int > 8

Filegroups

ParticionamentoFG1

ParticionamentoFG1

ParticionamentoFG2

ParticionamentoFG2

Data_Space

2

2

3

3

Tabela 2 – Estrutura para alocação de dados de acordo com a partição e filegroup.

Estamos chegando ao final, agora falta pouco para concluirmos este artigo, nesta parte final vamos criar uma tabela chamada Valores e ao invés de vincular esta tabela com um Filegroups, vamos fazer o vínculo com nosso Esquema de Particionamento PS_Valores, através da coluna Valor que é do tipo inteiro, o mesmo tipo que configuramos na criação da Função PF_Valores. Com isso o SQL Server, terá a capacidade de distribuir os dados entre os Filegroups, conforme apresenta o Código 6.

— Código 6 – Criação da Tabela Valores –

Create Table Valores

 (Codigo Int Identity(1,1),

  Descritivo Varchar(20) Not Null,

  Valor Int Not Null)

On PS_Valores(Valor)

Nossa tabela esta criada e pronta para receber os dados, agora podemos realizar a série de comandos Inserts e observar o crescimento dos nossos arquivos de dados, vinculados de acordo com seus Filegroups.

Vamos então executar em partes do Código 7, neste código estamos realizando a inserção de massas de dados divididas em faixas de valores para forçar e apresentar o particionamento de dados. Vale ressaltar que estamos inserindo em cada porção o número de 100 mil linhas de registros, o que poderá demandar um certo tempo de processamento.

— Código 7 – Parte I – Inserção de Dados —

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,1)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,10)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,2)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,9)

Go 100000

— Código 7 – Parte II – Inserção de Dados —

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,3)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,8)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,4)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,7)

Go 100000

— Código 7 – Parte III – Inserção de Dados —

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,5)

Go 100000

 

Insert Into Valores (Descritivo, Valor)

Values (‘Este é um teste’,6)

Go 100000

Após inserir todas as faixas de valores, foi possível observar que o SQL Server realizou a distribuição dos dados de acordo com as faixas definidas em nossa função, trabalhando em conjunto com o nosso esquema.

Todo este trabalho foi feito de forma transparente, sem que fosse necessário mais algum tipo de customização, na verdade toda a regra de negócio já havia sido definida na função e esquema. Isso nos mostra o quanto podemos evoluir com este tipo de cenário, fazendo uso deste recurso fantástico em nosso ambiente de trabalho, proporcionando uma forma de trabalho rápida, inteligente, simples e prática.

Como também, é importante destacar que momento em que estivermos realizando qualquer tipo de manipulação de dados, o SQL Server estará fazendo acesso a locais distintos, arquivos distintos e conseguindo ler dados distribuídos de forma contínua e simultânea.

Outro detalhe interessante foi à forma de crescimento de nossos arquivos de dados, sendo feito de acordo com a distribuição de dados, por parte do esquema, o que também indica todo processo de análise e inteligência realizado pelo SQL Server, conforme os dados estão sendo manipulados.

Bom, acredito que este artigo com certeza vai ajudar em muito o sua dia-a-dia, principalmente no que diz respeito à particionamento de tabelas, índices, dados e Filegroups. Por outro lado, poderá servir como base para outras soluções, dentre elas a melhoria para acesso a grandes tabelas e bancos de dados.

Mais uma vez agradeço a sua visita, nos encontramos em breve.

Novidades – SQL Server 2012 – Novas Funções Textuais


Pessoal, bom dia.

Tudo bem?

Estou de volta com mais uma novidade apresentada pela Microsoft com o lançamento do SQL Server 2012 em Abril deste ano. Como todo novo produto, existem milhares e milhares de novidades, aprimoramentos, recursos removidos, como também, novos recursos que na verdade já deveriam estar presente em versões passadas.

É justamente sobre estes recursos que já deveriam estar presentes em versões anteriores do SQL Server que vou falar um pouco. Nas novidades que vou apresentar hoje, destaco asNovas Funções para se trabalhar com Textos no SQL Server 2012, mais especificamente as funções:

  • Concat: Responsável em efetuar concatenação de seus parâmetros realizando a conversão se necessário.
  • Format: Utilizada para possibilitar a formatação de uma expressão com base em uma máscara ou idioma.

Ambas as funções já deveriam estar presentes desde o SQL Server 2005, por apresentar comportamento similares a recursos atuais que podem ser utilizados como forma de contornar a falta destas funções, principalmente a função Concat. Mas não é fácil entender como a Microsoft prioriza o que deve ou não ser adicionado em cada nova versão ou atualizado, muito menos definir o que realmente este recurso pode trazer de diferencial para o usuários.

Pois bem, passados 7 anos do lançamento do SQL Server 2005 e agora no 2012 estas funções estão presentes e com certeza vão ajudar em muito os Administradores, Desenvolvedores, Programadores de Sistemas e Banco de Dados, trabalhando em conjunto com outras ferramentas da própria Microsoft como por exemplo o Microsoft Visual Studio 2010 ou 2012.

Existe alguns questionamentos presentes para estas novas funções, dentre eles, destaco:

Como a função Concat realiza a concatenação de valores?

CONCAT usa um número variável de argumentos de cadeia de caracteres e os concatena em uma única cadeia de caracteres. Exige um mínimo de dois valores de entrada; caso contrário, é gerado um erro. Todos os argumentos são convertidos implicitamente em tipos de cadeia de caracteres e depois concatenados. Os valores nulos são convertidos implicitamente em uma cadeia de caracteres vazia.varchar(1) is returned. Se todos os argumentos forem nulos, uma cadeia de caracteres vazia do tipo varchar(1) será retornada.

A conversão implícita em cadeias de caracteres segue as regras existentes para conversões de tipo de dados, com base nas funções CAST e CONVERT existentes na Linguagem Transact-SQL.

A função Format utiliza algum mecanismo para identificar o tipo de formatação que deve ser utilizado?

Por padrão a função Format realiza a formatação de um conjunto de valores de acordo com o tipo de formatação definida no seu conjunto de parâmetros. Vale ressaltar que a formatação é realizada com base na máscara especifica e também em relação ao idioma informado.

Ela reconhece a Linguagem ou Idioma para definir a formatação?

Na verdade a função Format não reconhece a Linguagem ou Cultura de Idioma que esta definido no SQL Server ou Sistema Operacional, o que é interpretado por parte da seu algoritmo é o conjunto de valores passados nos parâmetros, dentre eles:

  • A máscara de formatação de valores; e
  • O idioma que deverá ser utilizado como forma de apresentação do valor formatado, sendo este, parâmetro reconhecido como Cultura(Linguagem ou Idioma).

Existe alguma compatibilidade da função Concat e Format em relação ao .Net?

Tanto a função Concat como a Format podem ser utilizadas em aplicações devolvidas em .Net, alem disso, podem utilizar tipos de dados .Net para suas respectivas finalidades.

Muito bem, agora que já conhecemos um pouco de cada função, vou apresentar alguns exemplos de como podemos utilizá-las:

Exemplos – Função – Concat:

1 – Retornando a concatenação da Letra A e B, retornando AB:

SELECT ‘A’ + ‘B’, CONCAT(‘A’,’B’);

2 – Retornando a concatenação dos números 2 e 40, retornando respectivamente 42 e 240:

SELECT 2 + ’40’, CONCAT(2,40);

3 – Retornando a concatenação do número 2 e a palavra AB, retornando um exceção:

SELECT 2 + ‘AB’, CONCAT(2,’AB’);

Exemplos – Função – Format:

1 – Formatando o valor atribuído para variável @Data, com base na máscara ‘d’ e idioma PT-BR, retornando 18/05/2012:

DECLARE @Data DATE = ‘2012-05-18’

SELECT FORMAT(@Data,’d’,’PT-BR’)

2 – Formatando o valor atribuído para variável @Data, com base na máscara ‘M’ e idioma PT-BR, retornando 18 de Maio:

DECLARE @Data DATE = ‘2012-05-18’

SELECT FORMAT(@Data,’M’,’PT-BR’)

3 – Formatando o valor atribuído para variável @Data, com base na máscara ‘Y’ e idioma PT-BR, retornando Maio de 2012:

DECLARE @Data DATE = ‘2012-05-18’

SELECT FORMAT(@Data,’Y’,’PT-BR’)

4 – Formatando o valor atribuído para variável @Data, com base na máscara ‘Y’, retornando 18/5/2012

DECLARE @Data DATE = ‘2012-05-18’

SELECT FORMAT(@Data,’dd/M/yyyy’)

Após a apresentação destes exemplos e formas de utilização, vou encerrando mais este post, espero ter apresentado mais uma novidades introduzida no SQL Server 2012 que possa ajudar em muito em suas atividades.

Agradeço a sua atenção e visita ao meu blog.

Nos encontramos em breve.

Até mais.

Criptografando dados em tempo real com Função HashBytes e Algortimos de Hashing.


Pessoal, boa tarde.

Tudo bem? Recentemente em minhas aulas e consultorias um dos assuntos que mais tenho recebido perguntas e questionamentos, esta relacionado as ferramentas, técnicas e recursos de segurança e criptografia de dados existente no SQL Server.

Pois bem, com base neste cenário, comecei a procurar mais informações e como de costume encontrei na minha biblioteca de Scripts e Materias de Apoio, um pequeno código que pode ser bastante útil e de fácil compreensão.

Mas antes de destacar este código, gostaria de apresentar uma Visão geral das exigências de regulamentações relacionadas a Segurança e Criptografia com base em Leis e Normais Nacionais e Internacionais. Conforme destaco a seguir:

Como outras empresas, a Microsoft vem reavaliando as estruturas de segurança atuais para garantir que as estruturas de segurança cumpram as leis federais, estaduais e internacionais recentes que definem as obrigações de conformidade com as regulamentações relativas às informações pessoais. Nos Estados Unidos, essas leis incluem as seguintes leis federais e estaduais:

  • Lei Sarbanes-Oxley de 2002;
  • GLBA (Gramm-Leach-Bliley Act) de 1999;
  • HIPAA (Health Insurance Portability and Accountability Act, Lei de Portabilidade e Responsabilidade dos Seguros Saúde) de 1996;
  • Lei FERPA (Family Educational Rights and Privacy Act, Lei de direitos educacionais e de privacidade da família);
  • Título FDA 21 Parte 11 do CFR;
  • Projeto de Lei do Senado da Califórnia nº 1.386; e
  • Projeto de Lei do Senado de Washington nº 6.043.

Além disso, alguns regulamentos internacionais definem as obrigações de conformidade reguladora para empresas que armazenam informações de identificação pessoal. Esses regulamentos englobam:

  • PIPEDA canadense (Personal Information Protection and Electronic Documents Act, Lei de Proteção a Informações Pessoais e Documentos Eletrônicos);
  • European Union Data Protection Directive (Diretiva de proteção a dados da União Européia); e
  • Acordo de Capital da Basiléia, também conhecido como Basiléia II.

As organizações que armazenam informações pessoais do consumidor devem considerar cuidadosamente as implicações relativas a essas novas exigências regulatórias. Essas exigências afetam todas as seguintes operações de bancos de dados:

  • Autenticação de banco de dados, inclusive diretivas de senha e protocolos de autenticação;
  • Controles de autorização e acesso a banco de dados;
  • Proteção a dados confidenciais armazenados em um banco de dados;
  • Proteção a dados confidenciais transferidos para um banco de dados ou de um banco de dados; e
  • Auditorias de transações de bancos de dados para ajudar a garantir confidencialidade e integridade de dados.

As empresas devem cumprir as obrigações de conformidade com regulamentações relativas às informações de identificação pessoal. Para fornecer proteção a dados eficaz e econômica, os departamentos de TI das empresas devem reexaminar como suas organizações armazenam e gerenciam dados confidenciais.

Tendo como base este conjunto de Leis, Normas e ISOs, o Centro de Pesquisa e Estudos da Microsoft decidiu introduzir desde a versão 2005 do SQL Server, as chamadas Funções e Algoritmos de HashBytes.

Para ajudar a entender como estas funções e algoritmos, sua finalidade e forma de uso, vou procurar responder as questões apresentadas abaixo:

1 – Mas o que é Hash? E qual sua função?

Um hash (ou escrutínio) é uma sequência de bits geradas por um algoritmo de dispersão, em geral representada em base hexadecimal, que permite a visualização em letras e números (0 a 9 e A a F), representando 1/2 byte cada. O conceito teórico diz que “hash é a transformação de uma grande quantidade de informações em uma pequena quantidade de informações”.

Essa sequência busca identificar um arquivo ou informação unicamente. Por exemplo, uma mensagem de correio eletrônico, uma senha, uma chave criptográfica ou mesmo um arquivo. É um método para transformar dados de tal forma que o resultado seja (quase) exclusivo. Além disso, funções usadas em criptografia garantem que não é possível a partir de um valor de hash retornar à informação original.

Como a sequência do hash é limitada, muitas vezes não passando de 512 bits, existem colisões (sequências iguais para dados diferentes). Quanto maior for a dificuldade de se criar colisões intencionais, melhor é o algoritmo.

Uma função de hash recebe um valor de um determinado tipo e retorna um código para ele. Enquanto o ideal seria gerar identificadores únicos para os valores de entrada, isso normalmente não é possível: na maioria dos casos, o contra-domínio de
nossa função é muito menor do que o seu domínio, ou seja, x (o tipo de entrada) pode assumir uma gama muito maior de valores do que HASH x (o resultado da função de hash).

2 -Quais são os algoritmos mais utilizandos em conjunto com a função Hash?

Os algoritmos de criptografia definem transformações de dados que não podem ser facilmente revertidas por usuários não autorizados. O SQL Server permite que administradores e desenvolvedores escolham entre diversos algoritmos, incluindo
DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, RC4 de 128 bits, DESX, AES de 128  bits, AES de 192 bits e AES de 256 bits.

Os mais usados algoritmos de hash são os de 16 bytes MD4 e MD5 ou o SHA-1, de 20 bytes. Características de alguns algoritmos:

  1. MD4: Desenvolvido em 1990/91 por Ron Rivest, vários ataques foram detectados, o que fez com que
    o algoritmo fosse considerado frágil;
  2. MD5: O MD5 (Message-Digest algorithm 5) é um algoritmo de hash de 128 bits unidirecional desenvolvido pela RSA Data Security, Inc., descrito na RFC 1321, e muito utilizado por softwares com protocolo par-a-par (P2P, ou Peer-to-Peer, em inglês), verificação de integridade e logins. Existem alguns métodos de ataque divulgados para o MD5;
  3. SHA-1 (Secure Hash Algorithm): Desenvolvido pelo NIST e NSA. Já foram exploradas falhas no SHA; e
  4. WHIRLPOOL: função criptográfica de hash desenvolvida por Paulo S. L. M. Barreto e por Vincent Rijmen (co-autor do AES). A função foi recomendada pelo projeto NESSIE (Europeu). Foi também adotado pelo ISO e IEC como parte do padrão internacional ISO 10118-3.

O processo é unidirecional e impossibilita descobrir o conteúdo original a partir do hash. O valor de conferência (“check-sum”) muda se um único bit for alterado, acrescentado ou retirado da mensagem.

3 – Posso utilizar qualquer algoritmo em qualquer situação de criptografia?

Nenhum algoritmo é ideal para todas as situações, porém, os seguintes princípios gerais se aplicam:

  • A criptografia segura geralmente consome mais recursos da CPU que criptografia menos segura;
  • As chaves extensas geralmente produzem uma criptografia mais segura que as chaves mais curtas;
  • A criptografia assimétrica é mais segura que a criptografia simétrica, que usa o mesmo comprimento de chave mas é relativamente mais lenta;
  • Codificações em bloco com chaves extensas são mais seguras que codificações em fluxo;
  • Senhas longas e complexas são mais seguras que senhas curtas;
  • Se você estiver criptografando muitos dados, deve criptografá-los usando uma chave simétrica e criptografar a chave simétrica com uma chave assimétrica; e
  • Dados criptografados não podem ser compactados, mas dados compactados podem ser criptografados. Se você usar compactação, deverá compactar os dados antes de criptografá-los.

Bem, com estas respostas, acredito que ficou mais fácil entender o nível de importância e complexidade que envolve os processos, técnicas e ferramentas de criptografia. Desta forma, vou apresentar o chamado Modelo de Criptografia Hashing, conforme apresenta a Figura 1.

Figura 1 – Modelo de Criptografia Hashing.

Basicamente o Modelo de Criptografia Hashing, pode ser considerado a forma mais fácil e simples de se evitar que informações contidas em seu banco de dados sejam lidas por pessoas não autorizadas, aplicando os conceitos de criptografia. Ao usar Hash, as informações que vão para o banco são criptografadas de tal maneira que é impossível descriptografá-lo.

A criptografia por hash usa basicamente 2 parâmetros:

1) O algorítimo de criptografia; e
2) Os dados a serem criptografados.
Com o Modelo de Criptografia Hashing apresentado, agora é hora de colocar a mão na massa, ou seja, vamos realizar a nossa criptografia com base no exemplo a seguir:

–Declarando um variável @HashThis–
DECLARE @MeuValorCriptografado NVarChar(max);

SELECT @MeuValorCriptografado = CONVERT(nvarchar,’Pedro’);

/* Utilizando a função HashBytes para converter a sentença com uso dos Algoritmos Hash + SHA1, SHA, MD5, MD4, MD2 */

SELECT HashBytes(‘SHA1’, @MeuValorCriptografado) Resultado
Union
SELECT HashBytes(‘SHA’, @MeuValorCriptografado)
Union
Select HashBytes(‘MD5’, @MeuValorCriptografado)
Union
Select HashBytes(‘MD4’, @MeuValorCriptografado)
Union
Select HashBytes(‘MD2’, @MeuValorCriptografado)
Go

Neste código de exemplo, utilizei todos os principais Algoritmos de Hash disponíveis para a Função HashBytes em conjunto com o Modelo de Criptografia Hashing.

Desta forma, estou encerramenta mais esta dica, espero que todos possam ter gostado e que as informações apresentadas aqui, possam ser úteis.

Agradeço mais uma vez a sua visita, nos encontramos em breve.

Até mais.

Introdução ao Data Collection no Microsoft SQL Server 2008 – Final.


Olá pessoal, bom dia.

Conforme o prometido, estou de volta para finalizar a série de artigos sobre o Data Collection, nesta última parte, vou destacar os recursos do CONJUNTO DE COLETA DE ESTATÍSTICAS DE CONSULTA.

Desejo uma boa leitura.

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

CONJUNTO DE COLETA DE ESTATÍSTICAS DE CONSULTA.

Reúne dados sobre estatísticas de consulta e texto de consulta individual, planos de consulta e consultas específicas. Esses dados, quando vinculados com as estatísticas e atividades no nível do sistema, permitem que os usuários façam busca detalhada
abaixo do nível de sessão para uma consulta individual. Esse conjunto de coleta reúne dados das fontes a seguir:

  • sys.dm_exec_requests – Retorna informações sobre cada solicitação que está em execução no SQL Server;
  • sys.dm_exec_sessions – Retorna uma linha por sessão autenticada no SQL Server. sys.dm_exec_sessions é um modo de exibição de escopo de servidor que mostra informações sobre todas as tarefas internas e conexões de usuário ativo. Esta informação inclui a versão do cliente, nome do programa cliente, tempo de logon do cliente, usuário de login, configuração de sessão atual e mais. Use sys.dm_exec_sessions para exibir pela primeira vez a carga do sistema atual e para identificar uma sessão de interesse e, em seguida, saiba mais informações sobre a sessão usando outras exibições de gerenciamento
    dinâmico ou funções de gerenciamento dinâmico; 
  • sys.dm_exec_query_stats – Retorna as estatísticas de desempenho agregadas para os planos de consulta em cache. O
    modo de exibição contém uma linha por instrução de consulta no âmbito do plano em cache e a vida útil das linhas estão ligados ao próprio plano. Quando um plano é removido do cache, as linhas correspondentes são eliminadas deste modo
    de exibição.

O conjunto de coleta de Estatísticas de Consultas usa o tipo de coletor de Atividade de Consulta. Este, por sua vez, coleta dados usando o pacote SSIS de QueryActivityCollect.dtsx e carrega dados usando o pacote SSIS de QueryActivityUpload.dtsx.

A tabela 3 a seguir fornecem informações sobre o conjunto de coleta de Estatísticas de Consulta e seu item de coleta.

Nome do conjunto de coleta

Estatísticas de consulta

Modo de coleta Em cache
Freqüência da agenda de carregamento A cada 15 minutos
Retenção de dados 14 dias
Item de coleta Estatísticas de Consulta – Atividade de Consulta

Tabela 3. Informações sobre o conjunto de dados coletados através das Estatísticas de consulta.

SEGURANÇA DO COLETOR DE DADOS

O coletor de dados usa o modelo de segurança baseado em função implementado pelo SQL Server Agent. Esse modelo permite que o administrador do banco de dados execute várias tarefas do data collector em um contexto de segurança que tem apenas as
permissões exigidas para executar a tarefa.

Essa abordagem também é usada para operações que envolvem tabelas internas que só podem ser acessadas usando views ou stored procedures. Nenhuma permissão é concedida a tabelas internas. Em vez disso, as permissões são verificadas no usuário da view ou da stored procedure usada para acessar a tabela.

Outro aspecto fundamental desse modelo de segurança são as permissões concêntricas. Nas permissões concêntricas, funções mais privilegiadas herdam as permissões de funções menos privilegiadas nos objetos (incluindo alertas, operadores, tarefas, agendas e proxy).

Segurança de rede

Informações confidenciais podem ser passadas entre instâncias de destino, a instância relacional associada ao servidor de configuração, os conjuntos de coleta em execução e o servidor que hospeda o data warehouse de gerenciamento.

Para proteger qualquer dado transferido em uma rede, são implementados mecanismos de segurança padrão, como criptografia de protocolo para Transact-SQL.  

Permissões para configurar e usar o coletor de dados

Dependendo da tarefa, os usuários devem ser membros de uma ou mais das funções de banco de dados fixas fornecidas para o coletor de dados. Em ordem de acesso mais privilegiado para acesso menos privilegiado, as funções são as seguintes:

  • dc_admin – Data Collection Administrator;
  • dc_operator – Data Collection Operator; e
  • dc_proxy – Data Collection Proxy.

Essas funções são armazenadas no banco de dados MSDB. Por padrão, nenhum usuário é membro dessas funções de banco de dados. A associação do usuário a elas deve ser explicitamente concedida.

O usuários que são membros da função de servidor fixa sysadmin têm acesso total às views do coletor de dados e aos objetos do SQL Server Agent. Porém, eles precisam ser adicionados explicitamente à funções de coletor de dados.

Os membros das funções db_ssisadmin e dc_admin podem elevar seus privilégios para sysadmin. Essa elevação de privilégios pode ocorrer porque essas funções podem modificar os pacotes do Integration Services e os pacotes do Integration Services podem
ser executados pelo SQL Server usando o contexto de segurança sysadmin do SQL Server Agent. Para se proteger contra essa elevação de privilégios ao executar planos de manutenção, conjuntos de coletas de dados e outros pacotes do Integration Services, é recomendado configurar os trabalhos do SQL Server Agent que executam pacotes para usar uma conta proxy com privilégios limitados ou adicionar apenas membros sysadmin às funções db_ssisadmin e dc_admin.

Função dc_admin

Usuários atribuídos à função dc_admin têm acesso total de administrador (Criação, Leitura, Atualização e Exclusão) à configuração do coletor de dados em uma instância de servidor. Membros dessa função podem executar as seguintes operações:

  • Definir propriedades de nível de coletor;
  • Adicionar novos conjuntos de coleta;
  • Instalar novos tipos de coleta; e
  • Executar todas as operações permitidas à função dc_operator.

A função dc_admin é membro da role SQLAgentUserRole, essa função é necessária para criar agendas e executar tarefas. Importante destacar que, proxies criados para o coletor de dados devem conceder acesso à dc_admin para criá-los e utilizá-los em qualquer etapa da tarefa que exija um proxy.

Função dc_operator

Membros da função dc_operator têm acesso de Leitura e Atualização. Essa função suporta tarefas de operações relacionadas com a execução e configuração de conjuntos de coleta. Membros dessa função podem executar as seguintes operações:

  • Iniciar ou parar um conjunto de coleta;
  • Enumerar conjuntos de coleta existentes;
  • Exibir informações detalhadas (por exemplo, itens e freqüência de coleta) associadas a um conjunto de coleta;
  • Alterar a freqüência de carregamento de conjuntos de coleta existentes;
  • Alterar a freqüência de coleta de itens de coleta que fazem parte de um conjunto de coleta existente.

A função dc_operator é membro da função db_ssisltduser. A associação a essa função é necessária para que os membros possam enumerar e exibir pacotes do coletor de dados.

Função dc_proxy

Membros da função dc_proxy têm acesso de Leitura aos conjuntos de coleta do coletor de dados e às propriedades de nível de coletor. A função dc_proxy é membro da função db_ssisltduser. A associação a essa função é necessária para que os membros possam enumerar e exibir pacotes do coletor de dados.

Os membros dessa função também podem executar tarefas de sua propriedade e criar etapas de tarefa executadas como uma conta proxy existente. Membros dessa função podem executar as seguintes operações:

  • Exibir informações de configuração do conjunto de coleta (por exemplo, parâmetros de entrada de itens de coleta e a freqüência de coleta desses itens);
  • Obter informações internas criptografadas que só podem ser acessadas por um procedimento armazenado assinado (por exemplo, informações de conexão de data warehouse usadas para carregamento de dados);
  • Registrar em log eventos de tempo de execução do conjunto de coleta;
  • Permissões para configurar usar o Data Warehouse de gerenciamento;
  • Dependendo da tarefa, os usuários devem ser membros de uma ou mais das funções de banco de dados fixas fornecidas para acessar o data warehouse de gerenciamento. Em ordem de acesso mais privilegiado para acesso menos privilegiado, as funções são as seguintes:
  • mdw_admin – Member Dynamic View Administrator;
  • mdw_writer – Member Dynamic View Writer; e
  • mdw_reader – Member Dynamic View Reader.

Essas funções são armazenadas no banco de dados MSDB. Por padrão, nenhum usuário é membro dessas funções de banco de dados. A associação do usuário a elas deve ser explicitamente concedida. O usuários que são membros da função de servidor fixa sysadmin têm acesso total às exibições do coletor de dados. Porém, eles precisam ser adicionados explicitamente à funções do banco de dados para executar outras operações.

Função mdw_admin

Membros da função mdw_admin têm acesso de Leitura, Gravação, Atualização e Exclusão no data warehouse de gerenciamento. Estes membros podem executar as seguintes operações:

  • Alterar o esquema do data warehouse de gerenciamento quando necessário (por exemplo, adicionando uma tabela nova quando é instalado um novo tipo de coleta);
  • Onde houver uma alteração de esquema, o usuário também deve ser membro da função dc_admin para instalar um novo tipo de coletor, pois esta ação exige permissão para atualizar a configuração do coletor de dados no MSDB; e
  • Executar tarefas de manutenção no data warehouse de gerenciamento, como arquivo ou limpeza.

Função mdw_writer

Membros da função mdw_writer podem carregar e gravar dados no data warehouse de gerenciamento. Qualquer coletor que armazena dados no data warehouse de gerenciamento deve ser membro dessa função.

Função mdw_reader

Membros da função mdw_reader têm acesso de Leitura ao data warehouse de gerenciamento. Como o objetivo dessa função é dar suporte à solução de problemas fornecendo acesso a dados históricos, os membros dessa função não podem exibir outros lementos do esquema do data warehouse de gerenciamento.

Conclusão

O coletor de dados (Data Collector) é um componente do SQL Server 2008 utilizado para coleta diferentes conjuntos de dados, muitas vezes funcionando como uma uma agenda previamente definida, conforme escolha do usuário. Os dados são armazenados em um banco de dados relacional, também chamado de Data Warehouse de Gerenciamento.

O coletor fornece um ponto central para coleta de dados em seus servidores de banco de dados e aplicativos, que pode obter dados de várias fontes, não se limitando apenas aos dados de desempenho.

Com esta nova funcionalidade o SQL Server 2008 oferece para o administrador de banco de dados, a possibilidade de obtenção de informações relacionadas ao seu funcionamento, bem como, o que se relaciona a utilização de recursos da máquina para execução de suas transações. O Data Collection torna-se um grande aliado ao DBA ajudando em suas atividades diárias na administração e gerenciamento de seus banco de dados e servidores.

Bom pessoal, vou encerrando aqui mais esta série de artigos, desta vez, destaquei toda a estrutura, funcionamento e recursos existentes no Data Collection.

Espero que vocês tenham gostado, nos encontramos em breve nas próximas séries de artigos.

Até mais.