Short Scripts – Junho 2017


Boa noite galera, olá comunidade de banco de dados.

Tudo bem? Graças a deus mais um final de semana se aproximando, finalzinho de noite de quinta – feira, acredito que neste momento minha linda esposa esta curtindo mais um episódio de uma das suas séries favoritas, ou lendo mais um dos seus intermináveis livros (kkkkkk), isso não é um crítica, muito ao contrário, a leitura faz bem para a alma e principalmente para a mente, mas posso dizer que minha pequena Fernanda é uma degustadora insaciável de livros.

Mudando de assunto, este é o segundo post deste ano dedicado exclusivamente a sessão Short Scripts, sessão criada a alguns anos que  esta atraindo um número interessante de visitantes, principalmente de 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

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

  • Check Constraint;
  • Common Table Expression;
  • Índices;
  • Information_schema.columns;
  • Monitoramento de Processos;
  • Network Protocol;
  • Operador Outer Apply;
  • Requisição de Disco;
  • Sys.dm_server_registry;
  • Sys.dm_tcp_listener_states;
  • sys.dm_exec_query_plan;
  • sys.dm_exec_sql_text;
  • sys.dm_exec_query_stats;
  • sys.types;
  • sys.tables;
  • sys.dm_db_index_usage_stats;
  • Sys.Identity_Columns; e
  • User Defined Function.

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

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

Short Scripts

— Short Script 1  – Utilizando Check Constraint para cálculo de datas —

Create Table Alunos
(Codigo Int)
Go

— Adicionando a coluna e constraint —
Alter Table Alunos
Add DataNascimento DateTime
Constraint CK_Alunos_DataNascimento Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Adicionando somente a constraint —
Alter Table Alunos
Add Constraint CK_Alunos_DataNascimento
Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Default – Estado —
Alter Table Alunos
Add Constraint [DF_Estado] Default ‘SP’ for Estado
Go

— Short Script 2  – Aplicando o uso de Common Table Expression para inserir registros com valores de um registro anterior —

DECLARE @Metas TABLE ( Data DATE, Meta INT );

INSERT INTO @Metas
( Data, Meta )
VALUES ( DATEFROMPARTS(2017, 03, 29), 50 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 13), 50 );

DECLARE @Producao TABLE
(
Data DATE ,
Quantidade INT
);

INSERT INTO @Producao
( Data, Quantidade )
VALUES ( DATEFROMPARTS(2017, 04, 10), 49 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 12), 36 ),
( DATEFROMPARTS(2017, 04, 13), 50 ),
( DATEFROMPARTS(2017, 04, 14), 50 );

WITH DadosProduzidos
AS ( SELECT P.Data ,
Quantidade = SUM(P.Quantidade)
FROM @Producao AS P
GROUP BY P.Data
)
SELECT D.Data ,
[Produzido] = D.Quantidade ,
Meta = ( SELECT TOP 1 M.Meta
FROM @Metas AS M
WHERE M.Data <= D.Data
ORDER BY M.Data DESC
)
FROM DadosProduzidos D;

— Short Script 3  – Identificando a relação de todos os índices existentes em um banco de dados —

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc,
i.name,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t
ON t.OBJECT_ID = i.OBJECT_ID
GO

— Short Script 4 – Obtendo informações de network protocols e device através da sys.dm_server_registry —

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpPort%’
AND CONVERT(float,value_data) > 0
Go

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpDynamicPort%’
AND CONVERT(float,value_data) > 0
Go

— Short Script 5 – Obtendo informações de network protocols e device através da sys.dm_tcp_listener_states —

SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states
WHERE is_ipv4 = 1
AND [type] = 0
AND ip_address <> ‘127.0.0.1’
Go

— Short Script 6 – Criando uma User Defined Function com operador Outer Apply —

CREATE FUNCTION AttributesOfTable (@tableToSearch nvarchar(500))
returns table
return SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @tableToSearch;
go

Declare @tableToSearch table (nome_tabela varchar(50));
INSERT into @tableToSearch values (‘Customer’), (‘Order’), (‘Papagaio’);

SELECT T1.nome_tabela as [nome da tabela],
T2.TABLE_SCHEMA as [nome do esquema],
T2.COLUMN_NAME as [nome da coluna]
from @tableToSearch as T1
outer apply dbo.AttributesOfTable(T1.nome_tabela) as T2;

— Short Script 7 – Utilizando a DMV sys.identity_columns para identificar o valor identity de uma determinada coluna —

SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns
INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 8 – Consultando informações sobre o SQL Server armazenadas no Registro do Windows —

— A. Display the SQL Server services —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%ControlSet%’
Go

— B. Display the SQL Server Agent registry key values —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%SQLAgent%’
Go

— C. Display the current version of the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name = N’CurrentVersion’
Go

— D. Display the parameters passed to the instance of SQL Server during startup —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%Parameters’
Go

— E. Return network configuration information for the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE keyname LIKE N’%SuperSocketNetLib%’
Go

— Short Script 9 – Identificando a relação de querys que apresentam o maior consumo de disco durante seu período de processamento – 

SELECT TOP 20 SUBSTRING(qt.text,
(qs.statement_start_offset/2)+1,
((CASE
qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset
END –
qs.statement_start_offset)/2)+1),
qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads,
qs.max_logical_reads,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY
qs.total_logical_reads DESC

Legal, 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

Agradeço sua visita, com certeza é imenso prazer saber que você teve interesse em acessar este post.

Nos encontramos novamente no mês de setembro, para o próximo post dedicado a sessão Short Scripts.

Um grande abraço, boa noite, bom descanso.

Valeu.

TechNet Virtual Lab: Office 365 Advanced Threat Protection


A Microsoft disponibilizou recentemente em sua página Laboratórios Virtuais TechNet o laboratório virtual Office 365 Advanced Threat Protection – Guia Interativo.

Os laboratórios virtuais permitem que usuários e profissionais de TI testem recursos dos sistemas operacionais e de outros produtos da Microsoft via nuvem como se estivessem usando seus próprios PCs.

Isto é útil para quem não quer perder tempo criando uma máquina virtual apenas para testar um recurso específico.

TechNet Virtual Lab: Office 365 Advanced Threat Protection – Guia Interativo

Para quem não sabe, o Office 365 Advanced Threat Protection oferece proteção em tempo real para email em ambientes corporativos. O serviço foi atualizado no início deste ano e ganhou novos recursos como URL Detonation e Dynamic Delivery.

De acordo com a Microsoft, o recurso URL Detonation ajuda a impedir que os usuários sejam comprometidos por arquivos oferecidos através de URLs maliciosas.

Quando o usuário recebe um email, o Advanced Threat Protection analisa as URLs na mensagem procurando por comportamento malicioso.

Se o usuário clicar no link durante a verificação, ele verá o alerta falando que a mensagem está sendo verificada. Se o link for detectado como malicioso, um novo alerta será exibido recomendando que o usuário não clique no link.

Os administradores podem configurar a política SafeLink para monitorar os cliques dos usuários. Isto é útil para casos onde eles burlam os alertas para acessar sites bloqueados pelo Advanced Threat Protection.

TechNet Virtual Lab: Office 365 Advanced Threat Protection
Já o recurso Dynamic Delivery do Office 365 Advanced Threat Protection permite que os usuários leiam as mensagens de email enquanto os anexos são verificados.

Durante a verificação, o anexo é substituído por um “placeholder” com a notificação indicando que a verificação do verdadeiro anexo está em andamento.

Se o usuário clicar neste “placeholder”, ele verá uma mensagem mostrando o progresso da verificação. Se o anexo for malicioso, o Office 365 Advanced Threat Protection notificará o usuário.

Neste laboratório virtual você verá como o Office 365 Office 365 Advanced Threat Protection pode ajudar a impedir que anexos maliciosos cheguem aos destinatários, proteger usuários contra links maliciosos e mais com recursos como os mencionados acima.

Confira o laboratório virtual clicando aqui e saiba mais sobre o serviço aqui.

Fontes e Direitos Autorais: Microsoft.com  https://products.office.com/en/exchange/online-email-threat-protection

Material de Apoio – Maio 2017


Muito boa tarde comunidade e amantes de banco de dados!!!

Tudo bem? Este é mais um post da sessão Material de Apoio, sendo o segundo no decorrer de 2017 e de número 152 no total desta sessão. Estamos nos aproximando da metade de 2017, que loucura isso, como a rotina do dia a dia não nos deixa perceber o quanto o tempo 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:

  • Check Constraint para Cálculo de Data;
  • CTE – Preenchendo dados de um registro com base no registro anterior;
  • Função para formatação de valores;
  • Função para retornar nome de tabelas e seus respectivos atributos(colunas);
  • Identificação do primeiro dia do mês corrente;
  • Monitoramento de Processos Bloqueados;
  • Movimentação de tabelas entre filegroups;
  • Obtendo o último valor Identity de uma coluna; e
  • Requisição de disco.

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:

1 – Material de Apoio – Maio – 2017 – Check Constraint – Cálculo de Data.sql

2 – Material de Apoio – Maio – 2017 – Preencher dados de um registro com dados do registro anterior.sql

3 – Material de Apoio – Maio – 2017 – Função – Formatação de Valores.sql

4 – Material de Apoio – Maio – 2017 – Função para retornar o nome e seus atributos – Utilizando operador Outer Apply.sql

5 – Material de Apoio – Maio – 2017 – Identificar o primeiro dia do mês com base na data atual.sql

6 – Material de Apoio – Maio – 2017 – Monitorando Processos Bloqueados no SQL Server.sql

7 – Material de Apoio – Maio – 2017 – Move Table between Filegroup – Stored Procedure.sql

8 – Material de Apoio – Maio – 2017 – Obtendo último valor identity.sql

9 – Material de Apoio – Maio – 2017 – Listando as querys que mais realizam requisição de disco.sql

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 aqui 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

Mais uma vez obrigado por 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 em Agosto, um forte abraço.

Até mais.

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.

Azure SQL Data Warehouse agora está disponível em 27 regiões


A Microsoft anunciou nesta semana em seu blog que o Azure SQL Data Warehouse agora está disponível em 27 regiões em todo o mundo.

As quatro novas regiões onde o banco de dados está disponível são: Germany Central, Germany Northeast, Korea Central e Korea South.

Em setembro de 2016 a empresa anunciou a disponibilidade no Brasil e em mais três regiões (North Europe, Japan East e Australia Southeast), elevando o total de regiões  época para 18.

Azure SQL Data Warehouse

De acordo com a Microsoft, o Azure SQL Data Warehouse é um banco de dados baseado em nuvem e expansível com capacidade de processar volumes imensos de dados, relacionais e não relacionais. Criado em arquitetura MPP (processamento paralelo maciço), o SQL Data Warehouse pode lidar com sua carga de trabalho corporativa.

– Combina o banco de dados relacional do SQL Server com os recursos de expansão na nuvem do Azure. Você pode aumentar, diminuir, pausar ou retomar a computação em segundos. Você economiza custos expandindo a CPU quando for necessário e cortando o uso durante os horários de pico.

Azure SQL Data Warehouse agora está disponível em 27 regiões

– Aproveita a plataforma do Azure. É fácil de implantar, apresenta uma manutenção tranquila e é totalmente tolerante a falhas graças aos backups automáticos.

– Complementa o ecossistema do SQL Server. Você pode desenvolver com o T-SQL (Transact-SQL) do SQL Server e ferramentas conhecidas.

Saiba mais sobre o Azure SQL Data Warehouse aqui e confira os preços clicando aqui.

Fontes e Direitos Autorais: Baboo.com –

Microsoft disponibiliza o SQL Server 2016 Training Kit


A Microsoft disponibilizou recentemente no Microsoft Download Center o SQL Server 2016 Training Kit.

O kit é voltado para desenvolvedores, instrutores, consultores e outros profissionais que precisam saber mais sobre as principais novidades introduzidas na nova versão do SQL Server.

Ele contém apresentações, demos, vídeos e outros recursos para ajudar no aprendizado ou para montar uma sessão de treinamento.

Faça o download do SQL Server 2016 Training Kit

O SQL Server 2016 Training Kit está disponível para download gratuitamente aqui. Ele é compatível com o Windows 10, Windows 8, Windows 8.1, Windows Server 2012 R2 e Windows Server 2016.

Microsoft disponibiliza o SQL Server 2016 Training Kit

SQL Server 2016

Lançado em junho deste ano, o SQL Server 2016 traz recursos críticos inovadores com desempenho em memória e análise operacional integrada. Os recursos de segurança abrangentes, como a tecnologia de criptografia ininterrupta (Always Encrypted), ajudam a proteger seus dados fixos e em movimento, e uma solução avançada de alta disponibilidade e recuperação de desastres acrescenta novos aprimoramentos à tecnologia AlwaysOn.

As organizações terão visões mais aprofundadas de todos os seus dados com novos recursos que vão além da business intelligence, executando a análise avançada diretamente dentro de seus bancos de dados e apresentando visualizações avançadas para oferecer visões de negócios de qualquer dispositivo.

Você também pode se beneficiar da nuvem em hiperescala, com novos cenários híbridos habilitados pela nova tecnologia de banco de dados elástico (Stretch Database), que permite expandir dinamicamente seus dados transacionais a quente e a frio até o Microsoft Azure de forma segura, para que seus dados estejam sempre à disposição para consultas, independentemente do tamanho.

O SQL Server 2016 também oferece uma plataforma de banco de dados completa para a nuvem híbrida, permitindo criar, implantar e gerenciar facilmente soluções que abrangem instalações locais e na nuvem.

Saiba mais sobre ele aqui.

Fontes e Direitos Autorais: Baboo.com & https://www.microsoft.com/en-us/download/confirmation.aspx?id=54089

Dica do Mês – Restrições de Integridade para Banco de Dados


Fala galera, bom dia.

Tudo bem?

Estou um pouco ausente neste mês devido as correrias da minha vida profissional e acadêmica, mas sempre que possível compartilhando com vocês um pouco do meu conhecimento e experiência.

O post de hoje poderia ser diferente dos outros, na sessão Dica do Mês vou apresentar um pouco mais sobre os conceitos básicos de banco de dados voltados para área de modelagem, estou me referindo as chamadas Restrições de Integridade (RI), algo bastante útil e importante quando estamos realizando as definições da estrutura lógica para armazenamento de dados em qualquer banco de dados. Então vamos começar a falar um pouco mais sobre este conceito também criado na década dos anos 70 por Edgar Frank Codd.


Falando um pouco sobre integridade…

A integridade de dados é uma das características essenciais da segurança da informação, e garante que as informações não sofreram alterações que não foram autorizadas ou que são impróprias. Utilizada para assegurar que um documento não é alterado depois de ter sido assinado.

Quando vamos projetar um banco de dados, imaginamos as possíveis formas para que nossa aplicação grave os dados corretamente no banco de dados, mas as vezes, esquecemos de definir, a nível de banco, quais as validações que devem ser feitas para evitar inconsistências nos dados e que, futuramente, se tornariam dores de cabeça.

No contexto de bancos de dados relacional é comum falar de integridade referencial, que tem como objetivo conservar as relações existentes entre tabelas quando algumas linhas são inseridas ou eliminadas.

Restrições de Integridade….

As chamadas RIs possuem o objetivo de garantir a exatidão e a consistência dos dados em uma Banco de dados relacional. Ou seja, garantir que dados representem assertivamente a realidade modelada. A integridade dos dados é tratada nas bases de dados através do conceito de integridade relacional e é garantida pelo próprio SGDB.

Existem vários tipos de restrições de integridade. Codd, inicialmente definiu 2 tipos de restrições, mas na sua segunda versão do modelo relacional ele definiu 5 tipos de restrições de integridade.

Mas antes de conhecer este tipos, vamos entender um pouco o conceito de domínio dos atributos: O domínio indica os possíveis valores de um atributo. A integridade de domínio verifica se os dados são do tipo permitido (alfanumerico, numerico,etc), tamanho do campo, se ele pode ser nulo ou não. Por exemplo, é possível definir que um atributo “idade” de um funcionário é sempre um valor inteiro positivo.

Os cinco tipos de restrições…

Restrição de Chave: Impede que uma chave primária se repita. Um campo chave primária diferencia de forma única os registros (linhas) de uma relação (tabela).

Restrição de Domínio: Impede que uma chave primária receba como valor NULL (nulo).

Integridade de vazio: Verifica se um campo pode ou não receber valor NULL. Sub-item da integridade de domínio.

Integridade referencial: Uma chave estrangeira de uma relação tem que coincidir com uma chave primária da sua tabela “pai” a que a chave estrangeira se refere. Ou seja, não só deve existir o atributo (campo), como também, o valor referenciado.

Integridade definida pelo usuário: Permite definir regras comerciais que não se encaixam em outras categorias de integridade.

Elementos que formam as Restrições de Integridade…

Integridade Semântica: Garante que o dado inserido em uma linha da tabela seja um valor válido. Para esse valor ser válido deve ser do mesmo tipo de dados definido na especificação da coluna na tabela.

Imagine o atributo de uma determinada entidade definido como DATA, por padrão este atributo deverá conter somente dados relativos a DATA. É justamente esta definição que nos permite ter a certeza que no campo DATA_CONTRATACAO só terá datas válidas.

Caso um SGDB permita a inserção de um outro tipo de dado diferente do definido, a integridade semântica será violada. A integridade semântica em um SGDB é aplicada com a utilização de constraints.

Constraints: Pode ser definido resumidamente como uma regra que limita o valor que pode ser inserido, modificado ou eliminado em uma tabela. Na linguagem SQL temos os seguintes tipos de constraints:

  • Constraint de dados;
  • Constraint NOT NULL (não nulo);
  • Constraint única; e
  • Constraint de validação (check constraint).

Constraints de Dados: Esse tipo de constraint pode ser considerado o mais simples e por muitas vezes ignorado como um constraint. Ele é o que delimita o tipo de dado de cada coluna em uma tabela.

Os tipos de informações disponíveis na maioria dos SGDBs existentes pode ser dividia em:

  • Numérico;
  • Alfanumérico ou caracteres;
  • Data e tempo; e
  • Grandes objetos.

Constraints Not Null: O conceito de nulo é utilizado quando uma determinada coluna ou atributo de uma linha na tabela não possui valor ou este valor é desconhecido. Por outro lado, existem colunas / atributo que obrigatoriamente precisam de valor informado.

Por exemplo, em uma tabela chamada FUNCIONARIO, onde estão dados de funcionários, o atributo NUMERO_FUNCIONARIO é obrigatório. Nesse caso é possível utilizar a constraint NOT NULL para garantir que haverá informação nessa coluna.

Importante frisar que NULO é diferente de brancos e zeros. Temos que lembrar também que tanto branco quanto zero são valores válidos e que são levados em conta em funções de coluna, tais como média, somatório, máximo, mínimo. Sendo que o NULO é desconsiderado nessas funções.

Constraints Única (Unique): Reconhecida e tratata como uma regra única que garante e não permite a existência de valores duplicados da mesma coluna ou em um conjunto de colunas na mesma tabela.

Usando o mesmo exemplo da tabela FUNCIONARIO, podemos utilizar uma constraint única na coluna NUMERO_FUNCIONARIO para garantir que dois ou mais funcionários possuam o mesmo número de identificação.

Podemos considerar que a chave primaria (primary key), que será explicada mais adiante, é um tipo de constraint única. Lembrando que uma tabela pode ter apenas uma chave primária, porém diversas constraint únicas.

Constraints de Validação (Check): Esta constraint determina um conjunto de valores permitidos para uma determinada coluna na tabela. Através deste tipo de constraints podemos definir de forma explícita através da linguagem DDL (Data Definition Language) de uma tabela com expressões Booleanas similares a clausula WHERE da linguagem Transact-SQL.

Uma constraint de validação é forçada em qualquer inserção ou atualização da coluna. Caso a inserção ou atualização da coluna não esteja de acordo com a definição da constraint, a mesma não será executada.

Por exemplo, vamos supor que a tabela FUNCIONARIO possua uma coluna SALARIO e que o valor do salário de cada funcionário não possa ser maior que 50.000,00, é possível criar uma constraint para erra regra:

CREATE TABLE FUNCIONARIO

(NUMERO_FUNCIONARIO SMALLINT NOT NULL,

SALARIO DECIMAL (9,2) NOT NULL CHECK SALARIO >= 50.000);

Observações: Uma constraint de validação pode ser muito útil para garantir regras de negócio, pois ela não pode ser sobreposta. Uma vez definida é dada a garantia que a regra será respeitada.

Utilizar esse tipo de integridade torna as suas aplicações mais robustas, consistentes e simples, pois não é necessário controlar as regras dentro do próprio código de programação ou utilizando uma subrotina. Dessa maneira é isolada em apenas um lugar a regra de negócio; e

Havendo a necessidade de mudar alguma regra de negócio, basta apenas alterar a constraint de validação na tabela ao invés de sair alterando códigos e mais códigos de programação uma vez que a mesma regra pode estar replicada em diversos pontos da sua aplicação.

Realizando uma prática…

Após conhecermos um pouco sobre o conceito e elementos que formam as restrições de integridade, vamos então colocar “a mão na massa” ou melhor como eu sempre digo no teclado e construir um simples exemplo de como podemos fazer uso de forma mais coerente e organizada do uso da restrição de integridade em nossas tabelas. Para tal utilizaremos o Bloco de Código 1 apresentado abaixo:

— Bloco de Código 1 — Aplicando o conceito de restrições de integridade —

— Criando o Banco de Dados —
Create Database RI
Go
— Acessando o Banco de Dados —
Use RI
Go
— Criando a Tabela Funcionarios utilizando Constrainst – Not Null, Null, Check, Default e Unique —
Create Table Funcionarios
(Codigo Int Primary Key Identity(1,1),
Nome Varchar(80) Not Null,
Sexo Char(1) Check (Sexo = ‘F’ or Sexo = ‘M’),
RG Int Not Null Unique NonClustered,
CPF Int Not Null Unique NonClustered,
DataNascimento Date Check (DataNascimento >= ‘1950-01-01’),
DataCadastro DateTime Default GetDate(),
Email Varchar(100) Null)
Go
— Criando a Tabela Clientes utilizando Constrainst – Not Null, Null, Check, Default e Unique —
Create Table Clientes
(Codigo Int Identity(1,1),
Nome Varchar(80) Not Null,
Sexo Char(1),
RG Int Not Null,
CPF Int Not Null,
DataNascimento Date,
 DataCadastro DateTime Constraint DF_Clientes_DataCadastro Default GetDate(),
Email Varchar(100) Null
  Constraint PK_Clientes_Codigo Primary Key (Codigo),
  Constraint CK_Clientes_Sexo Check (Sexo = ‘F’ or Sexo = ‘M’),
  Constraint UQ_Clientes_RG Unique NonClustered (RG),
  Constraint UQ_Clientes_CPF Unique NonClustered (CPF),
  Constraint CK_Clientes_DataNascimento Check (DataNascimento >= ‘1950-01-01’))
Go
— Adicionando uma nova Constraint —
Alter Table Clientes
Add Constraint DF_Clientes_Sexo Default ‘M’ for Sexo
Go
— Removendo uma Constraint já existente —
Alter Table Clientes
Drop Constraint CK_Clientes_DataNascimento
Go
— Adicionando uma nova Constraint do tipo Check —
Alter Table Clientes
Add Constraint CK_Clientes_DataNascimento
Check(DataNascimento >=’1900-01-01′)
Go

Perfeito, após executarmos este bloco de código temos nosso ambiente totalmente criado seguindo as definições de restrições de integridade que aplicamos no script.

Você pode estar se perguntando mas o que existe de diferença entre criar uma tabela sem definir o nome dados constraints em comparação com uma tabela que possui o nome das constraints definidas. A resposta para esta sua dúvida será respondida através da Figura 1 apresentada abaixo:

constraints
Figura 1 – Restrições de integridade criadas em cada tabela.

Analisando a Figura 1 podemos notar claramente a diferença, quando definimos um nome para nossas constrainst o Microsoft SQL Server atribui exatamente o nome de definimos no momento da crição da tabela, com isso, teremos mais facilidade para realizar uma manutenção nestes objetos, bem como, toda documentação e apresentação da estrutura do nosso banco de dados será mais limpa e organizada.


 

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

O conhecimento técnico é muito importante para qualquer profissional, mas não podemos deixar de lado o conhecimento acadêmico adquirido ao longo dos anos dentro das instituições de ensino.

Este é um ponto fundamental, valorizar e conhecer a diferença entre um bom profissional e o profissional reconhecido e respeitado no mercado de trabalho, está justamente ligado na capacidade do mesmo em saber aliar o conhecimento teórico com o conhecimento prático, como muitos costumam dizer aliar a téoria a prática, sendo este o objetivo deste post.

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

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

Até mais.