Short Scripts – Fevereiro 2018 – Transaction Log


Olá, bom dia, mais uma semana começando….

E você já esta aqui acessando o meu blog, que alegria poder te encontrar em mais um post da sessão Short Scripts, uma das sessões mais recentes do meu blog que esta alçando a marca de 32 posts, sendo estes publicados trimestralmente.

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

Mas como este é o primeiro post desta sessão em 2018, farei algo um pouco diferente, você terá uma pequena surpresa.

Desejo que o conteúdo aqui compartilhado possa lhe ser útil, como também sirvo de referência e sugestões para novas formar de resolução de problemas e aprendizado.

Vamos então conhecer um pouco mais sobre este novo post….

O post de hoje

Como já destacado no início do post, ao invés de compartilhar os últimos scripts adicionados a  minha biblioteca, quero dividir com você um conteúdo dedicado especificamente a um assunto muito importante quando nos referimos ao Microsoft SQL Server, mais especificamente ao Transaction Log (Log de Transações), funcionalidade presente em todos os bancos de dados criados em qualquer versão e edição do SQL Server.

E ai que você achou desta surpresa, gostou? Eu gostei, não é fácil você conseguir encontrar em um único local um conteúdo focado exclusivamente a este assunto tão importante, que muitos profissionais que trabalham com banco de dados até hoje não conseguem entender o conceito e forma de atuação do Transaction-Log.

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

Short Scripts

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

— Short Script 1 – Log Record —

— Altera o Recovery Model para SIMPLE
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Truncar o Transaction Log —
CHECKPOINT
Go

— Conteúdo do log – todas as colunas —
USE AdventureWorks2016
Select * from ::fn_dblog(null, null)
Go

–update
Begin Transaction

UPDATE dbo.Pessoa
SET nome = ‘XUXA’
Where ID=3

Rollback
Go

— Conteúdo armazenado no Log File —
Select [Current LSN],
Operation,
Context,
[Transaction ID],
[Log Record Length],
[Previous LSN],
AllocUnitName,
[Page ID],
[Slot ID],
[Checkpoint Begin],
[Checkpoint End],
[Minimum LSN],
SPID,
[Begin Time],
[Transaction Name],
[Parent Transaction ID],
[Lock Information],
Description,
[RowLog Contents 0],
[RowLog Contents 1],
[Log Record]
From ::fn_dblog(null, null)
Go

— DBCC SQLPERF —
DBCC SQLPERF(LOGSPACE)
Go

— Short Script 2 – CheckPoint —

— Criando a Base de Dados —
CREATE DATABASE DemoCheckpoint
ON PRIMARY
(NAME = ‘DemoCheckpoint_data’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_data.mdf’)
LOG ON
(Name = ‘DemoCheckpoint_Log’,
FILENAME = ‘D:\MSSQL\DemoCheckpoint_log.ldf’,
SIZE = 100MB,
FILEGROWTH = 10MB)
GO

— Alterando o Recovery Model —
ALTER DATABASE DemoCheckpoint
SET RECOVERY SIMPLE
Go

— Criando a Tabela Teste —
USE DemoCheckpoint
GO

CREATE TABLE Teste
(C1 varchar(50) NOT NULL,
C2 varchar(50) NOT NULL)
GO

— Forçando o Checkpoint —
CHECKPOINT
Go

— Abrir o Perfmon com os contadores

— em outra sessão
USE DemoCheckpoint
GO

WHILE 1=1
BEGIN

INSERT INTO dbo.teste
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Short Script 3 – Log Chain Simple —

— Iniciar nova sessão do Perfmon —
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Alterar Recovery Model para Simple —
ALTER DATABASE AdventureWorks2016
SET RECOVERY SIMPLE
Go

— Abrir nova Query —
USE AdventureWorks2016
Go

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END

— Alterar Recovery Model para Full —
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
GO

— Realizar Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backupcompress.bak’
WITH COMPRESSION,
DIFFERENTIAL
GO

— Short Script 4 – DBCC LogInfo —

— Criando uma nova base de dados —
CREATE DATABASE TestDB
ON PRIMARY
(NAME = ‘TestDB_data’,
FILENAME = ‘D:\MSSQL\TestDB_data.mdf’)
LOG ON
(Name = ‘TestDB_Log’,
FILENAME = ‘D:\MSSQL\TestDB_log.ldf’,
SIZE = 10MB,
FILEGROWTH = 10MB)
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

–Forçando o crescimento do Transact-Log manualmente em 20MB —
ALTER DATABASE TestDB
MODIFY FILE
(NAME = ‘TestDB_Log’,
SIZE = 20MB);
GO

— Obtendo informações sobre a base de dados —
DBCC LOGINFO(TestDB)
Go

— Short Script 5 – Natureza Circular —

— Alterando Recovery Model FULL —
ALTER DATABASE TestDB
SET RECOVERY FULL;
Go

— Realizando Backup Database —
BACKUP DATABASE TestDB
TO DISK = ‘D:\TestDB.bak’
Go

— Forçando o encolhimento do Transaction – Log —
DBCC LOGINFO(TestDB)
Go

BACKUP LOG TestDB
TO DISK = ‘bkplogTestDB.trn’
Go

USE TestDB
Go

DBCC SHRINKFILE (TestDB_Log,1)
Go

DBCC LOGINFO(TestDB)
Go

— Criando uma nova Tabela —
USE TestDB
GO

CREATE TABLE dbo.pessoa
(ID int identity PRIMARY KEY NOT NULL,
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL,
Nascimento date NOT NULL,
Cargo varchar(50))
GO

— Abrir nova query —
USE TestDB
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘Junior’, ‘Galvão’, ‘19800428’, ‘Database Administrator’)
END

— Monitorar o crescimento do log em tempo de execução —
DBCC LOGINFO(TestDB)

CHECKPOINT

SELECT name,
Log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘TestDB’
Go

— Realizar Backup do Arquivo de Log —
BACKUP LOG TestDB TO DISK = ‘d:\log.trn’
Go

— Alterando Recovery Model para Full
ALTER DATABASE AdventureWorks2016
SET RECOVERY FULL
Go

— Realizando novo Backup Database —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
WITH COMPRESSION
Go

— Short Script 6 – Backup and Transaction Log —

— Preparando a base – 1m10s se não preparada na demo 5
ALTER DATABASE AdventureWorks2016 SET RECOVERY FULL
GO
BACKUP DATABASE AdventureWorks2016 TO DISK = ‘d:\backup.bak’ WITH COMPRESSION
GO

— Realizando Backup do Arquivo de Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Encolhendo o Transaction Log —
USE AdventureWorks2016
Go

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Ajustando o tamanho do Transaction Log —
USE AdventureWorks2016
Go

— Encolhendo o Transaction Log —
DBCC SHRINKFILE (AdventureWorks2016_Log,1)
Go

— Obtendo informações sobre o Log —
DBCC LOGINFO(AdventureWorks2016)
Go

— Modificando o arquivo de Log —
ALTER DATABASE AdventureWorks2016
MODIFY FILE
(NAME = AdventureWorks2016_Log,
SIZE = 4MB)
Go

— Encolhendo o Transaction Log —
DBCC LOGINFO(AdventureWorks2016)

— Abrir nova query —
BACKUP DATABASE AdventureWorks2016
TO DISK = ‘d:\backup.bak’
Go

— Abrir nova query —
USE AdventureWorks2016
GO

WHILE 1=1
BEGIN
INSERT INTO dbo.pessoa
VALUES (‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’),
(‘bbbbbbbbbbbbbbbbbbbb’, ‘bbbbbbbbbbbbbbb’)
END
Go

— Forçando o Truncate do Log —
BACKUP LOG AdventureWorks2016
TO DISK = ‘bkplog.trn’
Go

— Obtedo informações do arquivo de log —
DBCC LOGINFO(AdventureWorks2016)
CHECKPOINT
SELECT name,
log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘AdventureWorks2016’
Go

— Ajustando o tamanho do TLog
USE AdventureWorks2016
GO

DBCC SHRINKFILE (AdventureWorks2016_Log,1)
GO

DBCC LOGINFO(AdventureWorks2016)
Go

— Short Script 7 – File Growth —

— Habilitando Trace Flags para evidênciar mudanças no Log —
DBCC TRACEON (3004, 3605, -1);
Go

— Limpar o log do SQL Server —
sp_cycle_errorlog
Go

— Criar uma nova Base de Dados —
CREATE DATABASE TransactionLog
ON PRIMARY
(NAME = ‘TransactionLog_data’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_data.mdf’,
SIZE = 10240MB)
LOG ON
(Name = ‘TransactionLog_Log’,
FILENAME = ‘D:\MSSQLSERVER\DATA\TransactionLog_log.ldf’,
SIZE = 1024MB,
FILEGROWTH = 1024MB)
GO

— Identificar o tempo decorrido para processamento relacionado somente ao Log —
xp_readerrorlog
Go

— Desabilitando Trace Flags —
DBCC TRACEOFF (3004, 3605, -1);
Go

— Short Script 8 – Open Transaction in Tracking —

— Identificando as Transações abertas e seu respectivo consumo do Transaction Log —
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM sys.dm_tran_database_transactions [s_tdt] Inner JOIN sys.dm_tran_session_transactions [s_tst]
ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
Inner JOIN sys.[dm_exec_sessions] [s_es]
ON [s_es].[session_id] = [s_tst].[session_id]
Inner JOIN sys.dm_exec_connections [s_ec]
ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er]
ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY [Begin Time] ASC;
Go

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

Quero agradecer ao amigo Luiz Mercante que colaborou com a criação e uso destes scripts em algumas palestras realizadas nos últimos anos no evento SQL Saturday.


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

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

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

Links

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

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

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

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

https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/

Agradecimento

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

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

Sucesso….

Anúncios

Material de Apoio – Fevereiro 2018


Boa tarde.

Tudo bem? E ai esta curtindo o carnaval?

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

O post de hoje

 

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

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

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

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

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

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

Material de Apoio

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

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

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

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

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

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

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

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

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

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

Links

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

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

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

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

Agradecimento

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

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

Até a próxima, sucesso….

#17 – Para que serve


Olá você, boa noite.

Tudo bem? Este é mais um post da sessão Para que serve, plena sexta – feira, enquanto meus alunos da Fatec São Roque estão quebrando a cabeça e gastando um pouco dos neurônios na resolução de exercícios, estou aqui para compartilhar um pouco do conhecimento adquirido nos alguns dias.

Quando eu falei sobre conhecimento adquirido, estou me referindo a algumas novidades adicionadas na nova versão do Microsoft SQL Server, neste caso mais especificamente a versão 2017. E ai você já realizou o download? Espero que a resposta seja positiva e você já esteja utilizando, pois caso contrário o post de hoje talvez não seja a solução da dúvida ou problema que você esta esperando.

Mas antes de falar do post, vamos destacar um pouco sobre o Microsoft SQL Server 2017. Acredito que você deva saber que no último mês de outubro, a Microsoft realizou mais um lançamento de uma nova versão do Microsoft SQL Server, estou me referindo a versão 2017. Por acaso você estão utilizando esta nova versão? Caso ainda não tenha feito, aproveite e faça agora mesmo acessando o link: https://www.microsoft.com/en-us/sql-server/sql-server-2017.

Se você, assim como eu realizou o download no mesmo dia do lançamento, ou seja, dia 02/10, pode ter um certo tempo para notar que a cada nova versão, o produto esta evoluindo, tanto no seu processo de instalação que realmente é fantástico e muito prático, como também, na quantidade de recursos, funcionalidades e componentes internos apresentados a partir desta da versão 2017.

Voltando para o post de hoje, como de costume a cada nova versão a Microsoft em conjunto com o seu time de engenheiros e desenvolvedores tem o hábito de adicionar um conjunto novo de funcionalidades e recursos, dentre eles alguns voltados especificamente para a área de desenvolvimento, no caso de comandos, stored procedures e functions adicionadas a grande linguagem Transact-SQL.

Logicamente na versão 2017 isso não seria diferente, e justamente pensando neste tipo de oportunidade para aquisição de conhecimento que o post de hoje será dedicado a duas novas funções adicionadas a partir desta versão sendo elas: Concat_WS e Translate.

E ai por acaso você já as conhece, espero que não, mas caso já tenha encontrado alguma informação ou até mesmo tenha feito uso, fique a vontade para contribuir com este post deixando seu comentário.

Seguindo em frente, chegou a hora de conhecer um pouco mais sobre estas novas funções, desta forma, seja bem vindo ao #17 – Para que serve – Novas String Functions Concat_WS e Translate adicionadas ao Microsoft SQL Server 2017.

Introdução

Em diversos momentos trabalhando com diversos dados armazenados em nossas tabelas temos a necessidade de realizar a concatenação entre eles, ou seja, estabelecer uma possível forma de união destes diversos valores e apresentar de uma única coluna ou até mesmo linha de registro.

Procedimento que até a versão 2012 do Microsoft SQL Server nos exigia um pouco de linhas de código para realizar esta atividade, sendo que, a mesma agora na versão 2o17 tornou-se ainda mais simples e fácil através da nova string function Concat_WS.

Você pode estar pensando, mas qual o motivo do tipo de engenheiros do SQL Server em adicionar uma função similar a Concat, na verdade não existe um motivo, o que existe e posso dizer é que a Concat e a Concat_WS podem ser consideradas irmãs ou até mesmo funções que se complementam.

Neste sentido o WS pode ser reconhecido como o argumento (concatenate with separator) separador, aquele caracterer que será utilizado para separar um valor string do outro mais ao mesmo tempo estará fazendo parte do conjunto de valores que serão concatenados.

Para que você possa entender e conhecer melhor a função Concat_WS, vou apresentar alguns exemplos:

— Exemplo 1 – Obtendo informações sobre as tabelas, utilizando o hífen como separador —
SELECT CONCAT_WS( ‘ – ‘, name, OBJECT_ID, create_date, modify_date) AS TablesInfo
FROM sys.tables
Go

Após a execução do Exemplo 1, você deverá obter um resultado similar conforme apresenta a Figura 1 abaixo:


Figura 1 – Dados concatenados e separados pelo sinal de hífen.

— Exemplo 2 – Concatenando caracteres utilizando o sinal de dois pontos como separador —
Select CONCAT_WS(‘ :: ‘, ‘Pedro Antonio Galvão Junior’, ‘Idade:37’, ‘MVP desde 2007’) As Info
Go

Após a execução do Exemplo 2, você deverá obter um resultado similar conforme apresenta a Figura 2 abaixo:


Figura 2 – Dados concatenados e separados pelo sinal de dois pontos.

Observação: Note que nos dois exemplos apresentados acima o primeiro argumento ou parâmetro obrigatório que deve ser especificado na função Concat_WS é justamente o elemento separador, o qual vai estar envolvido diretamente entre cada conjunto de valores informados sequencialmente na função.

Dando continuidade, vamos conhecer a função Translate, inicialmente fazendo uma rápida analogia ao seu nome parece que esta nova função seria algo similar a um tradutor de texto, na verdade ela tem um papel entre aspas próximo em relação a tradução de um valor ou sentença de valores string, mas dizer que ela realiza a tradução não é o entendimento correto.

Na verdade esta função realiza em tempo de execução retorna uma nova sentença de valores string com base no conjunto de argumentos declarados em sua sintaxe, sendo que obrigatoriamente o primeiro argumento representa a sentença de valores que deverá ser utilizada, para posteriormente servir como base para nova sentença que será resultando da “tradução”.

Vamos então conhecer um pouco mais sobre esta função, através dos exemplos apresentados a seguir:

— Exemplo 1 – Equação de 2º Grau — Substituindo a letra x pelo número 4 —Select ‘x² – 10x + 24 = 0’ As ‘Antes’
Go

Select Translate(‘x² – 10x + 24 = 0’, ‘x’, ‘4’) As ‘Depois’
Go

Após a execução do Exemplo 1, você deverá obter um resultado similar conforme apresenta a Figura 3 abaixo:


Figura 3 – Uso da função Translate aplicada em uma equação de segundo grau.

— Exemplo 2 – Método de Bhaskara – Substituindo as letras A e C pelos valores 1 e 8 informados respectivamente com grupos de valores em cada argumento —
Select N’∆ = b² – 4 * a * c’ As ‘Antes’

Go

Select Translate(N’∆ = b² – 4 * a * c’ , ‘ac’, ’18’) As ‘Depois’
Go

Após a execução do Exemplo 2, você deverá obter um resultado similar conforme apresenta a Figura 4 abaixo:


Figura 4 – Uso da função Translate aplicada ao método de bhaskara.

Observação: Note que nos dois exemplos apresentados anteriormente argumento ou parâmetro obrigatório que deve ser especificado na função Translate corresponde ao valores ou sentença string, o qual será utilizada como elemento base para “tradução” e apresentação do novo conjunto de valores ou sentença após sua execução. 

Muito bem, desta forma, chegamos ao final de mais uma post da sessão Para que Serve….


Referências

https://technet.microsoft.com/pt-br/library/ms186862(v=sql.110).aspx

https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql

https://docs.microsoft.com/pt-br/sql/t-sql/functions/translate-transact-sql

https://docs.microsoft.com/pt-br/sql/t-sql/functions/string-functions-transact-sql

Links

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

https://pedrogalvaojunior.wordpress.com/2017/10/01/16-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/06/28/15-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/04/30/14-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/03/25/13-para-que-serve/

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

Conclusão

Como sempre a Microsoft e toda sua equipe nos surpreende com sua capacidade de trabalho, fortalecendo cada vez mais o Microsoft SQL Server não somente com um SGBD ou ferramenta de banco de dados, mas sim um ambiente completo para qualquer tipo de análise, desenvolvimento e administração que esteja relacionada com dados.

Destacando as novas funções apresentadas neste post Concat_WS e Translate, atividades como concatenação de dados que já havia se tornada mais fácil a partir da versão 2o12, agora se tornou algum praticamente irrelevante no que diz respeito a complexidade.

Sem se esquecer da função Translate que através de um simples argumento nos permite “realizar uma possível tradução de caracteres” muito similar a antiga e útil função Replace, mas que trabalha de uma forma mais ágil independente da posição do caracter dentro do conjunto de valores apresentados.

Este é o fantástico Microsoft SQL Server, eita produto bão so……

Agradecimentos

Chegou a hora do descanso, se preparar para um novo dia que daqui a pouco estará raiando, espero que você possa fazer o mesmo, aproveitar o seu dia ainda mais, tentar viver um pouco sem se preocupar com os problemas.

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve…..

Valeu.

 

 

Microsoft SQL Server Migration Assistant v7.6


A Microsoft disponibilizou para download nesta semana o Microsoft SQL Server Migration Assistant v7.6. Disponível para MySQL, SAP Adaptive Server Enterprise (ex-Sybase), Oracle Database, IBM DB2 e Access, o Microsoft SQL Server Migration Assistant é uma ferramenta gratuita que simplifica o processo de migração destes produtos para o SQL Server e Azure SQL.

A ferramenta automatiza todos os aspectos da migração. A versão 7.6 inclui o suporte para:

– Migração do MySQL 4.1 e posteriores para todas as edições do SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 e Azure SQL DB.

– Migração do Access 97 e posteriores para todas as edições do SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 e Azure SQL DB.

– Migração do SAP Adaptive Server Enterprise (ex-Sybase) 11.9 e posteriores para todas as edições do SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 e Azure SQL DB.

– Migração do Oracle Database 9.0 e posteriores para todas as edições do SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, Azure SQL DB e Azure SQL Data Warehouse.

– Migração do IBM DB2 9.0 e 10.0 no z/OS e das versões 9.7 e 10.1 no Linux/Unix/Windows para o SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 e Azure SQL DB.

IMPORTANTE: A versão 32 bits da ferramenta foi descontinuada. Ela está disponível agora somente para plataformas 64 bits.

Microsoft SQL Server Migration Assistant v7.6

Baixe o Microsoft SQL Server Migration Assistant v7.6

Download da versão 7.6 para MySQL
Download da versão 7.6 para Access
Download da versão 7.6 para SAP Adaptive Server Enterprise (ex-Sybase)
Download da versão 7.6 para Oracle Database
Download da versão 7.6 para IBM DB2

O Microsoft SQL Server Migration Assistant v7.6 é compatível com o Windows 10, Windows 7, Windows 8, Windows 8.1, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2 e Windows Server 2016. Ele também requer o .NET Framework 4.5.2 ou posterior.

As páginas de download também listam alguns requisitos específicos, como MySQL Connector/ODBC e SAP ASE OLEDB/ADO.Net/ODBC provider.

Fontes e Direitos Autorais: Microsoft.com – https://www.microsoft.com/en-us/download/details.aspx?id=54258&WT.mc_id=rss_alldownloads_all – 25/09/2017.

#07 – Para que serve


Boa tarde comunidade, boa tarde Brasil!!!!

Começando mais uma tarde de sábado, neste primeiro final de semana do mês de agosto, clima olímpico e muito feliz em ver que o Brasil foi capaz de fazer uma linda festa ontem na abertura dos Jogos Olímpicos Rio 2016, desejo muito sucesso para todos os participantes principalmente aos atletas brasileiros.

O post dedicado a sessão Para que serve deste mês, também esta no clima olímpico, você pode estar se perguntando o porque eu destaquei na minha abertura este clima. Quando estamos pensando em esporte muitas vezes pensamos que não existem possibilidades ou possíveis situações de um determinado time ou atleta ser superado por outro mais fraco, pode ser definido como algo “Hipotético”, sim “Hipotético” na sua definição com base em diversos dicionários: fictício, figurado, imaginário, suposto. Na área de banco de dados isso também pode ser aplicado, principalmente no SQL Server.

Mas de que maneira podemos pensar em algo hipotético, fictício ou imaginário quando estamos trabalhando com banco de dados? Pergunta que inicialmente pode ser difícil de ser respondida, complexa ou simplesmente hipotético(kkkkk).

Foi então que eu comecei a buscar mais informações em um conceito que pra mim era realmente imaginário de ser adotado, e recentemente em um dos posts publicados nos fóruns de SQL Server aqui no Brasil veio a tona o chamado Índices Hipotéticos.

Essa é uma possível resposta quando estamos trabalhando com banco de dados, fazer uso de índices hipotéticos pode nos ajudar a identificar ou similar possíveis situações de impacto na performance de uma query durante sua execução, ainda mais se estivermos trabalhando com um conjunto volumoso de dados.

Para tentar compartilhar com você um pouco sobre este mistorioso recurso que podemos adotar em nosso ambiente, o post de hoje o próximo da sessão para que serve serão dedicados justamente ao entendimento, criação e uso dos índices hipotéticos.

E como de costume aquelas perguntas já conhecidas dos posts anteriores desta sessão:

E ai, você conhece esta funcionalidade? Já utilizou? Sabe para que ela serve?

Pois bem, estas e outras possíveis perguntas serão respondidas a partir de agora em mais este post da sessão Para que Serve!


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

Mais um final de semana esta chegando, hoje sexta – feira, você já esta começando a se preparar para desligar sua estação de trabalho, pegar suas coisas e voltar para casa feliz por mais um dia de trabalho duro e gratificante e por saber que fez o melhor possível para manter tudo em ordem em seu local de trabalho, eis que após alguns minutos o seu ramal de telefone toca e no display aparece 2801 – Fernanda Galvão, meu deus você pensa, respira e atende sabendo que ela é a gerente de produção da empresa e para estar ligando no final do dia não deve ser nada muito simples, mesmo assim sabendo dos seus deveres e obrigações realiza o atendimento a ligação e escuta:

Junior Galvão, boa tarde!!!

Aqui é a Fernanda, tudo bem? Estamos com um pequeno problema na emissão do relatório de produção diária, estou aqui com o analista de produção João Pedro, você pode falar com ele?

Junior responde sim, claro!!!

Neste momento, João Pedro apresenta o cenário: Junior, olá boa tarde.

Estou com dificuldades para emitir o relatório de produção diária, ao tentar filtrar os dados por clientes e categoria de clientes, o sistema aparentemente entra em loop de processamento e os dados não são apresentados em tela. 

O que será que pode estar acontecendo? Alguns segundos se passam…. Junior começa a pensar e observa que seu final de semana aparentemente foi por água, ou melhor dizendo o final de semana não vai ser tão tranquilo.

Você responde: João Pedro, boa tarde, vou verificar o que pode esta acontecendo, sei que hoje realizamos algumas mudanças na estrutura da tabela de clientes e categoria de clientes, parece-me que o time de suporte adicionou um novo índice, vou tentar verificar.

João Pedro responde dizendo.

Ok! Junior, fico no aguardo, assim que você tiver uma posição por favor me informe.

Junior responde: Certo, perfeito, deixa comigo, vou verificar o que esta acontecendo garanto que hoje não vou conseguir dar uma resposta mais concreta!

Sabendo justamente que a equipe de suporte esta trabalhando para realizar alguns testes de performance nas tabelas de clientes e categoria de clientes, onde milhares de registros estão sendo processados diariamente, Junior chama um dos seus analistas de suporte Eduardo Galvão e pergunta:

Edu por acaso vocês realizaram alguma alteração na estrutura das tabelas de clientes ou categoria de clientes?

A resposta é simples e direta, sim Junior, estamos fazendo uso de um recurso que até então novo ou supostamente desconhecido para nossa equipe,  pelo que pesquisei é uma funcionalidade conhecida como índices que possuem somente estatíticas mas não existem fisicamente.

Junior responde: Índices somente com estatísticas, índices que não existem, que raio de recurso é esse, por acaso vocês estão se referindo a índices hipotéticos?

Eduardo responde, sim, sim, acredito que seja isso a analista de suporte de banco de dados Maria Luíza, esta fazendo um estudo sobre isso em nosso ambiente de testes e identificou que se fizermos a adoção deste recurso poderemos ter mais facilidade em reconhecer a necessidade de um novo índice ou se o mesmo realmente é útil mesmo após ter sido criado anteriormente.

Junior responde, certo Eduardo, mas este tipo de teste ou implementação deve ser planejada, não podemos simplesmente pesquisar um recurso na internet ou livros e já sair aplicando em nossos ambientes de teste, muito menos em produção, devemos sempre por em prática nosso check-list de boas práticas e principalmente ter um ambiente de contigência caso algo aconteça de errado.

Quero saber qual será a forma para identificar o que esta acontecendo e como vamos resolver este problema até segunda – feira, questiona Junior!!!”

Muito bem, este é nosso cenário, com base, nesta pequena estória que acabamos de conhecer, será criado nosso ambiente de testes para colocar em prática o conceito de índices hipotéticos, antes disso iremos comecer um pouco mais sobre este conceito.

Índices

Falando de uma maneira simples quando criamos um novo índice no SQL Server ou em qualquer outro banco de dados, estamos criando uma estrutura que basicamente servirá como caminho na busca e identificação de um ou mais dados solicitados pelos mecanismos de banco de dados durante o processamento de uma determinada query.

Ao realizar a criação deste elemento normalmente os índices físicos apresentam em sua estrutura os dados, distribuídos de maneira demográfica confome as manipulações são realizadas, além disso, apresentam densidade, granularidade e seletividade de acordo com seu conjunto de valores, com isso, temos um conjunto de informações técnicas conhecidas como estatísticas do índice o que permite servir como elemente auxiliar no obtenção mais ágil e simples dos dados solicitados.

Índices Hipotéticos

Ao se falar de índices hipotéticos, estamos se referindo a uma estrutura completamente oposta, sem qualquer tipo estrutura física, muito menos dados, um índice hipotético é conhecido como algo imaginário que não possue estrutura física, somente estrutura lógica ou seja, somente estatísticas que podem servir como recurso para tentar criar o mecanismo de banco de dados e também o plano de execução por parte do SQL Server na busca de um ou mais dados.

Como podemos criar um índice hipotético?

A partir do SQL Server 2008 R2 a Microsoft adicionou uma opção no comando Create Index conhecido como With Statistics_Only, traduzindo ao pé da letra para o português vamos encontrar ao similar à somente estatísticas. É com base nesta opção não documentada que temos a possibilidade de fazer uso de índices hipotéticos em nossos bancos de dados.

O uso desta opção é muito simples, basta ao final da linha de comando que referencia a criação de um novo índice adicionar a instrução With Statistics_Only = 0, onde o mecanismo de banco de dados vai entender que esta novo índice deverá ser criado possuindo somente uma estrutura lógica controlada e direcionada através dos dados estatísticos coletados durante as manipulações de dados ou execução de querys que fazem uso do mesmo. Quando criamos um novo índice e não informamos esta opção por padrão o mecanismo de banco de dados repassa internamente para processador de querys que este índice deve ser criado da maneira padrão ou seja, um índice que conterá estrutura física e lógica, e o valor correspondente a instrução With Statistics_Only será igual á -1, ou seja:

  • With Statistics_Only = 0 — Indica que o índice deve ser criado de maneira hipotética, índice forma somente por estrutura lógica, conhecida como estatíticas; e
  • With Statistics_Only = -1 — Indica que o índice deve ser criada da maneira clássica, índice formado por estrutura física e lógica.

Uma forma simples é fácil para saber se um ou mais índices apresentam esta diferença pode ser encontrada na visão de sistema sys.indexes através da coluna is_hypothetical, onde a mesma deverá apresentar os valores: 0(zero) ou 1(hum), sendo estes valores que identificam e diferenciam a ocorrência da existência de um ou mais índices clássicos e hipotéticos.

Mas não tudo sem flores como diria meu irmão, a criação de um índice hipotético é fácil, tranquila, sem muitos segredos. Agora, imagine se você deseja orientar otimizador de consultados existentes no SQL Server no uso deste tipo de índice durante o processamento de uma query, ou então se você deseja omitir o seu uso, situação que pode parecer muito comum de ser realizada ou automática, mas não é bem assim.

Temos a necessidade de dirigir isso mesmo, mostrar o caminho que deve ser seguido pelo Database Engine em conjunto com o Query Optimizer e posteriormente o Execution Plan, como deve ser feito o uso de um índice hipotético. Isso parece ser algo bastante complicado, não é bem assim, como sempre existe uma solução que a Microsoft muitas vezes também não reconhece como recurso documentado ou simplesmente não documento, e ai mais uma vez “Mister M de SQL Server” surge para nos ajudar e apresentar ao mundo como uma possível solução pode ser adotada maneira mais suave, mostrando como podemos  resolver este problema e sair desta sinuca de bico.

Pra variar surge para muitos um novo  DBCC – Database Command Console não documentado conhecido como DBCC AutoPilot e uma nova diretiva Set AutoPilot, onde:

  • Set AutoPilot – Orienta o query optimizer a considerar ou não o uso do índice hipotético no momento da criação do plano de execução da query; e
  • DBCC AutoPilot – Orienta o query optimizer fazer uso do índice hipotético de acordo com o conjunto de parâmetros a ser utilizado e posteriormente repassado para o plano de execução.

Preste atenção o nome dele não tem nada haver com piloto de Fórmula 1 (kkkk), vou repetir o seu nome DBCC AutoPilot e ele vai justamente nos ajudar e saber mais sobre os dados que estão relacionados com um determinado índice hipotético.

DBCC AUTOPILOT

Este comando DBCC é mais um dos diversos comandos de console de banco de dados que a Microsoft não reconhece como comando documentado ou suportado nativamente, através do conjunto de instruções “parâmetros” que compõem sua sintaxe o query optimizer vai se comportar de uma determinado maneira ou de outra.

Abaixo apresento a relação de parãmetros que formam o DBCC AutoPilot:

Parâmetro

Descrição
typeid Existem alguns valores, os mais utilizados basicamente são:
Type ID = 5: Iniciar a sessão ou comandos anteriores limpos;
Type ID = 0: Fazer uso de índices não clusterizados; e
Type ID = 6: Usar apenas índices clusterizados.
dbid Id do banco de dados habilitado para executar o comando.
maxQueryCost Supostamente definir um possível custo em relação ao processamento da query. “Sinceramente não entendi bem como usar (kkkk)”
tabid Id da Tabela a ser utilizada.
indid Id do índice a ser utilizado.
pages Ao executar o DBCC AutoPilot simular o comportamento e uso de páginas de dados.
flag Parâmetro desconhecido, não encontrei informações sobre ele….
rowcounts Parâmetro utilizado para definir o número de linhas de execução e processamento para alguns comandos.

Bom vou deixar você agora com um gostinho de quero mais, como destacado anteriormente este é a primeira parte deste Para que serve…. Na segunda parte vamos criar nossos índices hipotéticos e fazer uso da diretiva SET AutoPilot, posteriormente na terceira parte vamos utilizar a não documentada DBCC AutoPilot.


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

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

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

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

Short Scripts – Março 2016


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

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

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

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

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

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

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

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

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

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

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

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

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

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

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

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

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

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

create table #customers (

id int,

customer varchar(50)

)

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

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

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

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

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

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

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

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

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

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

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

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

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

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

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

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

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

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

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

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

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

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

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

Exec sp_helpdb ‘Master’

Go

 

 

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

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

go

declare @txt varchar(255)

set @txt = ‘Question’

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

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

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

select @txt

Go

 

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

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

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

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

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

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

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

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

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

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

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

— The after update Select Statement

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

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

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

GO

 

Exec [QOD_Test_1]

Go

 

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

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

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

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

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

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

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

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

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

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

Go

 

 


 

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

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

Valeu!!!

Short Script – Dezembro – 2015


Bom dia, Comunidade. Good Morning Everyone!!!!!

Como diria aquele apresentador “O louco meu…..”, 2015 esta chegando ao seu final parece que foi ontem que publiquei o primeiro post deste ano, realmente o tempo esta passando de forma assustadora e não estamos dando conta do quanto estamos vivendo, ou melhor sobrevivendo as loucuras do mundo.

Mantendo a escrita e tradição não poderia deixar passar a oportunidade de compartilhar com vocês os mais novos Short Scripts que adicionei a minha biblioteca de Scripts dedicados ao Microsoft SQL Server desde a versão 2000. Neste momento minha modesta biblioteca de arquivos esta composta por 1.121 scripts organizados em 66 pastas, sub-dividos em 4 categorias:

  • Comuns;
  • Básicos;
  • Intermediários; e
  • Avançados.

Pois bem, no post de hoje destaco os seguintes recursos ou funcionalidades:

  • Arquivo de ErrorLog e Logs Management;
  • Contagem de linhas existentes em tabelas;
  • CTE Recursiva;
  • Diferença entre datas desconsiderando sábado e domingo;
  • Extended Events Target;
  • Função para formatar a primeira letra de cada palavra em maiúscula;
  • Geração de combinação de letras;
  • Multiple Server in Query Windows;
  • Stored Procedure para gerar CNPJ e CPF;
  • SQLCMD Mode; e
  • Variável Table.

Fique a vontade para compartilhar, sugerir melhoras, críticas ou questionamentos sobre estes exemplos.

Segue abaixo a relação de short scripts:

1 – Exemplo – Utilizando Extended Events Target para separar e contar loings e logouts:

CREATE EVENT SESSION [CounterTest] ON SERVER

ADD EVENT sqlserver.login,

ADD EVENT sqlserver.logout(

ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.username))

ADD TARGET package0.event_counter

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO
— login and logout a few times

GO

— query for data

— Query the Target

SELECT

n.value(‘@name[1]’, ‘varchar(50)’) AS Event

, n.value(‘@count[1]’, ‘int’) AS EventCounts

FROM

( SELECT

CAST(t.target_data AS XML) AS target_data

FROM

sys.dm_xe_sessions AS s

JOIN

sys.dm_xe_session_targets AS t

ON

t.event_session_address = s.address

WHERE

s.name = ‘CounterTest’

AND t.target_name = ‘event_counter’

) AS tab

CROSS APPLY target_data.nodes(‘CounterTarget/Packages/Package/Event’) AS q ( n )

GO

 

2 – Exemplo – Stored Procedure para gerar CNPJ e CPF:

CREATE PROCEDURE dbo.stpGerador_CPF_CNPJ (
@Quantidade INT = 1,
@Fl_Tipo BIT = 1
)
AS BEGIN

IF (OBJECT_ID(‘tempdb..#Tabela_Final’) IS NOT NULL) DROP TABLE #Tabela_Final
CREATE TABLE #Tabela_Final (
Nr_Documento VARCHAR(18)
)

DECLARE
@n INT,
@n1 INT,
@n2 INT,
@n3 INT,
@n4 INT,
@n5 INT,
@n6 INT,
@n7 INT,
@n8 INT,
@n9 INT,
@n10 INT,
@n11 INT,
@n12 INT,

@d1 INT,
@d2 INT

— CPF
IF (@Fl_Tipo = 0)
BEGIN

WHILE (@Quantidade > 0)
BEGIN

SET @n = 9
SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @d1 = @n9 * 2 + @n8 * 3 + @n7 * 4 + @n6 * 5 + @n5 * 6 + @n4 * 7 + @n3 * 8 + @n2 * 9 + @n1 * 10
SET @d1 = 11 – ( @d1 % 11 )

IF ( @d1 >= 10 )
SET @d1 = 0

SET @d2 = @d1 * 2 + @n9 * 3 + @n8 * 4 + @n7 * 5 + @n6 * 6 + @n5 * 7 + @n4 * 8 + @n3 * 9 + @n2 * 10 + @n1 * 11
SET @d2 = 11 – ( @d2 % 11 )

IF ( @d2 >= 10 )
SET @d2 = 0

INSERT INTO #Tabela_Final
SELECT CAST(@n1 AS VARCHAR) + CAST(@n2 AS VARCHAR) + CAST(@n3 AS VARCHAR) + ‘.’ + CAST(@n4 AS VARCHAR) + CAST(@n5 AS VARCHAR) + CAST(@n6 AS VARCHAR) + ‘.’ + CAST(@n7 AS VARCHAR) + CAST(@n8 AS VARCHAR) + CAST(@n9 AS VARCHAR) + ‘-‘ + CAST(@d1 AS VARCHAR) + CAST(@d2 AS VARCHAR)

SET @Quantidade = @Quantidade – 1

END

END

— CNPJ
ELSE BEGIN

WHILE (@Quantidade > 0)
BEGIN

SET @n = 9
SET @n1 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST(( @n + 1 ) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = 0
SET @n10 = 0
SET @n11 = 0
SET @n12 = 1

SET @d1 = @n12 * 2 + @n11 * 3 + @n10 * 4 + @n9 * 5 + @n8 * 6 + @n7 * 7 + @n6 * 8 + @n5 * 9 + @n4 * 2 + @n3 * 3 + @n2 * 4 + @n1 * 5
SET @d1 = 11 – ( @d1 % 11 )

IF (@d1 >= 10)
SET @d1 = 0

SET @d2 = @d1 * 2 + @n12 * 3 + @n11 * 4 + @n10 * 5 + @n9 * 6 + @n8 * 7 + @n7 * 8 + @n6 * 9 + @n5 * 2 + @n4 * 3 + @n3 * 4 + @n2 * 5 + @n1 * 6
SET @d2 = 11 – ( @d2 % 11 )

IF (@d2 >= 10)
SET @d2 = 0

INSERT INTO #Tabela_Final
SELECT ” + CAST(@n1 AS VARCHAR) + CAST (@n2 AS VARCHAR) + ‘.’ + CAST (@n3 AS VARCHAR) + CAST (@n4 AS VARCHAR) + CAST (@n5 AS VARCHAR) + ‘.’ + CAST (@n6 AS VARCHAR) + CAST (@n7 AS VARCHAR) + CAST (@n8 AS VARCHAR) + ‘/’ + CAST (@n9 AS VARCHAR) + CAST (@n10 AS VARCHAR) + CAST (@n11 AS VARCHAR) + CAST (@n12 AS VARCHAR) + ‘-‘ + CAST (@d1 AS VARCHAR) + CAST (@d2 AS VARCHAR);

SET @Quantidade = @Quantidade – 1

END

END

SELECT * FROM #Tabela_Final

END

 

3 – Exemplo – Reciclando o arquivo de ErrorLog e Logs Management:

EXEC sp_cycle_errorlog
GO

 

4 – Exemplo – Função – Formatar a primeira letra de cada palavra em maiúscula:

CREATE FUNCTION dbo.udfNomeProprio (
@Nome varchar(250)
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @Pos tinyint = 1
DECLARE @Ret varchar(250) = ”

WHILE (@Pos < LEN(@Nome) + 1)
BEGIN
IF @Pos = 1
BEGIN
–FORMATA 1.LETRA DA “FRASE”
SET @Ret += UPPER(SUBSTRING(@Nome, @Pos, 1))
END
ELSE IF (SUBSTRING(@Nome, (@Pos-1), 1) = ‘ ‘
AND SUBSTRING(@Nome, (@Pos+2), 1) <> ‘ ‘) AND (@Pos+1) <> LEN(@Nome)
BEGIN
–FORMATA 1.LETRA DE “CADA INTERVALO””
SET @Ret += UPPER(SUBSTRING(@Nome, @Pos, 1))
END
ELSE
BEGIN
–FORMATA CADA LETRA RESTANTE
SET @Ret += LOWER(SUBSTRING(@Nome,@Pos, 1))
END

SET @Pos += 1
END

RETURN @Ret
END
GO

SELECT dbo.udfNomeProprio(‘pedro antonio galvão junior’)
GO

 

5 – Exemplo – Utilizando Multiple Server in Query Windows using SQLCMD Mode:

— Sem o GO o SQLCMD Mode não entende o final do bloco —
:connect saom4276
select @@SERVERNAME

:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME

— Utilizando o comando Go para encerrar o bloco —
:connect saom4276
select @@SERVERNAME
Go

:connect SAOM4276\SQLEXPRESS2014
select @@SERVERNAME
Go

 

6 – Exemplo – Gerando combinação de letras:

create table #t (string varchar (2))

declare @a1 varchar(1), @a2 varchar(1)
set @a1=’A’

while @a1 <= ‘Z’
begin
set @a2=’A’

while @a2 <= ‘Z’
begin
insert into #t select @a1 + @a2
set @a2 = char (ascii(@a2) + 1)
end

set @a1 = char (ascii(@a1) + 1)
end

select string from #t
where string like ‘_’ /*single underscore*/

Go

 

7 – Exemplo – Calculando diferença entre datas desconsiderando sábado e domingo:

declare @Data datetime set @Data = ‘2015/10/01’

–Calcula a quantidade de dias entre a data inicial e a data atual, excluindo sbados e domingos soma 1 no fim pois no conta o proprio dia

WITH AllDates AS

(   SELECT  TOP (DATEDIFF(DAY, @Data, GETDATE()))

D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @Data)

FROM    sys.all_objects a

CROSS JOIN sys.all_objects b

)

SELECT  WeekDays = COUNT(*) +1 –

–Clcula a quantidade de feriados entre as datas

(select count(*) from FTAFE(NOLOCK)

where convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112) between @Data and GETDATE()

and FTAFE.CD_CIDADE in(0)

and DATEPART(weekday, convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112)  ) not in(6,7))

FROM    AllDates

WHERE   DATEPART(WEEKDAY, D) NOT IN(6, 7)

Go
8 – Exemplo – Variável Table + CTE Recursiva para gerar sequência de letras:

declare @Tabela table
( COL1 INT,  COL2 VARCHAR(1));

insert into @Tabela values
(75, NULL),
(78, ‘C’),
(12, ‘B’),
(24, ‘D’)

;with CTE_Rec (COL1, COL2) as
(
select
COL1,
COL2
from @Tabela

union all

select
COL1,
case when ASCII(COL2) > 66 then CAST(CHAR(ASCII(COL2) – 1) AS VARCHAR(1))  end
from CTE_Rec as c
where
COL2 is not null
)

select
COL1,
COL2
from CTE_Rec
order by
COL1,
COL2

Go

 

9 – Exemplo – Quantidade de Linhas – Todas as Tables:

— Exemplo 1 —
SELECT t.[name], p.[rows]
FROM sys.schemas s INNER JOIN sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
ON p.[object_id] = t.[object_id]
AND p.[index_id] = i.[index_id]
Go

— Exemplo 2 —
;With Contador (name, rows)
As
(
SELECT t.[name], p.[rows]
FROM sys.schemas s INNER JOIN sys.tables t
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.[type] IN (0,1)
INNER JOIN sys.partitions p
ON p.[object_id] = t.[object_id]
AND p.[index_id] = i.[index_id]
)
Select name, rows,  ” as soma from Contador
Union all
Select ‘Total’ , Null, convert(varchar(10),sum(rows)) as soma from contador

Go

Caso você queria acessar os demais Short Scripts publicados em 2015, segue abaixo os links:

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

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

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

https://pedrogalvaojunior.wordpress.com/2015/05/04/short-script-maio-2015/

Mais uma vez obrigado por sua visita, espero que estes material posso ser útil e venha a colaborar em seu aprendizado.

Nos encontramos em breve…..