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….

Anúncios

Short Scripts – Março 2016


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

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

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

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

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

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

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

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

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

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

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

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

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

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

8.8Eight,‘Six”Seven’ ‘Eight’

Go

 

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

USE tempdb;

GO

 

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘bitwise’)

DROP TABLE bitwise;

GO

CREATE TABLE bitwise

(

a_int_value int NOT NULL,

b_int_value int NOT NULL

);

GO

INSERT bitwise VALUES (250, 255);

GO

 

USE tempdb;

GO

SELECT a_int_value | b_int_value

FROM bitwise;

GO

 

Select convert(binary, a_int_value | b_int_value) from bitwise

Go

 

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

create table #customers (

id int,

customer varchar(50)

)

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

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

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

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

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

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

a)

SELECT 1 AS tag,

NULL AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersa

UNION ALL

SELECT 2 AS tag,

1 AS parent,

id AS [customers!1!id],

customer AS [customers!2!customer]

FROM #customers AS customersb

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

FOR XML EXPLICIT, ROOT (‘customers’)

 

B)

SELECT id AS [customers/@id],

customer AS [customers/customers/@customer]

FROM #customers AS customesra

ORDER BY customer DESC

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

 

C)

SELECT customersa.id AS [customers.id],

customersb.customer AS [customers.customer]

FROM #customers AS customersa

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

ORDER BY customersa.customer DESC

FOR XML AUTO, ROOT(‘customers’)

 

D)

DECLARE @x AS XML

SET @x=(

SELECT id, customer

FROM #customers AS customersa

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

)

SELECT @x.query(

for $e in customers/customer

order by $e/@customer descending

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

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

</customers>

</customers>’)

FOR XML RAW(‘customers’)

Go

 

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

— Obtendo a lista de Database Owners —

Select d.database_id,

d.name,

suser_name(d.owner_sid) as ‘Owner’,

d.user_access_desc,

d.compatibility_level

from sys.databases as d

Go

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

Exec sp_helpdb ‘Master’

Go

— Alterando o Database Owner —

Exec sp_changedbowner ‘NomedoNovoOwner’

Go

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

Exec sp_helpdb ‘Master’

Go

 

 

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

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

go

declare @txt varchar(255)

set @txt = ‘Question’

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

insert xxx values (‘of’)

insert xxx values (‘the’)

insert xxx values (‘day’)

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

select @txt

Go

 

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

create table #Table1 (Col1 varchar(10))

create table #Table2 (Col1 varchar(10))

Go

insert into #table1 values (‘1’)

insert into #table2 values (‘1’)

insert into #table2 values (‘_1’)

insert into #table2 values (‘%1’)

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

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

Go

 

select * from #table1 t1 inner join #table2 t2

on t1.Col1 like t2.Col1

Go

 

 

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

Create Table QOD_Customers

(CompanyName Varchar(20),

Region nvarchar(15) Null)

Go

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

Go 30

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

— Before rollback Select Statement

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

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

— The after update Select Statement

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

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

— The after rollback Select Statement

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

GO

 

Exec [QOD_Test_1]

Go

 

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

declare @i int, @j int

set @i = 1

create table #temp (id int)

while (@i<=5)

begin

begin try

begin transaction

if (@i = 3)

set @j = @i/0

insert into #temp values (@i)

commit transaction

end try

begin catch

rollback transaction

print ‘this is an exception’;

end catch

set @i = @i + 1

end

 

select * from #temp

Go

 

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

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

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

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

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

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

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

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

Go

 

 


 

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

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

Valeu!!!

Short Script – Maio – 2015


Bom dia, pessoal, mais uma semana começando.

Mantendo a tradição e escrita, estou começando mais um mês no meu blog, compartilhamento com todos vocês a minha relação de Scripts que atualmente esta crescendo muito graças ao SQLServerCentral.com.

Venho diariamente estudando e respondendo as questões diárias que são publicadas no SQLServerCentral.com, pelos principais nomes e especialistas em SQL Server do Mundo, estou seguindo nesta caminhada desde o final do mês de fevereiro. Posso dizer que esta sendo uma oportunidade fantástica de relembrar, conhecer, aprender e também descobrir muitos segredos, novidades e principalmente informações sobre o Microsoft SQL Server em todas as suas versões.

Aproveito a oportunidade para indicar e recomendar sua visita ao SQLServerCentral.com, principalmente a sessão de Question of the Day: http://www.sqlservercentral.com/Questions

Pois bem, na relação de Short Scripts que estou disponibilizando hoje, boa parte destes códigos são resultado das questões que eu respondi no SQLServerCentral.com e identifiquei como questões importantes ou relevantes para meus estudos, bem como, para repassar para meus alunos.

Nesta relação, você terá a oportunidade de conhecer e aprender um pouco mais sobre:

·         Análise e Lógica Condicional com operadores Not, And e Or;
·         Colunas Computadas;
·         Complexidade Lógica;
·         CTE Recursiva;
·         Declaração de variáveis do Tipo XML em conjunto com Select;
·         Função Coalesce em conjunto com Left Join e valores nulos; e
·         Funções Char(), NChar(), Unicode() e ASCII();
·         Identificando ausência de índices em tabelas;
·         Índices Clusterizados e Índices Chaves;
·         Índices com colunas duplicadas;
·         Operador Intersect;
·         Taxas de Fragmentação de Tabelas antes e depois do Rebuild;
·         Transações aninhadas;
·         Utilitário BCP para criação de arquivos XML.

 

Legal, a seguir você poderá encontrar os Short Scripts separados por seus respectivos conteúdos:

  • Análise, Precedência e Lógica Condicional com operadores Not, And e Or    VALUES ( 1, ‘Baseball Hat’ ),           ( 3, ‘Snowboard’ ),           ( 5, ‘Shows’ )SELECT ID   WHERE Product = ‘Bicycle’ OR Product = ‘Snowboard’ AND ID = 4

 

  1.    FROM @Logic
  2.            ( 4, ‘Goggles’ ),
  3.          ( 2, ‘Bicycle’ ),
  4. INSERT INTO @Logic
  5. DECLARE @Logic TABLE ( ID INT, Product VARCHAR(50) )

 

  • Colunas Computadas   (   , col1Computed AS LEFT(col1, 4)   , col2Computed AS LEFT(col2, 4)   , col3Computed AS LEFT(col3, 4)   , col4Computed AS LEFT(col4, 4) 

 

  1.    );
  2.    , col4 CHAR(8) NOT NULL
  3.    , col3 CHAR(8) NULL
  4.    , col2 VARCHAR(8) NOT NULL
  5.      col1 VARCHAR(8) NULL
  6. CREATE TABLE t
  7. Complexidade Lógica

DECLARE @AA VARCHAR(12) = ‘AAA’

,@XXX           VARCHAR(12) = ‘AAA’

,@YYY           VARCHAR(12) = ‘BBB’

,@ZZZ           VARCHAR(12) = ‘CCC’

,@AAXXX         VARCHAR(12) = ‘XXAAA’

,@AAYYY         VARCHAR(12) = ‘XXBBB’

,@AAZZZ         VARCHAR(12) = ‘XXCCC’

,@XXXCount       INT = 1

,@YYYCount       INT = 1

,@ZZZCount       INT = 1

,@AAXXXCount     INT = 1

,@AAYYYCount     INT = 1

,@AAZZZCount     INT = 1

,@Result       INT;

 

— Much to your chagrin, you encounter the following logic in a SQL SP.

— You may assume that each local variable is DECLAREd and @AA,

— @XXX, @YYY, @ZZZ, @AAXXX, @AAYYY, @AAZZZ each have a value assigned,

— with only @AA required to be NOT NULL. Each @xxxCount variable

— is also assigned an integer value.

SET @Result = 0;

 

IF @AA = @XXX

BEGIN

IF @XXXCount > @AAXXXCount SET @Result = 2

END

ELSE IF @AA = @YYY

BEGIN

IF @YYYCount > @AAYYYCount SET @Result = 2

END

ELSE IF @AA = @ZZZ

BEGIN

IF @ZZZCount > @AAZZZCount SET @Result = 2

END

ELSE IF @AA = @AAXXX

BEGIN

IF @XXXCount = @AAXXXCount SET @Result = 3

END

ELSE IF @AA = @AAYYY

BEGIN

IF @YYYCount = @AAYYYCount SET @Result = 3

END

ELSE IF @AA = @AAZZZ

BEGIN

IF @ZZZCount = @AAZZZCount SET @Result = 3

END

 

— Identify the equivalent logic from the options shown:

— Option 1:

SET @Result = 0;

 

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

 

SELECT [Option 1:]=@Result

SELECT @Result = NULL;

 

 

— Option 2:

IF @AA = @XXX AND @XXXCount > @AAXXXCount SET @Result = 2

ELSE IF @AA = @YYY AND @YYYCount > @AAYYYCount SET @Result = 2

ELSE IF @AA = @ZZZ AND @ZZZCount > @AAZZZCount SET @Result = 2

ELSE IF @AA = @AAXXX AND @XXXCount = @AAXXXCount SET @Result = 3

ELSE IF @AA = @AAYYY AND @YYYCount = @AAYYYCount SET @Result = 3

ELSE IF @AA = @AAZZZ AND @ZZZCount = @AAZZZCount SET @Result = 3

ELSE SET @Result = 0;

 

SELECT [Option 2:]=@Result

SELECT @Result = NULL;

 

— Option 3:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

END;

 

SELECT [Option 3:]=@Result          — Not identical

SELECT @Result = NULL;

 

— Option 4:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 ELSE 0 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 ELSE 0 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 ELSE 0 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 ELSE 0 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 ELSE 0 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 ELSE 0 END

ELSE 0 END;

 

SELECT [Option 4:]=@Result

SELECT @Result = NULL;

 

— Option 5:

SELECT @Result = CASE @AA

WHEN @XXX THEN CASE WHEN @XXXCount > @AAXXXCount THEN 2 END

WHEN @YYY THEN CASE WHEN @YYYCount > @AAYYYCount THEN 2 END

WHEN @ZZZ THEN CASE WHEN @ZZZCount > @AAZZZCount THEN 2 END

WHEN @AAXXX THEN CASE WHEN @XXXCount = @AAXXXCount THEN 3 END

WHEN @AAYYY THEN CASE WHEN @YYYCount = @AAYYYCount THEN 3 END

WHEN @AAZZZ THEN CASE WHEN @ZZZCount = @AAZZZCount THEN 3 END

ELSE 0 END;

 

SELECT [Option 5:]=@Result   — Not identical

 

  • CTE Recursiva set @DataFim = ‘20150430’;with CTE_Datas as   select @DataIni as Data, @DataFim as DataFim   union all   select DATEADD(DAY, 1, Data), DataFim   where Data < DataFim 

 

  1. select Data from CTE_Datas
  2. )
  3.    from CTE_Datas
  4. (
  5. set @DataIni = ‘20150401’;
  6. declare @DataIni date, DataFim date;

 

  • Declaração de variáveis do Tipo XML em conjunto com Select

 

DECLARE @Xml XML = N’

<products>

<product id=”1″ name=”Adjustable Race” />

<product id=”879″ name=”All-Purpose Bike Stand” />

<product id=”712″ name=”AWC Logo Cap” />

<product id=”19910″ name=”Cozonac” />

</products>’;

 

SELECT

xt.xc.value(‘@id’, ‘INT’) AS ProductID,

xt.xc.value(‘@name’,’NVARCHAR(50)’) AS Name

FROM

@Xml.nodes(‘/products/product’) AS xt(xc);

 

  • Função Coalesce em conjunto com Left Join e valores nulos(a int null, c int null)(x int null, z int null)

 

  1. y int null,
  2. Create table #xyz
  3. b int null,
  4. Create table #abc

 

 

Insert Into #abc values(null, null, 3), (1,null,3),(1,null,null),(null,2,null)

 

Insert Into #xyz values(null,2,3),(null,null,1),(null,null,2)

 

— Q1

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON a.a = x.x

AND a.b = x.y;

 

— Q2

SELECT

*

FROM

#abc a

LEFT JOIN #xyz x

ON COALESCE(a.a, 9) = COALESCE(x.x, 9)

AND COALESCE(a.b, 9) = COALESCE(x.y, 9);

 

 

  • Funções Char(), NChar(), Unicode() e ASCII()SELECT ASCII(36)   SELECT NCHAR(10084)  

 

    1. SELECT UNICODE(10084)
    2. SELECT CHAR(36)
  • Identificando ausência de índices em tabelas             user_scans, ID.equality_columns, FROM sys.dm_db_missing_index_group_stats GS LEFT OUTER JOIN sys.dm_db_missing_index_groups IG                                                                                                                                                                                                                                LEFT OUTER JOIN sys.dm_db_missing_index_details ID Where ID.database_id = DB_ID(‘CycleCount’) 

 

    1. ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC
    2.                                                                                                                                                                                                                                On (ID.index_handle = IG.index_handle)
    3.                                                                                    On (IG.index_group_handle = GS.group_handle)
    4.              ID.inequality_columns, ID.included_columns, ID.statement
    5. SELECT avg_total_user_cost, avg_user_impact, user_seeks,
  • Índices Clusterizados e Índices Chaves

 

SELECT t.name, i.name, i.type_desc, c.name

FROM sys.tables t

INNER JOIN sys.indexes i

INNER JOIN sys.index_columns ic

INNER JOIN sys.columns c

ON ic.object_id = c.object_id AND ic.column_id = c.column_id

ON i.object_id = ic.object_id AND i.index_id = ic.index_id

ON t.object_id = i.object_id AND i.index_id = 1;

GO

 

  • Índices com colunas duplicadas   (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                      order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id    (select distinct object_name(i.object_id) tablename,i.name indexname,                                       from sys.index_columns ic1 inner join                                                              ic1.column_id=c.column_id                                           ic1.object_id=i.object_id and                                     order by index_column_id FOR XML PATH(”)),1,2,”)               where object_id=i.object_id and index_id=i.index_id) as columnlist        sys.index_columns ic on i.object_id=ic.object_id and            sys.objects o on i.object_id=o.object_id        substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and          (t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))

 

  1. Go
  2.        (t1.columnlist<>t2.columnlist or
  3. where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and
  4.                                    i.index_id=ic.index_id inner join
  5.        from sys.indexes i inner join
  6.                from sys.index_columns ic
  7.                                            ic1.index_id=i.index_id
  8.                                      where ic1.index_id = ic.index_id and
  9.                                            sys.columns c on ic1.object_id=c.object_id and
  10.              (select distinct stuff((select ‘, ‘ + c.name
  11.      where o.is_ms_shipped=0) t1 inner join
  12.                                    i.index_id=ic.index_id inner join
  13.        from sys.indexes i inner join
  14.                from sys.index_columns ic
  15.                                            ic1.index_id=i.index_id
  16.                                      where ic1.index_id = ic.index_id and
  17.                                            sys.columns c on ic1.object_id=c.object_id and
  18.              (select distinct stuff((select ‘, ‘ + c.name
  19. select t1.tablename,t1.indexname,t1.columnlist,t2.indexname,t2.columnlist from

 

  • Operador Intersect

 

CREATE TABLE A ( x INT )

 

INSERT INTO A

VALUES ( 18 ),

( 20 ),

( 4 ),

( 1 ),

( 2 )

 

CREATE TABLE B ( Y INT )

 

INSERT INTO B

VALUES ( 1 ),

( 20 ),

( 2 ),

( 3 )

 

SELECT X AS ‘Intersecting’

FROM A

INTERSECT

SELECT Y

FROM B

 

  • Taxas de Fragmentação de Tabelas antes e depois do Rebuild

 

Select

object_name(ddips.object_id) [Table_name],

convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) [Ext_frag],

ddips.page_count [Pages],

ddips.compressed_page_count

From sys.dm_db_index_physical_stats(db_id(), object_id(‘produtos’), null, null, ‘detailed’) ddips

  • Transações aninhadas

 

CREATE Table TranTable(col varchar(3))

GO

 

BEGIN TRAN A

INSERT INTO TranTable (col) Values (‘abc’)

BEGIN TRAN B

INSERT INTO TranTable (col) Values (‘def’)

SAVE TRAN B

BEGIN TRAN C

INSERT INTO TranTable (col) Values (‘ghi’)

COMMIT TRAN C

ROLLBACK TRAN B

INSERT INTO TranTable (col) Values (‘xyz’)

COMMIT TRAN A

GO

 

Select col from TranTable

GO

 

Drop TABLE TranTable

GO

 

  • Utilitário BCP para criação de arquivos XML

 

— Exemplo 1 —

 

DECLARE @FileName varchar(50),

@bcpCommand varchar(2000)

 

SET @FileName = ‘C:\Users\test.xml’

SET @bcpCommand = ‘bcp “SELECT

”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”, ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”, CompanyName AS ”Parameter/value”

FROM Sales.Customers

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

print(@bcpCommand)

 

EXEC master..xp_cmdshell @bcpCommand

 

 

 

— Exemplo 2 —

Declare @Contador Int,

@FileName varchar(50),

@bcpCommand varchar(2000)

 

Set @Contador = 1

 

While @Contador <=(Select Count(IDdaSuaTabela) from SuaTabela)

Begin

 

SET @bcpCommand = ‘bcp “SELECT ”CustomerID” AS ”Parameter/name”, CustomerID AS ”Parameter/value”,

”ContactName” AS ”Parameter/name”,

ContactName AS ”Parameter/value”,

”CompanyName” AS ”Parameter/name”,

CompanyName AS ”Parameter/value”

FROM Sales.Customers

Where IDdaSuaTabela = @Contador

FOR XML PATH(”T2Method”), ROOT(”Parking”), TYPE, ELEMENTS” queryout “‘

 

SET @FileName = ‘C:\Users\test-arquivo-‘+Convert(Varchar(10),@Contador)+’.xml’

SET @bcpCommand = @bcpCommand + @FileName + ‘” -T -c -x’

 

EXEC master..xp_cmdshell @bcpCommand

 

Set @Contador += 1

End

Mais uma vez obrigado por sua visita, fique à vontade para compartilhar este material com seus contatos. Dúvidas, comentários, sugestões e críticas, envie seu comentário ou se preferir um e-mail.

Até mais.

Conhecendo o Microsoft SQL Server 2012 e 2014 – Utilizando o ColumnStored Index.


Salve, Salve Comunidade! Tudo bem?

Após um período de diversos eventos que participei e realizei desde Março deste ano, até a última semana, consegui um pouco de tempo para poder compartilhar com vocês, mais um pouquinho sobre o Fantástico Mundo que forma o Microsoft SQL Server, que neste momento se encontra em destaque com o lançamento da nova versão denonimada 2014.

Dentre as diversas inovações que normalmente uma nova versão apresenta, eu vou destacar hoje com você um recurso que foi criado e implementado na versão 2012 mas que apresentou um grande evolução neste nova versão. Estou me referindo ao ColumnStore Index, ou simplesmente, Índice armazenado em Coluna, numa tradução bem comum.

Como de costume foi fazer uma pequena introdução sobre este recursos e no decorrer deste artigo, destacarei:

  • Pontos Positivos e Negativos;

 

 

  • Considerações;

 

 

  • Restrições;

 

 

  • Cenários de Uso; e

 

 

  • Códigos de exemplo.

 

 

Então boa leitura e Lets Go!!!

Introdução ao ColumnStore Index

Implementado no Microsoft SQL Server 2012 desde as primeiras versões disponibilizadas para comunidade, o ColumnStore Index foi apresentado como um dos maiores recursos e inovações do SQL Server 2012, principalmente no quesito de performance para se trabalhar com grandes volumes de dados, algo muito desejado por todos aqueles que trabalham com SQL Server, mas que entendiam que era um ponto a ser investido e tratado pela Microsoft.

Através do ColumnStore Index a Microsoft, possibilitou ao mercado de Banco de Dados e Data Warehouse, o uso de tecnologia considerada inovadora na forma de tratar os dados que estão armazenados em tabelas em bases relacionais e tabelas fatos em ambientes de Data Warehouse.

O funcionamento do ColumnStore Index

Com base, no novo algoritmo de processamento de dados chamado de xVelocity, o Microsoft SQL Server a partir da versão 2012, permite que suas consultas sejam processadas com um ganho de desempenho para padrões de alto nível com grandes volumes de dados, este um recurso que permite aumentar a velocidade das consultas em até 100% dentro de tabelas fatos em um Data Warehouse.

Vindo da tecnologia VertiPaq o ColumnStore Index, recurso que esta vinculado ao xVelocity, permite a transformação das páginas de dados em colunas, onde cada página de dados é acessada independentemente, fazendo com que a compressão seja muito mais efetiva.

Outro fator importante consiste no novo modo interno de busca de dados chamado Batch Mode, que estabelece e permite uma busca de dados nas colunas de forma mais ágil, inteligente, sendo realizado em blocos de 1000 em 1000 registros, possibilitando uma maior velocidade na busca de dados.

Nas versões anteriores do Microsoft SQL Server, os dados eram armazenados em modo de linhas, como também em tabelas Heaps e Índice em estrutura de armazenamento no formato B-Tree. Com o ColumnStore Index, este cenário muda, estes mesmos dados podem ter um maior aproveitamento, onde os valores são armazenados em modo coluna, sendo as colunas que formam o respectivo índice, conforme apresenta a Figura 1.

ColumnStoreIndex1

Figura 1 – Forma de armazenamento de dados utilizados no ColumnStore Index em comparação a forma normal realizada por Tabelas e Índices.

Como qualquer outro recurso e tecnologia o ColumnStore Index, também possui algumas características e comportamentos, dentre os quais destaco:

Pontos Positivos

A seguir apresento os principais Pontos Positivos existentes para o ColumnStore Index:

  • Batch Mode = Blocos de 1.000 linhas que são retornados a consulta ao ínves de linha-a-linha;

 

 

  • Algoritmo de Redução de Custo de I/O, tornando a consulta assim mais eficiente; e

 

 

  • “Segment  Elimination” de acordo com os filtros passados ao Query Optimizer, possibilitará trazer a consulta mais rápido, isso porque o mecanismo possibilita a quebra da partição em diversas partes selecionando assim os dados de uma forma mais eficiente.

 

 

Pontos Negativos

A seguir apresento alguns elementos considerados Pontos Negativos para em relalção ao ColumnStore Index:

  • Ao criar um ColumnStored Index no SQL Server 2012, a tabela se torna “Read-Only”, não podendo ser permitido operações de DML tais como: INSERT/UPDATE/DELETE e MERGE. Sendo assim só é possivel alterá-la se o ColumnsStore Index for desabilitada ou excluída no Microsoft SQL Server 2012. No Microsoft SQL Server 2014 esta limitação foi contornado, onde podemos criar ColumnStore Index no Clustered que permitem alteração e manipulação;

 

 

  • Algumas operações não são possíveis no novo modo “Batch Mode” como: Outer Joins, Join entre strings, NOT IN, IN, EXISTS e agregações escalares;

 

 

  • Se houver pressão na memória ou um grande uso de paralelismo, provavelmente o Query Optimizer utilizirá o modo linha-a-linha para a execução da consulta; e

 

 

  • Somente nas edições Enterprise, Standard e Developer é possível criar índices do tipo ColumnStore Index.

 

 

Considerações

A seguir apresento a relação de Considerações aplicadas ao ColumnStore Index:

  • Tabelas contendo milhões a bilhões de registros (Fact Tables) representam cenários mais indicados para se trabalhar com o ColumnStore Index;

 

 

  • O ColumnStore Indexes não suporta operações de Seek, somente Scan;

 

 

  • Operações de agregação como SUM(), AVG, joins e filtros utilizados na pesquisa;

 

 

  • Os índices podem ser criados em uma tabela temporária. Quando a tabela for removida ou a sessão encerrada, os índices serão removidos;

 

 

Os tipos de dados corporativos comuns podem ser incluídos em um índice columnstore, sendo eles:

 

  • char e varchar;

 

 

  • nchar e nvarchar (exceto varchar(max) e nvarchar(max));

 

 

  • decimal (e numeric) (exceto com precisão maior que 18 dígitos.);

 

 

  • int , bigint, smallint e tinyint;

 

 

  • float (e real);

 

 

  • Bit; e

 

 

  • money e smallmoney.

 

 

Observação: Todos os tipos de dados de data e hora (exceto datetimeoffset com escala maior que 2) podem ser utilizado.

Por outro lado, existem vários tipos de dados que não podem ser utilizados com ColumnStore Index, sendo eles:

 

  • binary e varbinary;

 

 

  • ntext , text e image;

 

 

  • varchar(max) e nvarchar(max);

 

 

  • Uniqueidentifier;

 

 

  • rowversion (e timestamp);

 

 

  • sql_variant ;

 

 

  • decimal (e numeric) com precisão maior que 18 dígitos;

 

 

  • datetimeoffset com escala maior que 2; e

 

 

  • Tipos CLR (hierarchyid e tipos espaciais).

 

 

Restrições

A seguir apresento a relação de Restrições existentes para o ColumnStore Index:

  • Não pode ter mais de 1024 colunas;

 

 

  • Apenas índices columnstore não clusterizados estão disponíveis, até a versão 2012. Na versão 2014 esta limitação foi removida, permitindo a utilização de ColumnStore Index, com base, em índices Clustered, permitindo a realizações de comandos de manipulação de Dados, sem necessitar que a tabela fique em “Read-Only”.

 

 

  • Não pode ser um índice exclusivo;

 

 

  • Não pode ser criado em uma exibição ou exibição indexada;

 

 

  • Não pode incluir uma coluna esparsa;

 

 

  • Não pode atuar como uma chave primária ou estrangeira;

 

 

  • Não pode ser alterado por meio da instrução ALTER INDEX. Em vez disso, remova e recrie o índice columnstore. Você pode usar ALTER INDEX para desabilitar e recriar um índice columnstore;

 

 

  • Não pode ser criado por meio da palavra-chave INCLUDE; e

 

 

  • Não pode incluir as palavras-chave ASC ou DESC para classificar o índice. Os índices columnstore são ordenados de acordo com os algoritmos de compactação. A classificação eliminará muitos dos benefícios de desempenho.

 

 

Índices ColumnStore não podem ser combinados com os recursos a seguir:

 

  • Compactação de página e de linha, e formato de armazenamento vardecimal (um índice columnstore já foi compactado em um formato diferente);

 

 

  • Replicação;

 

 

  • Controle de alterações;

 

 

  • Change Data Capture; e

 

 

  • Fluxo de arquivos.

 

 

Agora que já conhecemos mais sobre o ColumnStore Index, vamos começar a trabalhar com o mesmo, para isso utilizaremos os Bloco de Código apresentado abaixo, seguindo o passo a passo:

— Passo 1 — Criando o Banco de Registros ColumnStoreIndex —

CREATEDATABASEColumnStoreIndex

Go

— Passo 1.1 — Acessando o Banco de Registros ColumnStoreIndex —

UseColumnStoreIndex

Go

— Passo 2 — Criando uma nova Sequência de Valores —

CREATESEQUENCESeqAsINT— Tipo

STARTWITH 1 — Valor Inicial (1)

INCREMENTBY 1 — Avança de um em um

MINVALUE 1 — Valor mínimo 1

MAXVALUE 100000 — Valor máximo 100000

CACHE 1000 — Mantém 1000 posições em cache

NOCYCLE— Não irá reciclar

Go

— Passo 3 — Criando a Tabela Registros com Primary Key —

CreateTableRegistros

(DescricaoVarChar(60)NotNull,

ValorFloatNull,

DateDateDefaultGetDate(),

TimeTimeDefaultGetDate())

Go

AlterTableRegistros

AddConstraint[PK_Registros]PrimaryKeyClustered (Descricao)On[Primary]

Go

— Passo 3.1 — Criando um Índice NonClustered para Tabela Registros —

CREATENONCLUSTEREDINDEX [IND_Registros_NonClustered]

ON Registros

(Descricao, Valor,Date,Time)

Go

— Passo 4 — Listando a Relação de Índices da Tabela dbo.Registros —

Execsp_helpindex‘Registros’

Go

— Passo 5 — Inserindo a Massa de Registros —

InsertIntoRegistros(Descricao,Valor)

Values (‘Ola…’+Convert(Varchar(100),Rand()),Rand())

Go 10000

No Passo 6, realizaremos a execução do primeiro Select para buscar dados na Tabela Registros em conjunto com o Plano de Execução, conforme apresentam a Figura 2 e Figura 3:

— Passo 6 — Executando o Select em conjunto com Sequence e Verificar o Plano de Execução —

SelectNextValueforSeqAsCodigo,Descricao,Valor,Date,TimefromRegistros

Go

ColumnStoreIndex2

Figura 2 – Resultado do Plano de Execução apresentado após o Passo 6 ser executado.

ColumnStoreIndex3

 

 

Figura 3: Indicadores de execução utilizados pelo operadores Index Scan, na Tabela Registro, sendo que, este operador consumiu 98% do custo de processamento da query executado na Passo 6.

Galera, até aqui nada de novo tirando o objeto Sequence chamado Seq, que criamos no Passo 2, como um recurso para criar um seqüenciador(incremento) de valores utilizado como mecanismo de numeração dos registros que estão sendo inseridos em nossa Tabela. Vale ressaltar que este numerador esta sendo gerado durante o processamento do comando Select.

— Passo 7 — Limpando o Cache de Execução – Procedure e Buffer —

DBCCDROPCLEANBUFFERS

DBCCFREEPROCCACHE

Go

Você pode estar se perguntando o porquê, estamos fazendo a Limpeza do Cache de Execução existente neste momento no SQL Server. Na verdade isso não é obrigatório, decidi fazer uso deste procedimento para garantir e demonstrar que não existe nada residente em memória e que o SQL Server deverá criar novos planos para as transações que estarão sendo processadas.

Observações

  • Use DBCC FREEPROCCACHE para limpar o cache do plano cuidadosamente. Por exemplo, liberar o cache do plano faz com que um procedimento armazenado seja recompilado em vez de reutilizado no cache.Isso pode causar uma diminuição súbita e temporária no desempenho de consulta.

 

 

  • Para cada armazenamento em cache limpo no cache do plano, o log de erros do SQL Server conterá a seguinte mensagem informativa: “O SQL Server encontrou %d ocorrência(s) de liberação de armazenamento em cache ‘% s’ (parte do cache do plano) devido às operações ‘DBCC FREEPROCCACHE’ ou ‘DBCC FREESYSTEMCACHE'”. Essa mensagem é registrada a cada cinco minutos, desde que o cache seja liberado dentro desse intervalo de tempo.

 

 

O próximo passo será realizar a criação do ColumnStore Index, chamado ColumnStoreIndex_Registros, conforme apresenta a Figura 4:

— Passo 8 — Criando o ColumnStore Index —

CREATENONCLUSTEREDCOLUMNSTOREINDEXColumnStoreIndex_Registros

ONdbo.Registros

(

Descricao,

Valor,

Date,

Time

)

Go

ColumnStoreIndex4

Figura 4: Relação de Índices existentes na Tabela Registros, incluindo o ColumnStore Index.

Pois bem, para ilustrar nosso cenário, vamos fazer a ativação das Estatíticas de Time e IO para utilizarmos como indicadores de comparação quando estivermos ou não fazendo uso do ColumnStore Index, para isso executaremos o Passo 9.

— Passo 9 — Ativando as Estatísticas de Time e IO —

SETSTATISTICSTIMEON

SETSTATISTICSIOON

Go

Muito bem, estatísticas ativas, a partir de agora nossas transações terão o controle de tempo de I/O sendo apresentadas em tela como indicadores ou medidores de desempenho. Nosso próximo passo é executar o Passo 10 e Passo 11, observando as diferenças apresentada entre os Planos de Execução.

— Passo 10 — Executando novamente o Select com ColumnStore Index e Verificar o Plano de Execução —

SelectNextValueforSeqAsCodigo,Descricao,Valor,Date,TimefromRegistros

Go

— Passo 11 — Executando novamente o Select ignorando o ColumnStore Index e Verificar o Plano de Execução —

SelectNextValueforSeqAsCodigo,Descricao,Valor,DatefromRegistros

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

Go

Neste momento podemos realizar a execução do Passos 10 e 11, em seguida poderemos observar o resultado deste processamento, com base, nos operadores e custo de processamento de cada operador apresentado pelo Plano de Execução. Então vamos nessa, hora de executar os próximos passos e conferir o seu resultado apresentado abaixo na Figura 5.

ColumnStoreIndex5

Figura 5: Resultado apresentado pelo Plano de Execução, no momento do processamento dos Passos 10 e 11.

Podemos facilmente notar que o custo de Processamento do Passo 10, que faz uso do nosso ColumnStore Index foi de 27%, sendo que, o operador ColumnStore Index Scan, consumiu 98% de processamento. Em contra partida o Passo 11, consumiu 73% do custo de processamento, onde 99% deste processamento foi consumido pelo operador Index Scan.

Entendo que neste momento, você já pode ter entendido e observado como o ColumnStore Index pode fazer diferença, principalmente quando estamos fazendo uma consulta de dados, os demais passos vou deixar como complemento, na verdade com passos adicionais para representar outros cenários de comparação e uso do ColumnStore Index em relação a Tabelas e Índices.

— Passo 12 — Executando novamente o Select + Group By com ColumnStore Index e Verificar o Plano de Execução —

SelectNextValueforSeqAsCodigo,Descricao,Valor,Date,Count(Time)AsContagemfromRegistros

GroupByDescricao,Valor,Date

Go

— Passo 13 — Executando novamente o Select + Group By ignorando o ColumnStore Index e Verificar o Plano de Execução —

SelectNextValueforSeqAsCodigo,Descricao,Valor,Date,Count(Time)AsContagemfromRegistros

GroupByDescricao,Valor,Date

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

Go

— Passo 14 — Desativando as Estatísticas de Time e IO —

SETSTATISTICSTIMEOFF

SETSTATISTICSIOOFF

Go

Finish!!!

Conclusão

O ColumnStore Index, com certeza representa uma evolução nos recursos e tecnologias de bancos de dados relacional criada para se trabalhar com grandes volumes de dados, a maneira com que os dados são armazenados no ColumnStore Index nos permitem conseguir um ganho de performance de maneira perceptível e satisfatória.

As vantagens que este tipo de recurso pode apresentar são inúmeras, desde o processo de configuração até mesma a forma de uso, que possibilita ao SQL Server adaptar-se no momento da execução da transação que esta fazendo a consulta dos dados.

Espero que o material apresentado neste artigo possa ser útil e colabore com suas atividades, vou continuar testando novos recursos presentes no Microsoft SQL Server 2012 que foram melhorados na versão 2014.

Mais uma vez obrigado, nos encontramos em breve.

Até mais.