Sumário - Portal SQL · 2018-10-17 · Curso de Banco de Dados – Plataforma: MS SQL Server Por: Herbert D. Perazzelli – DBA Certificado MCSA MS SQL Server 2012 2 1. A IMPORTÂNCIA

Curso de Banco de Dados – Plataforma: MS SQL Server

Por: Herbert D. Perazzelli – DBA Certificado MCSA MS SQL Server 2012 1

Boas Práticas de Banco de Dados: Padronização e Dicas

Sumário 1. A IMPORTÂNCIA DO VERSIONAMENTO DE SCRIPTS ........................................................... 2

1.1. Principais benefícios ...................................................................................................... 2

1.2. Principais softwares versionadores ............................................................................... 2

1.3. Modelo de divisão do repositório ................................................................................. 2

2. PADRONIZAÇÃO DOS OBJETOS ............................................................................................ 3

2.1. Nomenclatura ................................................................................................................ 3

2.2. Desenvolvimento T-SQL (scripts) .................................................................................. 6

3. SUGESTÕES PARA IMPLEMENTAÇÃO A LONGO PRAZO .................................................... 26



1. A IMPORTÂNCIA DO VERSIONAMENTO DE SCRIPTS

Todo script DDL (Data Definition Language – Create/Alter/Drop) e DML (Data

Manipulation Language – Insert/Update/Delete) que for criado, alterado ou removido

deve ser controlado por alguma ferramenta de versionamento (repositório de código),

que tenha o cabível tratamento (backups frequentes, controle de acesso, organização

de diretório, etc.).

O controle pode ser feito individualmente, ou seja, cada script ou bloco de

scripts estarem em um mesmo arquivo “.sql” ou todos os scripts estarem em um único

“.sql”.

1.1. Principais benefícios

Ter um repositório seguro

Controle cronológico das alterações em um mesmo arquivo

Controle de autoria de cada atualização do arquivo

Comparação de alterações de versões distintas de um mesmo script

Recuperação da base na versão desejada

Utilização e gerenciamento de um mesmo arquivo por mais de uma pessoa

Divisão de arquivos por projetos, demandas, etc.

1.2. Principais softwares versionadores

TFS (Team Fundation Service) – Microsoft

GitHub

Bitbucket – Atlassian

Tortoise SVN (Apache Subversion)

1.3. Modelo de divisão do repositório

Abaixo o exemplo de um modelo bastante usual de divisão dos arquivos da base de

dados:

1.3.1. Estrutura

Arquivo “Estrutura.sql” com os scripts Create/Alter/Drop em ordem

sequencial (para evitar erros de dependência de objeto) para os seguintes objetos:

o Schemas

o Types

o Tabelas

o Constraints



o Triggers

o Índices

o Estatísticas

1.3.2. Negócio

Arquivo “Negocio.sql” com os scripts Cerate/Drop em ordem sequencial (para

evitar erros de dependência de objeto) para os seguintes objetos:

o Views

o Procedures

o Functions

1.3.3. DML

Arquivo “DML.sql” com os scripts em ordem sequencial (para evitar erros de

dependência de registro) para as seguintes operações:

o Insert

o Update

o Delete

2. PADRONIZAÇÃO DOS OBJETOS

A padronização é capaz de dar identidade a tudo que é criado no SQL Server. Isso

facilitará na orientação e intuição de quem precisar dar manutenção, por exemplo, em algum objeto ou script. Abaixo, algumas regras de padrão e boas práticas do SQL Server que podem ser aplicadas nesse desenvolvimento.

2.1. Nomenclatura

Quando se cria um padrão de nome é possível, implicitamente, fazer referência à finalidade de um objeto ou funcionalidade de um determinado grupo. Lembramos que não é recomendado utilizar caracteres especiais (ç, acentos, etc.) em nome de objetos, nem tampouco utilizar espaço em branco (neste caso, substitui o espaço em branco por “underline/linha baixa”). O principal motivo são potenciais erros de integração com outras ferramentas que não conseguirão converter ou trabalhar com esse tipo de estrutura.

É claro que cada usuário, DBA, Empresa, etc. pode ter um padrão que já atenda sua necessidade. Há vários casos em que o nome dos objetos iniciam com a sigla da empresa/responsável que os criou, ou seja, identifica a origem dos objetos nativos e separa da criação/customização por usuários/clientes finais. O item “View” tem um exemplo bem interessante nesse aspecto, mas também iremos mostrar alguns outros exemplos abaixo para os seguintes objetos:



2.1.1. Tabelas

Nome da tabela: por extenso e iniciando pelo owner “dbo.” + “grupo” de dados que ela irá armazenar. Ex.: “dbo.Pedido_Venda” e “dbo.Pedido_Venda_Item” (e não “dbo.Item_Pedido_Venda”). Esse agrupamento por “Pedido_Venda” auxiliará na ordenação e localização de tabelas do mesmo nicho (pedidos de venda, impostos, endereço, etc.) no próprio SQL Server Management Studio.

Nome da Coluna: utilizar notação com dois dígitos iniciais que indicam o tipo de informação e o resto do nome, em bloco de quatro dígitos (incluindo referência ao nome da tabela). Ex.: ID_ = código do registro (“ID_PediVenda”) NM_ = nome (“NM_Merc”)

DT_ = data (“DT_PediVend”) QT_ = quantidade (“QT_PediVendItem”) SG_ = sigla (“SG_UF”) FL_ = flag (“FL_UsuaAtiv”)

VL_ = valor (“VL_NotaFisc”) PC_ = percentual (“PC_ICMSMerc”) NR_ = número (“NR_FonePessCont”) GN_ = genérico (“GN_ObsePediVend”)

A ideia de restringir à 2 dígitos iniciais e 4 para descrição da informação é apenas encurtar o nome das colunas. Mas é muito usual deixar a descrição por extenso (ex: “Nome_Mercadoria”).

Tipos de Dados: Procurar utilizar os tipos de dados default do SQL Server ao invés de personalizados (recurso “User-Defined Data Types”). Tipos que são criados pelo usuário podem gerar transtornos futuros de integrações com outras ferramentas por terem nomes personalizados. Outro potencial problema é a falta de padronização pois, nem todas as pessoas que forem criar algo no banco de dados vão se lembrar de utilizar esses novos tipos de dados ao invés de usarem o nativo. Exemplo: criar um “User-Defined Data Types” chamado “Codigo” que corresponde ao tipo “INT”. Provavelmente haverá tabelas com colunas que tenham os dois data types (INT e Codigo).

Chave primária (Primary Key – PK): toda tabela deverá ter chave primária. Preferencialmente o tipo de dados será inteiro e simples (apenas uma coluna), isso por que uma única coluna e do tipo inteiro ocupa menos bytes na tabela deixando a orientação e localização dos registros mais ágil e ocupando menos espaço em disco se comparado à uma PK composta em com tipos de dados que comportam texto alfanuméricos. O nome do campo começará com ID_ e o resto será a abreviação, de 4 em 4 caracteres, do nome da tabela (ex.: “ID_PEDIVEND”). Quando criar uma chave estrangeira para esse campo, preservar o nome da PK na tabela filha. Exemplo: PK = “dbo.Pedido_Venda.ID_PediVend” e FK = “dbo.Pedido_Venda_Item.ID_PediVend”. Assim, será mais fácil se orientar na elaboração dos Joins, por exemplo.



O nome interno da constraint deverá começar com PK_ mais o nome da

tabela abreviado. Ex.: PK_PediVend. Isso facilitará a separação do que é uma constraint PK, FK, check, etc.

Chave estrangeira (Foreign Key – FK): A coluna que representará a FK deverá ter o mesmo nome e tipo de dado da sua respectiva PK. O nome interno da constraint deverá começar com FK_ + o nome da tabela “filho” abreviada mais o nome da tabela “pai” abreviada. Ex.: FK_PediVendItem_PediVend.

Check: os campos que tiverem valor pré-estabelecidos de entrada deverão ter validações para garantir integridade dos registros que serão cadastrados. Ex.: “dbo.Usuario.FL_UsuaAtiv” = “Sim” ou “Não”. O nome da constraint deverá começar com CHK_ + nome descrição

abreviada. Ex.: “CHK_UsuaAtiv_SN”.

Índices (Index): inicialmente, criar índices apenas para as colunas que são chaves estrangeiras em tabelas que receberão muitos registros e tais colunas também serão bastante consumidas em consultas (where,

join, etc.). O nome do índice deverá iniciar com IDX_ + o nome da tabela abreviada

mais o nome da coluna. Ex.: IDX_PediVendItem_ID_PediVend.

Trigger: evitar o uso deste tipo de objeto pois sua ação tem impacto direto nos registros que estão manipulados. Então, toda regra que é aplicada na camada de negócio (software, procedure, etc.) não conseguirá validar o que está sendo feito na trigger, além do fato da existência/funcionalidade da trigger geralmente cair no esquecimento. Partindo que existem três ações onde a trigger será disparada (Insert, Update e Delete), o nome da trigger pode iniciar com “tri_” ou “tru_” ou “trd_”+ nome da tabela + a funcionalidade. Exemplo: “tri_Mercadoria_Valida_Cod_Barras”. O script deverá conter comentário das funcionalidades. Obs.: Toda vez que for criar uma tabela ou um de seus objetos vinculados (Constraint, index, trigger, add column, etc.), verificar antes se o mesmo existe. Caso exista, nada deverá ser feito. Toda vez que for alterar ou “dropar” um objeto, verificar se o mesmo ainda existe. Caso não exista, nada deverá ser feito. Isso garantirá que a execução do script não gerará erros, seja pela pré-existência de algo que está sendo criado, ou pela não existência de algo que estão tentando remover.

2.1.2. View:

Nome da view: iniciando por “dbo.viw_” + “SiglaEmpresa_” (se for algo específico do cliente, colocar nome ou sigla do cliente) mais o módulo

se for específica (Compras_ ou Vendas_, etc.) mais a descrição da funcionalidade. Ex.: dbo.viw_IBAM_Compras_Lista_Fornecedor. Obs.: Toda vez que for criar uma view, verificar antes se a mesma existe. Caso exista, deverá excluí-la e recriá-la em seguida.



Caso seja uma alteração, nunca utilizar a operação “Alter”, sempre Drop/Create. Caso for “dropar” uma view, verificar antes se ela existe. Se não existir, nada deverá ser feito.

2.1.3. Function:

Nome da function: iniciando por “dbo.fct_” + “SiglaEmpresa _” (se for algo específico do cliente, colocar nome ou sigla do cliente) mais o módulo se for específica (Estoque_ ou Imposto_) mais a descrição da funcionalidade. Ex.: dbo.fct_IBAM_Imposto_Calcula_ISS(<valor>,<cod_munic

ipio>).

2.1.4. Procedure:

Nome da Procedure: iniciando por “dbo.stp_” + “SiglaEmpresa _” (se for algo específico do cliente, colocar nome ou sigla do cliente) mais o módulo se for específica (Relatorio_ ou Contabil_) mais a descrição da funcionalidade. Ex.: dbo.spt_IBAM_Relatorio_Vendas_Por_Mes.

2.2. Desenvolvimento T-SQL (scripts) 2.2.1. Select

Utilizar “Top <n>” apenas quando necessário. Utilizar “Distinct” apenas quando necessário. Verificar porque está

duplicando registros e, preferencialmente, utilizar “Group By” (consome menos processamento / e tentar entender porque há redundância de registros). Ex.:

Retornar apenas as colunas necessárias na consulta (evitar “select *

from” (quanto mais colunas tiver e maior for seu tipo de dado, mais demorado será o retorno da consulta; muito cuidado ao retornar colunas do tipo “IMAGE”). Ex.:



Ao invés de “Select count(*)...”, utilizar “Select count(1)...”. Ao utilizar “*” (asterisco), o SQL fará a contagem passando por todas as colunas de um registro e, com isso, os tipos de dados das colunas poderão interferir na performance. Então, o caractere “1” é o suficiente e o resultado será o mesmo. Ex.:

Utilizar order by apenas quando necessário. Isso por que ele interfere

negativamente na performance da consulta. Quanto maior o número de colunas para ordenação e maior o tipo de dado dessas colunas, mais processamento SQL Server consumirá para criar a ordem. Índices podem ajudar a melhorar a velocidade.

Utilizar sempre o nome da tabela/alias como prefixo da coluna. Ex.:

Evitar utilizar sub-select no retorno do select. Neste caso, tentar sempre

vincular no join. Estruturalmente, o SQL Server monta a consulta partindo da cláusula “From” e, por último, cláusula “Select”. Então, se houver sub-select na cláusula “Select”, o SQL fará essa sub-consulta a cada registro que será retornado e o processo ficará mais custoso. Ex.:



Dar sempre um nome/alias para as colunas retornadas (hard code ou

calculadas). Assim, saberemos o que representará o retorno de cada coluna ao invés de termos o título “(No column name)”. Ex:

Evitar de retornar mais de uma coluna com o mesmo nome.



Inserir comentário antes de qualquer ação para descrever a funcionalidade da consulta.

Evitar escalabilidade, ou seja, se tiver que fazer uma consulta, faça o

“Select” diretamente na tabela. Evite fazer “escalas” como, por exemplo, consulta em view, onde a view busca dados de uma função e essa função trás os dados de outras três funções (join) sendo que essas três funções estão trazendo seus dados baseados em outras três procedures que, por fim, essas procedures são as que estão fazendo consulta diretamente na tabela. Essa situação poderá representar um tempo de resposta e processamento maior do SQL Server. Por mais que você coloque filtros (where) consultando sua view, as demais estruturas que estão no sub-níveis (functions, etc.) farão a pré-consulta e processamento com todos os dados possíveis que elas poderão oferecer ao destino final (view).

2.2.2. From/Join Utilizar sempre o owner (dbo.) antes do nome da tabela. Essa informação

é importante para que seja referenciado o objeto do proprietário correto



(caso haja objetos com nomes iguais em owner distintos) ao invés owner do default. Outro ponto é que o SQL pode perder alguns milissegundos na tradução do script para colocar o owner default. Ex.:

Em caso de consultas para relatórios ou consultas a colunas “estáticas” (de pouca movimentação), utilizar o comando “with(nolock) “na frente do nome/alias das tabelas. Assim, a consulta não irá bloquear a tabela para as outras sessões do banco de dados enquanto estiver sendo executada. Com esse recurso, a consulta também conseguirá buscar informações de tabelas que estejam bloqueadas por outros usuários. Ex.:

2.2.3. Insert Sempre especificar os campos que irão receber os valores. Ex.:

Verificar antes se o registro já existe. Se existir, não incluir.



2.2.4. Delete Tomar cuidado com os registros que serão excluídos (certificar que a

clausula “where” está sendo usada corretamente). Se possível, sempre consulte antes o que será excluído para validar os registros que serão afetados. Ex.:

Verificar se há registros dependentes que deveriam ser apagados em cascata. Caso contrário, será gerado erro de violação de integridade referencial. Ex.:

2.2.5. Update

Verificar se o registro que será alterado não receberá o próprio valor (para não fazer algo “à toa”) ou irá comprometer a integridade (gerar chave duplicada).



Tomar cuidado com os registros que serão alterados (certificar que a clausula “where” está sendo usada corretamente). Se possível, sempre consulte antes o que será alterado para validar os registros que serão afetados. Ex.:

2.2.6. Where Evitar utilizar funções (ex.: YEAR(PV.DT_PediVend)). A cada registro

selecionado, o SQL fará a execução da função e “calculará” a informação a ser tratada. Isso consome processamento e pode interferir negativamente na performance da operação. Ex.:



Evitar usar “In”. Neste caso, quando possível, utilizar “exists” ou “not exists”.

2.2.7. Procedure

Toda vez que for criar uma Procedure, verificar antes se a mesma existe. Caso exista, deverá excluí-la e recriá-la em seguida. Isso irá evitar erro de pré-existência de outra procedure com mesmo nome.

Caso seja uma alteração, nunca utilizar a operação “Alter”, sempre Drop/Create. Caso for “dropar” uma Procedure, verificar antes se ela existe. Se não existir, nada deverá ser feito



Antes do comando “Create Procedure”, inserir cabeçalho com descritivo de alteração (BackLog e atividade). Ex.:

---------------------------------------------------------------

--------------------------(Atualização)------------------------

--> Autor Data Release

--> -------------- ---------- ---------------------

--

--> Fulano 01/01/2016 BL: 1234 – Criar Proc.

---------------------------------------------------------------

Colocar, já no início da procedure (após o “As”), o comando “Set

NoCount On”.



Alteração: se for incluir um parâmetro em uma Procedure já existente, colocar sempre após último parâmetro e, também, colocar valor default = Null para garantir que não irá interferir em funcionalidades que já consome esse objeto.



Obs.: ao executar uma procedure com parâmetros, indicar nominalmente os parâmetros para garantir que, por mais que alguém troque a ordem dos parâmetros, a execução está passando os valores para os parâmetros corretos. Ex.: “Exec dbo.stp_Procedure_Teste @PCodigo =1, @PData = ‘2020-01-05’”.

Por padrão, sempre que houver necessidade de encerrar a procedure por causa de algum erro ou inconsistência, retornar 1, senão 0 (zero). Ex.: “Return(1)”.

Depois de criar a procedure, inserir comando de permissão (grant), para os devidos logins de banco que deverão executá-la.



2.2.8. Function Toda vez que for criar uma function, verificar antes se a mesma existe. Caso

exista, deverá excluí-la e recriá-la em seguida. Caso seja uma alteração, nunca utilizar a operação “Alter”, sempre

Drop/Create. Caso for “dropar” uma function, verificar antes se ela existe. Se não existir, nada deverá ser feito.



Antes do comando “Create function”, inserir cabeçalho com descritivo de alteração (BackLog e atividade). Ex.:

---------------------------------------------------------------

--------------------------(Atualização)------------------------

--> Autor Data Release

--> -------------- ---------- ---------------------

--

--> Fulano 01/01/2016 BL: 1234 – Criar Func..

---------------------------------------------------------------

Alteração: se for incluir um parâmetro em uma function já existente,

colocar sempre após último parâmetro e, também, colocar valor default = Null para garantir que não irá interferir em funcionalidades que já consome esse objeto.

Após criar a function, verificar se há a necessidade de dar permissão. Nem todo tipo de função necessita de grant.



2.2.9. Dicas e novas funcionalidades MS SQL Server 2008 ou edições superiores Toda vez que for referenciar uma tabela, utilizar o schema como prefixo

(ex.: dbo.Pedido_Venda).

Manter o script identado/tabulado (utilizar, preferencialmente, a tecla “Tab”).



Sempre inserir um comentário em cada operação que realizada e em casos de instruções hard code. Ex.:

Pode-se inserir mais de um registro por insert. Ex.: “Insert Into dbo.Teste (ID_Teste) Values (1), (2), (3)”. Recurso disponível a partir da versão 2008 do MS SQL Server.

Pode-se declarar variável passando valor inicial (“Declare @Teste Int

= 1”). Ficar atento quando utilizar escalabilidade (procedure, que chama

function, que chama procedure, etc.), pois pode impactar na performance. Utilizar “Coalesce” ao invés de IsNull. Além de recomendado pela

MS, o “Coalesce” também pode ser utilizado para vários níveis de checagem: “Coalesce(null, null, null, 1)” (recomendação MS). Ex:



Utilizar “sysdatetime” ao invés de “getdate” para data (recomendação MS).

Tomar cuidado ao reaproveitar um script ou utilizar o script gerado pelo wizard do MS Mnagement Studio, principalmente por conta do file group e compatibilidade. No menu “Tools -> Options...” é possível determinar a compatibilidade/edição que o SQL Server irá utilizar para criar seus scripts através do wizard.

Sempre testar o script em uma máquina mais lenta (para tentar simular o ambiente com menos recurso e ver se algo pode ser melhorado).

Verificar, antes, se o que será criado já não existe ou que possa ser aproveitado.

Ao incluir um campo na tabela (seja criando ou alterando a tabela), colocar uma descrição (description) para o campo.



Tentar utilizar “>” e “<” ao invés de “beteween” ou “>=” e “<=” (recomendação MS). Ex.:

Evitar de utilizar transação dentro de transação.



Não utilizar transação em operações simples.



Antes de criar transação, verificar se já não há uma em aberto (transação aberta é um dos principais motivos de travamento da base).



Utilizar try catche para tratar exceções (recurso disponível apenas a partir da edição 2008 do MS SQL Server)



Não utilizar indexação na cláusula “Order By”. Ex.:

Utilizar o recurso “sequence” para geração de números sequenciais únicos (Ids, códigos, etc.) ao invés de “select (max(id_atual) + 1) novo_id from tabela with(rowlock)”. Esse recurso só está disponível a partir da edição 2012 do MS SQL Server.

3. SUGESTÕES PARA IMPLEMENTAÇÃO A LONGO PRAZO

3.1. Controle dos objetos

Criar cadastro onde possa vincular o objeto (tabela, procedure, view, function, etc.) com a funcionalidade do sistema (cadastro, relatório, etc.) que está consumindo esse objeto.



Depois, criar mecanimos de recuperar essas informações para facilitar o mapeamento do que está sendo usado de fato, e onde está sendo. Servirá até para aujudar na busca para não repetirem código. Se possível, vincular até os campos do objeto com a funcionalidade. Assim possível fazer um “de para” campo com coluna.

3.2. Definir responsáveis

Definir as pessoas que serão responsáveis por cada área do projeto, atribuindo-lhes atividades e delegando responsabilidades. Ex.: Alguem ficar responsável por validar os scripts (ou, dependendo do caso, criá-los) para o programador. Até mesmo fazer o cadastro do item acima.

3.3. Definir valores padrões para constraints do tipo chcek

Para organizar e homogeneizar os valores fixos da mesma natureza que podem ser aproveitados na criação de várias colunas, pode-se criar padrões como nos exemplos abaixo:

Criar padrão para campos “booleanos” (Ativo/Inativo, Sim/Não, Habilitado/Desabilitado, Etc.) e utilizá-los nas constraints “Check”.

Criar padrão para campos de lista fixa (Bloqueado/Em Estudo/Ativo, etc.) e utilizá-los nas constraints “Check”.

Documents

Sumário - Portal SQL · 2018-10-17 · Curso de Banco de Dados – Plataforma: MS SQL Server Por: Herbert D. Perazzelli – DBA Certificado MCSA MS SQL Server 2012 2 1. A IMPORTÂNCIA