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.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s