SQL Operations Studio versão preview v0.29.3 liberado

Nova versão compatível com Microsoft SQL Server, Azure SQL Database e Azure SQL Data Warehouse.

Anúncios

A Microsoft disponibilizou para download no último dia 05/08 a nova versão preview do SQL Operations Studio denominada v0.29.3. A ferramenta está disponível para Windows, macOS e Linux.

O SQL Operations Studio é uma ferramenta gratuita para gerenciamento do SQL Server, Azure SQL Database e Azure SQL Data Warehouse.

De acordo com o changelog, a versão 0.29.3 traz correções de bugs, nova extensão Redgate SQL Search disponível no Extension Manager, suporte para mais idiomas e outras novidades. Quer saber mais sobre todas as novidades, melhorias e mudança, clique aqui.

A versão para Windows do SQL Operations Studio Preview v0.29.3 está disponível para download com instaladorou versão portátil (não requer instalação).

Para maiores informações sobre o SQL Operations Studio para Windows e demais plataformas de sistemas operacionais acessem link: https://docs.microsoft.com/en-us/sql/sql-operations-studio/download?view=sql-server-2017.

Fontes e Direitos Autorias: Microsoft.com – 05/08/2018 –  Docs | SQL | Tools | SQL Operations Studio.

Short Scripts – Maio 2018


Olá Mundo, bom dia, comunidade….

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

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

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

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

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

O post de hoje

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

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

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

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

Short Scripts

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

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

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

CLOSE spidCursor
DEALLOCATE spidCursor
SET NOCOUNT OFF

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

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

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

GROUP BY TheDatabase
ORDER BY TheDatabase

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

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

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

SELECT id FROM t6;
SELECT id FROM t7;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

Links

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

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

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

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

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

Agradecimento

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

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

Abraços…

Dica do Mês – Microsoft SQL Server 2017 – SQL Graph Databases


Muito bom dia…… Salve amantes de banco de dados.

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

No post de hoje, quero compartilhar com você uma das maiores novidades implementadas na última versão do Microsoft SQL Server, neste caso, a versão 2017 lançada oficialmente em outubro de 2017 e muito destacada aqui no meu blog em diversos posts.

Destacando um pouco sobre o post, quando se referimos a banco de dados, normalmente pensamos em uma estrutura organizada basicamente em tabelas, colunas, chaves primárias, chaves estrangeiras e relacionamentos. Mas a partir do Microsoft SQL Server 2017 temos uma nova possibilidade de elaborar uma estrutura de banco de dados saindo um pouco deste tradicional cenário conhecimento como modelo relacional e sim partindo para o chamado modelo grafos ou banco de dados em grafos. Talvez em algum momento você já deve ter ouvido falar um pouco sobre esta forma de modelagem.

Pois bem, neste nova versão o time de engenheiros do SQL Server adicionaram ao conjunto de novas funcionalidades (features) o SQL Graph Databases ou simplesmente Banco de Dados em Grafos, isso mesmo banco de dados no formato de grafos, parece ser algo muito diferente do que estamos acostumados a fazer, na verdade não é bem assim, e você vai poder conhecer um pouco mais sobre este recurso, suas características e curiosidades no decorrer deste post.

Esta curioso em saber um pouco mais sobre esta nova feature? Eu estou, e não vejo a hora de poder dividir com você um pouco do vasto conteúdo relacionado com este recurso. Então, 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 – Microsoft SQL Server 2017 – SQL Graph Databases.

Você vai se surpreender com este novo recurso e suas possibilidades relacionadas com modelagem de banco de dados.


Introdução

O SQL Graph Database é uma nova forma de se estruturar um banco de dados criado no Microsoft SQL Server 2017, tendo como base um estrutura formada por uma coleção de tabelas de nó (Node Table) e  tabelas de borda (Edge Table).

Basicamente um nó representa uma entidade — por exemplo, uma pessoa ou uma organização e uma borda representa uma relação entre os dois nós que ele conecta. As tabelas de nó ou borda podem ser criadas em qualquer esquema em um banco de dados, mas todas pertencem a uma estrutura de grafos representada de forma lógica.

Os bancos de dados na estrutura de grafos são úteis quando o aplicativo tem relacionamentos complexos de muitos para muitos e precisamos analisar as relações complexas.

Algumas das características importantes de um bancos de dados na estrutura de grafos:

  • Bordas (edge) ou relacionamentos(node) são entidades de primeira classe em um banco de dados de grafos e podem ter atributos ou propriedades associadas a eles;
  • Uma única edge table pode unir flexivelmente vários nós em um banco de dados de grafos;
  • Demonstrar a relação de padrões e consultas de navegação de vários saltos facilmente; e
  • Demonstrar o encerramento transitivo de dados e as consultas polimórficas facilmente.

A Figura 1 abaixo apresenta a estrutura básico do SQL Graph Databases e seus principais componentes:

Architecture of SQL Server 2017 Graph Database
Figura 1 – Estrutura básica do SQL Graph Databases.

Analisando a Figura 1 apresentada acima, podemos dizer que um banco de dados na estrutura de grafos é um tipo de banco de dados cujo conceito é baseado em nós e bordas. Este novo tipo de bancos de dados, denominada de grafos, baseiam-se na teoria dos grafos (um grafo é um diagrama de pontos e linhas conectados aos pontos), respeitando a seguinte estrutura:

  1. Os nós representam dados ou entidade e bordas representam conexões entre nós; e
  2. As bordas são propriedades que podem estar relacionadas a nós, essa capacidade nos permite mostrar interações mais complexas e profundas entre os nossos dados.

Elementos básicos

A seguir destaco os elementos básicos que compõem a estrutura do SQL Graph Databases:

Node Table
Representa uma entidade em um esquema de grafos. Sempre que criamos uma tabela de nós, juntamente com as colunas definidas pelo usuário, uma coluna implícita $node _id é criada, o que identifica exclusivamente um determinado nó no banco de dados.

Os valores na coluna $node _id são gerados automaticamente e são uma combinação de object_id dessa tabela de nós e um valor bigint gerado internamente. No entanto, quando a coluna $node _id é selecionada, um valor calculado na forma de uma cadeia de caracteres JSON é exibido.
Além disso, $Node _id é uma coluna pseudo, que mapeia para um nome interno com String hex nele. Quando selecionamos $node _id da tabela, o nome da coluna aparecerá como $node _id_ hex_string.

É recomendável que os usuários criem uma restrição ou índice exclusivo na coluna $node _id no momento da criação da tabela de nós, mas se um não for criado, um índice padrão exclusivo não clusterizado será criado automaticamente.

Edge Table
Como mencionado anteriormente, uma tabela de borda(Edge Table) representa uma relação em um grafos. As bordas são sempre direcionadas e conectam dois nós.

Uma tabela de borda permite que os usuários modelem relacionamentos muitos-para-muitos no grafos, esta mesma tabela pode ou não ter quaisquer atributos definidos pelo usuário.

$Edge _id
A primeira coluna na tabela de borda representa $Edge _id que identifica exclusivamente uma aresta fornecida no banco de dados. O valor da coluna edge_id é gerado com a combinação de object_id da tabela de borda e um valor bigint gerado internamente.

No entanto, quando selecionamos a coluna $Edge _id, ela é exibida como a seqüência de caracteres JSON que é calculada a partir do valor da coluna.

$from _id
Coluna que armazena o $node _id do nó, de onde a borda é originada. Semelhante ao $Edge _id este é também um pseduo e pode ser usado como $from _id no entanto, o nome da coluna inclui Strings hex nele.

$to _id
Armazena o $node _id do nó, no qual a borda termina. Comportamento desta coluna em também como por $Edge _id e $from coluna _id.

Funções

Existem algumas funções adicionadas a linguagem Transact-SQL, que visam ajudar os usuários a extrair informações das colunas geradas. Abaixo estão as funções:

OBJECT_ID_FROM_NODE_ID: Função que permite extrair o object_id de um node_id. Precisamos passar node_id para esta função e ele retornará o object_id, também podemos obter o nome do objeto do object_id.

GRAPH_ID_FROM_NODE_ID: Função que permite extrair o GRAPH_ID de um node_id. Precisamos passar node_id para esta função e ele retornará o object_id.

NODE_ID_FROM_PARTS: Através desta função podemos construir um node_id de um object_id e um graph_id.

OBJECT_ID_FROM_EDGE_ID: Função utilizada para extrair um object_id de um EDGE_ID.

GRAPH_ID_FROM_EDGE_ID: Função utilizada para identificar um GRAPH_ID de EDGE_ID.

EDGE_ID_FROM_PARTS: Função que nos permite identificar o id utilizada entre as conexões de EDGE_ID de object_id e identidade.

Tabelas de Sistemas e Metadados

Sys.Tables – Novas colunas foram adicionadas ao sys.tables para identificar se uma tabela é um nó ou uma borda, conforme apresenta a Tabela 1 abaixo:

Column Name Data Type Description
is_node bit 1 = this is a node table
is_edge bit 1 = this is an edge table

Tabela 1 – Novas colunas adicionadas a system table sys.tables.

Sys.Columns – Novas colunas foram adicionadas ao sys.tables para indicar o tipo da coluna em tabelas de nó e borda, permitindo o relacionamento entre as systems tables sys.columns e sys.tables. A Tabela 2 abaixo apresenta a relação de novas colunas adicionadas a sys.columns:

Column Name Data Type Description
graph_type int Internal column with a set of values.

The values are between 1-8 for graph columns and NULL for others:

1 – GRAPH_ID
2 – GRAPH_ID_COMPUTED
3 – GRAPH_FROM_ID
4 – GRAPH_FROM_OBJ_ID
5 – GRAPH_FROM_ID_COMPUTED
6 – GRAPH_TO_ID
7 – GRAPH_TO_OBJ_ID
8 – GRAPH_TO_ID_COMPUTED

graph_type_desc nvarchar(60) internal column with a set of values

Tabela 2 – Novas colunas adicionadas a system table sys.columns.

Nossa, quanta coisa nova foi adicionado ao Microsoft SQL Server a partir desta nova funcionalidades, como também, diversas mudanças internadas como de costume também foram realizadas no produto afim de possibilitar o uso destas e outras funcionalidades, algo comumente realizado a cada nova versão.

Vamos então conhecer de forma prática o SQL Graph Databases e entender como estes elementos podem ser utilizados através do cenário que estaremos implementando a partir de agora.

Implementando o SQL Graph Databases

Se estamos nos referindo ao um banco de dados de grafos, nada melhor do que representar a estrutura que será utilizada em nosso cenário de exemplo através de um Diagrama conforme ilustra a Figura 2 abaixo:

Figura 2 – Diagrama – Estrutura utilizada para o exemplo.

Os nós são SQL Server, Azure e Windows e segue (bordas) fornecem conexões entre nós. Este modelo de banco de dados não pode ser tratado como uma alternativa a um modelo de banco de dados relacional, mas confrontados com alguns problemas específicos, o modelo de banco de dados em grafos pode ser alternativo e efetivo.

Observações: Se você olhar para o diagrama de perto, talvez você pode projetar este modelo de dados em um banco de dados relacional por junções, mas imagine que se você tiver um monte de nós e bordas, em seguida, quantas junções você vai precisar? Outra consideração importante se relaciona na forma de como este projeto funcionaria? Por esta razão, ao lidar com alguns problemas de negócios, precisamos de um banco de dados representado na teoria de grafos.

Um cenário muito interessante para a adoção de um banco de dados em grafos a ser considerado são meios de comunicação social. Por exemplo, há um monte de ações sociais, cada ação social cria uma marca. Quando combinamos estas marcas, parece uma teia de aranha. O modelo de banco de dados em grafos é ideal para armazenar esse tipo de dados.

Criando o ambiente

Para nossa prática vamos trabalhar com o seguinte ambiente:

  • Database – SQLGraphDatabase;
  • Node Table – Products; e
  • Edge Table – Microsoft.

Vamos começar criando nosso banco de dados e a node table através do Bloco de Código 1 apresentado na sequência:

— Bloco de Código 1 – Criando Banco de Dados e Node Table —

— Criando o Banco de Dados —
Create Database SQLGraphDatabases
Go

— Acessando o Banco de Dados —
Use SQLGraphDatabases
Go

— Criando a Node Table Products —
Create Table Products
(ProductID TinyInt Primary Key,
ProductName Varchar(100)
) As Node
Go

Note que instrução Create Table especificamos ao final da mesma a palavra chave Node, desta forma, o Microsoft SQL Server vai entender que nossa tabela será um nó e posteriormente estará relacionada com no mínimo uma borda.

O próximo passo é realizar a inserção de dados na tabela Products, para isso vamos utilizar o Bloco de Código 2 apresentado a seguir:

— Bloco de Código 2 – Inserindo dados na Node Table Products —

— Inserindo dados na Node Table Products —
Insert Into Products
Values (1,’SQL Server’),
(2,’Azure’),
(3,’Windows’)
Go

— Consultando os dados —
Select ProductId, ProductName from Products
Go

Você poderá notar que após a execução do comando Select teremos o resultado das três linhas de registros lógicos adicionadas a node table Products apresentados em tela, para que seja possível apresentar toda estrutura da tabela incluindo o $Node_Id, vamos então utilizar um simples e conhecido Select * (técnica não recomendável, mas para este cenário será útil para apresentar de maneira rápida a estrutura da node table Products).

— Apresentando toda estrutura da node table Products e seus respectivos dados —

Select * From Products
Go

A Figura 3 abaixo ilustra o retorno dos dados obtidos após a execução do Select acima:

Figura 3 – Estrutura da node table Products, incluindo $Node_ID e dados.

Continuando a nossa caminhada, vamos agora criar nossa tabela borda (edge table) denominada Microsoft, sendo esta o elemento utilizado para estabelecer as ligações entre os dados da node table para com a edge table, desta forma utilizaremos o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 – Criando a Edge Table Microsoft —

— Criando a Edge Table Microsoft —
Create Table Microsoft As Edge
Go

— Consultando a estrutura da Edge Table Microsoft —
Select * from Microsoft
Go

Pronto nossa tabela borda esta criada, nosso penúltimo passo será adicionar os dados oriundos da node table Products, estabelecem assim as relações entre ambos os elementos, para tal procedimentos utilizaremos o Bloco de Código 4 apresentado a seguir:

— Bloco de Código 4 – Inserindo os dados na Edge Table Microsoft —

— Azure com SQL Server —
Insert Into Microsoft ($from_id , $to_id )
Values ((Select $node_id from Products where ProductId=2),
(Select $node_id from Products where ProductId=1))
Go

— Windows com SQL Server —
Insert Into Microsoft ($from_id ,$to_id )
Values ((Select $node_id from Products where ProductId=3),
(Select $node_id from Products where ProductId=1))
Go

— Windows com Azure —
Insert Into Microsoft ($from_id ,$to_id )
Values ((Select $node_id from Products where ProductId=3),
(Select $node_id from Products where ProductId=2))
Go

— Consultando os dados inseridos na Edge Table Microsoft —
Select * from Microsoft
Go

Muito bom, acabamos de estabelecer as ligações entre nossa node table e a edge table, note que para realizar este vinculo entre os dados oriundas da node table Products para a edge table Microsoft, utilizamos os valores da coluna ProductID existente na tabela de Products, como elemente chave a ser inserido nas colunas $from_id e $to_id no momento da criação da edge table Microsoft.

Figura 4 a seguir ilustra os dados inseridos na edge table Microsoft e apresentados no formato de string JSON:

Figura 4 – Dados inseridos na edge Table Microsoft.

Agora falta muito pouco e chegamos ao último passo que consiste justamente em identificar as conexões estabelecidas com base no Bloco de Código 4 processado anteriormente, onde através da nova função Match() em conjunto com seus operadores ‘-‘ ou ‘->’ podemos definir o caminho para encontrar os dados respeitando a estrutura de nós e bordas. Para tal procedimento executaremos o Bloco de Código 5 apresentado abaixo:

— Bloco de Código 5 – Identificando as conexões entre os dados —

— Utilizando a função Match(), encontrando as conexões do ProductName = Azure —
Select Concat(Products.ProductName,’ –> ‘, ProductsDetails.ProductName) As Connections
From Products, Microsoft, Products ProductsDetails
Where Match(Products-(Microsoft)->ProductsDetails)
And Products.ProductName = ‘Azure’
Go

— Utilizando a função Match(), encontrando as conexões do ProductName = Windows —
Select Concat(Products.ProductName,’ –> ‘, ProductsDetails.ProductName) As Connections
From Products, Microsoft, Products ProductsDetails
Where Match(Products-(Microsoft)->ProductsDetails)
And Products.ProductName = ‘Windows’
Go

Após a execução do Bloco de Código 5 apresentado anteriormente o Management Studio deverá retornar um resultado similar ao apresentado pela Figura 5 abaixo:

Figura 5 – Conexões de dados estabelecidas com base nas colunas $from_id  e $to_id.

Incrível, chegamos ao final de mais um post da sessão Dica do Mês, tenho a certeza que você gostou do conteúdo apresentado hoje neste post.

Vale ressaltar que alguns pontos não foram abordados neste post, dentre eles como utilizar um índice clusterizado ou não clusterizado, como também, a maneira que uma Node Table e Edge Table são apresentadas na estrutura de um banco de dados através do Management Studio.

No que diz respeito ao novo modelo de banco de dados, tenha a certeza que a minha missão foi cumprida, principalmente no entendimento de como esta uma nova forma de representação baseada em grafos poderá nos ajudar a estabelecer uma nova técnica de relacionamento entre nossas tabelas e seus respectivos dados, indo muito além dos tradicionais conceitos dentre eles: chaves primárias e estrangeiras, caminhando para uma nova proposta com base no conceito de relacionamento lógico de dados.

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/graphs/sql-graph-sample

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview

https://www.red-gate.com/simple-talk/sql/sql-development/sql-server-graph-databases-part-1-introduction/

https://www.sqlshack.com/implement-graph-database-sql-server-2017/

https://www.mssqltips.com/sqlservertip/4883/sql-server-2017-graph-database-example/

 

Posts Anteriores

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

https://pedrogalvaojunior.wordpress.com/2017/11/01/dicadomessql2017novascolunasinternas/

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

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

Conclusão

Neste post, conhecemos um pouco sobre este novo recursos SQL Graph Databases implementado a partir da versão 2017 do Microsoft SQL Server.

O SQL Server Graph é um recurso fantástico que nos permite implementar dentro da mesma estrutura de banco de dados existente no Microsoft SQL Server dois modelos de relacionamento de dados totalmente diferentes.  Através desta arquitetura híbrida temos a capacidade de utilizar recursos do mecanismo do SQL Server com um banco de dados na estrutura de grafos de maneira muito similar para não se dizer parecida com os recursos aplicados no modelo relacional, com base, na linguagem Transact-SQL nos dando todo suporte a consultas para este novo formato.

O banco de dados baseados na estrutura de grafos (SQL Graph Databases) apresentam como toda nova tecnologia algumas limitações técnicas neste momento, que tendem nas próximas versões do produto evoluírem ou até mesmo deixar de existir, algo que neste momento não podem ser consideradas limitações que impossibilitem a sua adoção ou uso em novos projetos de bancos de dados, como também, não se tornem  limitações a outros recursos excepcionais no existentes no Microsoft SQL Server 2017, mesmo assim podemos reconhecer que este novo modelo de banco de dados, pode ser considerada uma tecnologia convincente e promissora.

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.

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

Viva o Microsoft SQL Server 2017 e suas novas funcionalidades…

Um forte abraço, sucesso e até o próximo post.

Material de Apoio – Fevereiro 2018


Boa tarde.

Tudo bem? E ai esta curtindo o carnaval?

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

O post de hoje

 

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

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

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

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

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

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

Material de Apoio

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

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

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

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

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

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

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

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

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

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

Links

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

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

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

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

Agradecimento

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

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

Até a próxima, sucesso….

Short Scripts – Dezembro 2017


Hello World, muito, mas muito bom dia….

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

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

O post de hoje

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

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

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

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

Short Scripts

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

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

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

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

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

Select * from #TempTable
Go

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

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

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

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

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

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

Drop Table #TempFileList
Go

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

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

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

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

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

PRINT @Sentence
Go

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

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

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

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

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

USE MyDatabase
Go

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

Print ‘Updating Permissions for MyDatabase’
Print ‘ ‘

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

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

Open tnames_cursor
Fetch Next From tnames_cursor INTO @tablename

While (@@Fetch_status <> -1)
Begin

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

Fetch Next From tnames_cursor INTO @tablename
End

Deallocate tnames_cursor
End
Go

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

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

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

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

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

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

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

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

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

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


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

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

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

Links

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

Agradecimento

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

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

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

Short Scripts – Março 2016


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

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

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

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

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

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

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

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

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

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

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

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

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

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

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

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

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

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

create table #customers (

id int,

customer varchar(50)

)

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

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

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

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

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

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

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

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

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

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

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

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

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

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

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

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

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

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

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

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

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

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

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

Exec sp_helpdb ‘Master’

Go

 

 

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

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

go

declare @txt varchar(255)

set @txt = ‘Question’

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

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

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

select @txt

Go

 

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

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

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

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

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

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

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

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

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

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

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

— The after update Select Statement

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

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

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

GO

 

Exec [QOD_Test_1]

Go

 

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

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

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

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

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

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

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

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

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

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

Go

 

 


 

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

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

Valeu!!!

Short Script – 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.