Boas Práticas de Programação - Transact-SQL - Sefaz-BA

SEFAZ-BA
SGF
DTI
GETEC
Secretaria da Fazenda do Estado da Bahia
Superintendência da Gestão Fazendária
Diretoria de Tecnologia da Informação
Gerência de Tecnologia
BOAS PRÁTICAS DE
PROGRAMAÇÃO
TRANSACT-SQL
Versão 01.06.00
Salvador (Ba), maio de 2017
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
CONTROLE DE VERSÃO
Versão
Data
01.01.00 29/12/2006
Responsável
Equipe AD
(Padrões)
Histórico
1. Substituição de “Departamento” por
“Diretoria” no cabeçalho;
2. Padronização da capa;
3. Transferência do Manual de Padrões
Banco para o Manual de Boas Práticas de
Programação SQL do conteúdo que provê
orientações sobre quando e como utilizar
as views de linked server;
4. Criação do tópico “TABELAS DE
HISTÓRICO” e inclusão neste tópico da
recomendação para armazenar as datas de
início e fim de vigência nas tabelas de
histórico, quando fizer sentido.
1. Incluir conteúdo relacionado aos recursos
do SQL Server 2005 (Tabelas
Particionadas, Novos Tipos de Dados,
Service Broker, Campos XML).
01.02.00 15/01/2009
Equipe AD
(Padrões)
01.03.00 18/04/2011
Equipe AD
(Padrões)
1. Inclusão do item “Controle de Execução de
Scripts” no tópico “OBSERVAÇÕES”.
01.04.00 01/10/2012
Equipe AD
(Padrões)
1. Inclusão do item “SET NOCOUNT ON” no
tópico “OBSERVAÇÕES”.
Equipe AD
(Padrões)
1. Alteração do item "Cursores" no tópico
"OBSERVAÇÕES";
2. Inclusão do item “CLÁUSULA CATCH”
no tópico “OBSERVAÇÕES”.
Equipe AD
(Padrões)
1. Inclusão do tópico "TRATAMENTO DE
ERROS"
2. Alteração no tópico "OBSERVAÇÕES" ,
no item Cursores.
3. Alteração no tópico "REFERÊNCIAS"
01.05.00 20/05/2015
01.06.00 27/08/2015
Página 2
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
ÍNDICE
1.
OBJETIVO ..................................................................................................................................... 4
2.
TABELAS TEMPORÁRIAS ........................................................................................................ 4
3.
TRANSAÇÕES .............................................................................................................................. 7
4.
ÍNDICES ......................................................................................................................................... 9
5.
PLANOS DE EXECUÇÃO ......................................................................................................... 11
6.
VISÕES......................................................................................................................................... 14
7.
VERTICALIZAÇÃO DE TABELAS ........................................................................................ 16
8.
FUNÇÕES DEFINIDAS PELO USUÁRIO .............................................................................. 18
9.
REFERÊNCIA CRUZADA ........................................................................................................ 20
10.
TABELAS DE HISTÓRICO .................................................................................................. 22
11.
TABELAS PARTICIONADAS .............................................................................................. 23
12.
NOVOS TIPOS DE DADOS DO SQLSERVER 2005 ......................................................... 24
13.
SERVICE BROKER ............................................................................................................... 24
14.
CAMPOS XML ...................................................................................................................... 25
15.
TRATAMENTO DE ERROS ................................................................................................. 26
15.1.
TRATAMENTO COM @@ERROR .................................................................................... 26
15.2.
TRATAMENTO COM TRY...CATCH ................................................................................ 26
16.
OBSERVAÇÕES ..................................................................................................................... 29
17.
CONCLUSÃO ......................................................................................................................... 31
18.
REFERÊNCIAS ...................................................................................................................... 31
Página 3
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
1.
29/05/2017
OBJETIVO
Este documento reúne um conjunto de técnicas de programação em Transact-SQL que devem
ser adotadas para garantir um bom desempenho das aplicações no ambiente SEFAZ.
2.
TABELAS TEMPORÁRIAS
Recomendação: Não utilizar tabela temporária em estado de transação.
Quando uma tabela temporária é construída, são gerados bloqueios em tabelas do catálogo
(sysobjects, sysindexes e syscolumns) no TEMPDB. O TEMPDB é um banco compartilhado
por todos os usuários de um servidor SQL Server e é um recurso extremamente utilizado na
geração de tabelas temporárias, cursores, etc. Se uma tabela temporária for criada em estado
de transação, os bloqueios serão mantidos até o final da transação, bloqueando outros usuários
e comprometendo a performance do servidor.
Tabelas temporárias podem ser substituídas por joins ou por tabelas derivadas em grande
parte das situações, evitando os bloqueios no TEMPDB e melhorando a performance das
aplicações. Seguem alguns exemplos de substituição de tabelas temporárias por joins e tabelas
derivadas:
Com tabelas temporárias:
(solução não recomendada)
CREATE TABLE #tipo_operacao (
cod_operacao int
des_operacao char(20)
)
not null,
not null
Resumo das operações:
2 criação/destruição de objetos
1 atualização
1 consulta
INSERT INTO #tipo_operacao
SELECT * FROM tipo_operacao
WHERE cod_operacao > 10
SELECT *
FROM operacao O INNER JOIN #tipo_operacao TO
ON (O.cod_operacao = TO.cod_operacao)
DROP TABLE #tipo_operacao
Com JOIN:
(solução recomendada)
SELECT cod_operacao, des_operacao
FROM operacao O INNER JOIN tipo_operacao TO
ON (O.cod_operacao = TO.cod_operacao)
WHERE TO.cod_operacao > 10
Página 4
Resumo das operações:
1 consulta
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
A seguir, analisaremos um exemplo mais trabalhado, semelhante à geração de relatórios.
Neste exemplo, a solução apresentada é o uso de uma tabela derivada que nada mais é do que
a substituição de uma tabela na cláusula FROM por uma consulta. O exemplo abaixo, assim
como outros exemplos e tópicos abordados podem ser encontrados no site
http://www.sql-server-performance.com
Com tabelas temporárias:
(solução não recomendada)
-- CREATE OUR TEMPORARY TABLE
CREATE TABLE #Temp_Example (
[CategoryID] INT NOT NULL,
[Category_Count] INT NOT NULL
)
Resumo das operações:
2 criação/destruição de objetos
1 atualização
1 consulta
-- INSERT THE VALUES WE LATER NEED INTO THE TEMP TABLE
INSERT INTO #Temp_Example (CategoryID, Category_Count)
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CategoryName
-- JOIN ON THE TEMP TABLE TO GET OUR VALUES
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice,
#Temp_Example.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID
ORDER BY C.CategoryName
-- DROP TEMPORARY TABLE
DROP TABLE #Temp_Example
Com tabela derivada:
(solução recomendada)
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice,
CT.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN (
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CategoryName
)CT ON C.CategoryID = CT.CategoryID
ORDER BY C.CategoryName
Resumo das operações:
1 consulta
Página 5
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
De forma geral, o número de operações para implementação de uma solução com tabelas
temporárias é maior e as operações são mais custosas (criação e exclusão de objetos,
atualização de dados, etc.), logo, evite o uso de tabelas temporárias e procure trabalhar com
joins e tabelas derivadas sempre que possível. Uma outra alternativa é utilizar o tipo table
para armazenar os dados temporários de forma menos custosa.
Para maiores informações sobre tabelas derivadas, consulte “Derived Tables” no Books
Online. O tipo de dados table pode ser encontrado em “Table Data Type” também no Books
Online. Informações sobre performance de tabelas temporárias X tabelas derivadas podem ser
encontradas em http://www.sql-server-performance.com
Página 6
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
3. TRANSAÇÕES
Recomendação: Procurar definir as transações com o menor escopo possível.
Transações são utilizadas para garantir que um conjunto de comandos SQL seja executado
como uma unidade atômica de código. Isto significa que todos os comandos devem ser
executados juntos ou nenhum deles poderá ser executado. Além disso, o banco deve garantir
que os comandos de uma transação não interfiram no resultado de outras transações que
estejam executando concorrentemente.
Para garantir estas premissas, o custo para banco de dados pode ser altíssimo, por isso, as
transações devem ser definidas com bastante critério. Só precisam estar em estado de
transação comandos que devem ser executados de maneira conjunta e não podem ser
executados separados. Comandos que podem ser executados de forma independente não
devem ser executados em estado de transação.
Exemplos
comando2
Transações grandes:
(solução não recomendada)
comando1
comando4
comando3
bloqueio
Tabela
comando2
comando1
bloqueio
comando4
comando3
comando6
comando2
comando5
comando1
comando4
comando3
Transações pequenas:
(solução recomendada)
comando2
comando1
comando2
comando1
Tabela
comando2
comando2
comando1
comando2
comando1
comando1
Página 7
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
O resultado da definição de transações com escopo muito grande é que uma quantidade menor
de processos poderá executar concorrentemente e a performance das aplicações diminuirá
consideravelmente. Por outro lado, se as transações forem definidas com escopo pequeno,
mais transações vão poder ser executadas concorrentemente e a performance das aplicações
será melhor.
Observações sobre finalização de transações:
a. Dentro de um trigger, todo ROLLBACK deve ser acompanhado por um RETURN, de
forma a garantir que o trigger será finalizado naquele ponto. Se um RETURN não for
executado, o trigger executará o ROLLBACK e continuará seguindo o fluxo normal
de execução, o que pode levar a inconsistências no banco de dados.
b. Dentro de stored procedures, todo ROLLBACK deve ser acompanhado por um
RAISERROR, garantindo que a aplicação será informada de que houve uma falha na
operação, dando o tratamento adequado. Se o RAISERROR não estiver presente, a
aplicação intepretará o resultado como sucesso e não fará nenhum tipo de tratamento.
Exemplos
Errado:
Correto:
CREATE PROC up_errada AS
...
IF @@error = 0
commit transaction
ELSE
rollback transaction
...
CREATE PROC up_correta AS
...
IF @@error = 0
commit transaction
ELSE
BEGIN
rollback transaction
raiserror
50001
‘Erro
na
procedure!!!’
END
...
Para maiores informações sobre transações, consulte “Transactions” no Books Online.
Informações sobre bloqueios (locks) podem ser encontradas no site
http://www.sql-server-performance.com
Página 8
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
4. ÍNDICES
Recomendação: Utilizar índices clustered em tabelas pouco atualizadas e índices
nonclustered nas demais.
a) Definição de Índice
Índices são estruturas de dados utilizadas para otimizar consultas em bancos de dados. Os
índices evitam a necessidade de uma leitura extensiva (table scan) em uma tabela para
localizar um ou mais registros. A figura abaixo ilustra a estrutura de um índice (ix_usuario01)
criado para o campo cod_usuario que armazena o código do usuário:
1
5
10
...
cod_usuario
1
2
3
4
5
6
7
8
9
10
...
nome
Ana
Mario
Joao
Jose
Paula
Marcos
Joana
Simão
Pedro
Fernando
...
Com o índice acima, uma consulta como select nome from usuário where cod_usuario = 7
seguiria o caminho indicado pelas seta tracejada, ou seja, escolheria o apontador do índice que
mais se aproximasse do registro desejado e realizaria uma pequena busca na tabela de usuário
para encontrar o registro. Se o índice não existisse, o SQL Server seguiria o caminho indicado
pela seta pontilhada, ou seja, faria uma busca extensiva na tabela de usuário até encontrar o
registro desejado. As principais conseqüências da falta de um índice adequado seriam o
aumento no tempo da consulta e um aumento dos bloqueios na tabela de usuário.
b) Tipos de Índice
Existem 2 tipos de índices, Clustered e Nonclustered:

Nos índices Clustered existe uma relação direta entre a ordenação do índice e a
ordenação física dos dados da tabela. Essa organização maximiza a performance de
consultas que retornam dados ordenados pelo(s) campo(s) do índice (exemplo 1) e
consultas que trabalham com intervalos de valores (exemplo 2).
Exemplo 1 - Índice clustered no campo cod_usuario:
SELECT cod_usuario, nom_usuario FROM usuario ORDER BY cod_usuario
Exemplo 2 - Índice clustered no campo dtc_atualizacao
SELECT nom_usuario FROM usuario
WHERE dtc_atualizacao BETWEEN ‘2003/04/01’ AND ‘2003/07/01’
Página 9
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
Apesar dos benefícios citados, os índices clustered geram um custo adicional durante
a atualização porque os dados precisam ser reordenados fisicamente a cada
atualização. Em virtude disso, índices clustered devem ser criados somente em
tabelas que não são atualizadas ou que tenham uma freqüência de atualização
muito baixo.
Obs.: Pelo fato de exigir uma ordenação física dos dados, somente um índice
clustered pode ser criado para cada tabela.

Nos índices Nonclustered não existe uma relação direta entre a ordenação do índice e
a ordenação física dos dados da tabela. Existe um custo em operações de atualização
de tabelas que utilizem índices nonclustered, mas o overhead é muito menor do que
ocorre com os índices clustered. A criação de índices nonclustered deve ser avaliada
pelo desenvolvedor seguindo as recomendações sobre planos de execução
documentadas neste manual.
Obs.: Pelo fato de não exigir uma ordenação física dos dados, vários índices
nonclustered podem ser criados para cada tabela.
c) Chaves Primárias x Índices Secundários
Quando uma constraint do tipo chave primária é criada para uma tabela, o SQL Server cria
automaticamente um índice na tabela. Este índice por padrão é CLUSTERED, portanto,
quando não houver necessidade de criação de um índice do tipo clustered, é importante
especificar explicitamente a cláusula NOCLUSTERED no comando ALTER TABLE. Os
exemplos a seguir ilustram o fato:
ALTER TABLE usuario ADD CONSTRAINT ix_usuario01
PRIMARY KEY (cod_usuario)
Será criado automaticamente um índice chamado ix_usuario01 do tipo clustered.
ALTER TABLE usuario ADD CONSTRAINT ix_usuario01
PRIMARY KEY NONCLUSTERED (cod_usuario)
Será criado automaticamente um índice chamado ix_usuario01 do tipo nonclustered.
Além da chave primária podem ser criados diversos índices por tabela, chamados de índices
secundários. Vale ressaltar que quanto maior for a quantidade de índices em uma tabela,
maior será o overhead de atualização. Índices secundários são criados por padrão como
nonclustered, devendo ser usada a cláusula CLUSTERED somente quando for avaliada a
necessidade de criação de um índice do tipo clustered.
Para maiores informações sobre índices, consulte “Indexes” no Books Online.
Página 10
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
5. PLANOS DE EXECUÇÃO
Recomendação: Analisar periodicamente os planos de execução dos comandos SQL,
avaliando a necessidade de criação de índices para otimização.
A partir da versão 7.0, o SQL Server disponibilizou uma ferramenta para avaliação do plano
de execução de comandos SQL, em modo gráfico. Esta ferramenta está embutida no Query
Analyzer (antigo ISQL/W) e será detalhada a seguir. Vamos tomar como exemplo uma tabela
“usuario” que contém a estrutura abaixo:
cod_usuario
nom_usuario
int
varchar(30)
not null
not null
Esta tabela já contém um índice primário no campo cod_usuario. No primeiro exemplo será
executada a seguinte consulta no banco:
SELECT * FROM usuario
Para gerar o plano de execução estimado dessa consulta, basta selecionar o(s) comando(s) em
questão e usar CTRL+L. O plano de execução da consulta acima é o seguinte:
Neste caso, o plano
estimado indica que
houve um table
scan, ou seja, uma
varredura em toda a
tabela para retornar
os
registros
desejados. O table
scan é uma operação
que deve ser evitada
porque consome uma quantidade grande de recursos para ser efetivada, diminuindo a
performance das consultas. Na consulta acima houve table scan por dois motivos:


Não foi determinado um filtro (join, cláusula WHERE ou HAVING); 
A lista de campos não foi colocada na consulta e foi usado * para retornar todos os
campos. 
Vamos modificar um pouco a consulta, supondo que só será necessário o campo cod_usuario:
SELECT cod_usuario FROM usuario
O plano de execução da consulta passa a ser o seguinte:
Página 11
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
Para uma visão detalhada do que
ocorreu em cada etapa, basta
apontar com o mouse para o
ícone da etapa desejada. Um
resumo da etapa será exibido
com informações sobre a
operação realizada, o custo de
I/O, CPU e dados adicionais.
(figura ao lado)
Neste caso foi realizado um
Index Scan devido aos seguintes
motivos:


Não foi determinado um filtro (isto justifica o scan); 
A lista de campos foi colocada na consulta (neste caso só o campo cod_usuario). 
Como existe um índice no campo cod_usuario, o índice é utilizado, mas ainda permanece o
scan (varredura). A situação é melhor do que a primeira consulta porque o table scan foi
transformado em index scan, ou seja, a varredura deixou de ser na tabela e passou a ser no
índice. De qualquer forma, esta ainda não é uma situação ideal, como veremos adiante.
Vamos adicionar um filtro à consulta e observar o resultado do plano de execução:
SELECT cod_usuario FROM usuario
WHERE cod_usuario >1000
Plano estimado:
Com o filtro o index scan passou a ser
index seek, ou seja, o otimizador trabalhará
somente com o conjunto de registros
especificado no filtro. Note que o index
seek consumiu menos recursos de I/O e
CPU do que o index scan. Essa diferença
pode ser ainda maior quando a tabela
consultada tiver um número elevado de registros. O index seek é a operação ideal na maioria
dos casos e deve ser um dos objetivos de toda otimização de consulta.
Obs.: Nem sempre o index seek pode ser alcançado numa otimização. Se o número de
registros que você está pesquisando for muito grande em relação ao total de registros da
tabela, o otimizador tende a fazer um index scan ou table scan, independentemente da
existência de filtros e índices. Outros fatores que contribuem para a execução de scans
são o uso de funções (datepart, convert, etc) na cláusula WHERE e o uso indevido de
Página 12
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
aspas em comparações (exemplo: WHERE num_inscicao_estad = ‘334’, sendo
num_inscricao_estad um campo do tipo INT.
Vamos analisar agora um exemplo com mais um campo na consulta:
SELECT cod_usuario, nom_usuario FROM usuario
WHERE cod_usuario >1000
Plano estimado:
No plano de execução da consulta acima, surgiu uma nova etapa chamada Bookmark Lookup,
indicando que, apesar da pesquisa no índice, campos adicionais correspondentes aos registros
procurados serão lidos diretamente da tabela. Esta operação gera um custo adicional para a
consulta.
Para eliminar esta etapa teremos que criar um índice que contenha todos os campos desejados
e todos os campos do filtro. Isto nem sempre é possível porque consultas complexas utilizam
muitos campos tanto na cláusula SELECT quanto na cláusula WHERE. Nestes casos o lookup
é inevitável.
Existem outros tipos de operações que podem aparecer no plano de execução estimado, mas
as operações citadas acima são as que mais interferem na peformance das consultas no banco
de dados. Para maiores informações sobre planos de execução, procure “Execution Plan” no
Books Online.
Página 13
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
6. VISÕES
Recomendações:
 Utilizar visões para consultas com algum grau de complexidade e quando existir
a possibilidade de reutilização;
 Verificar o código da visão antes de utilizá-la numa consulta.
Uma visão (ou view) é um mecanismo usado pelo servidor de banco de dados para encapsular
o código de uma consulta. Esse encapsulamento traz 2 grandes benefícios: simplicidade de
uso e possibilidade de reutilização de código. Apesar disso, a complexidade de uma consulta
pode ser camuflada através de uma visão, logo, a clareza fica comprometida. De uma forma
geral, visões devem ser utilizadas para consultas com um certo grau de complexidade e que
possam ser reutilizadas no mesmo sistema ou por uma equipe de desenvolvedores. Criar
visões indiscriminadamente adiciona complexidade ao ambiente desnecessariamente,
portanto, é importante ter bom senso para decidir se uma visão deve ou não ser criada. É
importante também verificar o código da visão antes de utilizá-la numa consulta, para ter uma
noção da complexidade da consulta como um todo.
Visões Indexadas
Recomendação: Utilizar visões indexadas em consultas complexas e quando a tabela for
atualizada com pouca freqüência.
Quando uma visão é criada, seu código é gravado no banco de dados e executado a cada
chamada à visão. Para visões mais complexas, com diversos joins e filtros, o tempo da
consulta pode ser consideravelmente alto, prejudicando a performance da aplicação. Para
minimizar esse problema, a partir do SQL Server 2000, foi adicionado o conceito de Visão
Indexada (Indexed View). Para criar uma visão indexada, é necessária a criação de um índice
clustered em uma visão. O grande benefício das visões indexadas é o fato do resultado da
consulta que gera a visão ficar armazenado em disco, permitindo a criação de índices. Apesar
disso, visões indexadas não devem ser criadas em tabelas com atualizações constantes ou
pesadas, pois a interação entre a tabela fonte e a visão ficará comprometida.
Para maiores informações sobre visões indexadas, consulte “Indexed Views” no Books
Online.
Visões de Linked Server
Recomenda-se utilizar visões de linked server quando todas as situações abaixo
ocorrerem:

Quando for necessário prover acesso a tabelas de outros servidores;
A visão de linked server servirá para garantir a independência das aplicações em
relação aos nomes dos servidores

Quando o volume de dados transferido for pequeno;
A adoção de visões de linked server para volumes grandes de dados acarreta um
tráfego indesejado entre os servidores e aumenta os riscos de perda de conexão.
Página 14
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação

29/05/2017
Quando a dependência do servidor remoto for pequena.
Para essa dependência ser pequena, a visão de linked server envolvida deve ser
chamada com uma freqüência baixa (seja através de um trigger, seja através de uma
stored procedure).
As funcionalidades críticas devem evitar o uso de visões de linked server, pois estas
representariam um fator a mais de risco para a aplicação.
Observações:

Quando houver um procedimento de alta complexidade no servidor remoto com
retorno reduzido para o servidor de origem, pode-se utilizar chamadas remotas a
procedures em lugar de views de linked server.

Quando houver um procedimento e este não for de alta complexidade, o analista deve
escolher entre uma chamada remota a procedure (caso a lógica do procedimento
dependa de objetos do servidor remoto), uma visão chamada de uma procedure local
(caso o procedimento possa estar inteiramente no servidor local) ou replicação (em
último caso).

A decisão sobre utilizar views de linked server, chamadas remotas a stored
procedures, replicação ou, ainda, múltiplas conexões deve ser sempre alinhada com a
GETEC.
Características:

Não podem ter qualquer filtro;

O número de campos retornados deve ser o menor possível;

Deve existir no máximo uma visão de linked server por tabela;

Cada visão de linked server só pode referenciar uma única tabela.

Um dos seguintes nomes deve ser utilizado como nome do servidor remoto:
“CORPORATIVO” (para o SSED03W, SSED09W e SSED20W) e “FINANCEIRO”
(para o SSED26W, SSED09W_02 e SSED20W_02).
Caso haja a necessidade de acesso via linked server (visão ou stored procedure), para
um servidor diferente dos citados, a GETEC deve ser consultada.
Página 15
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
7. VERTICALIZAÇÃO DE TABELAS
Recomendação: Utilizar uma estrutura vertical para armazenar consolidados.
Algumas tabelas, entre elas boa parte dos consolidados, estão modeladas com uma estrutura
semelhante a que segue:
ano
2000
2001
2002
2003
...
val01
86
23
49
76
...
val02
56
65
74
15
...
val03
54
78
95
12
...
val04
89
50
47
34
...
...
...
...
...
...
...
Nesta estrutura existe uma chave (o ano) e uma coluna para cada mês. Quando se deseja
somar todos os valores de um determinado ano, basta localizar o registro a partir da chave e
somar os campos val01, val02, val03, etc. Por exemplo, para somar os valores
correspondentes ao ano 2003 seria necessário executar a seguinte consulta:
SELECT val01 + val02 + ...
FROM consolidado
WHERE ano = 2003
Apesar de funcionar bem para consultas desse tipo, esta estrutura possui uma série de
limitações:

Quando o valor de um determinado mês ainda é desconhecido (por exemplo, os
valores de 2003 correspondentes aos meses de agosto, setembro, outubro...), há
desperdício de espaço em disco pois está sendo reservado espaço para armazenar um
valor que ainda não existe.

Para consultas que fogem ao padrão citado, a estrutura acima é deficiente do ponto de
vista de performance. Se for necessário, por exemplo, consolidar os resultados de
dezembro de 2002 a fevereiro de 2003 seria necessária uma consulta mais complexa
com CASE, IFs, cursores, ou o uso de variáveis. Além disso, como o período a ser
consultado pode variar, seria necessária a utilização de consultas dinâmicas,
degradando ainda mais a performance.

A criação de índices nesse caso fica limitada à chave (ano), pois, para otimizar filtros
nos campos val01, val02, etc, seria necessário criar índices para todas as combinações
possíveis dos campos e isso geraria um overhead de atualização altíssimo.
Página 16
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
Uma alternativa para atender às diversas demandas de consultas em consolidados desse tipo é
utilizar uma estrutura vertical semelhante a que segue:
dtr
200207
200208
200209
200210
200211
200212
200301
200302
200303
...
val
77
54
13
89
104
63
76
15
12
...
Nesta estrutura o período é representado por uma data reduzida,
composta por ano e mês, permitindo que consultas de períodos possam
ser realizadas de forma direta, como no exemplo abaixo:
SELECT val
FROM consolidado
WHERE dtr BETWEEN 200212 AND 200302
As principais vantagens deste modelo são:
 Melhor aproveitamento de espaço: os espaço em disco só será ocupado quando o
valor de um determinado mês for conhecido. Não existirão, por exemplo, registros
para agosto, setembro, outrubro, novembro e dezembro de 2003 porque estes valores
ainda não foram calculados;

Facilidade de otimização: com poucos índices é possível otimizar a maioria das
consultas no formato citado. Um índice para o campo dtr seria usado para consultas
diretas (dtr = xxxxyy) ou por períodos (dtr between xxxxyy and aaaabb). Outro
índice, em val, garantiria consultas rápidas por intervalos de valores (val > n, por
exemplo).
Observação sobre datas reduzidas:
O modelo para datas reduzidas (ano+mês = AAAAMM)
traz benefícios em consultas por períodos e minimiza a
necessidade de operações aritméticas durante a execução
das consultas. Apesar disso, é importante que seja
sempre avaliado como as datas serão representadas para
evitar redundâncias.
A estrutura vertical pode ser utilizada não só em consolidados, mas em diversas outras tabelas
que sigam o modelo acima. O modelo vertical deve ser utilizado como padrão, podendo
ser usada uma abordagem horizontal em casos específicos. Dúvidas a respeito da escolha
do modelo a ser utilizado devem ser discutidas com a GETEC.
Página 17
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
8. FUNÇÕES DEFINIDAS PELO USUÁRIO
Recomendação: Desenvolver funções com códigos simples, para funcionalidades
genéricas ou para substituir determinados tipos de visões ou stored procedures.
O SQL Server possui um conjunto de funções, chamadas BUILT-IN Functions, amplamente
utilizadas para programação em Transact-SQL. Alguns exemplos dessas funções são convert,
getdate, datepart, dateadd, datediff, left, right, max, count, etc. Essas funções não podem ser
modificadas.
A partir da versão 2000 do SQL Server, tornou-se possível a criação de Funções Definidas
pelo Usuário (User-Defined Functions) onde a lógica da função é determinada pelo
desenvolvedor. Existem 3 tipo de funções definidas pelo usuário:



Escalares
Tabulares “Inline”
Tabulares “Multistatement”
Funções escalares retornam um valor pontual, enquanto as funções tabulares retornam uma
tabela. Seguem alguns exemplos dessas funções:
Exemplo 1 Função Escalar que retorna somente a data a partir de um campo
SMALLDATETIME ou DATETIME.
Recomendação: Utilizar funções escalares para substituir rotinas simples e genéricas
como up_dia_util, conversão de moeda, etc.
CREATE FUNCTION uf_somente_data (@data_hora datetime)
RETURNS CHAR(10)
AS
BEGIN
DECLARE @data CHAR(10)
SELECT @data = LEFT(CONVERT(CHAR(10),@data_hora,101),10)
RETURN(@data)
END
Depois de criada, a função poderia ser utilizada da seguinte forma:
SELECT dbo.uf_somente_date(dtc_atualizacao)
FROM bd_contribuinte..contribuinte_inscrito
WHERE num_inscricao_estad = 334
Exemplo 2 Função Tabular “Inline” que retorna informações sobre os usuários de um
determinado grupo passado como parâmetro.
Recomendação: Utilizar funções tabulares inline para substituir visões que necessitem
de passagem de parâmetros. Valem as mesmas recomendações sobre
visões descritas neste documento.
Segue...
Página 18
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
CREATE FUNCTION uf_usarios_grupo (@cod_grupo int)
RETURNS table
AS
RETURN (
SELECT nom_usuario, dtc_criacao
FROM usuario
WHERE cod_grupo = @cod_grupo
)
Perceba a mudança na sintaxe da função tabular. A cláusula RETURNS deve
obrigatoriamente ser preenchida pelo tipo table e os delimitadores de bloco (BEGIN e END)
não são utilizados. A função acima é inline, ou seja, o retorno da função se resume ao
resultado de comando SELECT especificado pela cláusula RETURN( ).
Depois de criada, a função poderia ser utilizada da seguinte forma:
SELECT nom_usuario, dtc_criacao
FROM uf_usuarios_grupo(17652)
É possível ainda a criação de funções tabulares “multistatement”, ou seja, o código da função
será composto por um conjunto de comandos que preencherão uma tabela a ser retornada pela
função. O uso de funções desse tipo deve ser negociado com a GETEC.
Funções definidas pelo usuários trazem flexibilidade à programação em Transact-SQL, mas
devem ser utilizadas com cautela, pois, assim como as visões, as funções encapsulam um
código Trasact-SQL e conseqüentemente camuflam sua complexidade. Criar códigos
complexos para funções pode gerar problemas sérios de performance, portanto, é essencial
que as funções sejam desenvolvidas com códigos simples, de preferência quando a
funcionalidade puder ser reaproveitada por diversas aplicações.
Para maiores informações sobre funções definidas pelo usuário, consulte “User-Defined
Functions” no Books Online.
Página 19
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
9. REFERÊNCIA CRUZADA
Determinados tipos de aplicações precisam, em alguns casos, gerar relatórios de referência
cruzada, como no exemplo abaixo:
Resultado Desejado
Faturamento
ano
bimestre
valor
1990
1990
1990
1990
1991
1991
1991
1991
1
2
3
4
1
2
3
4
1,1
1,2
1,3
1,4
2,1
2,2
2,3
2,4
ano
1990
1991
semestre 1
2,3
4,3
semestre 2
2,7
4,7
Problema:
A tabela de faturamento citada, armazena o faturamento em milhões de uma determinada
empresa, a cada bimestre. Foi solicitado um relatório que cruzasse o faturamento por
semestre, a cada ano.
Solução 1
(solução não recomendada)
Para chegar ao resultado desejado é comum a utilização de cursores e atualizações em tabelas
temporárias, aumentando o custo da consulta.
CREATE TABLE #ref_cruzada (
ano
SMALLINT
semestre1
DECIMAL(5,2)
semestre2
DECIMAL(5,2)
)
NOT NULL,
NULL,
NULL
INSERT INTO #ref_cruzada (ano)
SELECT DISTINCT ano
FROM tab_fonte
UPDATE #ref_cruzada
SET semestre1 = SUM(F.valor)
FROM faturamento F INNER JOIN #ref_cruzada R
ON F.ano = R.ano
WHERE F.bimestre <= 2
Resumo das operações:
2 criação/destruição de objetos
3 atualização
1 consulta
UPDATE #ref_cruzada
SET semestre2 = SUM(F.valor)
FROM faturamento F INNER JOIN #ref_cruzada R
ON F.ano = R.ano
WHERE F.bimestre >= 3
Segue...
Página 20
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
SELECT ano, semestre1, semestre2
FROM #ref_cruzada
DROP TABLE #ref_cruzada
Comentário: a solução acima é extremamente custosa pois é necessário criar uma tabela
temporária, executar uma série de atualizações para preenchê-la, ler a tabela para retornar o
resultado e depois destruí-la. A quantidade de atualizações ainda poderá aumentar se o
número de colunas da tabela de referência cruzada aumentar.
Solução 2
(solução recomendada)
SELECT
ano,
SUM(CASE bimestre WHEN 1 THEN Valor
WHEN 2 THEN Valor
ELSE 0
END) AS semestre1,
SUM(CASE bimestre WHEN 3 THEN Valor
WHEN 4 THEN Valor
ELSE 0
END) AS semestre2
Resumo das operações:
FROM vendas
1 consulta
GROUP BY Ano
Comentário: o segredo da solução acima é utilizar o comando CASE em conjunto com a
função SUM para consolidar os resultados de cada semestre. A solução se resume a uma
consulta e é muito menos custosa que a primeira.
Para maiores informações sobre referência cruzada, consulte “Cross-tab Reports” no Books
Online.
Página 21
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
10.TABELAS DE HISTÓRICO
Armazenar na mesma tabela dados freqüentemente atualizados e dados históricos é uma
política bastante perigosa do ponto de vista de performance. O ideal é armazenar dados
históricos em uma tabela separada, gerenciando a transferência de dados entre as tabelas
envolvidas. Vale ressaltar que esta solução só é viável quando existe uma clara distinção entre
o que é histórico (somente leitura) e o que é atualizado.
Recomenda-se armazenar nas tabelas de histórico a data de inclusão de cada uma de suas
linhas, como forma de diferenciá-las. Com o objetivo de maximizar a performance das
consultas às tabelas de histórico, recomenda-se também armazenar as datas de início e fim de
vigência, desde que faça sentido para essas tabelas o conceito de vigência oficial. Em certas
situações, pode-se conhecer apenas a data de inclusão de uma linha em uma tabela de
histórico, que não corresponderá necessariamente à data oficial de fim de vigência dos dados
em questão.
Existindo as datas de início e fim de vigência oficial em uma tabela de histórico, bastará que
se use na cláusula WHERE das consultas uma expressão do tipo “@dtc_pesquisada between
dtc_inicio_vigencia and dtc_fim_vigencia”, o que dispensará o uso de subselects para obter
(deduzir, derivar) a data de início de vigência em uma linha diferente e, portanto, melhorará a
performance dessas consultas.
Página 22
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
11.TABELAS PARTICIONADAS
(Solução de particionamento oferecida pelo SQL Server 2005)
Os dados de tabelas e índices particionados são divididos em unidades que podem ser
difundidas por mais de um grupo de arquivos em um banco de dados. Os dados são
particionados horizontalmente, de forma que os grupos de linhas são mapeados em partições
individuais. A tabela (ou índice) é tratada como uma única entidade lógica quando são
executadas consultas ou atualizações nos dados. Todas as partições de um único índice ou de
uma única tabela devem residir no mesmo banco de dados.
Tabelas ou índices particionados suportam todas as operações suportadas por tabelas e índices
convencionais. Durante a análise para decisão de particionar ou não um objeto, devemos levar
em consideração o tamanho atual e o desempenho esperado das consultas.
A utilização de objetos particionados diminui a complexidade de manutenção de objetos com
altíssima taxa de crescimento que normalmente causam baixa performance em consultas
devido ao grande volume de dados.
Existem dois fatores a considerar ao planejar uma função de partição: a coluna cujos valores
determinam como uma tabela é particionada, conhecida como coluna de particionamento, e o
intervalo de valores da coluna de particionamento para cada partição. Esse intervalo de
valores determina o número de partições que compõem sua tabela. Uma tabela poderá ter
1.000 partições, no máximo.
As opções que você tem para as colunas de particionamento e para o intervalo de valores são
determinados, basicamente, pela extensão na qual seus dados podem ser agrupados de forma
lógica, como por exemplo, por data; e, se este agrupamento lógico é adequado para gerenciar
subconjuntos de dados.
Resumo:
Objetivo:
 Aumento de Performance
 Facilidade de administração
Composição dos 2 fatores do particionamento:
 Função de particionamento (Partition Function) que define as fronteiras das partições;
 Distribuição física das partições (Patition Scheme) que determina onde cada partição
será armazenada.
Página 23
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
12.NOVOS TIPOS DE DADOS DO SQLSERVER 2005
Large-Value Data Types:
 Varchar(max), NVarchar(max), Varbinary(max);
 Permitem armazenar até 2GB de dados
 Substituem os antigos, Text, NText e Image, garantindo maior flexibilidade
XML
 Permite o armazenamento de um documento ou fragmento XML em variáveis ou
campos de tabelas;
 Vantagens:
o Permite a criação de índices específicos para esse tipo de dado;
o Suporta consultas através da linguagem X-Query
o A estrutura do conteúdo XML pode ser validada
 Limitação:
o O conteúdo XML original não é plenamente preservado.
13.SERVICE BROKER
O Serviço Broker é responsável pelo processo de envio e recebimento de mensagens
assíncronas em diferentes processos no SQL Server 2005. A linguagem TSQL é utilizada para
o envio de mensagens para uma fila no banco de dados do remetente ou para outro banco de
dados do SQL Server, ou outro servidor/instância remoto. No processo do Service Broker são
envolvidas filas de espera, os diálogos e a ativação.
Consultando filas
Às vezes pode ser necessário inspecionar o conteúdo de uma fila como um todo. Você pode
querer saber quantas mensagens a fila contém ou assegurar que o aplicativo processou todas
as mensagens de um serviço que está prestes a se tornar off-line. Você pode precisar descobrir
por que as mensagens não estão sendo processadas por um aplicativo.
Para obter essas informações, use o nome da fila como a tabela de origem de uma instrução
SELECT. Uma instrução SELECT em uma fila tem o mesmo formato que uma instrução
SELECT em uma exibição ou uma tabela.
NOTA:
O Agente de Serviços é projetado para permitir que vários leitores de fila recebam
mensagens eficientemente de uma fila. Porém, uma instrução SELECT em uma fila
pode causar bloqueio. Ao usar uma instrução SELECT em uma fila, especifique a
dica NOLOCK para evitar o bloqueio de aplicativos que usam a fila
A seguir, está uma instrução SELECT de exemplo para descobrir o número de mensagens na
fila ExpenseQueue:
SELECT COUNT(*) FROM dbo.ExpenseQueue WITH (NOLOCK) ;
Página 24
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
14.CAMPOS XML
O padrão XML ( eXtensible Markup Language) é um meio de transferir informações de
forma semi-estruturada, usa marcadores (tags) e atributos. Ele usa os marcadores apenas para
delimitar os trechos de dados, deixando sua interpretação completamente à cargo da aplicação
de front-end.
O XML permite maior flexibilidade, auxilia na camada de transporte e permite a
interoperabilidade entre diferentes plataformas. Adicionalmente, existem padrões de
transformação para convertê-lo em um formato mais adequado (XSL) e para validar sua
sintaxe (XSD).
No SQL Server 2005, é possível especificar alguns métodos para consultar dados em
estruturas XML. Os métodos de consulta em estruturas XML são:
query() - Realiza consultas em estruturas XML que retornam XML;
value() - Realiza consultas em estruturas XML que retornam tipos SQL;
exist() - Realiza consultas para determinar a existência de um valor dentro de uma estrutura
XML;
nodes() - Utiliza para segmentar uma estrutura XML em vários registros
O SQL Server 2005 possui o tipo de dados XML que pode ser utilizado para simplicar
consideravelmente aplicações que possuam esse tipo de características. Nas versões anteriores
do SQL Server era necessário armazenar códigos XML em campos do tipo varchar.
Campos XML podem ser consultados via XQuery desta forma:
Retorna o XML do campo t do XML, o [p="d"] é um filtro para a propriedade p do marcador
part, retornará <t>NE</t>;
select CampoTipoXML.Query('//prev//part[p="d"]//t') from TabelaComDadosXML
Para retornar o valor do campo, sem o marcador envolvendo o texto, podemos usar a função
Data, o exemplo abaixo retorna o conteúdo do marcador, retornará NE;
select CampoTipoXML.Query('Data(//prev//part[p="d"]//t)') from TabelaComDadosXML
Para acessar uma propriedade do marcador como a parX, você deve utilizar o @ para indicar
o nome da propriedade;
select
CampoTipoXML.Query('Data(//prev//part[p="d"]//@parX)')
TabelaComDadosXML
from
Atenção, você está trabalhando com campos XML, então o retorno é sempre em XML, para
poder usar como texto em querys no SQL Server 2005 com tipos varchar por exemplo, você
tem que converter o dado XML, em texto, usando por exemplo a função convert.
select Convert( varchar(max), CampoTipoXML.Query('Data(//prev//part[p="d"]//t)') ) from
TabelaComDadosXML
Página 25
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
15.TRATAMENTO DE ERROS
O SQL Server oferece duas formas de realizar tratamento de erros em código Transact-SQL:
 Através da variável de ambiente @@ERROR
 Através da utilização de blocos TRY...CATCH.
15.1. Tratamento com @@ERROR
A utilização da variável @@ERROR era a única opção válida até a versão 2000 do SQL
Server. Ela oferece um mecanismo simples de validação que permite verificar se um comando
foi executado com sucesso ou não. Para trabalhar com a @@ERROR, deve-se verificar o
valor da variável após a execução de cada comando:
Exemplo:
INSERT INTO funcionario (
cod_funcionario,
nom_funcionario,
dtc_atualizacao
VALUES (1, 'Joao da Silva', getdate());
)
IF @@ERROR <> 0
BEGIN
RAISERROR 50001 'Erro ao cadastrar funcionario'
RETURN
END;
Esse mecanismo está sujeito às seguintes limitações:
 Necessidade de testar a variável @@ERROR a cada comando executado;
 Necessidade de interrupção explícita do fluxo de execução a cada teste realizado;
 Informação limitada ao código do erro. Detalhes como a linha em que o erro ocorreu,
a severidade, o estado e o nome objeto (procedure, trigger, função) que executou o
comando com erro não podem ser capturados e tratados.
15.2. Tratamento com TRY...CATCH
Recomendação: Realizar o tratamento de erros através de blocos TRY...CATCH.
A utilização de blocos TRY...CATCH permite um tratamento de erros mais eficiente e
flexível. O bloco TRY define um conjunto de comandos a serem executados e transfere a
execução para o bloco CATCH, em caso de erro. No bloco CATCH é possível capturar
detalhes sobre o erro ocorrido através de funções do próprio SQL Server.
Exemplo:
A procedure "up_teste_try_catch" insere um registro na tabela de teste "funcionario". A
primeira execução dessa procedure será bem sucedida e somente o bloco TRY será executado.
A partir da segunda execução, ocorrerá uma violação de chave primária e o controle será
transferido para o bloco CATCH.
O bloco efetua o tratamento de erros que inclui:
 Captura dos detalhes do erro;
 Tratamento de transação (rollback quando necessário);
 Lançamento do erro para a aplicação (RAISERROR);
Página 26
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação

29/05/2017
Interrupção do fluxo de execução.
-- Criacao da tabela de teste
CREATE TABLE funcionario (
cod_funcionario SMALLINT NOT NULL,
nom_funcionario VARCHAR(50) NOT NULL
);
ALTER TABLE funcionario ADD CONSTRAINT ix_funcionario01
PRIMARY KEY (cod_funcionario);
GO
-- Criacao da procedure de teste
-- Exemplo de uso do TRY...CATCH
CREATE PROCEDURE up_teste_try_catch
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- Inicio do bloco TRY
BEGIN TRANSACTION;
INSERT INTO funcionario
VALUES (1, 'Jose da Silva');
COMMIT TRAN;
PRINT 'Operação realizada com sucesso!';
END TRY -- Fim do bloco TRY
-- Se nao ocorrer erro, somente o bloco TRY sera executado
-- Caso ocorra algum erro, o bloco CATCH sera acionado
BEGIN CATCH -- Inicio do bloco CATCH
DECLARE
@num_erro int,
-- Codigo do erro
@num_severidade tinyint,
-- Severidade do erro
@sts_erro
tinyint,
-- Estado do erro
@nom_proc_erro varchar(128), -- Nome da procedure
@num_linha_erro smallint,
-- Posicao no codigo
@des_mensagem_erro varchar(4000), -- Descricao
@sts_transacao tinyint;
-- Estado da transacao
-- Armazenando os detalhes sobre o erro
-- ocorrido e o estado da transacao
SELECT
@num_erro = ERROR_NUMBER(),
@num_severidade = ERROR_SEVERITY(),
@sts_erro = ERROR_STATE(),
@nom_proc_erro = ERROR_PROCEDURE(),
@num_linha_erro = ERROR_LINE(),
@des_mensagem_erro = ERROR_MESSAGE(),
@sts_transacao = XACT_STATE();
-- Verifica o estado da transacao
IF @sts_transacao <> 0 -- Em estado de transacao
BEGIN
Página 27
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
ROLLBACK TRANSACTION;
END;
RAISERROR ( 'Erro %d na linha %d, ao executar %s: %s',
@num_severidade,
@sts_erro,
@num_erro,
@num_linha_erro,
@nom_proc_erro, @des_mensagem_erro);
RETURN;
END CATCH
END
O tratamento através de blocos TRY...CATCH permite um controle maior do fluxo de
execução, simplificando a codificação, facilitando a manutenção e permitindo um
detalhamento maior do erro ocorrido.
Observação:
 O uso do TRY...CATCH não dispensa a utilização do RAISERROR, conforme
exemplo acima, a menos que o tratamento direcione os detalhes do erro para uma
tabela;
 O tratamento de transações é fundamental para evitar que o bloco CATCH seja
concluído com a transação em aberto. Nesse contexto o uso da função
XACT_STATE() é mais indicado do que a variável @@TRANCOUNT porque
permite verificar se a transação entrou em um estado uncommittable;
 Maiores informações sobre o TRY...CATCH, seus tratamentos e variações podem ser
encontradas no SQL Server 2005 Books Online.
Página 28
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
16.OBSERVAÇÕES
Seguem algumas observações importantes sobre Transact-SQL.
HINTS
O SQL disponibiliza cláusulas adicionais que podem ser utilizadas para mudar condições do
contexto em que uma consulta é executada. Estas cláusulas, chamadas HINTS, são muito
poderosas, mas devem ser utilizadas com muito cuidado. Seguem 4 dos hints mais
conhecidos:
tablockx
Atribui um lock exclusivo em toda a tabela que está sendo
consultada. Ninguém poderá ler ou atualizar absolutamente nada
nesta tabela até o final da transação que gerou o tablockx.
holdlock
Mantém os locks nas tabelas envolvidas numa operação até o final
da transação. O contexto da transação será definido pelo BEGIN
TRAN mais externo.
nolock
Nenhum tipo de lock será respeitado na consulta. Os resultados
retornados poderão estar inconsistentes.
index
Força a utilização de um determinado índice. Esta opção foi muito
utilizada na versão 6.5, mas tornou-se quase desnecessária a partir
da versão 7.0.
Importante: Hints podem influenciar muito a performance de uma aplicação e até mesmo do
servidor como um todo, por isso, o uso de hints deverá sempre ser negociado
com a GETEC.
Cursores
Cursores, assim como tabelas temporárias, têm um custo muito grande para o banco. É
aconselhável evitar o uso de cursores, procurando substituí-los por joins e/ou tabelas
derivadas.




Quando for utilizar um cursor, procure selecionar os mais simples como FAST
FOWARD (este tipo mescla as opções FOWARD ONLY e READ ONLY)
Só use cursores SCROLL se for realmente necessário
Feche sempre os cursores depois da utilização com CLOSE e DEALLOCATE
Lembre sempre de executar o FETCH dentro do WHILE que varre o cursor evitando
LOOPs infinitos.
ORDER BY
A cláusula ORDER BY é bastante conhecida e utilizada, mas compromete a performance nas
consultas. Esta cláusula só deve ser utilizada quando a ordenação não puder ser realizada no
cliente.
CONTROLE DE EXECUÇÃO DO SCRIPT
Página 29
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
Ao elaborar um script com blocos de instruções dependentes, é recomendável utilizar o
RETURN ou declarar numa transação. Desta forma, é possível controlar as instruções que
serão executadas. Seguem abaixo, 2 exemplos:
Exemplo 1:
IF @@SERVERNAME <> 'PCORPORATIVO\BDP_CORPORATIVO'
BEGIN
PRINT 'A instância não é de produção'
RETURN
END
ELSE
BEGIN
SELECT loginname as 'Logins de Produção:'
FROM master..syslogins
END;
Exemplo 2:
BEGIN TRANSACTION
UPDATE usuario_dba SET usuario = 'ab'
WHERE usuario = 'ad'
IF @@ERROR <> 0
ROLLBACK
ELSE
COMMIT
SET NOCOUNT ON
O SET NOCOUNT ON pode proporcionar um aumento significativo de desempenho,
pois o tráfego de rede é bastante reduzido.
Vale lembrar que SET NOCOUNT ON somente deve ser utilizado quando o retorno
da quantidade de registros afetados não for necessário. A função @@ROWCOUNT é
atualizada mesmo quando SET NOCOUNT é ON.
CLÁUSULA CATCH
Não existem restrições para o uso do comando SELECT dentro da cláusula CATCH.
A instrução TRY...CATCH captura erros de execução com severidade maior que 10.
Os erros com severidade menor não serão tratados.
Se houver um TRY...CATCH dentro de um cursor,deve-se obrigatoriamente
desalocar e fechar o cursor antes de finalizar o CATCH.
Página 30
841067067
Secretaria da Fazenda do Estado da Bahia
DTI - Diretoria de Tecnologia da Informação
29/05/2017
17.CONCLUSÃO
Alguns dos tópicos acima foram indicados como recomendação ou simplesmente difusão de
conhecimento, outros devem ser seguidos à risca por terem conseqüências sérias para o banco
de dados e para as aplicações.
A programação em Transact-SQL, por envolver muitos fatores, deve ser realizada sempre de
maneira criteriosa, através da discussão de soluções, análise de planos de execução e
principalmente atualização de conhecimentos.
Este documento estará sendo atualizado sempre que algum fator importante for identificado,
portanto servirá como referência técnica e deverá ser consultado com freqüência.
18.REFERÊNCIAS
SQL Server Performance.com
http://www.sql-server-performance.com
Microsoft SQL Server 2005 Books Online
http://msdn.microsoft.com/en-us/library/ms130214(v=sql.90).aspx
SQL Server Customer Advisory Team - SQL Server Best Practices
http://sqlcat.com/
Microsoft Sql Server 2000 Performance Tuning Edward Whalen, Marcilina Garcia, Steve
Adrien Deluca, Edward Whalen Reding,
Jamie Marcilina Garcia – Microsoft Press
ISBN: 0735612706
Página 31
841067067