Todos os post de Emanuel Peixoto

* Capitão do Exército Brasileiro (QCO/Informática/2011) * Formado em Sistemas de Informação. * Criador do blog "Rumo à EsFCEx" * Chefe da Seção de Informática do Hospital Militar de Área de Recife * Profissional com mais de 15 anos de experiência na área de TI,atuando como Engenheiro de Sistemas e DBA * Microsoft Certified – MCDBA | MCSE | MCSA | MCITP | MCTS * IBM Certified Developer – Cognos 8 BI Data Warehouses * ITIL® V2 e V3 * Green IT Citizen * ISO/IEC 27002 http://www.mycertprofile.com/Profile/1915602619 http://www.mycertprofile.com/Profile/1915602619
DIVERSOS

DOCUMENTAÇÃO DE PROCEDURES E TRIGGERS

Deixe um comentário
Deparei-me com o seguinte desafio no Fórum do MSDN: backup da base de dados SQL Server em um Web Hosting.
Como a única opção fornecida pelo fornecedor do serviço era uma exportação de tabelas, os objetos como procedures, triggers, functions views, dentre outros, não poderiam ser exportados, então sugeri a criação de uma tabela de documentação desses objetos.
 
Existe também o "Database Publishing Wizard", que pode gerar um script de todo o schema do banco e dar suporte a web hosting.
 
Segue script.
 
/* ##### CRIA TABELA DE CONTROLE (DOCUMENTAÇÃO) #####*/
if not exists(select 1 from sysobjects where name = ‘documentacao’ )
create table documentacao
(objetoid int primary key,
nome_objecto varchar(250),
Objeto_referencia varchar(250),
tipo char(1),
dt_criacao datetime,
script text)
go
/* ##### CRIA PROCEDURE COM INSTRUÇÕES PARA DOCUMENTAÇAO #####*/
create proc pr_documentacao
as
set nocount on
declare @cmd varchar(1000),@objeto varchar(250),@objeto_referencia varchar(250),@criacao datetime,@id int,@tipo char(1),@script varchar(8000)
declare obj cursor for
select o.id,o.name,object_name(o.parent_obj),o.crdate,o.xtype,c.text from sysobjects o, syscomments c where c.id=o.id
open obj
fetch next from obj into @id,@objeto,@objeto_referencia,@criacao,@tipo,@script
while @@fetch_status=0
begin
insert into documentacao(objetoid,nome_objecto,Objeto_referencia,dt_criacao,tipo,script)
select @id,@objeto,@objeto_referencia,@criacao,@tipo,@script where @id not in(select objetoid from documentacao )
fetch next from obj into @id,@objeto,@objeto_referencia,@criacao,@tipo,@script
end
close obj
deallocate obj
go
/* ##### Executa procedure para documentar objetos #####*/
exec pr_documentacao
 
Até mais,
Emanuel Peixoto.
DIVERSOS

TRIGGER DE AUTO-INCREMENTO

Deixe um comentário
 

create table tel_pac
(cod_pac int not null,
tel int not null)
go
alter table tel_pac add constraint pk_codpac primary key(cod_pac)
go
create trigger auto_num
on tel_pac
instead of insert
as
declare @cod int,@tel int
select @cod = case when max(cod_pac) is null then 1 else max(cod_pac)+1 end from tel_pac(xlock)
select @tel=i.tel from inserted i
insert into tel_pac(cod_pac,tel) values (@cod,@tel)

DIVERSOS

VISUALISAR SALDO

1 Comentário
Bom, estou postando aqui, um exemplo de como realizar uma consulta de saldo.
Existem diversas formas de se realizar essa consulta, mas segue aqui um pequeno exemplo.
 
 

create

table mov_data

(

codigo int identity,

codigo_conta

int not null,

tipo

char(1),

data

datetime,

valor

float)

go
insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘C’,’10/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘C’,’10/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘D’,’11/10/2008′,50)

insert

into mov_data(codigo_conta,tipo,data,valor) values(1,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘D’,’12/10/2008′,100)

insert

into mov_data(codigo_conta,tipo,data,valor) values(2,‘C’,’12/10/2008′,300)

go

/* ##### Visualiza saldo #####*/

select

a.codigo,a.codigo_conta,a.tipo,a.data,a.valor,

(select sum(b.valor)

from (select codigo,codigo_conta,case when tipo = ‘C’ then valor else –valor end valor from mov_data) b

where a.codigo>=b.codigo and a.codigo_conta=b.codigo_conta) as Saldo

from

mov_data a

order

by a.codigo

 
 
Resultados do select:
Cod   CC   Tipo Data     Valor   Saldo
1     1    C    20081010 100     100
2     1    C    20081010 100     200
3     1    D    20081110 50      150
4     1    D    20081210 100     50
5     2    D    20081210 100     -100
6     2    D    20081210 100     -200
7     2    D    20081210 100     -300
8     2    C    20081210 300      0
DIVERSOS

REMOVER ACENTUAÇÃO USANDO REPLACE

Deixe um comentário
 
 
 
— Cria Tabela de Exemplo

Create

Table #Textos (Indice int identity(1,1), Texto VarChar(1000))

–insere caracteres com acentos

Insert

Into #Textos (Texto) Values (‘teste teste é, teste teste á’)

Insert

Into #Textos (Texto) Values (‘teste teste é, ãõâêî teste á’)

Insert

Into #Textos (Texto) Values (‘teste ãõâêî é, teste teste á’)

Insert

Into #Textos (Texto) Values (‘teste teste á, ãõâêî’)

Insert

Into #Textos (Texto) Values (‘teste teste’)

— Inicio da Rotina

create

table #TabelaAcentos (Indice Int Identity(1,1), Acento char(01), SemAcento Char(01))

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘á’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘é’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘í’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ó’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ú’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘à’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘è’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ì’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ò’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ù’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ã’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘õ’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘â’,‘a’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ê’,‘e’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘î’,‘i’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ô’,‘o’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘û’,‘u’)

Insert

Into #TabelaAcentos (Acento, SemAcento ) Values (‘ç’,‘c’)

declare

@id int,@id_acento int,@old_char char(1), @new_char char(1),@texto varchar(1000)

set

@id=1

while

@id <= (select COUNT(1) from #Textos)

begin
select

@texto=Texto from #Textos where indice=@id

set @id_acento=1

while @id_acento <= (select count(1) from #TabelaAcentos)

begin

select @old_char=Acento, @new_char=SemAcento from #TabelaAcentos where Indice=@id_acento

set @texto=REPLACE(@Texto,@old_char,@new_char)

set @id_acento=@id_acento+1

end

select @texto

set

@id=@id+1

end

DIVERSOS

ÍNDICES E ESTATÍSTICAS

5 Comentários


Hoje, me deparei com uma dúvida de um dos participantes do fórum "FÓRUM DA RM" e essa dúvida era sobre como funcionavam os índices e estatísticas.

 

Vou tentar, explicar como funcionam as estatísticas e os índices. Primeiramente, vou me abster a contextualizar sobre os diferentes tipos de índices (clustered, nonclustered e unique), prometo que meu próximo assunto abordado dará ênfase aos tipos de estatísticas e como criá-los.

Os índices de uma tabela são como os índices de um livro, eles indicam onde encontrar o que você está procurando.

O SQL permite criar vários índices em uma mesma tabela, de forma que você pode organizar os dados na ordem que quiser, exemplo:
se você cria um índice que organiza os dados por data, no momento que realiza consultas por data, o resultado será o melhor possível, pois o SQL irá utilizar o índice que você criou por data.

Se criar outro índice que organiza a tabela por código do movimento, quando realizar consultas que pesquisem por ordem de código de movimento, a consulta irá ser rápida, pois os índices irão apontar para as informações de forma correta.

Mas, como o SQL sabe qual índice utilizar quando você está realizando uma consulta em uma tabela que possui mais de um índice?

Resposta: ESTATÍSTICAS!!!

As estatísticas são contadores que informam para o SQL quais são os melhores índices ou os índices mais rápidos. O SQL sabe quais são os melhores índices baseado num contador sobre os campos que compõem os índices, os índices que possuem campos com maior seletividade são escolhidos e a consulta executa feito uma BALA!!!
Para isso, você deve criar estatísticas para os índices, não adianta ter índices sem uma boa estatísticas.

Realizando uma verificação do plano de execução de qualquer consulta, você pode verificar a inexistência de índice e criá-los. Outra forma é o Index Tuning wizard(SQL2000) ou o DTA(SQL2005), que sugere ou cria índices e estatísticas automaticamente.

O FullScan comentado pelo colega Mafra, só é realizado quando não existem índices para os dados que estão sendo pesquisados, quando as estatísticas não existem, são ineficientes ou estão desatualizadas. Pra ver como a falta de estatísticas é crucial, imagine a seguinte situação:

Você chega em uma biblioteca e vai procurar o assunto "Atualização de estatísticas" pesquisando nos índices de todos os livros de informática relacionados com banco de dados, até encontrar um livro que tenha o assunto no índice pode demorar bastante. Apesar de todos os livros possuírem índices isso não quer dizer que você irá encontrar rapidamente o que quer.

Agora, se a biblioteca possui uma estatística, onde você pode realizar a seguinte consulta: quais os livros que possuem o assunto "Atualização de estatísticas" no índice? Se existir essa estatística, você irá direto para os livros certos, sem perder muito tempo.

Se, por acaso, não existirem as estatísticas, você até pode encontrar os livros que contém o assunto, porém, se não existir nem estatística e nem índices, você estará perdido! Terá que verificar todas as folhas de todos os livros para encontrar o assunto "Atualização de estatísticas”, rsrsrsrs.

Então, tão importante quanto criar índices e estatísticas é a manutenção delas. Realize, diariamente, reindexação dos índices de todas as tabelas e atualize as estatísticas também para todos os objetos.

 

Espero ter lhe ajudado,

 

Emanuel Peixoto.