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.

#08 – Para que serve


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

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

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

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

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


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

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

— Passo 1 – Criando o Banco de Dados HypotheticalDB —

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

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

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

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

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

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

Figura 1 – Estrutura da Tabela ClientesCategorias.

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

 

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

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

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

HypotheticalIndex2

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

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

  • IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas; e
  • IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas.

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

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoSemEstatisticas)

DBCC SHOW_STATISTICS (ClientesCategorias, IND_ClientesCategorias_NaoClusterizado_CodigoComEstatisticas)
Go

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

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

HypotheticalIndex3

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

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

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

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

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

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

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

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

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

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

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

Dica do Mês – Conhecendo a Álgebra Relacional e seus operadores


Olá, boa noite, tudo bem comunidade?

Como é bom poder curtir alguns dias de descanso que mais um feriadão, aproveitar para recarregar as baterias, renovar as ideias e se preparar para mais um mês que esta chegando, e se estamos chegando ao final de mais um mês é hora de compartilhar e postar mais um post dedicado a dica do mês. Falando nisso, a dica do mês de hoje será um pouco diferente das últimas postados aqui no meu blog.

Atendendo aos pedidos de alguns internautas que me enviaram e-mails nos últimos dias, como também, para alegrar os meus alunos, vou postar um pouco de conteúdo acadêmico, algo mais conceitual relacionado a teoria de banco de dados. Neste post vou falar um pouco sobre um dos assuntos mais importantes relacionadas a teoria geral de banco de dados, estou me referindo a Álgebra Relacional, ou como eu gosto de dizer a matemática do relacionamento de banco de dados.

Então lá nessa, espero que você goste e também aprenda um pouco sobre este conceito extremamente importante para qualquer profissional da área de banco de dados.


Introdução

A álgebra relacional é uma linguagem de consulta procedural. Ela consiste em um conjunto de operações que tornam uma ou duas tabelas como entradas e produzem uma nova tabela como resultado. Essas operações baseiam-se na teoria dos conjuntos (as tabelas correspondem a conjuntos). Linguagem procedural: linguagem que requer sempre a existência de definição quanto à ordem em que as operações serão realizadas.

 

Operadores

São definidas nove operações ou operadores para se trabalhar com álgebra relacional, eles podem ser classificados da seguinte maneira:

Fundamentais: Através dela qualquer expressão de consulta de dados é permitida:

¤ 1. Projeção

¤ 2. Seleção

¤ 3. Produto Cartesiano

¤ 4.União

¤ 5. Diferença, Subtração

Derivados: Derivam dos operadores fundamentais, são definidos para facilitar a especificação de certos procedimentos:

¤ 6. Intersecção

¤ 7. Junção (normal e natural)

¤ 8. Divisão

Especiais: Operadores que não se enquadram nos itens anteriores:

¤ 9.Renomeação e alteração

 

Quanto ao número de relações (tabelas) operandas:

¤ Unários – operam em uma única tabela .

São eles: seleção, projeção, renomeação e alteração;

¤ Binários – operam em duas tabelas.

São eles: união, intersecção, diferença, produto cartesiano, junção e divisão.

 

Quanto à origem da área da matemática:

¤ Teoria dos Conjuntos – operadores usuais da teoria de conjuntos da matemática. São eles: união, intersecção, diferença e produto cartesiano;

¤ Especiais – operadores adicionais, definidos pela álgebra relacional para manipulação de dados. São eles: seleção, projeção, junção, divisão, renomeação e alteração.

Além desses operadores, é definido também o operador de atribuição que permite atribuir o resultado de uma expressão de álgebra a uma tabela.

 

Símbolos que representam os operadores

Cada um dos noves operadores apresenta um símbolo que identifica sua função e operação dentro da expressão relacional, ou melhor dizendo dentro da fórmula que identifica e apresenta a operação relacional que esta sendo realizada, a Tabela 1 apresentada abaixo ilustra os símbolos representativos de cada operador:

Operadores

Tabela 1 – Relação de símbolos representativos de cada operador.

Vamos agora conhecer um pouco sobre cada operador, sua forma de utilização, após a apresentação de todos os operadores, disponibilizarei um código de exemplo para que você posso praticar e conhecer de maneira operacional como cada operador pode ser utilizado, vamos conhecer pelo primeiro operador conhecido como Projeção.

 

Projeção

Pode ser entendida como uma operação que filtra as colunas de uma tabela de nosso banco de dados ou uma tabela resultante de uma outra operação relacional executada. Por operar em apenas um conjunto de entrada, a projeção é classificada como uma operação unária.

Sintaxe:       coluna1, coluna2,…, colunaN (Tabela)

 

Seleção / Restrição

Pode ser entendida como a operação que filtra, seleciona as linhas de uma tabela, realizando também uma projeção, e opera em um conjunto de dados, sendo portando uma operação unária.

Sintaxe:         <condição de seleção ou predicado> (Tabela)

 

Produto Cartesiano

Utiliza a mesma notação de operação matemática de dois conjuntos, tendo como resultado do produto cartesiano de duas tabelas uma terceira tabela contendo as cominações possíveis entre os elementos das tabelas originais.

Essa tabela resultante possui um número de colunas que é igual à soma do número de colunas das tabelas iniciais e um número de linhas igual ao produto do número de linhas das duas tabelas.

Sintaxe: (Tabela 1) X (Tabela 2)

 

União

É uma operação binária, ou seja cria uma tabela a partir de duas outras tabelas união compatíveis levando as linhas comuns e não comuns a ambas. As informações duplicadas aparecerão somente uma vez no resultado. Tabelas União Compatíveis: tabelas cuja quantidade, disposição e domínio dos atributos/ campos sejam os mesmos.

Sintaxe: (Tabela 1)     (Tabela 2)

 

Intersecção

É uma operação binária, ou seja cria uma tabela a partir de duas outras tabelas levando sem repetição as linhas, que pertençam a ambas as tabelas presentes na operação.

Sintaxe: (Tabela 1)     (Tabela 2)

 

Diferença

Essa operação permite encontrarmos linhas que estão em uma tabela mas não estão em outra. A expressão Tabela 1 Tabela 2 resulta em uma tabela que contém todas as linhas que estão na tabela 1 e não estão na Tabela 2. Observamos que Tabela 1 – Tabela 2 (Exemplo1) é diferente de Tabela 2 – Tabela 1 (Exemplo2).

Sintaxe: (Tabela 1) – (Tabela 2)

 

Junção

Essa operação interage com o modelo relacional, ou seja trabalha com o modelo de relações entre tabelas realizando um produto cartesiano, combinando as linhas e somando as colunas de duas tabelas, só que partindo de campos comuns de ambas para realizar essa “seleção relacional. Essa operação possui uma condição onde se colocam os campos das tabelas que estão sendo usados para se efetivar a junção. Chamamos essa junção de junção com predicado.

(Sintaxe 1): (Junção com Predicado): (Tabela 1) |x| <condição de junção> (Tabela 2)

Outro tipo de junção é a junção natural:

Sintaxe : (Junção Natural): (Tabela 1) |x| (Tabela 2)

Nela não há especificação de condição sendo usado para isso todas as colunas comuns às duas tabelas. As colunas resultantes são a soma das colunas das duas tabelas sem a repetição das colunas idênticas (aparecerão uma vez somente). Não deve ser empregada quando se deseja associar duas tabelas apenas por um ou alguns dos seus atributos idênticos, caso isso seja feito os resultados são imprevisíveis.

 

Divisão

Essa operação produz como resultado a projeção de todos os elementos da primeira tabela que se relacionam com todos os elementos da segunda tabela. Essa operação também pode ser obtida através de outras operações de álgebra relacional.

Sintaxe: (Tabela 1) ÷ (Tabela 2)

 

Renomeação / Atribuição

Renomeação ( p ): É a operação que renomeia uma tabela.

Sintaxe: p Nome(Tabela)

Atribuição ( ß ): É utilizada para simplificar comandos muito extensos definindo então passos de comando.

Sintaxe: Variável ß Tabela


 

Colocando a mão na massa

Agora que já conhecemos um pouco sobre cada operador, suas sintaxes e formas de utilização, vamos colocar a mão na massão e utilizar nosso código de exemplo apresentado a seguir:

— Criando o Banco de Dados – AlgebraRelacional —

Create Database AlgebraRelacional

Go

 

— Acessando o Banco de Dados – AlgebraRelacional —

Use AlgebraRelacional

Go

 

— Criando a Tabela Cargos —

Create Table Cargos

(CodigoCargo Char(2) Primary Key Not Null,

DescricaoCargo Varchar(50) Not Null,

VlrSalario Numeric(6,2) Not Null)

Go

 

— Criando a Tabela Departamentos —

Create Table Departamentos

(CodigoDepartamento Char(2) Primary Key Not Null,

DescricaoDepartamento Varchar(30) Not Null,

RamalTel SmallInt Not Null)

Go

 

— Criando a Tabela Funcionarios —

Create Table Funcionarios

(NumeroRegistro Int Primary Key Not Null,

NomeFuncionario Varchar(80) Not Null,

DtAdmissao Date Default GetDate(),

Sexo Char(1) Not Null Default ‘M’,

CodigoCargo Char(2) Not Null,

CodigoDepartamento Char(2) Not Null)

Go

 

— Criando os relacionamentos —

Alter Table Funcionarios

Add Constraint [FK_Funcionarios_Cargos] Foreign Key (CodigoCargo)

References Cargos(CodigoCargo)

Go

 

Alter Table Funcionarios

Add Constraint [FK_Funcionarios_Departamentos] Foreign Key (CodigoDepartamento)

References Departamentos(CodigoDepartamento)

Go

 

— Inserindo os Dados —

Insert Into Cargos (CodigoCargo, DescricaoCargo, VlrSalario)

Values (‘C1’, ‘Aux.Vendas’, 1350.00),

(‘C2’, ‘Vigia’, 600.00),

(‘C3’, ‘Vendedor’, 1200.00),

(‘C4’, ‘Aux.Cobrança’, 1550.00),

(‘C5’, ‘Gerente’, 1200.00),

(‘C6’, ‘Diretor’, 2500.00),

(‘C7’, ‘Presidente’,5500.00)

Go

 

Insert Into Departamentos (CodigoDepartamento,DescricaoDepartamento,RamalTel)

Values (‘D1’, ‘Assist.Técnica’, 246),

(‘D2’, ‘Estoque’, 589),

(‘D3’, ‘Administração’, 772),

(‘D4’, ‘Segurança’, 810),

(‘D5’, ‘Vendas’, 512),

(‘D6’, ‘Cobrança’, 683)

Go

 

Insert Into Funcionarios (NumeroRegistro, NomeFuncionario, DtAdmissao, Sexo, CodigoCargo, CodigoDepartamento)

Values (1, ‘Cezar Sampaio’, ‘2013-08-10’, ‘M’, ‘C3’, ‘D5’),

(4, ‘Luis Alves Pereira’, ‘2014-03-02’, ‘M’, ‘C4’, ‘D6’),

(34, ‘Pedro Luiz Alves’, ‘2012-05-03’, ‘M’, ‘C5’, ‘D1’),

(21, ‘Silva Souza’, ‘2011-12-10’, ‘M’, ‘C3’, ‘D5’),

(95, ‘Eduardo Oliveira Silveira’, ‘2012-01-05’, ‘F’, ‘C1’, ‘D5’),

(39, ‘Ana Marta Silva’, ‘2013-01-12’, ‘F’, ‘C4’, ‘D6’),

(23, ‘Sergio Mota’, ‘2013-06-29’, ‘M’, ‘C7’, ‘D3’),

(48, ‘Larissa Roberto Aleluia’, ‘2012-06-01’, ‘F’, ‘C4’, ‘D6’),

(5, ‘Ricardo Martins’, ‘2013-10-15’, ‘M’, ‘C3’, ‘D5’),

(2, ‘Simone Gasparini Tune’, ‘2010-02-10’, ‘M’, ‘C2’, ‘D4’)

Go

 

— Exemplo – Operador – Projeção — Qual o nome e data de admissão dos funcionários? —

Select NomeFuncionario, DtAdmissao From Funcionarios

Go

 

— Exemplo – Operador – Seleção/Restrição — Quais os funcionários de sexo masculino? —

Select NumeroRegistro, NomeFuncionario, DtAdmissao,

Sexo, CodigoCargo, CodigoDepartamento

From Funcionarios

Where Sexo = ‘M’

Go

 

— Exemplo – Operadores – Projeção e Seleção — Quais os nomes e data de admissão dos funcionários de sexo masculino? —

Select NomeFuncionario, DtAdmissao From Funcionarios

Where Sexo = ‘M’

Go

 

— Exemplo – Operador – Produto Cartesiano – Trazer as informações dos funcionários e de seus cargos: Linhas de Funcionarios X Linhas de Cargos —

Select F.NumeroRegistro,

F.NomeFuncionario,

F.DtAdmissao,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento

From Funcionarios F Cross Join Cargos C

Order By F.NumeroRegistro Desc

Go

 

— Exemplo – Operador – União — Combinação de Tabelas e Linhas entre selects distintos —

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C1’,‘C3’,‘C5’,‘C7’)

Union

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Go

 

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C1’,‘C3’,‘C5’,‘C7’)

Union All

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Union All

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Go

 

— Exemplo – Operador – Intersecção — Combinação de Tabelas e Linhas entre selects distintos sem repetição de dados no resultado —

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C4’,‘C6’)

Intersect

Select CodigoCargo, DescricaoCargo, VlrSalario

From Cargos

Where CodigoCargo In (‘C2’,‘C3’,‘C6’,‘C7’)

Go

 

— Exemplo – Operador – Diferença – Linhas existentes em uma Tabela que não existem em outra —

Insert Into Cargos (CodigoCargo, DescricaoCargo, VlrSalario)

Values (‘C8’, ‘Aux.Vendas II’, 550.00)

Go

Select F.NumeroRegistro,

F.NomeFuncionario,

F.DtAdmissao,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento

From Funcionarios F

Where Exists (Select CodigoCargo From Cargos)

Go

 

— Exemplo – Operador – Junção – Combinação de Linhas e Colunas entre tabelas que possuem algum tipo de vínculo relacional —

Select F.NumeroRegistro,

F.NomeFuncionario,

F.DtAdmissao,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento

From Funcionarios F Inner Join Cargos C

On F.CodigoCargo = C.CodigoCargo

Order By F.NomeFuncionario, F.CodigoCargo Asc

Go

 

— Exemplo – Operador – Divisão – Relação Completa de todos os dados da Tabela da Esquerda com todos os dados da Tabela da Direita —

Create Table Projetos

(CodigoProjeto Char(8) Primary Key Not Null,

DescricaoProjeto Varchar(50) Not Null)

Go

 

Create Table Equipe

(Codigo Int Primary Key Identity(1,1) Not Null,

NumeroRegistroFuncionario Int Not Null,

CodigoProjeto Char(8) Not Null)

Go

 

— Criando os relacionamentos —

Alter Table Equipe

Add Constraint [FK_Equipe_Funcionarios] Foreign Key (NumeroRegistroFuncionario)

References Funcionarios(NumeroRegistro)

Go

 

Alter Table Equipe

Add Constraint [FK_Equipe_Projetos] Foreign Key (CodigoProjeto)

References Projetos(CodigoProjeto)

Go

 

— Inserindos os Dados —

Insert Into Projetos (CodigoProjeto, DescricaoProjeto)

Values (‘Projeto1’, ‘Suporte’),

(‘Projeto2’, ‘Manutenção’),

(‘Projeto3’, ‘Desenvolvimento’)

Go

 

Insert Into Equipe (NumeroRegistroFuncionario, CodigoProjeto)

Values (‘101’, ‘Projeto1’),

(‘104’, ‘Projeto1’),

(‘134’, ‘Projeto1’),

(‘101’, ‘Projeto2’),

(‘104’, ‘Projeto2’),

(‘101’, ‘Projeto3’)

Go

 

Select E.NumeroRegistroFuncionario,

E.CodigoProjeto,

P.DescricaoProjeto

From Equipe E Inner Join Projetos P

On E.CodigoProjeto = P.CodigoProjeto

Where E.CodigoProjeto = ‘Projeto1’

Go

 

— Exemplo – Operador – Renomeação —

sp_rename ‘Cargos’,‘NovosCargos’

Go

Select * from NovosCargos

 

— Exemplo – Operador – Atribuição —

Select F.NumeroRegistro As ‘Número de Registro’,

F.NomeFuncionario,

F.DtAdmissao As ‘Data de Admissão’,

F.Sexo,

F.CodigoCargo,

F.CodigoDepartamento,

Data=(Select GetDate()) — Atribuindo um valor para uma coluna

From Funcionarios F Inner Join NovosCargos C

On F.CodigoCargo = C.CodigoCargo

Order By F.NomeFuncionario, F.CodigoCargo Asc

Go

 

Legal, legal, você pode reparar que este código de exemplo é bastante simples e abrangente, demonstrando como podemos trabalhar com cada operador e de que forma o SQL Server se enquadra perfeitamente neste conceitos, algo muito comum para qualquer banco de dados ou sistema gerenciador de banco de dados, mas que pode variar a sua forma de análise e utilização.


Sendo assim chegamos ao final de mais uma dica do mês.

Espero que você tenha gostado, que as informações e exemplos publicados aqui possam de alguma maneira ajudar e colaborar com suas atividades diárias sejam elas: profissionais ou acadêmicas.

O conhecimento técnico é muito importante para qualquer profissional, mas o conceito é algo que sempre devemos valorizar e conhecer, a diferença entre um bom profissional e um profissional reconhecido e respeitado no mercado de trabalho é saber aliar o conhecimento teórico com o conhecimento prática, como muitos costumam dizer aliar a téoria a prática, sendo este o objetivo deste post.

Desejo um forte abraço, agradeço mais uma vez a sua visita.

Até mais.

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.

Short Scripts – Janeiro – 2014


Bom dia, Comunidade….

Salve, Salve galera que gosta de Banco de Dados, mais especificamente o Microsoft SQL Server!!!!

Estamos começando mais um ano de muita expectativa com o possível lançamento do Microsoft SQL Server 2014, mas enquanto esta nova versão no chega, vamos trabalhando com as atuais versões.

Revirando o meu baú de Scripts, encontrei alguns códigos que a muitos anos eu utilizei e para falar a verdade nem se lembrava mais.

Pois bem, na relação de hoje você vai encontrar Short Scripts sobre:

  • Acessos de Usuários;
  • Conexões de Usuários;
  • Bloco de Códigos com Múltiplas CTEs;
  • Cálculo de dias úteis;
  • Cláusula values no Select;
  • Função PWDCompare;
  • Função PWDEncrypt;
  • Operador Neested Loop;
  • Operadore Merge Join; e
  • Trigger DDL.

Fique a vontade para copiar os códigos de exemplo, fazer suas alterações, propor melhorias ou mudanças, como também, suas sugestões e críticas.

Segue abaixo a relação de Short Scripts:

1 –  Exemplo – Básico – Multiplas – CTEs – Bloco de Código

;With CTE1 (Codigo, Descricao) As (Select Codigo, Descricao from Tabela1), CTE2 (Valor1, Valor2) As (Select Valor1, Valor2 From Tabela2)

Select T1.*, T2.* From Tabela1 T1, Tabela2 T2

2 – Exemplo – Básico – Trigger DDL – Criação e Exclusão Tabelas

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You must disable Trigger “safety” to drop or alter tables!’
ROLLBACK ;

3 – Exemplo – Calcular Dias Úteis

Set DateFormat dmy

Declare @DiaFinal Int,            @ContarDias Int

Set @DiaFinal = (Select DatePart(Day,GetDate())) Set @ContarDias=0

Set Datefirst 1

While @DiaFinal >= (Select DatePart(Day,GetDate())) And @DiaFinal < 28  Begin   Set @ContarDias = @ContarDias + 1   Set @DiaFinal=(Select DatePart(Day,GetDate()+@ContarDias))  End

SELECT @@DATEFIRST AS ‘1st Day’,  DATEPART(dw,GetDate()+@ContarDias) AS ‘Today’, @ContarDias As “Total de Dias”

4 – Exemplo – Informações – Conexões e Acessos

— Quantidade de Conexões Simultâneas —

SELECT status,

client_net_address as [IP do cliente],

p.hostname as [Nome da máquina do cliente],

[text] as [Texto da consulta],

SPID,

DB_NAME(p.dbid) as [Nome do BD no qual foi executada a query],

p.[program_name] as [Programa solicitante]

FROM sys.dm_exec_connections c INNER JOIN sys.sysprocesses p

on c.session_id = p.spid

CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST

where STATUS IN (‘RUNNABLE’, ‘RUNNING’, ‘SUSPENDED’)

ORDER BY STATUS

— Quantidade de Conexões por banco de dados —

SELECT db_name(dbid) as Banco_de_Dados,

count(dbid) as Qtd_Conexoes

FROM sys.sysprocesses

WHERE dbid > 50

and db_name(dbid) = ‘smartgp’

GROUP BY dbid, loginame

5 – Exemplo – Relação de Usuário conectados

Select SPID As ‘Id’,
Loginame As ‘Usuário – SQL Server’,
HostName As ‘Computador’,
NT_Domain As ‘Domínio’,
NT_UserName As ‘Usuário – Domínio’
From Master..SysProcesses
Where Loginame <> ‘sa’
Order By Loginame

6 – Exemplo – Trabalhando com Operadores Neested Loop + Merge Join

— NESTED LOOP —
SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID = 43659

— MERGE JOIN —
SELECT o.SalesOrderID, o.OrderDate, od.ProductID

FROM dbo.Orders o INNER JOIN dbo.OrderDetails od

ON o.SalesOrderID = od.SalesOrderID

WHERE o.SalesOrderID BETWEEN 43659 AND 44000

7 – Exemplo – Utilizando – Claúsula – Values no Select

Select * from (Values (‘Pedro’, 1),(‘FIT’, 2)) As Teste (About, Valor)

Select * from (Values (‘Valor’)) As Tabela (“Teste”)

8 – Exemplo – Utilizando a Função – PWDCompare e PWDEncrypt

— Inserindo a senha criptografada —

INSERT INTO Usuarios_novos VALUES (CONVERT(VARBINARY(255), PWDENCRYPT(‘Senha_Teste’)))

— Comparando a senha digitada com a senha criptografada —

SELECT PWDCOMPARE(‘Senha_Teste’,CONVERT(VARBINARY(255), PWDENCRYPT(‘Senha_Teste’)), 0) AS RESULTADO

Mais uma vez obrigado por sua visita, espero que esta material possa ser útil.

Nos encontramos na próxima Short Script.

Até mais.

Dica – Respeitando a precedência de operadores no SQL Server

Dica – Respeitando a precedência de operadores no SQL Server.


Salve comunidade, tudo bem?

A dica de hoje tem a finalidade de ilustrar algo que normalmente não se preocupamos muito e que por diversos motivos pode se passar despercebido. Estou me referindo a Precedência de Operadores Transact-SQL utilizados no SQL Server.

Não estou somente me referindo as operadores matemáticos, mas sim a todos os operadores que podem ser utilizados em querys, scripts, atividades administrativas, entre outros.

Os operadores são classificados e definidos em níveis de precedência e relavância, algo que representa ao SQL Server uma possível ordem de análise e execução. Para ilustrar melhor esta ordem de precedência e níveis, apresenta abaixo a Tabela 1.

Nível

Operadores

1

~ (Não de bit a bit).

2

* (Multiplicação), / (Divisão),% (Módulo).

3

+ (Positivo), – (Negativo), + (Adição), +  (Concatenar) – (Subtração) & (AND de bit a bit), ^ (OR Exclusivo de bit a   bit), | (OR de bit a bit).

4

=, >, <, >=, <=, <>, !=, !>,  !< (Operadores de comparação).

5

NOT

6

AND

7

ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

8

= (Atribuição).

Tabela 1 – Relação de operadores, níveis de precedência e descrição.

Observações: Uma expressão reconhecida pelo SQL Server como complexa, pode conter vários operadores, isso indica a análise da precedência de operador determina a seqüência na qual as operações são executadas. A ordem de execução pode afetar o valor resultante significativamente. Um operador em níveis superiores é avaliado antes de um operador em um nível inferior.

Quando dois operadores em uma expressão tiverem o mesmo nível de precedência de operador, eles serão avaliados da esquerda para a direita em sua posição na expressão.

Para ilustrar este comportamento, apresenta o Exemplo 1 a seguir:

— Exemplo 1: Expressão utilizando operadores de mesmo nível —

DECLARE @MyNumber int

SET @MyNumber = 4 – 2 + 27 — Operadores de mesmo nível declarados na instrução —

SELECT @MyNumber

Como já foi destacado, a ordem de declaração dos operadores poderá gerar impactos e mudanças nos resultados gerados pelo SQL Server, para evitar possíveis mudanças nos resultados, indico a utilização de parênteses para substituir a precedência definida dos operadores em uma expressão. Tudo que estiver entre parênteses é avaliado primeiro para gerar um único valor antes daquele que poderá ser usado por qualquer operador fora dos parênteses.

Para ilustrar este cenário, apresento a seguir o Exemplo 2.

— Exemplo 2: Utilizando parênteses em instruções com operadores do mesmo nível —

DECLARE @MyNumber int

SET @MyNumber = 2 * 4 + 5

SELECT @MyNumber

Na instrução apresentada anteriormente, a expressão usada na seguinte instrução SET, o operador de multiplicação tem uma precedência maior que o operador de adição. Portanto, ele é avaliado primeiro; o resultado da expressão é 13. Neste caso o resultado desta instrução esta incorreto, para corrigir esta pequena falha adicionamos o parênteses, conforme apresenta a seguir:

DECLARE @MyNumber int

SET @MyNumber = 2 * (4 + 5) — Adicinando os parênteses para auxiliar na definição de precedência e ordem de execução —

SELECT @MyNumber

Após adicionarmos os parênteses o SQL Server, irá realizar uma nova análise e definição da ordem de precedência e execução dos operadores, neste caso, será realizado primeiro a adição dos valores e depois a multiplicação, gerando como resultado o número 18.

Para finalizar, vamos analisar uma instrução com parênteses aninhados. Se uma expressão tiver parênteses aninhados, a expressão mais aninhada será avaliada primeiro. O Exemplo 3 apresentado a seguir contém parênteses aninhados, com a expressão 5 – 3 no conjunto de parênteses mais aninhado. Essa expressão gera um valor de 2. Em seguida, o operador de adição (+) adiciona esse resultado a 4. Isso gera um valor de 6. Finalmente, os 6 são multiplicados por 2 para gerar um resultado de expressão de 12.

— Exemplo 3: Utilizando parênteses aninhados em uma instrução —

DECLARE @MyNumber int

SET @MyNumber = 2 * (4 + (5 – 3) )

SELECT @MyNumber

Bom pessoal, vou encerrar esta dica, espero que todos possam ter gostado e entendido a importância em se analisar a precedência dos operadores em suas instruções.

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

Até mais.