Short Script – Dezembro – 2013


Pessoal, bom dia.

Fim de ano chegando e com certeza o gás já esta acabando!!!

Mas não posso deixar de compartilhar com vocês mais alguns dos meus companheiros de trabalho, você já sabe do que estou falando, estou me referindo aos Short Scripts que ajudam em muito na resolução de problemas.

Neste último Short Script do ano, vou disponibilizar um conteúdo variado para o Microsoft SQL Server 2005, 2008, 2008 R2 e 2012, dentre eles:

  • Application Role;
  • Backup Partial and Differential;
  • DBCC OpenTran;
  • FileGroup;
  • FileStream;
  • Query com Múltiplas CTEs; e
  • XML com Version e Enconding.

Segue abaixo a relação de Short Scripts:

1 – Exemplo – Backup – Partial And Differential

Create a full partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS TO DISK = ‘C:\TestBackup_Partial.BAK’ GO

Create a differential partial backup

BACKUP DATABASE TestBackup READ_WRITE_FILEGROUPS TO DISK = ‘C:\TestBackup_Partial.DIF’ WITH DIFFERENTIAL GO

2 – Exemplo – XML com Version e Enconding

DECLARE @xmlsample varchar(max),

@ArquivoXML Varchar(Max)

SET @xmlsample='<?xml version=“1.0” encoding=“UTF-8”?>’

Set @ArquivoXML= (Select CODIGO, Descricao from Produtos for XML Auto)

Select @xmlsample + Char(10) + @ArquivoXML

3 – Exemplo – Querys com Múltiplas CTEs

declare @DataInicialdatetime,@DataFinaldatetime;

set @DataInicial=convert(date,‘1/1/2012’, 103);

set @DataFinal=convert(date,’31/12/2013′, 103);

set @DataFinal=DateAdd(day,+1,@DataFinal);

;with

— calcula a quantidade média para cada produto

X1

as

(SELECT CodProd,(sum(Qtd)/count(distinctconvert(char(6),Data, 112)))asQtdMedia

from CAIXAGERAL

where Data >= @DataInicial and Data < @DataFinal

groupbyCodProd

),

— somatório dos valores de QtdMedia

X2

as (SELECT sum(QtdMedia) as TotalQtdMedia from X1),

— calcula o percentual de cada produto no todo

X3

as (SELECT CodProd,QtdMedia,(QtdMedia/TotalQtdMedia* 100)as[% prod],

Seq=row_number() over (orderbyQtdMediadesc,CodProd)

from X1 cross join X2

),

— calcula acumulado de percentual

X4

as (SELECT CodProd, QtdMedia,[% prod],Seq,

[% acum]=(SELECTsum([% prod])from X3 as X3i where X3i.Seq <=X3.Seq)

from X3

)

— planilha para montar a curva ABC

SELECT CodProdDescr_Prod=(SELECTtop (1)Descr_Prod from CAIXAGERAL as CX where CX.CodProd=X4.CodProd),

QtdMedia,[% prod],[% acum],

Classe=casewhen[% acum]>= 75 then‘A’

when[% acum]>= 50 then‘B’ else ‘C’ end

from X4

order by Seq;

4 – Exemplo – Adicionando FileGroup para trabalhar com FileStream

Create Database ABC

— Adicionando o FileGroup para trabalhar com FileStream —

ALTER DATABASE ABC

ADD FILEGROUP [ABCFSGROUP] CONTAINS FILESTREAM

— Adicionando arquivo vinculado com o FileGroup do FileStream —

ALTER database ABC

ADD FILE (NAME=‘ABC_Data_FSGroup’FILENAME=‘D:\Data\ABC_Data_FSGroup.ndf’)

TO FILEGROUP [ABCFSGROUP]

— Criando uma Tabela vinculada com o FileGroup pertencente ao FileStream —

CREATE TABLE[dbo].[LOGFILE]

([ID][int]IDENTITY(1,1)NOTNULL,

[DT_LOG][date]NULL,

[LOG_FILE][varbinary](max)FILESTREAM  NULL,

[ROWGUIDE][uniqueidentifier]ROWGUIDCOL  NOTNULL,

PRIMARY KEYCLUSTERED ([ID]ASC)

WITH (PAD_INDEX =OFFSTATISTICS_NORECOMPUTE  =OFFIGNORE_DUP_KEY=OFFALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)

ON [PRIMARY] FILESTREAM_ON [ABCFSGROUP],

UNIQUE NONCLUSTERED ([ROWGUIDE]ASC)

WITH (PAD_INDEX  =OFF, STATISTICS_NORECOMPUTE  =OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS  =ON, ALLOW_PAGE_LOCKS  =ON)ON[PRIMARY])

ON [PRIMARY] FILESTREAM_ON [ABCFSGROUP]

GO

5 – Exemplo – Habilitando – FileStream

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

6 – Exemplo – Criando e Atribuindo – Applicantion Role

Create Database Teste Go

Use Teste

CREATE APPLICATION ROLE AppRoleTeste    WITH PASSWORD = ‘senha@123’; GO

— Atribui permissão de select da role em um schema, por exemplo:

GRANT SELECT ON SCHEMA::dbo TO AppRoleTeste;

GO

— define a permissão da role no banco

EXEC sp_setapprole AppRoleteste, ‘senha@123’;

GO

7 – Exemplo – Identificando – Transações Abertas – Comando – DBCC OpenTran

— Exemplo 1 —

CREATE TABLE T1(Col1 int, Col2 char(3));

GO

BEGIN TRAN INSERT INTO T1 VALUES (101, ‘abc’);

GO

DBCC OPENTRAN;

ROLLBACK TRAN;

GO

DROP TABLE T1;

GO

 

— Exemplo 2 —

CREATE TABLE #OpenTranStatus (    ActiveTransaction varchar(25),    Details sql_variant    )

INSERT INTO #OpenTranStatus    EXEC (‘DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS’);

SELECT * FROM #OpenTranStatus;

GO

———————————————————————————————————————————————————————————-

———————————————————————————————————————————————————————————-

Fique a vontade para copiar e compartilhar estes códigos.

Aproveito para agradecer mais um ano de muito reconhecimento ao meu blog, para todos que contribuíram com suas visitas, comentários, entre outros…

Um Feliz Natal e Próspero Ano Novo.

Que venha 2014.