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.

SQL Server Management Studio 17.2 liberado para download


Você conhece o SQL Server Management Studio?

SQL Server Management Studio é um ambiente integrado para o gerenciamento de qualquer infra-estrutura SQL do SQL Server banco de dados SQL. SQL Server Management Studio fornece ferramentas para configurar, monitorar e administrar as instâncias de SQL. Use o SQL Server Management Studio para implantar, monitorar e atualizar os componentes de camada de dados usados por suas aplicações, bem como construir consultas e scripts.

Use o SQL Server Management Studio (SSMS) para consultar, projetar e gerenciar seus bancos de dados e data warehouses, onde quer que estejam – no computador local ou na nuvem.

SQL Server Management Studio é grátis!

Baixar o SQL Server Management Studio 17,2

Baixar o SQL Server Management Studio 17,2 atualizar pacote (atualizações 17 para 17,2)

A instalação de 17 SSMS não atualizar ou substituir as versões do SQL Server Management Studio 16.x ou anterior. SSMS 17 instalações lado a lado com as versões anteriores para que ambas as versões estão disponíveis para uso. Se um computador contiver instalações lado a lado de SQL Server Management Studio, verifique se que você começar a versão correta para suas necessidades específicas. A versão mais recente é rotulado como Microsoft SQL Server Management Studio 17e tem um novo ícone:

SSMS 17.x

Informações de versão

O número de versão: 17,2 o número de compilação para esta versão: 14.0.17177.0.

Novidades

SSMS 17,2 é a versão mais recente do SQL Server Management Studio. A geração de 17 do SQL Server Management Studio fornece suporte para quase todas as áreas de recurso no SQL Server 2008 através de SQL Server de 2017. Versão 17 também suporta SQL Analysis Service PaaS.

Versão 17,2 inclui:

    • Autenticação de vários fatores (AMF)
      • Autenticação de usuário múltiplo AD Azure para autenticação Universal com autenticação de vários fatores (UA com AMF)
      • Um novo campo de entrada de credencial do usuário foi adicionado para autenticação Universal com AMF para oferecer suporte à autenticação multi-usuário.
    • A caixa de diálogo de conexão agora suporta os seguintes métodos de 5 autenticação:
      • Autenticação do Windows
      • Autenticação do SQL Server
      • Active Directory – Universal, com o apoio do MFA
      • Active Directory – senha
      • Active Directory – integrado
    • Banco de dados de importação/exportação para assistente de DacFx agora pode usar autenticação Universal com AMF.
    • ADAL biblioteca gerenciada usada pela autenticação Azure AD Universal com AMF foi atualizada para versão 3.13.9.
    • Uma nova interface CLI suportando configuração de admin AD Azure para banco de dados SQL e SQL Data Warehouse.
    • Janela de saída possui entradas para consultas executadas durante a expansão de nós do pesquisador de objetos.
    • Habilitado o modo de exibição designer para bancos de dados SQL Azure
    • Mudaram-se as opções de script padrão para scripts de objetos de Object Explorer no SQL Server Management Studio:
      • Anteriormente, o padrão em uma instalação nova era ter o destino do script gerado a versão mais recente do SQL Server (atualmente SQL Server 2017).
      • No SSMS 17,2 foi adicionada uma nova opção: Configurações de Script Match a fonte. Quando definida como True, o script gerado destina-se a mesma versão, tipo do motor e do motor edition como servidor do objeto sendo roteirizado é de.
      • O valor de Configurações de Script Match a fonte é definido para True por padrão, para que novas instalações do SQL Server Management Studio automaticamente padrão serão sempre scripts de objetos para o mesmo destino que o servidor original.
      • Quando o valor de Configurações de Script Match a fonte é definido como False, as opções de destino script normal serão habilitadas e funcionam como fizeram anteriormente.
      • Além disso, todas as opções de script foram movidas para a sua própria seção – Opções de versão. Eles não estão mais sob Opções gerais de Scripting.
    • Adicionado suporte para nuvens nacional em “Restaurar a partir de URL”
    • QueryStoreUI relatórios agora suporta adicional métricas (número de linhas, DOP, CLR tempo etc.) do sys.query_store_runtime_stats.
    • IntelliSense agora é suportado por banco de dados SQL Azure.
    • Segurança: caixa de diálogo conexão será o padrão para não confiar em certificados de servidor e solicitando criptografia para conexões de banco de dados SQL Azure
    • Melhorias gerais em torno de suporte para o SQL Server no Linux:
      • Nó de correio do banco de dados está de volta
      • Abordadas algumas questões relacionadas com caminhos
      • Melhorias de estabilidade de Monitor de atividade
      • Caixa de diálogo Propriedades de Conexão exibe a plataforma correta
    • Relatório de servidor Dashboard desempenho agora disponível como um relatório padrão:
      • Pode se conectar ao SQL Server 2008 e versões mais recentes.
      • Subrelatório de índices ausentes usa Pontuação para ajudar a identificar índices mais útil.
      • Estatísticas de espera histórica sub relatam agora agregados esperas ser categoria. Ocioso e esperas de sono filtrados por padrão.
      • Novo histórico travas subrelatório.
    • Pesquisa de plano de execução nó permite buscar em Propriedades do plano. Facilmente procure qualquer propriedade de operador como nome da tabela. Para usar esta opção quando visualizar um plano:
      • Botão direito do mouse no plano e no menu de contexto clique na opção Localizar nó
      • Usar CTRL + F

Para obter uma lista completa de alterações, consulte SQL Server Management Studio – Changelog (SSMS).

Suporte para versões

    • Esta versão do SQL Server Management Studio funciona com todas as versões suportadas do SQL Server 2008 – 2017 de SQL Server e fornece o maior nível de suporte para trabalhar com as mais recentes características de nuvem no banco de dados SQL Azure e SQL Azure Data Warehouse.
    • Não há nenhum bloco explícito para SQL Server 2000 ou SQL Server 2005, mas alguns recursos podem não funcionar corretamente.
    • Além disso, o SSMS 17 pode ser instalado lado a lado com o SQL Server Management Studio 16.x ou SQL Server 2014 SSMS e versões anteriores.

Sistemas operacionais com suporte

Esta versão do SQL Server Management Studio suporta as seguintes plataformas de 64 bits quando usado com o mais recente disponível service pack:

    • 10 Windows (64 bits)
    • Windows 8.1 (64 bits)
    • Windows 8 (64 bits)
    • Windows 7 (SP1) (64-bit)
    • Windows Server 2016 *
    • Windows Server 2012 R2 (64 bits)
    • Windows Server 2012 (64 bits)
    • Windows Server 2008 R2 (64 bits) 

* O SSMS 17 baseia-se na shell do Visual Studio 2015 isolada, que foi lançado antes de 2016 de servidor Windows. Microsoft leva a sério o compatibilidade de aplicativo e garante que já enviados com aplicações continuam a correr sobre as versões mais recentes do Windows. Para minimizar problemas na execução do SQL Server Management Studio em 2016 de servidor Windows, certifique-se de que SSMS tem todas as últimas atualizações aplicadas. Se você tiver quaisquer problemas com o SQL Server Management Studio no Windows Server 2016, contate o suporte. A equipe de suporte determina se o problema for com o SQL Server Management Studio, Visual Studio, ou com compatibilidade do Windows. A equipe de suporte roteia a questão para a equipe apropriada para maiores investigações.

Idiomas disponíveis

Esta versão do SQL Server Management Studio pode ser instalado nos seguintes idiomas:+

SQL Server Management Studio 17,2:
chinês (República Popular da China) | Chinês (Taiwan) | Inglês (Estados Unidos) | Francês | Alemão | Italiano | Japonês | Korean | Português (Brasil) | Russas | Espanhol

SQL Server Management Studio 17,2 atualizar pacote (atualizações 17 para 17,2):
chinês (República Popular da China) | Chinês (Taiwan) | Inglês (Estados Unidos) | Francês | Alemão | Italiano | Japonês | Korean | Português (Brasil) | Russas | Espanhol

Download de versões anteriores

Versões anteriores do SQL Server Management Studio

Links adicionais

Fontes e Direitos Autorais: Microsoft – 07/08/2017 – https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Dica do Mês – Microsoft SQL Server – Identificando as transações que estão utilizando o Transact Log


Bom dia pessoal, tudo bem?

Véspera de feriado religioso e um dos mais importantes para boa parte da população mundial! Justamente por ser véspera de feriado hoje é um daqueles dias que qualquer profissional da área de banco de dados reza para que nada ocorra de errado próximo ás 17hrs ou mais conhecido como 5 horas da tarde.

Pensando neste tipo de necessário o post de hoje dedicado a sessão Dica do Mês tem o objetivo de ajudar você profissional que esta em apuros neste momento tentando identificar o que pode estar ocorrendo em seu ambiente ou servidor Microsoft SQL Server.

Hoje vou apresentar como você pode no Microsoft SQL Server a partir da versão 2008 R2 identificar quais transações estão realizando o acesso e demandando informações para o Transact – Log, arquivo utilizado por qualquer banco de dados existente em seu servidor ou instância SQL Server para registrar dados relacionados as transações atuais ou já processadas, sendo este mais conhecido como log de transações.

Estão vamos em frente…..


Introdução

Quando se referimos ao log de transações, temos sempre aquele boa e velha definição, “o transact log é o arquivo utilizado pelo banco de dados para registrar o que esta sendo processado por nossas transações, conhecido como o dedo duro”, na verdade o transact-log é bem mais do que isso, trata-se de um dos principais componentes de qualquer banco de dados criado no Microsoft SQL Server que nos permite através do seu uso garantir a continuidade e disponibilidade de nossos dados para diversos cenários de armazenamento e principalmente recuperação de informações.

Durante o processamento de uma ou mais querys o transact-log pode apresentar comportamentos diversas de acordo com o que esta sendo processado, mas principalmente no que se relacionado aos processos de escrita e leitura do log, onde nossas querys podem gerar processos de gravação de dados no transact-log e internamente o SQL Server pode necessitar a acessar outros dados em log para atender outras possíveis demandas.

Estes processos são conhecidos como leitura ou escrita do transact-log, situações que podem impactar aleatoriamente no processamento de nossas querys.

Pensando neste tipo de possibilidade, o post de hoje visa ilustrar e apresentar como através do Management Studio ferramenta de administração e gerenciamento do Microsoft SQL Server podemos em conjunto com as DMVs – Dynamic Management Views listadas abaixo, monitorar, catalogar e identificar quais querys ou transações estão fazendo uso do Transact-Log em possíveis processos de leitura ou escrita do log.

Dynamic Management Views

Para saber mais sobre cada DMV acesse as referências apresentadas no final do post:

  • sys.dm_tran_session_transactions;
  • sys.dm_tran_active_transactions;
  • sys.dm_tran_database_transactions;
  • sys.dm_exec_sessions;
  • sys.dm_exec_requests; e
  • sys.dm_exec_sql_text.

O Cenário

Para tentar ilustrar como podemos realizar de maneira fácil este tipo de monitoramento e identificação, vamos criar um pequeno cenário através do Bloco de Código 1, onde estaremos realizando a criação de um novo banco de dados denominado UsingTransactLog para armazenar a tabela TabelaTransactionLog.

— Bloco de Código 1 —
— Criando o banco de dados —
Create Database UsingTransactLog
Go

— Acessando o banco de dados —
Use UsingTransactLog
Go
— Criando a tabela —
Create Table TabelaTransactLog
(TransactionID Int Default Current_Transaction_ID(),
TransactionUser Varchar(100) Default System_User,
TransactionDate DateTime Default Current_TimeStamp)
Go

 

Note que nosso tabela TabelaTransactLog possui um simples estrutura composto por três colunas: TransactionID, TransactionUser e TransactionDate, sendo estes elementos básicos utilizados para catalogar o ID da transação em processamento, o usuário conectado ao banco de dados e data/hora do momento da inserção da linha.

Até aqui nada de tão importante, mas importante para podemos simular o uso do log, nosso próximo passo consiste no processamento do Bloco de Código 2, onde estaremos realizando o processamento de uma transação de inserção de 50.000 mil linhas de registros, sendo a operação de insert uma das mais diversas operações que demando processos de escrita ao transact – log, sendo assim, apresento a seguir o Bloco de Código 2:

— Bloco de Código 2 —
Insert Into TabelaTransactLog Default Values
Go 50000

Observe que não estamos passando nenhum tipo de valores para nossas colunas, na verdade estou forçando o SQL Server a utilizar a criação de valores padrões durante o processamento de cada linha de registro através da instrução Default Values (valores padrões), o que vai obrigar nossa instância e ter que gerar valores durante o tempo de processamento e posteriormente registrar em log.

Muito bem, o processamento desta massa de dados, teremos a necessidade de abrir uma nova query para justamente fazer uso do Bloco de Código 3, sendo este o principalmente elemento do nosso cenário, a query que nos permiti monitorar e identificar as transações que estão em tempo real utilizando o transact-log. Então abra uma nova query e log o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Go

SELECT tst.session_id As ‘Sessão’,
es.original_login_name As ‘Login’,
DB_NAME(tdt.database_id) AS ‘Database’,
DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS ‘Tempo decorrido’,
tdt.database_transaction_log_record_count AS ‘Espaço Utilizado’,
CASE tat.transaction_state
WHEN 0 THEN ‘A transação não foi completamente inicializada ainda…’
    WHEN 1 THEN ‘A transação foi inicializada, mas não começou…’
    WHEN 2 THEN ‘A transação esta ativa…’
    WHEN 3 THEN ‘A transação foi encerrada…’
    WHEN 4 THEN ‘Foi iniciado o processo de confirmação sobre o transação distribuída…’
    WHEN 5 THEN ‘A transação está em estado preparação e esperando resolução…’
    WHEN 6 THEN ‘A transação foi confirmada…’
    WHEN 7 THEN ‘A transação esta sendo revertida para o estado anterior…’
    WHEN 8 THEN ‘A transação foi revertida para o estado anterior…’
   ELSE ‘Estado da transação desconhecido’
   END AS ‘Estado da Transação’,
SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1, ((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset ) / 2 ) + 1) AS ‘Query Atual’,
TXT.text AS ‘Query relacionada’,
es.host_name As ‘Hostname’,
   CASE tat.transaction_type
    WHEN 1 THEN ‘Transação Read/Write’
    WHEN 2 THEN ‘Transação Read-Only’
    WHEN 3 THEN ‘Transação de Sistema’
                WHEN 4 THEN ‘Transação distribuída’
            ELSE ‘Tipo de Transação desconhecido’
            END AS ‘Tipo da Transação’,
tat.transaction_begin_time AS ‘Iniciado em’
FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS tdt
ON tst.transaction_id = tdt.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON tst.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON tst.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
ORDER BY [Tempo decorrido] DESC
Go
Pois bem, você pode estar querendo saber um pouco mais sobre o que exatamente esta acontecendo ao processar o Bloco de Código 3, pode ficar tranquilo que a seguir vou fazer um pequeno overview sobre este bloco de código, começando pelo conjunto de colunas retornadas por ele:
  • Sessão – Apresenta o ID da sessão que esta utilizando o transact-log;
  • Login – Nome da conta de login que esta conectada ao SQL Server;
  • Database – Nome do banco de dados utilizado pela query que esta acessando o transact-log;
  • Tempo decorrido – Representa o tempo de processamento que o query esta demandando para utilizar o transact-log;
  • Log Record Count– Identifica a quantidade de registros de log gerados pela query ou transações em processamento;
  • Estado da transação – Esta coluna apresenta o estado atual da query em processamento, sendo este formado por um conjunto de valores;
  • Query Atual – Apresenta as instruções que estão sendo processadas por cada query;
  • Query Relacionada– Apresenta as instruções processadas por querys que podem estar relacionadas com as atuais querys em processamento;
  • Hostname – Apresenta o nome dos hosts utilizados por cada query em processamento;
  • Tipo de Transação – Identifica se a transação em processamento esta realizando acesso de leitura ou escrita ao log, bem como, se esta query é uma query de sistema ou distribuída; e
  • Iniciado em – Apresenta o horário de início do acesso ao transact-log por parte da query em processamento.

Agora que você já conhece um pouco sobre cada coluna que será retornada após o processamento do Bloco de Código 3, vou abordar o uso da instrução Set Transaction Isolation Level declarada no início do código, que possui a finalidade de alterar no nível de isolamento do nosso banco de dados para Read Uncommitted afim de força o SQL Server a descartar ou desconsiderar qualquer possível ocorrência de bloqueios tanto no nível de tabela e registros, mas principalmente no escopo de banco de dados.

Nota: Se você me perguntar que isso é obrigatório ser feito para conseguir identificar as querys que estão utilizando o transact-log eu vou responder NÃO, isso não é obrigatório, mas pode ser considerada uma possível solução para garantir que o Bloco de Código 3 possa ser processado e consultar as dynamic management views na busca das informações catalogadas por ela em tempo de execução.

Ufa, estamos quase lá, agora nos resta fazer a execução do mesmo e observar o retorno dos dados coletados por este bloco de código, com base, na Figura 1 apresentada abaixo:

Figura 1 – Relação de querys em execução utilizando transact-log.

Perfeito, nosso monitoramento funcionou, conseguimos coletar os dados referentes as querys que estão neste momento em processamento fazendo acesso e utilizando o transact-log criado para o banco de dados UsingTransactLog.

Isso não é sensacional, desta forma, chegamos ao final de mais um post dedicado a sessão Dica do Mês, espero que você tenha gostado.

Referências

Post anteriores

  1. https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/
  2. https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

  3. https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

  4. https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Administrar, gerenciar, cuidar e prover um ambiente sempre no melhor estado possível não é uma das atividades mais tranquilas e simples desempenhadas por profissionais ou administradores de banco de dados, mas também não pode ser considerada um “bicho de sete cabeças” ou uma “caixa preta” ainda mais quando este servidor de banco de dados utiliza o Microsoft SQL Server.

Neste post você pode observar que através do uso de alguns DMVs – Dynamic Management Views a Microsoft nos permite coletar, analisar e entender de forma visual e muito intuitiva como o um servidor ou instância SQL Server gerencia o que esta sendo processado por nossas aplicações, querys ou transações no que se relaciona aos seus componentes e recursos internas, algo que nos permite em diversas situações de administração ou manutenção tentar identificar o que pode estar ocorrendo, bem como, impactando em nosso ambiente.

Agradecimentos

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

Um forte abraço, bom feriado, espero que neste momento você não esteja no seu trabalho tentando resolver problemas do seu servidor (kkkkk).

Valeu….

Short Scripts – Março 2017


Hoje é sexta – feira, sexta – feira…

Salve, salve comunidade e amantes de bancos de dados e SQL Server, Tudo bem? Mais uma final de semana próximo após uma longa semana de muito trabalho, se eu for falar de muito trabalho, sinceramente  esta semana foi complicada, repleta de novidades e muita troca de conhecimento.

Conforme o prometido no final de 2016, estou retornando com o primeiro post da sessão Short Scripts, sessão criada a alguns anos no meu blog que lentamente esta conseguindo ajudar diversos profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Como já destaquei acima, este é o primeiro post de 2017 dedicado exclusivamente a sessão Short Scripts, na relação de scritps selecionados para hoje, você vai poder encontrar códigos relacionados com os seguintes assuntos:

  • Auditoria,
  • Comando Intersect,
  • Comando OpenQuery,
  • Comando Order By,
  • Datatype Char,
  • Datatype Int,
  • DMF Sys.dm_exec_sessions,
  • DMV Sys.system_internals_partitions,
  • DMV Sys.system_internals_allocation_units,
  • Função Substring,
  • Índices,
  • Informações sobre conexão de usuário,
  • Páginas de Dados,
  • Trigger, e
  • Variáveis.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Março 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados antes de serem publicados, 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, melhorar cada um destes códigos.

Short Scripts

— Short Script 1 – Realizando Auditoria in Live —

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END –
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50              — Ignore system spids.
AND session_Id NOT IN (@@SPID)     — Ignore this current statement.
ORDER BY 1, 2
Go

— Short Script 2 – Utilizando comando Intersect com Datatypes Char e Int —

Create Table #A (x Char(2));
Go
Insert Into #A Values (‘1’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘2’);
Insert Into #A Values (‘3’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘5’);
Insert Into #A Values (‘6’);
Insert Into #A Values (‘9’);
Go
Create Table #B (M BigInt);
Go
Insert Into #B Values(5);
Insert Into #B Values(5);
Insert Into #B Values(6);
Insert Into #B Values(7);
Insert Into #B Values(7);
Go
— (Select #1)
SELECT x AS ‘Select #1’ FROM #A
INTERSECT SELECT M FROM #B
Go
— (Select #2)
SELECT DISTINCT(x) AS ‘Select #2’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #3)
SELECT DISTINCT(x) AS ‘Select #3’
FROM #A LEFT OUTER JOIN #B
ON #A.x = #B.M
Go
— (Select #4)
SELECT DISTINCT(x) AS ‘Select #4’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— (Select #5)
SELECT x AS ‘Select #5’
FROM #A INNER JOIN #B
ON #A.x = #B.M
Go
— Short Script 3 – Utilizando comando OpenQuery com variáveis —
— Valores Básicos —
DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = ‘teste’
SELECT  @TSQL = ‘SELECT * FROM OPENQUERY(MeuLinkedServer,”SELECT * FROM MinhaTabela WHERE User = ””’ + @VAR + ”””’)’
EXEC (@TSQL)
Go
— Query Complexa —
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = ‘MyLinkedServer’
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(‘+ @LinkedServer + ‘,”’
SET @TSQL = ‘SELECT au_lname, au_id FROM pubs..authors”)’
EXEC (@OPENQUERY+@TSQL)
— Use o Sp_executesql procedimento armazenado —
DECLARE @VAR char(2)
SELECT  @VAR = ‘CA’
EXEC MyLinkedServer.master.dbo.sp_executesql
N’SELECT * FROM pubs.dbo.authors WHERE state = @state’,
N’@state char(2)’,
@VAR
Go
— Short Script 4 – Realizando order by com base na função SubString —
Declare @Tabela Table
(Codigo VarChar(15))
Insert Into @Tabela Values(‘191-XXX-003’)
Insert Into @Tabela Values(‘192-XXX-003’)
Insert Into @Tabela Values(‘193-XXX-003’)
Insert Into @Tabela Values(‘194-XXX-003’)
Insert Into @Tabela Values(‘195-XXX-003’)
Insert Into @Tabela Values(‘191-XXX-001’)
Insert Into @Tabela Values(‘192-XXX-001’)
Insert Into @Tabela Values(‘193-XXX-001’)
Insert Into @Tabela Values(‘194-XXX-001’)
Insert Into @Tabela Values(‘195-XXX-001’)
Insert Into @Tabela Values(‘191-XXX-002’)
Insert Into @Tabela Values(‘192-XXX-002’)
Insert Into @Tabela Values(‘193-XXX-002’)
Insert Into @Tabela Values(‘194-XXX-002’)
Insert Into @Tabela Values(‘195-XXX-002’)
Select * from @Tabela
Order By SubString(codigo,Len(Codigo)-2,3) Asc
Go
— Short Script 5 – Monitorando querys em execução —
SELECT
DES.SESSION_ID,
DES.CPU_TIME,
DES.READS,
DES.WRITES,
DES.LOGICAL_READS,
DES.ROW_COUNT,
DER.SESSION_ID,
DES.STATUS,
DES.HOST_NAME,
DES.PROGRAM_NAME,
DES.LOGIN_NAME,
DES.ORIGINAL_LOGIN_NAME,
DEC.CLIENT_NET_ADDRESS,
DEC.AUTH_SCHEME,
DEC.NET_TRANSPORT,
SUBSTRING(T.[TEXT], DER.[STATEMENT_START_OFFSET] / 2,
COALESCE(NULLIF(DER.[STATEMENT_END_OFFSET], – 1) / 2, 2147483647)) AS COMANDO
FROM
SYS.DM_EXEC_SESSIONS AS DES
INNER JOIN SYS.DM_EXEC_REQUESTS DER
ON DER.BLOCKING_SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_CONNECTIONS DEC
ON DEC.SESSION_ID = DES.SESSION_ID
INNER JOIN SYS.DM_EXEC_REQUESTS DER2
ON DER2.SESSION_ID = DES.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DER.[SQL_HANDLE]) AS T
GO
— Short Script 6 – Identificando tabelas e suas respectivas páginas de dados —
— Exemplo 1: —
SELECT P.partition_id,
OBJECT_NAME(P.object_id) As ObjectName,
U.allocation_unit_id,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.Partitions As P INNER JOIN Sys.Allocation_Units As U
ON P.hobt_id = U.container_id
Inner Join Sys.system_internals_allocation_units SU
On u.allocation_unit_id = su.allocation_unit_id
Go
— Exemplo 2: —
SELECT SIP.partition_id,
OBJECT_NAME(SIP.object_id) As ObjectName,
sip.rows,
SU.First_Page,
SU.Root_Page,
SU.First_IAM_Page
From Sys.system_internals_partitions As SIP Inner Join Sys.system_internals_allocation_units SU
On sip.partition_id = su.allocation_unit_id
Go
— Short Script 7 – Identificando índices com Escrita Excessiva —
— Quantidade de Índices com Escrita Excessiva em comparação a leitura —
SELECT  OBJECT_NAME(s.object_id),
i.name,
i.type_desc
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK ) Inner JOIN sys.indexes i WITH (NOLOCK)
ON s.index_id = i.index_id
AND s.object_id = i.object_id
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Quantidade de Índices com Escrita Excessiva —
SELECT  COUNT(*)
FROM    sys.dm_db_index_usage_stats s WITH ( NOLOCK )
WHERE   OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND s.user_updates > ( s.user_seeks + s.user_scans + s.user_lookups )
AND s.index_id > 1
Go
— Short Script 8 – Identificando o espaço ocupado por conexões em uso —
SELECT A.session_id,
B.host_name, B.Login_Name ,
(user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 as TotalalocadoMB,
D.Text
FROM sys.dm_db_session_space_usage A Inner JOIN sys.dm_exec_sessions B
ON A.session_id = B.session_id
Inner JOIN sys.dm_exec_connections C
ON C.session_id = B.session_id
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) As D
WHERE A.session_id > 50
and (user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128 > 100 — Ocupam mais de 100 MB
ORDER BY totalalocadoMB desc
COMPUTE sum((user_objects_alloc_page_count + internal_objects_alloc_page_count)*1.0/128)
Go
— Short Script 9 – Obtendo a diferença de datas entre registro anterior e o próximo registro —
Create Table Datas
(ID Int Identity(1,1),
DataInicio Date,
DataFinal Date)
Go
Insert Into Datas Values (GETDATE(), GETDATE()+30)
Insert Into Datas Values (GETDATE()+1, GETDATE()+20)
Insert Into Datas Values (GETDATE()+2, GETDATE()+10)
Insert Into Datas Values (GETDATE(), GETDATE()+5)
Go
— Exemplo 1 —
Select I.ID, I.DataInicio, F.DataFinal,
DateDiff(D, I.DataInicio, F.DataFinal) As Intervalo
From Datas I Left Join Datas F
On I.ID = F.ID + 1

Go

— Exemplo 2 —
SELECT
[current].Id,
[current].Time CurrentValue,
[next].Time          NextValue
FROM #temp AS [current] LEFT JOIN #temp AS [next]
ON [next].Id = (SELECT MIN(Id) FROM #temp

                                 WHERE Id > [current].Id)
Go
— Short Script 10 – Criando um trigger condicional —
–Criando a Table de Novos Produtos–
Create Table NovosProdutos
(Codigo Int Identity(1,1),
Descricao VarChar(10))
–Criando a Table de Histórico Novos Produtos–
Create Table HistoricoNovosProdutos
(Codigo Int,
Descricao VarChar(10))
Go
–Inserindo valores —
Insert Into Novosprodutos Values(‘Arroz’)
Insert Into Novosprodutos Values(‘Arroz1’)
Insert Into Novosprodutos Values(‘Arroz2’)
Insert Into Novosprodutos Values(‘Arroz3’)
Go
–Criando a Trigger para controle de histórico–
Create TRIGGER T_Historico
ON NovosProdutos
for update
AS
IF (Select Descricao from Inserted) <> (Select Descricao from Deleted)
BEGIN
INSERT Into HistoricoNovosProdutos (Codigo, Descricao)
SELECT Codigo, Descricao FROM INSERTED
END
Go
–Fazendo os teste —
Update NovosProdutos
Set Descricao=’Arroz 4′
Where Codigo = 1
Go
Update NovosProdutos
Set Descricao=’Arroz1′
Where Codigo = 2
Go
Select * from NovosProdutos
Go

Muito bem, 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 e 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 bastante 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

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

Nos encontramos em breve nos próximos posts desta e outras sessões do meu blog, valeu.

Short Scripts – Dezembro 2016


Buenas tardes….. Comunidade.

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

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

O post de hoje

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

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

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

Short Scripts

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

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

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

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

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

Links

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

Agradecimento

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

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

Dica do Mês – SQL Server 2016 SP1 – Comando Create Or Alter


Muito bom dia…..

Olá pessoal, mais uma semana começando. Estou retornando com mais um post dedicado a sessão Dica do Mês, este é o post de número 11 dedicado de forma exclusiva a esta sessão.

Como você já deve ter observado desde o mês de outubro todos os posts das sessões: Dicas do Mês, Para que Serve, Short Scripts e Material de Apoio estão apresentado o mesmo layout, sendo esta uma das sugestões que os meus seguidores solicitaram, e hoje não será diferente.

A dica de hoje é bastante simples e esta totalmente relacionada a uma novidade apresentanda com o lançamento do Service Pack 1 do Microsoft SQL Server 2016.

E ai você já esta se informando sobre este novo service pack? Já realizou o download? Posso lhe dizer que diversas melhorias foram implementas, bem como, muitas mas muitas mudanças identificadas pela equipe de desenvolvimento da Microsoft foram realizadas.

Se quiser saber mais sobre este novo conjunto de atualizações e melhorias dedicadas para o Microsoft SQL Server 2016, acesses os links abaixo:

 

Voltando ao post de hoje, vamos em frente, seja bem vindo a mais um Dica do Mês!!!


Introdução

A cada nova versão do Microsoft SQL Server que a Microsoft libera ou até mesmo após um service pack ou cummulative pack disponibilizados para download, sempre fazemos aquelas perguntas:

  • O que será que melhorou?
  • Quais correções foram definitivamente corrigidas?
  • Será que alguma solicitação do Connection foram atendidas?

Enfim sempre estamos querendo descobrir o que realmente o tipo de engenheiros e desenvolvedores voltados para o SQL Server estão pensando em melhorar e corrigir no produto.

Para esta versão não foi diferente como de costume desde a versão 2012 a Microsoft esta liberando em doses homeopáticas pequenas novidades voltadas para a linguagem Transact-SQL e isso se manteve no SP1 da versão 2016 com a introdução do comando Create or Alter na mesma instrução.

Isso mesmo você pode estar se perguntando ou até mesmo pensando, mas somente agora na versão 2016 este tipo de instrução foi adicionada? A resposta é sim, somente agora pois, algo que para muitos parecia ser simples se comparado com outras ferramentas de banco de dados como Oracle ou MySQL, mas como diz aquele velho ditado: “Nem tudo o que parece é” e isso também se justifica ao Microsoft SQL Server.

Problema

Você saberia me dizer quantas vezes teve que realizar a criação de um recurso programável em um servidor ou instância SQL Server? Algo do tipo Stored Procedure, Function, Trigger ou Views?

E realizar uma ou várias alterações na estrutura de codificação de algum tipo destes recursos? Com certeza é algo bastante comum de ser realizado principalmente quando estamos criando nossos ambientes.

Por muitos anos este tipo de atividade a ser realizada na linguagem Transact-SQL deveria ser feita de forma separada. Onde devemos basicamente seguir uma regra de criar um destes tipos de objetos através do comando Create e caso tenhamos a necessidade de realizar algum tipo de alteração utilizar o comando Alter.

Então, e se agora com o lançamento do service pack 1 para o SQL Server 2016 eu te dizer que isso já pode ser realizado de uma única vez, você acredita?

Eu posso te dizer que acredito pois é verdade…..

Solução

Dizer que demorou um pouco para a equipe de desenvolvimento entender o quanto esta melhoria seria útil e importante não é verdade, ao meu ver demorou bastante, eu posso dizer isso pois trabalho com SQL Server desde a versão 7, isso já se vão 18 anos.

Mas enfim dizer do passado muitas vezes não ajuda em nada, o que vale e deve ser destacado é que agora temos esta pequena mas importante melhoria implementada no Microsoft SQL Server 2016 SP1 que nos permite ao mesmo tempo que declaramos o comando Create adicionar na mesma linha o comando Alter, mas a princípio somente para os objetos programáveis dentre eles:

  • Function;
  • Stored Procedure;
  • Triggers; e
  • Views.

O Create or Alter voltado para tabelas ainda não esta presente neste momento, e sinceramente falando acredito que vai demorar mais um pouco para ser adicionado ao produto.

Legal, mesmo assim já tivemos um grande avanço com a liberação deste nova capacidade adicionada a linguagem Transact-SQL.

Exemplos

Pois bem, seguindo em frente e dando seta para direita afim de realizar uma ultrapassagem na atual versão do SQL Server 2016 mudando para nova versão SQL Server 2016 SP1 ou se preferir versão 13.0.4001 número que identifica e evolução do kernel para o SP1.

Acelarando um pouco mais, vamos trabalhar com alguns exemplos de código que ilustram como podemos fazer uso do comando Create or Alter, para tal torna-se necessário realizar o download do SP1, caso você ainda não tenha feito, utilize o link apresentado a seguir:

Dando continuidade vamos executar o primeiro bloco de código, denominado Bloco de Código 1 apresentado abaixo:

— Bloco de Código 1 —

create-alter-procedure-1
Podemos observar que a forma de uso desta instrução é bastante simples, não necessitando de algum tipo de alteração na sintaxe tanto do comando Create como também do Alter.

Observe que realizamos a criação de um simples Stored Procedure denonimado P_Teste e após a execução do create/alter realizar a apresentação do código fonte da mesma através do system stored procedure SP_HelpText.

Um detalhe importante que vale a pena ser destacado, se relacionado a apresentação do código fonte da nosso stored procedure, note que o SQL Server não adiciona a palava chave Alter na apresentação do código, você pode pensar, mas é claro nós acabamos de criar o objeto. Eu também pensei assim, mas isso não altera caso você realize qualquer tipo de alteração no código fonte, mesmo após realizar alguma mudança na codificação do seu objeto o Database Engine não reconhece a execução do comando Alter ele mais uma vez adiciona o comando ou palavra chave create antes do nome do objeto.

Isso me faz pensar que todas as vezes que utilizarmos este tipo de implementação o Database Engine vai na verdade realizar o processo de drop físico do objeto e posteriormente a criação deste objeto.

Vamos agora trabalhar da mesma forma para criação/alteração de uma function, para isso vamos utilizar o Bloco de código 2 apresentado a seguir:

— Bloco de Código 2 —

create-alter-function-1

Legal, legal, muito bem, simples, fácil, rápido e por quê não dizer que é prático! Ao mesmo tempo que criamos um objeto programável já podemos realizar a alteração. Isso não é algo que realmente estava faltando para o SQL Server?

Acredito que sim, e fazendo um comparativo era a mesma coisa que faltava quando se referiamos a existência de tipos de dados para armazenar somente data ou hora, algo que também foi implementada após um longo tempo a partir da versão 2008 do SQL Server, ou os comandos IIF ou Choice que já existiam na linguagem C#, mas não estavam presentes no Microsoft SQL Server até a versão 2012.

Bom vou deixar para que praticar o uso deste comando na criação de Triggers e Views, com certeza não terá problemas.

Referências

Conclusão

Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server.

Desta forma, em um novo service pack lançado novas melhorias ou mudanças de comportamento do produto são esperadas, algo que a partir do service pack o SQL Server 2016 também vai apresentar.

O que a partir do momento que este conjunto de melhorias for instalada em seu ambiente, será possível dentre elas fazer uso da nova sintaxe combinado dos comandos Create e Alter utilizados de maneira simultânea formando o Create or Alter.

Agradecimentos

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

Até mais.

Material de Apoio – Novembro 2016


Bom dia, bom dia, bom dia…..

Olá comunidade, amantes de bancos de dados e profissionais de tecnologia, estou chegando com mais um post dedicado a sessão Material de Apoio, sendo este o último desta sessão no ano de 2016.

Mesmo sendo o último desta sessão, este post é o primeiro com o nome layout que eu decidi adotar desde o final de Outubro para as novas publicações compartilhadas com você no meu blog, espero que vocês estejam gostando.

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, como diria naquele filme vou “compartilhagram” os meus scripts catalogados 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:

  • Funções de Conversão de dados;
  • Funções Temporáis e Lógicas;
  • Funções Textuais;
  • Funções de Ranking;
  • Páginação de Dados;
  • Objeto Sequence;
  • Windows Function First e Last Value;
  • Windows Function Lad e Lead;
  • Windows Function Percent_Rank e Cume_Dist; e
  • Windows Function Range e Row_Number.

Como de costume gosto de destacar que todos estes arquivos são fruto do trabalho realizado como DBA desde 2000, bem como, a pesquisa e coleta de informações através da internet, onde grande parte do conhecimento aqui compartilhado também é obtido através da  colaboração de seus autores, grande profissionais nacionais e internacionais como reconhecidos especialistas nas áreas de:

  • Modelagem de Dados;
  • Engenharia de Banco de Dados;
  • Engenharia de Softwares e Processos;
  • Banco de Dados;
  • Servidores;
  • Alta Disponibilidade;
  • Desenvolvimento; e
  • Migração e Contingência de Dados.

Obseravação: Toda e qualquer informação, código, exemplo e lógica utilizada nestes scripts foi aplicada em um determinado cenário, situação ou necessidade, o que em muitos casos talvez não se aplica a sua necessidade mas pode ser útil como material de consulta ou aprendizado.

Além disso, todos os arquivos estão renomeados tendo ao final do seu nome a extensão .doc acrescido, isto se torna necessário para garantir a compatibilidade com a plataforma do WordPress.com.

Vale ressaltar que após o seu download será necessário remover esta extensão mantendo a extensão .sql padrão para arquivos do script do SQL Server.

Material de Apoio

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

Exemplo – 1 – Funcoes-de-conversao-sql

Exemplo – 2 – Funcoes-tempororais-e-logicas-sql

Exemplo – 3 – Funcoes-textuais-sql

Exemplo – 4 – Utilizando-paging-sql

Exemplo – 5 – Utilizando-sequence-rank-function-sql

Exemplo – 6 – Windows-function-first-e-last-value-sql

Exemplo – 7 – Windows-function-lad-e-lead-sql

Exemplo – 8 – Windows-function-percent_rank-e-cume_dist-sql

Exemplo – 9 – Windows-function-range-e-row-sql

Bom pessoal, chegamos ao final de mais um Material de Apoio, espero que estes arquivos possam lhe ajudar e ilustrar como podemos fazer dos recursos aqui compartilhados.

Acredito que você tenha observado que estes códigos já são bastante conhecidos no meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server 2012, publicados anteriormente.

Links

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

 

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço, nos encontramos em breve.