Short Script – Outubro – 2014

Bom dia, Ufa, Sexta – Feira…. Graças a Deus.

Já estamos no final do mês de Outubro e para encerrar mais este mês, vou compartilhar com vocês alguns dos Short Scripts que utilizei nos últimos dias em minha atividades diárias, como também, na solução de dúvidas nos Fóruns MSDN dedicados ao SQL Server.

Com você deve saber, esta Sessão Short Script tem a finalidade de compartilhar pequenos códigos de exemplos, ou códigos que não ultrapassem 10 linhas em relação a sua estrutura de código fonte, apresentado como você Usuário, Profissional de TI, Desenvolvedor, DBA, Estudante ou simplesmente um amante da área de tecnologia, pode fazer uso deste códigos na solução ou simulação das suas necessidades.

Todos os códigos foram testados e estão funcionando, vale ressaltar que os mesmos estão compatíveis com o SQL Server a partir da versão 2005.

Na relação de hoje destaco os seguintes recursos:

  • Buffer Pool;
  • Conversão de dados;
  • Comando Merge;
  • Informações sobre a Instância;
  • Plan Cache; e
  • Mapeamento de SIDs entre SQL Server e Windows.

A seguir você poderá encontrar a relação de Short Scripts, espero que você goste, e se surpreenda com este material:

— Short Script – Convertendo BigInt para Time —

Declare @Tabela Table

(Codigo Int Identity(1,1) Primary Key,

ValorBigInt BigInt)

Insert Into @Tabela

Values (216000000000), (252000000000), (288000000000), (324000000000), (360000000000),

(396000000000), (432000000000), (468000000000), (504000000000), (540000000000),

(576000000000), (612000000000), (648000000000), (684000000000), (720000000000)

Select CONVERT(TIME, DATEADD(SECOND, ValorBigInt / 10000000, ‘19700101 00:00’), 114) From @Tabela

Select DATEADD(SECOND, ValorBigInt / 10000000, ‘19700101 00:00’) From @Tabela

Select DATEADD(SECOND, ValorBigInt / 10000000, ’00:00′) From @Tabela

— Short Script – Obtendo Informações sobre Buffer Pool – Sumarizado —

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SA.data_pages As ‘Páginas de Dados’,

SA.used_pages As ‘Páginas de Dados Utilizadas’,

SA.total_pages As ‘Total de Páginas de Dados’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Go

— Short Script – Obtendo Informações sobre Buffer Pool – Detalhada —

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SA.data_pages As ‘Páginas de Dados’,

SA.used_pages As ‘Páginas de Dados Utilizadas’,

SA.total_pages As ‘Total de Páginas de Dados’,

SB.Row_Count

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Inner Join sys.dm_os_buffer_descriptors SB

On SB.allocation_unit_id = SA.allocation_unit_id

Go

— Short Script – Obtendo Informações sobre Buffer Pool – Mais Detalhada —

Select ST.Name As ‘Tabela’,

SP.rows As ‘Linhas’,

SB.free_space_in_bytes As ‘Espaço Livre em Bytes’,

SB.page_id As ‘Identificador da Página’,

SB.page_type As ‘Tipo da Página’,

SB.Row_Count As ‘Total de Linhas por Página’

from sys.tables ST Inner Join sys.partitions SP

On ST.object_id = SP.object_id

Inner Join sys.allocation_units SA

On SA.container_id = SP.partition_id

Inner Join sys.dm_os_buffer_descriptors SB

On SB.allocation_unit_id = SA.allocation_unit_id

Go

— Short Script – Obtendo informações sobre o Plan Cache —

SELECT cp.refcounts,

cp.usecounts,

cp.objtype,

st.dbid,

st.objectid,

st.text,

qp.query_plan

FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp;

Go

— Short Script – Obtendo informaçõe sobre a Instância do SQL Server —

DECLARE @Local varchar(8000)

EXEC master.dbo.xp_instance_regread

N’HKEY_LOCAL_MACHINE’,

N’Software\Microsoft\MSSQLServer\Setup’,

N’SQLPath’,

@Local output

SELECT @Local

GO

— Short Script – Utilizando o Comando Merge de Forma Incremental —

CREATE TABLE TB_ALVO

(

CD_PESSOA int IDENTITY(1, 1),

NM_PESSOA varchar(30),

CONSTRAINT PK_ALVO PRIMARY KEY(CD_PESSOA)

);

CREATE TABLE TB_ORIGEM

(

CD_PESSOA int,

NM_PESSOA varchar(30),

CONSTRAINT PK_ORIGEM PRIMARY KEY(CD_PESSOA));

GO

INSERT TB_ALVO(NM_PESSOA) VALUES(‘Jose’);

INSERT TB_ALVO(NM_PESSOA) VALUES(‘João’);

INSERT TB_ALVO(NM_PESSOA) VALUES(‘Durval’);

GO

INSERT TB_ORIGEM(CD_PESSOA, NM_PESSOA) Values(103, ‘Miguel’);

INSERT TB_ORIGEM(CD_PESSOA, NM_PESSOA) Values(104, ‘Sandro’);

GO

SELECT * FROM TB_ALVO

SELECT * FROM TB_ORIGEM

MERGE TB_ALVO AS A

USING TB_ORIGEM AS O

ON (A.CD_PESSOA = O.CD_PESSOA)

WHEN NOT MATCHED BY TARGET AND O.NM_PESSOA LIKE ‘S%’

THEN INSERT(NM_PESSOA) VALUES(O.NM_PESSOA)

WHEN MATCHED

THEN UPDATE SET A.NM_PESSOA = O.NM_PESSOA

WHEN NOT MATCHED BY SOURCE AND A.NM_PESSOA LIKE ‘S%’

THEN DELETE;

GO

SELECT * FROM TB_ALVO

SELECT * FROM TB_ORIGEM

GO

DROP TABLE TB_ALVO

DROP TABLE TB_ORIGEM

GO

— Short Script – Mapeamento entre SQL Server SIDs e Windows SIDs —

— Criando Tabela para armazenar Numeração —

CREATE TABLE dbo.TinyNumbers(Number TINYINT PRIMARY KEY);

Go

— Inserindo numeração sequência de valores para mapear ID —

INSERT dbo.TinyNumbers(Number)

SELECT TOP (256) ROW_NUMBER() OVER (ORDER BY number)-1

FROM master.dbo.spt_values;

Go

— Criando a Função para Obter o Windows SIDs —

CREATE FUNCTION dbo.GetWindowsSID (@sid VARBINARY(85))

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

(SELECT ADsid = STUFF((SELECT ‘-‘ + part FROM

(SELECT Number = 1, part = ‘S-‘

+ CONVERT(VARCHAR(30),CONVERT(TINYINT,CONVERT(VARBINARY(30),LEFT(@sid,1))))

+ ‘-‘

+ CONVERT(VARCHAR(30),CONVERT(INT,CONVERT(VARBINARY(30),SUBSTRING(@sid,3,6))))

UNION ALL

SELECT TOP ((LEN(@sid)-5)/4) Number,

part = CONVERT(VARCHAR(30),CONVERT(BIGINT,CONVERT(VARBINARY(30),

REVERSE(CONVERT(VARBINARY(30),SUBSTRING(@sid,9+Number*4,4))))))

FROM dbo.TinyNumbers

ORDER BY Number

) AS x ORDER BY Number

FOR XML PATH(), TYPE).value(N’.[1]’,‘nvarchar(max)’),1,1,)

);

Go

— Criando View para relacionar SQL Servers SIDs com Windows SIDs —

CREATE VIEW dbo.server_principal_sids

AS

SELECT sp.name, sp.[sid], ad.ADsid, sp.type_desc

FROM sys.server_principals AS sp

CROSS APPLY dbo.GetWindowsSID(sp.[sid]) AS ad

WHERE [type] IN (‘U’,‘G’)

AND LEN([sid]) % 4 = 0;

Go

— Retornando os SIDs mapeados —

SELECT name,

[sid],

ADSid,

type_desc

FROM dbo.server_principal_sids;

Go


Fique a vontade para compartilhar este conteúdo, fazer suas críticas e sugestões.

Agradeço a sua visita nos encontramos em breve.