Apostila Sistema de Banco de Dados II

Prévia do material em texto

66
	UNIPam
	 Sistema de Banco de Dados II
	
	Fernando Corrêa de Mello Júnior / José dos Reis Mota
Agosto/2016
ÍNDICE
1. INTRODUÇÃO / REVISÃO	5
1.1.	Processo de Modelagem	5
1.1.1. Objetivos do Modelo de Dados	5
1.1.2. Execução	5
1.1.3. Níveis de Modelagem	6
1.2. Requisitos	7
1.2.1. Definição	7
1.2.2. Tipos de Requisitos.	7
1.2.3. Requisitos Funcionais / não funcionais	7
1.3. Especificação de Requisitos	9
1.3.1. Análise de Requisitos	9
1.3.2. Projeto Conceitual do BD	9
1.3.3. Documento de Requisitos	10
1.3.4. Usuários do Documento de Requisitos	10
1.3.5. Estrutura do Documento de Requisitos	10
2. LINGUAGEM SQL	12
2.1. DML – Linguagem de manipulação de dados	12
2.1.1. SELECT	12
2.1.2. Operações Matemáticas	13
2.1.3. Produto Cartesiano	14
2.1.4. Subconsultas	14
2.1.5. Funções de grupo	14
2.1.6. Comando Insert	15
2.1.7. Comando Update	15
2.1.8. Comando Delete	16
2.2. DDL – Linguagem de definição de dados (BASE SQLServer)	16
2.2.1. Tabela	16
2.2.2. Convenções para nomes de tabelas	16
2.2.3. Criando uma tabela	16
2.2.4. Tipo de Dados	17
2.2.5. Exemplo	17
2.2.6. Alterando a estrutura de uma tabela	18
2.2.7. Removendo uma tabela	19
2.2.8. Constraints	19
2.2.9. Tipos de integridade de dados	19
2.2.10. Impondo a integridade de dados	20
2.2.11. Definindo constraints	20
2.2.12. Constraints NOT NULL	20
2.2.13. Constraints PRIMARY KEY	21
2.2.14. Constraints DEFAULT	22
2.2.15. Constraints CHECK	23
2.2.16. Constraints UNIQUE	23
2.2.17. Constraints FOREIGN KEY	24
2.2.18. Integridade referencial em cascata	25
2.2.19. Removendo uma constraint	26
2.2.20. Desativando uma constraint	26
2.3. DCL – Linguagem de Controle de dados	27
2.3.1. GRANT	27
2.3.2. REVOKE	27
2.4. Comandos Úteis	28
2.4.1. DBCC CHECKIDENT	28
2.5. Views	29
2.6. Estruturas de Comandos Transact-SQL	31
2.7. Triggers (Gatilho)	32
2.7.1. Exemplo 1: Caixa (Devmedia)	33
2.7.2. Exemplo 2	35
2.7.3. Exemplo 3	36
2.7.4. Exemplo 4	37
2.7.5. Cadastrar erros no SQLServer	38
2.8. Stored Procedure	38
2.8.1. Vantagens na utilização de SPs	39
2.8.2. Exemplo 1	39
2.8.3. Exemplo 2	39
2.8.4. Exemplo de Store Procedure com valor de retorno	40
2.9. Funções	41
2.10. Cursores	43
2.10.1. Cursor: Exemplo 1	45
2.11. Tabelas temporárias	45
2.12. Exercício	46
3. ÍNDICE – CONCEITOS	47
3.1. Tipos de índices Ordenados	49
3.2. Índices Primários	49
3.3. Índices clustering	50
3.4. Índices Secundários	51
3.5. Comando de criação de índice	53
4. OTIMIZAÇÃO DE CONSULTA	54
4.1. Fluxo de processamento de uma consulta	54
4.2. Plano de Execução	55
4.3. Símbolos utilizados na análise dos índices no SQL	55
5. CONTROLE DE TRANSAÇÃO	59
5.1. Transação	59
5.1.1. Propriedades da transação	59
5.1.1. Exemplo 1	60
5.1.2. Exemplo 2	60
6. ADMINISTRAÇÃO DO BANCO DE DADOS	62
6.1. Administração do banco de Dados	62
6.2. Banco de Dados Centralizados x Banco de Dados Distribuídos	63
6.3. Segurança	65
6.4. Backup / Recovery	68
6.4.1. Best Practices for Scheduling Differential Database Backups	68
6.4.2. Observações sobre Backup:	72
6.4.3. Observações sobre Restore:	72
6.4.4. Recovery Models	73
6.4.5. Copy-only backups	74
6.4.6. Differential backups	74
6.4.7. Comandos Transact SQL – Backup complete	75
6.5. Disponibilidade de Banco de Dados	76
6.6. Auditoria em Bancos de Dados	81
INTRODUÇÃO / REVISÃO
Processo de Modelagem
Objetivos do Modelo de Dados
Representar um ambiente observado;
Servir de instrumento para comunicação;
Favorecer o processo de verificação e validação;
Capturar aspectos de relacionamentos entre os objetos observados;
Servir como referencial para a geração de estruturas de dados;
Estabelecer conceitos únicos a partir de várias.
Execução
Observação dos objetos: de acordo com a definição de abrangência e de detalhamento;
O entendimento dos conceitos: para que um objeto possa ser representado no modelo ele deve ser primeiramente: identificado, conceituado, entendido e assimilado.
A representação dos objetos: após a identificação dos objetos, suas características relacionamentos e comportamentos, aplica-se a técnica de modelagem.
A verificação de fidelidade e coerência: verificar se o modelo gerado representa de forma fiel e coerente o que foi levantado anteriormente. Caso existam falhas ou anomalias, verificar se estas se derivam por conceitos mal formados, pontos de vistas equivocados, falha na concepção ou aplicação errada de técnica de representação.
Validação: procure criticar e ser o mais rigoroso possível com o seu modelo. Os usuários envolvidos no processo precisam ter conhecimento das técnicas de modelagem de dados, para poderem ajudar na validação.
Níveis de Modelagem
Partindo-se de um mundo observado, composto por seus objetos e relacionamentos, poderemos definir um modelo independente de tecnologia (relacional, rede, hierárquica). Esse modelo poderá ser derivado para um modelo lógico que por sua vez será dependente de modelos físicos de implementação. A cada um desses níveis de modelagem serão associadas técnicas de representação gráfica e métodos de especificação de esquema.
Fonte: http://www.sirmacstronger.eti.br/bd/introdbd.php
Modelo Conceitual de Dados (MCD)
Aquele em que os objetos, suas características e relacionamentos têm a representação fiel ao ambiente observado, independentemente de quaisquer limitações impostas por tecnologias, técnicas de implementação ou dispositivos físicos. Separa o problema de modelagem do problema de implementação do modelo em um tipo de SGBD específico. Permite abstrair e compreender melhor o ambiente observado. Deve determinar:
Quais são as entidades e os relacionamentos no empreendimento?
Que informações a respeito dessas entidades e desses relacionamentos deverão ser armazenadas no BD? 
Quais são as restrições de integridade ou regras de negócios que devem ser levadas em consideração?
Um esquema do BD pode ser representado por um diagrama de Entidade-Relacionamento.
Modelo Lógico de Dados (MLD)
Aquele em que os objetos, suas características e relacionamentos têm a representação de acordo com as regras de implementação e limitações impostas por algum tipo de tecnologia. Essa representação é independente dos dispositivos ou meios de armazenamento físico das estruturas de dados por ela definidas. É necessário escolher um SGBD para implementar nosso projeto de BD, e converter o projeto conceitual do BD em um esquema de BD do modelo de dados do SGBD. Analisar as coleções de relações em nosso esquema de BD relacional para identificar potenciais problemas, e refiná-los. Esta fase pode ser guiada por algumas teorias como, por exemplo, a normalização.
Modelo Físico de Dados (MFD)
Aquele em que a representação dos objetos é feita sob o foco do nível físico de implementação das ocorrências, ou instâncias das entidades e seus relacionamentos. O conhecimento do modo físico de implementação das estruturas de dados é ponto básico para o domínio desse tipo de modelo. Depende especificamente de cada SGBD. Deve-se, identificar trabalhos que o BD tem que suportar e refinar o projeto de BD para garantir critérios de desempenho, como a criação de índices para tabelas.
Requisitos
Definição
Definem o que é solicitado ao sistema fazer e com quais limitações ele é requisitado a operar
Condição ou capacitação que deve ser contemplada pelo software, necessitada pelo usuário para resolver um problema ou alcançar um objetivo.
Requisito é algo (verificável) que o produto deve fazer ou alguma qualidade (mensurável) que deve apresentar e que (pelo seu risco de comprometer o sucesso do projeto) deve ser testado.
Tipos de Requisitos.
Requisitos do usuário: declarações em linguagem natural e também em diagramas sobre as funções que o sistema deve fornecer e as restrições sob as quais deve operar.
Requisitos do sistema: um documento estruturado que estabelece detalhadamente as funções e as restrições de sistema. Escrito como um contrato entre o cliente e o desenvolvedordo software.
Especificação do software: uma descrição detalhada do software que serve como base para projeto e a implementação. Escrito para os desenvolvedores.
Requisitos Funcionais / não funcionais
Os Requisitos Funcionais são as descrições das diversas operações que clientes e usuários querem (conhecidos também como requisitos de usuário), ou precisam que sejam realizadas pelo sistema. As facilidades que deve oferecer a seus usuários;
Os Requisitos Não Funcionais são restrições ou atributos de qualidade de um software ou de um processo de desenvolvimento de software. É necessário que estes requisitos sejam considerados na fase inicial do processo de desenvolvimento de software. 
Restrições sobre a operação;
Como o sistema deve se comportar;
Restrições sobre o desenvolvimento;
Como uma facilidade deve ser implementada;
Classificando os Requisitos Funcionais
Requisitos de Negócio (RqN) ou “Business Why” 
Fornecem a razão para o desenvolvimento de sistemas e software. 
São derivados a partir dos objetivos de negócio. 
É o que precisa ser entregue para agregar valor ao negócio do Cliente. 
Focado no entendimento do “POR QUE” e “PARA QUE” o projeto/sistema é requerido e como contribuirá para os objetivos do negócio e do usuário. 
Declaração escrita em linguagem de negócio contendo requisitos de alto-nível. 
Devem ser escritos do ponto de vista do solicitante. 
São a base para os requisitos de sistemas. 
As seguintes perguntas devem ser respondidas:
Por que vamos gastar dinheiro com este projeto?
O que o nosso negócio ganha com isso?
O que devemos entregar para gerar valor ao negócio?
Requisitos de Processo (RqP) ou “Process What” 
descrevem “o quê” deve ser disponibilizado pelo processo/sistema/software para atender ao requisito de negócio.
Definem as funcionalidades esperadas sob o ponto de vista de funções. 
Descrevem um comportamento que deve ser percebido pelo usuário.
Traduzem o que o sistema/produto precisa fazer para atender as necessidades dos usuários relacionados a um problema de negócio 
Determinam “O QUE” é requerido (sem a preocupação do “COMO” será feito).
Requisitos Técnicos (RqT) ou “System How”, também encontrados como requisitos de implementação, 
São derivados dos requisitos de sistemas. 
Focado no “COMO” os requisitos serão construídos e disponibilizados. 
Escritos sob a ótica dos desenvolvedores.
Classificação de requisitos não funcionais
Requisitos do produto: requisito que especificam que o produto entregue se deve comportar de uma determinada forma, p. ex., velocidade de execução, fiabilidade, etc.
Requisitos organizacionais: requisitos que são uma conseqüência das políticas e procedimentos organizacionais, p. ex. processos standard utilizados, requisitos de implementação
Requisitos externos: requisitos que se devem a fatores que são externos ao sistema e ao seu processo de desenvolvimento, p. ex. requisitos de interoperacionalidade, requisitos legais, etc.
Classificação dos requisites segundo Ian Sommerville
Especificação de Requisitos
Pontos a serem definidos antes do trabalho da modelagem:
Abrangência
Nível de detalhamento
Tempo para a produção do modelo
Recursos disponíveis
Análise de Requisitos
Processo informal que envolve discussões entre grupos de stakeholders
Entender quais dados serão armazenados no BD
Quais aplicações têm que ser construídas
Quais operações são mais freqüentes
Projeto Conceitual do BD
As informações colhidas na análise de requisitos são usadas para desenvolver uma descrição em alto nível dos dados a serem armazenados no BD (modelo ER)
Documento de Requisitos
O documento de requisitos é um documento formal utilizado para comunicar os requisitos aos clientes, engenheiros e gestores.
O documento de requisitos é a declaração oficial do que é exigido dos desenvolvedores de sistema;
Deve incluir os requisitos de usuário e uma especificação detalhada dos requisitos de sistema;
NÃO é um documento de projeto. Deve esclarecer o que o sistema deve fazer e não como deve ser feito.
O documento de requisitos descreve: 
Os serviços e funções que o sistema deve proporcionar;
Especificar o comportamento externo do sistema;
As restrições sob os quais o sistema deve operar;
Propriedades genéricas i.e. restrições relativas às definições de outros sistemas com os quais o sistema deverá ficar integrado; 
Informação acerca do domínio de aplicação do sistema, p. ex. como realizar tipos particulares de cálculos;
Restrições do processo utilizado para desenvolver o sistema - descrições do hardware no qual correrá o sistema;
Registrar a estratégia sobre o ciclo de vida do sistema.
Usuários do Documento de Requisitos
Clientes do sistema: especifica os requisitos, permitindo-lhes a leitura para verificar se respondem às suas necessidades.
Gestores de Projeto: utilizam o documento de requisitos para estimar os custos do sistema e planejar o processo de desenvolvimento.
Engenheiros de sistema: utilizam os requisitos para compreender o sistema em desenvolvimento .
Engenheiro de teste do sistema: utilizam os requisitos para desenvolver testes de validação para o sistema.
Engenheiros de manutenção do sistema: utilizam os requisitos para ajuda de compreensão do sistema.
Estrutura do Documento de Requisitos
O standard IEEE/ANSI 830-1993 propõe uma estrutura para o documento de requisitos de software.
1 Introdução
1.1 Propósito do documento de requisitos
1.2 Escopo do produto
1.3 Definições, acrónimos e abreviaturas
1.4 Referências
1.5 Visão geral do restante documento
2. Descrição geral
2.1 Perspectiva do produto
2.2 Funções do produto
2.3 Características do usuário
2.4 Restrições gerais
2.5 Pressupostos e dependências
3. Requisitos específicos
Cobrindo requisitos funcionais, não funcionais e de interface
4. Anexo / Apêndices 
LINGUAGEM SQL
SQL (Structured Query Language):
Linguagem padrão para lidar com BD Relacionais
Originalmente desenvolvida no Laboratório de Pesquisas da IBM San Jose, no início da década de 70.
Originalmente chamada de SEQUEL.
Embora as versões do produto SQL difiram em diversos detalhes de linguagem, as diferenças são, na maioria das vezes, secundárias. 
	DML (Data Manipulation Language)
	DDL (Data Definition Language)
	DCL (Data Control Language)
	SELECT; 	 
INSERT; 
UPDATE;	 
DELETE.
	CREATE;
ALTER; 
DROP.
	GRANT
REVOKE.
DML – Linguagem de manipulação de dados
SELECT
SELECT   [DISTINCT] {*, coluna [pseudónimo], ... }
FROM     tabela
WHERE    condição(ões)
GROUP BY coluna(s)
HAVING   condição(ões)
ORDER BY {coluna, expr} [ASC|DESC]; 
SELECT - seleciona pelo menos uma coluna. A sua ordem de execução segue a seqüências das cláusulas apresentadas acima; 
Pseudônimo (alias) - usado como nome alternativo em colunas do select; 
* - designa todas as colunas; 
DISTINCT - utilizado para eliminar linhas duplicadas (se as houver); 
FROM tabela - designa a tabela de onde provêm as colunas; 
WHERE condição - limita a consulta às linhas que satisfazem a condição; 
GROUP BY - recebe as linhas selecionadas anteriormente (com ou sem WHERE) e agrupa por coluna. Se forem indicadas várias colunas usa a segunda para desempatar a primeira (faz quebra); 
HAVING - só utilizada quando há GROUP BY. Permite excluir grupos formados por GROUP BY 
ORDER BY - recebe as linhas selecionadas anteriormente e ordena por uma ou mais colunas. Sempre a ultima cláusula; 
ASC - ordem crescente de ordenação; é a opção por omissão; 
DESC - ordem decrescente de ordenação; 
	OPERADOR
	SIGNIFICADO
	=
	Igual a
	!=
<>
	Diferente
	>
	Maior que
	>=
	Maior ou igual a
	<
	Menor que
	<=
	Menor ou igual a
	Between val1 and val2
	Intervalo fechado compreendido entre os dois valores val1 e val2
	Not Between val1 and val2
	Não pertence ao intervalo
	IN (val1, val2, val3, val4)
	Pertence à lista de valores val1, val2, val3, val4
	NOT IN (val1, val2, val3,val4)
	Não pertence à lista de valores
	[NOT] EXISTS (true, false)
	Verifica a existência em um conjunto de valores.
	LIKE 
	Lista de acordo com a cadeia de caracter que satisfaz a condição. Utiliza-se para montar a condição os operadores % e _
	NOT LIKE
	Não pertence à lista de acordo com a cadeia de caracter.
	IS NULL
	Lista a partir de campo com o valor nulo
	IS NOT NULL
	Lista a partir de campo que não tem valor nulo
	And, OR
	Operadores lógicos para ligar condições
	+, -, *, / 
	Expressões aritméticas 
Operações Matemáticas
Levando em consideração que as relações podem ser tratadas como conjuntos, podemos então aplicar um conjunto de operações matemáticas sobre as mesmas. Estas operações são: união, interseção e diferença. Este conjunto de operações não é unário, ou seja, podem ser aplicadas sobre mais de uma tabela, porém, existe a necessidade das tabelas possuírem tuplas exatamente do mesmo tipo. Estas operações podem ser definidas da seguinte forma:
União (Union) - o resultado desta operação representada por R  S é uma relação T que inclui todas as tuplas que se encontram em R e todas as tuplas que se encontram em S;
Interseção (Intersect) - o resultado desta operação representada por R  S é uma relação T que inclui as tuplas que se encontram em R e em S ao mesmo tempo;
Diferença (Except) - o resultado desta operação representada por R - S é uma relação T que inclui todas as tuplas que estão em R, mas que não estão em S.
Produto Cartesiano
O produto cartesiano é uma operação binária que combina todas as tuplas de duas relações. Diferente da operação união, o produto cartesiano não exige que as tuplas das relações possuam exatamente o mesmo tipo. O produto cartesiano permite então a consulta entre relações relacionadas utilizando uma condição de seleção apropriada. O resultado de um produto cartesiano é uma nova relação formada pela combinação das tuplas das relações sobre as quais se aplicou a operação. O formato geral do produto cartesiano entre duas relações R e S é: R x S.
É utilizada quando se deseja consultar dados a partir de uma ou mais tabelas
SELECT tabela1.coluna, tabela2.coluna FROM tabela1 
[INNER | { {LEFT | RIGHT | FULL } [OUTER] }
JOIN tabela2 ON tabela1.coluna = tabela2.coluna
A palavra-chave JOIN especifica quais tabelas serão associadas e como associá-las
A palavra-chave ON especifica as colunas que as tabelas têm em comum 	
Subconsultas
A subconsulta (consulta interna) é executada uma vez antes da consulta principal. 
O resultado da subconsulta é utilizado pela consulta principal (consulta externa).
SELECT lista_colunas 
FROM tabela [,tabela]
WHERE expressão operador (SELECT lista_colunas FROM tabela [,tabela])
Funções de grupo
Operam sobre um conjunto de linhas para fornecer um resultado por grupo
AVG - retorna a média aritmética de um conjunto de valores
SUM - retorna a soma de um conjunto de valores 
MIN - retorna o valor mínimo de um conjunto de valo
MAX - retorna o valor máximo de um conjunto de valores
COUNT(*) - retorna o número total de linhas retornadas
COUNT (Expressão) - retorna o número total de linhas não nulas retornadas
COUNT (ALL) - Considera todos os valores nos cálculos, inclusive os repetidos. Esta é a condição default
COUNT (DISTINCT) - Considera somente valores distintos nos cálculos
Comando básico
SELECT 	[coluna,] função_grupo(expressão) FROM tabela
[WHERE 	condição]
[GROUP BY coluna]
[HAVING condição]
Comando Insert
Adicione novas linhas em uma tabela usando a instrução INSERT
Utilize a seguinte sintaxe para inserir uma linha por vez
INSERT INTO tabela [{coluna [, coluna...]}]
VALUES {valor [, valor...]} 
Adicione uma nova linha contendo valores para cada coluna
Liste na cláusula VALUES, os valores dos dados a serem inseridos nas colunas da tabela
Liste opcionalmente as colunas na cláusula INSERT
Valores de data e de caracteres devem ser colocados entre aspas 
Pode-se também utilizar uma subconsulta na instrução INSERT
Não especifique a cláusula VALUES
A quantidade e tipo de dados das colunas especificadas na cláusula INSERT devem ser compatíveis com as colunas na subconsulta
INSERT INTO mineiros (codigo, nome, cidade)
(SELECT codcliente, nome, cidade FROM cliente
 WHERE estado = 'MG')
Comando Update
Altere linhas existentes em uma tabela utilizando a instrução UPDATE
Atualize mais de uma linha por vez, se necessário
UPDATE tabela
SET coluna = valor [, coluna = valor, ...]
[FROM tabela]
[WHERE condicao]
Especifique a tabela a ser atualizada na cláusula UPDATE
Especifique as colunas e os seus novos valores na cláusula SET
Se necessário, especifique na cláusula WHERE as condições para restrição das linhas a atualizar. Caso esta cláusula seja omitida, a tabela toda será alterada
Caso exista necessidade de se utilizar outra(s) tabela(s) para restringir as linhas a serem atualizadas, especifique esta(s) tabela(s) na cláusula FROM
Comando Delete
Remova linhas de uma tabela utilizando a instrução DELETE
Remova mais de uma linha se necessário
DELETE [FROM] tabela
[FROM tabela]
[WHERE condicao]
Especifique na cláusula DELETE a tabela de onde as linhas serão removidas
Se necessário, especifique na cláusula WHERE as condições para restrição das linhas a remover. Caso esta cláusula seja omitida, todas as linhas da tabela serão removidas. Entretanto, a estrutura da tabela será mantida
Caso exista necessidade de se utilizar outra(s) tabela(s) para restringir as linhas a serem excluídas, especifique esta(s) tabela(s) na cláusula FROM 	
DDL – Linguagem de definição de dados (BASE SQLServer)
Tabela
Unidade básica de armazenamento de um banco de dados relacional.
É composta por uma ou mais colunas (campos)
Cada linha armazena dados sobre uma instância da entidade representada através da tabela
Convenções para nomes de tabelas
Nomes podem conter até 128 caracteres
Devem começar com uma letra, _ ou #
Após o primeiro caractere, podem incluir letras, números ou símbolos _, @ ou #
Não deve duplicar o nome de outro objeto de propriedade do mesmo usuário
Não deve ser uma palavra reservada do SQL Server
Criando uma tabela
CREATE TABLE [database_name.[owner].|owner.]table_name 
(column_name data_type [DEFAULT expressao] , ...)
Na cláusula CREATE TABLE, especifique o nome da tabela
Em seguida, especifique os nomes das colunas, seus tipos de dados e, caso exista, seu valor default 
Tipo de Dados 
Numéricos 
Inteiros: int, smallint, tinyint, bigint, bit
Valores decimais precisos: decimal (p, [s]) ou dec (p, [s]), numeric(p, [s]), money, smallmoney 
Valores de ponto flutuante: real, float[(p)] 
Datas
Date
Time
Datetime 
Smalldatetime 
Caracteres
Tamanho fixo: char(n), nchar(n) 
Tamanho variável: varchar(n), nvarchar(n)
Binários
binary[(n)] 
varbinary[(n)] 
Imagem e texto
varbinary(max)
varchar(max)
Tipos de dados definidos pelo usuário
Baseados nos tipos de dados do sistema
Exemplo
Create table [TB_ModeloCarro]
(
	[ID_Modelo] Integer Identity(1,1) NOT NULL,
	[NomeModelo] Varchar(100) NOT NULL,
Primary Key ([ID_Modelo])
)
Ou
Create table [TB_ModeloCarro]
(
	[ID_Modelo] int Identity(1,1) NOT NULL Primary Key,
	[NomeModelo] Varchar(100) NOT NULL,
)
Create table [TB_Fila]
(
	[ID_Pessoa] Integer NOT NULL,
	[ID_ZonaGeografica] Integer NOT NULL,
	[DataHoraInicioFila] Datetime NOT NULL,
	[DataHoraFimFila] Datetime NULL,
	[ID_TipoMovimentacaoFila] Integer NOT NULL,
Primary Key ([ID_Pessoa],[ID_ZonaGeografica])
) 
go
Alterando a estrutura de uma tabela
Utilize a instrução ALTER TABLE para:
Adicionar uma nova coluna
Alterar o tipo de dados e precisão de uma coluna 
Remover colunas de uma tabela
ALTER TABLE table_name 
ADD column_name data_type [DEFAULT expressao] , ...
ALTER TABLE table_name 
ALTER COLUMN column_name new_data_type 
ALTER TABLE table_name 
DROP COLUMN column_name,Utilize a cláusula ADD para adicionar colunas a uma tabela
ALTER TABLE emp 
ADD data_nascimento DATETIME,
	nome_pai		VARCHAR(50),	
 	nome_mae		VARCHAR(50)	
As novas colunas tornam-se as últimas colunas da tabela
Utilize a cláusula ALTER COLUMN para alterar o tipo de dados e a precisão de uma coluna
ALTER TABLE emp 
ALTER COLUMN nome_pai VARCHAR(60)
Somente uma coluna pode ser alterada por vez
A precisão de uma coluna só pode ser diminuída se a tabela estiver vazia ou se o tamanho de todos os dados da coluna for menor ou igual à nova precisão
O tipo de dados de uma coluna só poderá ser alterado se a tabela estiver vazia ou se todos os dados da coluna forem compatíveis com o novo tipo
Utilize a cláusula DROP COLUMN remover colunas de uma tabela
ALTER TABLE emp 
DROP COLUMN nome_pai, nome_mae 
Removendo uma tabela
Utilize a cláusula DROP TABLE para remover uma tabela do banco de dados
Todos os dados e a estrutura da tabela serão removidos
A tabela não poderá ser removida se estiver sendo referenciada por outra tabela através da restrição de FOREIGN KEY. A restrição deverá ser removida antes.
DROP TABLE nomeTabela
Constraints
As constraints impõem regras no nível da tabela.
As constraints evitam que uma linha da tabela seja excluída se houver dependências.
Tipos de integridade de dados
Impondo a integridade de dados
Integridade de dados declarativa 
Critérios estabelecidos em definições de objeto 
O SQL Server impõe automaticamente 
Implemente a integridade declarativa usando constraints e defaults
Integridade de dados procedural
Critérios definidos em script
Triggers e stored procedures 
Definindo constraints
	Tipos de Integridade
	Tipo de Constraints
	Domínio
	Default
	
	Check
	Entidade
	Primary Key
	
	Unique
	Referencial
	Foreign Key
	
	Check
Crie uma restrição:
No momento em que a tabela for criada (CREATE TABLE)
Depois que a tabela tiver sido criada (ALTER TABLE)
É possível adicionar constraints a uma tabela com dados existentes 
É possível incluir constraints em uma única ou em várias colunas 
Única coluna, chamada de constraint em nível de coluna 
Várias colunas, chamada de constraint em nível de tabela 
Tipos de Constraints
Constraints NOT NULL
Constraints PRIMARY KEY
Constraints DEFAULT
Constraints CHECK
Constraints UNIQUE
Constraints FOREIGN KEY
Integridade referencial em cascata 
Constraints NOT NULL
Assegura que os valores nulos não sejam permitidos na coluna
Definido em nível de coluna 
Create table [TB_Corrida]
(
	[ID_Corrida] Integer Identity(1,1) NOT NULL,
	[ID_SituacaoCorrida] Integer NOT NULL,
	[ID_Chamado] Integer NOT NULL,
	[DataHoraInicioCorrida] Datetime NOT NULL,
	[DataHoraRecepcaoPassageiro] Integer NULL,
	[DataHoraFimCorrida] Datetime NULL,
	[Kilometragem] Decimal(10,2),
Primary Key ([ID_Corrida])
) 
	Ou
alter table TB_Corrida
 alter column Kilometragem Decimal (10,2) not null
Constraints PRIMARY KEY
Apenas uma restrição PRIMARY KEY por tabela.
Os valores devem ser exclusivos e não podem ser nulos.
Cria um índice exclusivo nas colunas especificadas.
Create table [TB_ModeloCarro]
(
	[ID_Modelo] Integer Identity(1,1) NOT NULL,
	[NomeModelo] Varchar(100) NOT NULL),
Primary Key ([ID_Modelo])
)
	
	Ou
Create table [TB_ModeloCarro]
(
	[ID_Modelo] Integer Primary key Identity(1,1) NOT NULL ,
	[NomeModelo] Varchar(100) NOT NULL 
)
	Ou
Create table [TB_ModeloCarro]
(
	[ID_Modelo] Integer Identity(1,1) NOT NULL ,
	[NomeModelo] Varchar(100) NOT NULL 
 constraint PK_ModeloCarro PRIMARY KEY (ID_Modelo) 
)
Criando uma constraint após a criação de uma tabela – a constraint somente é criada se o conteúdo do campo respeitar a regra de integridade da constraint
alter table TB_ModeloCarro
 add Constraint PK_ModeloCarro Primary key (ID_Modelo)
Chave composta - somente nível de tabela
Create table [TB_ControleChamada]
(
	[ID_Chamado] Integer NOT NULL,
	[ID_Pessoa] Integer NOT NULL,
	[ID_ZonaGeografica] Integer NOT NULL,
	[DataHoraControleChamada] Datetime NOT NULL,
	[ID_TipoAcaoControle] Integer NOT NULL,
	[DataHoraRetornoMotorista] Datetime NULL,
	[Observacao] Text NULL,
Primary Key ([ID_Chamado],[ID_Pessoa],[ID_ZonaGeografica],[DataHoraControleChamada])
)
	Ou
Create table [TB_ControleChamada]
(
	[ID_Chamado] Integer NOT NULL,
	[ID_Pessoa] Integer NOT NULL,
	[ID_ZonaGeografica] Integer NOT NULL,
	[DataHoraControleChamada] Datetime NOT NULL,
	[ID_TipoAcaoControle] Integer NOT NULL,
	[DataHoraRetornoMotorista] Datetime NULL,
	[Observacao] Text NULL,
CONSTRAINT PK_ControleChamada
 Primary Key ([ID_Chamado],[ID_Pessoa],[ID_ZonaGeografica],[DataHoraControleChamada])
)
Constraints DEFAULT
Aplicam-se apenas a instruções INSERT
Apenas uma restrição DEFAULT por coluna 
Permitem alguns valores fornecidos pelo sistema 
Create table [TB_Fila]
(
	[ID_Pessoa] Integer NOT NULL,
	[ID_ZonaGeografica] Integer NOT NULL,
	[DataHoraInicioFila] Datetime Constraint [DF_DataHoraInicioFila] Default getdate() NOT NULL,
	[DataHoraFimFila] Datetime NULL,
	[ID_TipoMovimentacaoFila] Integer NOT NULL,
Primary Key ([ID_Pessoa],[ID_ZonaGeografica])
) 
go
ou
Create table [TB_Fila]
(
	[ID_Pessoa] Integer NOT NULL,
	[ID_ZonaGeografica] Integer NOT NULL,
	[DataHoraInicioFila] Datetime Default getdate() NOT NULL,
	[DataHoraFimFila] Datetime NULL,
	[ID_TipoMovimentacaoFila] Integer NOT NULL,
Primary Key ([ID_Pessoa],[ID_ZonaGeografica])
) 
go
			ou
alter table tb_fila 
ADD constraint DF_DataHoraInicioFila Default getdate() 
for DataHoraInicioFila
Constraints CHECK
São usadas com as instruções INSERT e UPDATE
Podem fazer referência a outras colunas na mesma tabela 
Não podem conter subconsultas 
CREATE TABLE TB_Pessoa 
(Id_Pessoa INT,
 Nome VARCHAR(100),
 DataNascimento DATETIME CONSTRAINT CK_DataNascimento CHECK 
(DataNascimento > '01-01-1900' AND DataNascimento < getdate()))
		 OU
 CREATE TABLE TB_Pessoa 
(ID_Pessoa INT,
 Nome VARCHAR(100),
 DataNascimento DATETIME CHECK 
(DataNascimento > '01-01-1900' AND DataNascimento < getdate()))
ou
ALTER TABLE TB_Pessoa 
ADD CONSTRAINT CK_DATANASC 
CHECK (DataNascimento > '01-01-1900' AND DataNascimento < getdate())
Constraints UNIQUE
Permitem um valor nulo 
São permitidas várias restrições UNIQUE em uma tabela 
Imposta com um índice exclusivo 
Create table [TB_Bairro]
(
	[ID_Bairro] Integer Identity(1,1) NOT NULL,
	[NomeBairro] Varchar(100) NOT NULL UNIQUE,
	[ID_ZonaGeografica] Integer NOT NULL,
	[ID_Cidade] Integer NOT NULL,
Primary Key ([ID_Bairro])) 
		OU
Create table [TB_Bairro]
(
	[ID_Bairro] Integer Identity(1,1) NOT NULL,
	[NomeBairro] Varchar(100) NOT NULL, Unique (NomeBairro), 
	[ID_ZonaGeografica] Integer NOT NULL,
	[ID_Cidade] Integer NOT NULL,
Primary Key ([ID_Bairro])
 
) 
Definidas com uma ou mais colunas 
Create table [TB_Cidade]
(
	[ID_Cidade] Integer Identity(1,1) NOT NULL,
	[NomeCidade] Varchar(100) NOT NULL,
	[UF] Varchar(2) NOT NULL,
Primary Key ([ID_Cidade]),
CONSTRAINT UK_CIDADE UNIQUE (NomeCidade, UF)
) 
Go
Ou 
alter table tb_cidade
 add Constraint UK_Cidade unique (NomeCidade,UF)
Constraints FOREIGN KEY
Devem fazer referência a uma restrição PRIMARY KEY ou UNIQUE
Fornecem uma integridade referencial de uma ou várias colunas 
Não criam índices automaticamente 
Os usuários devem ter as permissões SELECT ou REFERENCES em tabelas referenciadas 
CREATE TABLE emp 
(empno INT,
 nome VARCHAR(100),
 deptno INT FOREIGN KEY REFERENCES dept (deptno), 	-- Nível de coluna
 datacontratacao DATETIME) 
					ou
CREATE TABLE emp 
(empno INT,
 nome VARCHAR(100),
 deptno INT, 
 datacontratacao DATETIME
 FOREIGN KEY (deptno) REFERENCES dept (deptno)) 	-- Nível de tabela
					ou
ALTER TABLE emp
ADD CONSTRAINT FK_EMP_DEPT 
FOREIGN KEY (deptno) REFERENCES dept (deptno)
Integridade referencialem cascata
FOREIGN KEY: Define a coluna na tabela filha no nível de restrição da tabela
REFERENCES: Identifica a tabela e a coluna na tabela mãe
ON DELETE CASCADE: Permite exclusão na tabela mãe e das linhas dependentes na tabela filha
ON UPDATE CASCADE: Permite alteração na tabela mãe e das linhas dependentes na tabela filha
CREATE TABLE emp 
(empno INT,
 nome VARCHAR(100),
 deptno INT, 
 datacontratacao DATETIME
 FOREIGN KEY (deptno) REFERENCES dept (deptno)
 ON DELETE CASCADE
 ON UPDATE CASCADE
) 			
			ou
ALTER TABLE emp
	ADD CONSTRAINT FK_EMP_DEPT 
		FOREIGN KEY (deptno) 
		REFERENCES dept (deptno)
			ON DELETE CASCADE
			ON UPDATE CASCADE
Removendo uma constraint
Remova uma constraint através de uma instrução 
ALTER TABLE tabela
DROP CONSTRAINT nome_constraint 
As constraints de PRIMARY KEY e de UNIQUE KEY só podem ser removidas se as mesmas não estiverem sendo referenciadas por nenhuma outra constraint de FOREIGN KEY
Para isto, remova primeiro a constraint de FOREIGN KEY
Removendo a constraint de PRIMARY KEY da tabela DEPT
Inicialmente, remova a constraint de FOREIGN KEY da tabela EMP
ALTER TABLE emp 
DROP CONSTRAINT FK_EMP_DEPT 
Em seguida, remova a constraint da tabela DEPT
ALTER TABLE dept 
DROP CONSTRAINT PK_DEPT 
Desativando uma constraint
Útil ao realizar processamento de operações em lote 
Quando não se deseja verificar os dados já existentes ao incluir uma constraint 
Ao definir uma constraint em uma tabela que já contenha dados, o SQL Server verifica os dados automaticamente para assegurar que eles atendam aos requisitos da constraint
É possível desativar a verificação da constraint nos dados existentes ao adicionar a constraint 
Só é possível desativar as constraints de CHECK e FOREIGN KEY
As outras devem ser removidas e inseridas novamente
Use a opção NOCHECK quando adicionar uma nova constraint 
Use a opção NOCHECK quando os dados existentes não forem alterados
Inclui constraint de chave estrangeira para a coluna GERENTE da tabela EMP, mas não verifica se dados existentes satisfazem à constraint 
ALTER TABLE emp 
WITH NOCHECK 
ADD CONSTRAINT FK_EMP
FOREIGN KEY (gerente)
REFERENCES emp (empno)
Use quando:
Os dados obedecem às restrições
Carregar novos dados que não obedecem às restrições
Desativando constraint de chave estrangeira para a coluna GERENTE da tabela EMP.
ALTER TABLE emp 
NOCHECK CONSTRAINT FK_EMP
Permite incluir empregados que não satisfazem à constraint de FOREIGN KEY
Para reativar a constraint, execute:
ALTER TABLE emp 
CHECK CONSTRAINT FK_EMP 
DCL – Linguagem de Controle de dados
A linguagem SQL inclui comandos para conceder e revogar privilégios. O conjunto de privilégios depende da versão SQL considerada. 
GRANT - autoriza ao usuário executar ou setar operações.
REVOKE - remove ou restringe a capacidade de um usuário de executar operações.
GRANT
O comando grant é usado para conferir autorização:
Grant <lista de autorizações> on <nome da relação> to <lista de usuários>;
Exemplos:
Concessão de Select sobre uma relação funcionários, para os usuários U1, U2: 
Grant select on funcionarios to U1, U2; 
A autorização update pode ser conferida a todos os campos da relação ou somente a alguns deles: 
Grant update(nome) on funcionarios to U1, U2; 
O privilégio insert também funciona como o update, sendo que os atributos restantes receberão o seu valor default; 
A instrução all privileges pode ser utilizada como forma abreviada para todos os privilégios permitidos: 
grant all privileges on emprestimo to U1; 
Por default, não é permitido ao usuário conceder seus privilégios a outros usuários, para que isto aconteça o DBA deve dar esta autorização: 
grant select on emprestimo to U1 with grant option; 
REVOKE
O comando revoke retira privilégios de usuários
revoke all privileges on emprestimo from U1; 
revoke select on emprestimo from U1, U2, U3 cascade; 
Exercício:
-- Criar um Login
CREATE LOGIN <login name> WITH PASSWORD = '<password>'
Exemplo:
Create Login Fernando with Password = 'fernando'
-- Criar um Banco de dados
Create DataBase Teste
go
use Teste
-- Criar uma tabela
Create table tb_teste (
 ID_Teste int,
 DescricaoTeste varchar (20)
)
-- Criar um usuário para um banco e associá-lo ao login.
CREATE USER [Fernando] FOR LOGIN [Fernando]
grant Select on tb_teste to Fernando
grant Insert on tb_teste to Fernando
grant update on tb_teste to Fernando
grant delete on tb_teste to Fernando
revoke select on tb_teste to Fernando
Comandos Úteis
DBCC CHECKIDENT
Disponível em https://msdn.microsoft.com/pt-br/library/ms176057%28v=SQL.120%29.aspx
Verifica o valor de identidade atual da tabela especificada no SQL Server 2014 e, se necessário, altera o valor da identidade. Você também pode usar DBCC CHECKIDENT para definir manualmente um novo valor de identidade atual para a coluna de identidade. 
Sintaxe
DBCC CHECKIDENT 
 ( 
 table_name
 [, { NORESEED | { RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]
Argumentos 
table_name 
É o nome da tabela sobre a qual verificar o valor de identidade atual. A tabela especificada deve conter uma coluna de identidade. 
NORESEED 
Especifica que o valor de identidade atual não deve ser alterado. 
RESEED 
Especifica que o valor de identidade atual deve ser alterado. 
new_reseed_value 
O novo valor a ser usado como o valor atual da coluna de identidade. 
WITH NO_INFOMSGS 
Suprime todas as mensagens informativas. 
Exemplo: Forçando o valor de identidade atual para um novo valor
O exemplo a seguir força o valor de identidade atual na coluna AddressTypeID na tabela AddressType para um valor de 10. Como a tabela tem as linhas existentes, a próxima linha inserida usará 11 como o valor, isto é, o novo valor de incremento atual definido para o valor da coluna mais 1. 
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
Views 
View são instruções Select pré-definidas e armazenas no banco de dados. É possível acessar uma view utilizando-se de um comando select. A sintaxe é:
CREATE VIEW <view_name> AS <instrução_SELECT>
Exemplo:
create view vw_pessoa as
 select id_pessoa,nome from tb_pessoa
View com order by não é permitido, exceto se possuir o comando TOP.
Errado:
create view vw_pessoa as
 select id_pessoa,nome 
 from tb_pessoa
 order by id_pessoa
Msg 1033, Level 15, State 1, Procedure vw_pessoa, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Correto:
create view vw_pessoa as
 select top 5 id_pessoa,nome 
 from tb_pessoa
 order by id_pessoa
O comando Drop view <view_name> exclui uma view do banco de dados
Exemplo:
drop view vw_pessoa
Para acessar o conteúdo da view utiliza-se o comando Select.
Exemplo:
select nome from vw_pessoa
Uma view pode ser usada para atualizar o banco de dados, desde que obedeça as regras de integridade. 
Exemplo:
drop view vw_pessoa
go
create view vw_pessoa as
 select id_pessoa,nome, ID_PessoaSocioTitular , ID_TipoPessoa, DataNasc
 from tb_pessoa
go
insert vw_pessoa (nome, ID_PessoaSocioTitular, ID_TipoPessoa, DataNasc) 
values ('Nome Teste View', 0, 1, '2009-08-01')
Uma view pode ser resultado, por exemplo, de uma junção.
Exemplo:
create view vw_pessoa as
 select id_pessoa,nome, ID_PessoaSocioTitular , a.ID_TipoPessoa, DataNasc, b.DesctipoPessoa
 from tb_pessoa as a inner join 
 tb_tipopessoa as b on a.id_tipopessoa = b.id_tipopessoa
Estruturas de Comandos Transact-SQL
O SQL Server, em sua linguagem Transact SQL, introduz o suporte a comandos de controle de fluxo em comandos adhoc, comandos de lote, stored procedures e triggers.
Comandos básicos:
BEGIN … END: define um bloco de comandos
IF … ELSE: testa condições
RETURN: terminaa execução e, opcionalmente, retorna um valor inteiro
WAITFOR: suspende a execução até certo evento
WHILE: laços
BREAK: sai de um laço WHILE
CONTINUE: desvia para o teste do laço WHILE
CASE: pode ser utilizado em expressões SELECT para retornar valores condicionais
• Expressões padrão SQL ANSI/92
• Comentários: /* … */ ou -- (no início da linha)
• DECLARE: declaração de variáveis
• PRINT: imprime uma mensagem na tela
• RAISERROR: retorna uma mensagem de erro
• EXECUTE(comando): executa o comando SQL representado pela seqüência de caracteres comando.
Controle de Fluxo:
BEGIN ... END
BEGIN
comandos_sql
END
IF … ELSE
IF condições THEN
bloco_de_comandos
ELSE
bloco_de_comandos
RETURN
 RETURN ( [valor_de_retorno] )
WAITFOR
 WAITFOR {DELAY ‘duração’ | TIME ‘instante’}
WHILE
 WHILE condições
comandos_sql
BREAK
comandos_sql
CONTINUE
Variáveis:
Variáveis podem ser declaradas com DECLARE
DECLARE nome_da_variável tipo_da_variável
O nome das variáveis locais devem ser precedidos por ‘@’ e o nome das variáveis globais por ‘@@’.
Variáveis globais são pré-definidas pelo SQL Server e contém informações sobre sua execução e sobre a execução de aplicações.
Comandos de atribuição a variáveis locais devem ser realizados utilizando-se o comando SELECT ou SET.
Exemplo:
DECLARE @cont integer, @msg char(30)
SELECT @cont = 0
WHILE (@cont < 10)
BEGIN
SELECT @msg = 'Teste ' + LTRIM(STR(@cont))
PRINT @msg
SELECT @cont = @cont + 1
END
Triggers (Gatilho)
Uma trigger (gatilho) é um bloco de comandos Transact-SQL automaticamente executado quando um comando INSERT , DELETE ou UPDATE for executado em uma tabela do banco de dados.
As triggers (gatilhos) são usados para realizar tarefas relacionadas com validações, restrições de acesso, rotinas de segurança e consistência de dados. Dessa forma, esses controles deixam de ser executados pela aplicação e passam a ser executados por triggers.
As triggers são vinculadas a uma tabela, sendo que em uma mesma tabela podem ser criadas várias triggers. São algumas de suas características:
Permitem impor regras de negócios, por exemplo, mecanismos de validação envolvendo múltiplas tabelas;
Permitem impor regras de integridade de dados, quando as constraints não atendem às necessidades funcionais do aplicativo;
Permitem consultar outras tabelas e incluir instruções Transact-SQL complexas;
Permite tratamento de erros mais complexos e mensagens personalizadas para os aplicativos.
Existem três aspectos importantes de um gatilho, conhecidos como modelo evento-condição-ação:
Evento: uma alteração no banco de dados que ativa o gatilho;
Condição: uma consulta ou teste executado quando o gatilho é ativado; 
Ação: procedimentos executados quando o gatilho é ativado e a condição é verdadeira.
Para projetar um mecanismo de gatilho, deve-se:
Especificar as condições sob as quais o gatilho deve ser executado; 
Especificar as ações que devem ser executadas pelo gatilho.
Triggers podem acessar duas tabelas especiais (INSERTED e DELETED), montadas automaticamente pelo SQL Server durante a execução da ação, dependendo do evento, estas tabelas irão conter:
	Evento
	Conteúdo Tabela Inserted
	Conteúdo Tabela Deleted
	Insert
	Valores inseridos
	- (Sem efeito)
	Update
	Novos valores após modificação.
	Valores antigos.
	Delete
	- (Sem efeito)
	Valores removidos.
As tabelas inserted e deleted, embora sejam temporárias e residentes em memória, podem ser consultadas através do comando SELECT.
Comando:
CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{ 
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } 
        [ WITH APPEND ] 
        [ NOT FOR REPLICATION ] 
        AS 
        [ { IF UPDATE ( column ) 
            [ { AND | OR } UPDATE ( column ) ] 
                [ ...n ] 
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
                { comparison_operator } column_bitmask [ ...n ] 
        } ] 
        sql_statement [ ...n ] 
    } 
} 
ON Table - a tabela para o qual o trigger esta sendo criado 
AFTER - especifica que o gatilho DML é disparado apenas quando todas as operações especificadas na instrução SQL de gatilho são executadas com êxito. Todas as verificações de restrição e ações referenciais em cascata também devem obter êxito para que este gatilho seja disparado.
AFTER é o padrão quando FOR é a única palavra-chave especificada.
INSTEAD OF - Determina que a trigger será executada ao invés do comando de disparo do mesmo. 
[ DELETE ] [INSERT] [UPDATE] - indicam o tipo de ação que deve disparar o trigger. 
A lista a seguir compara gatilhos DML com restrições e identifica quando os gatilhos DML trazem mais benefícios que as restrições (constraints) (https://msdn.microsoft.com/pt-br/library/ms178110.aspx)
Os gatilhos DML podem fazer alterações em cascata através das tabelas relacionadas no banco de dados; no entanto, essas alterações podem ser executadas com mais eficiência utilizando restrições de integridade referencial em cascata.
Podem proteger contra operações mal-intencionadas ou incorretas do tipo INSERT, UPDATE, e DELETE, e fazer cumprir as outras restrições mais complexas do que aquelas definidas nas restrições CHECK.
Diferentemente das restrições CHECK, os gatilhos DML podem fazer referência a colunas em outras tabelas. Por exemplo, um gatilho pode usar um SELECT de outra tabela para comparar com os dados atualizados ou inseridos e para efetuar ações adicionais, como modificar os dados ou exibir uma mensagem de erro definida pelo usuário.
Podem avaliar o estado de uma tabela antes e depois da modificação dos dados e efetuar ações com base nessa diferença.
Vários gatilhos DML do mesmo tipo (INSERT, UPDATE, ou DELETE), em uma tabela, permitem que múltiplas ações diferentes ocorram em resposta à mesma instrução de modificação.
Restrições só podem comunicar erros através de mensagens de erro padronizadas do sistema. Se o aplicativo exigir ou beneficiar-se de mensagens personalizadas e tratamento de erros mais complexo, é necessário usar um gatilho
RAISERROR
Gera uma mensagem de erro e inicia o processamento de erros da sessão. RAISERROR pode referenciar uma mensagem de erro definida pelo usuário na exibição do catálogo sys.messages ou criar uma mensagem dinamicamente. A mensagem é retornada como uma mensagem de erro de servidor ao aplicativo de chamada ou a um bloco CATCH de uma construção TRY...CATCH.
Exemplo 1: Caixa (Devmedia)
Disponível em http://www.devmedia.com.br/triggers-no-sql-server-teoria-e-pratica-aplicada-em-uma-situacao-real/28194
Para exemplificar o uso de gatilhos, tomaremos como cenário uma certa aplicação financeira que contém um controle de caixa e efetua vendas. Sempre que forem registradas ou excluídas vendas, essas operações devem ser automaticamente refletidas na tabela de caixa, aumentando ou reduzindo o saldo.
Vamos então criar as tabelas que utilizaremos neste exemplo e inserir o primeiro registro no caixa.
Criando as tabelas do exemplo
CREATE TABLE CAIXA
(
	DATA			DATETIME,
	SALDO_INICIAL		DECIMAL(10,2),
	SALDO_FINAL		DECIMAL(10,2)
)
GO
INSERT INTO CAIXA
VALUES (CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 103)), 100, 100)
GO
CREATE TABLE VENDAS
(
	DATA	DATETIME,
	CODIGO	INT,
	VALOR	DECIMAL(10,2)
)
GO
Por lógica, o saldo final do caixa começa igual ao saldo inicial.
Criemos então a primeira trigger sobre a tabela de vendas, que irá aumentar o saldo final do caixa na data da venda quando uma venda for inserida.
Trigger no insert na tabela de vendas
CREATE TRIGGER TGR_VENDAS_AI
ON VENDAS
FOR INSERT
AS
BEGIN
	DECLARE
	@VALOR	DECIMAL(10,2),
	@DATA	DATETIME
	SELECT @DATA = DATA, @VALOR = VALOR FROM INSERTED
	UPDATE CAIXA SET SALDO_FINAL = SALDO_FINAL + @VALOR
	WHERE DATA = @DATA
END
GO
Agora precisamos criar uma trigger para a instrução de delete, que irá devolver o valor ao caixa quando uma vendafor excluída.
Trigger no delete na tabela vendas
CREATE TRIGGER TGR_VENDAS_AD
ON VENDAS
FOR DELETE
AS
BEGIN
	DECLARE
	@VALOR	DECIMAL(10,2),
	@DATA	DATETIME
	SELECT @DATA = DATA, @VALOR = VALOR FROM DELETED
	UPDATE CAIXA SET SALDO_FINAL = SALDO_FINAL - @VALOR
	WHERE DATA = @DATA
END
GO
Com este exemplo bastante simples é possível perceber um ponto muito importante da utilização de triggers para automatização de certas ações. Por exemplo, o programador responsável por esta parte do sistema poderia optar, antes de ler este artigo, por atualizar a tabela de caixa manualmente após cada operação na tabela vendas, utilizando sua linguagem de programação de preferência. Agora, ele apenas precisará se preocupar com o registro e cancelamento da venda, pois a atualização da tabela de caixa será feita automaticamente pelo próprio banco de dados.
Com isso, o sistema em si, ou seja, o aplicativo, tende a ficar mais leve, pois parte da responsabilidade de execução de algumas tarefas foi transferida para o servidor de banco de dados
Exemplo 2
Suponha que você queira indicar quando um cliente passa a ter saldo negativo: mostrando o nome do cliente e o saldo; 
create database ControleVendas
go
use ControleVendas
go
create table tb_cliente (
 ID_Cliente int not null primary key,
 NomeCliente varchar (100),
 Saldo money
)
go
create trigger TG_Cliente
 on TB_Cliente
 for insert, update as
 declare @Saldo money
 
 select @saldo = saldo from inserted
 if (@saldo < 0) 
 print 'Cliente com saldo menor que zero'
go
insert into tb_cliente (id_cliente, nomecliente, saldo) values
 (1,'Fernando',10)
go
select * from tb_cliente
go
insert into tb_cliente (id_cliente, nomecliente, saldo) values
 (2,'Julia',-10)
go
select * from tb_cliente
go
update tb_cliente set saldo = -20 where id_cliente = 1
go
select * from tb_cliente
go
update tb_cliente set saldo = 20 where id_cliente = 2
go
select * from tb_cliente
Exemplo 2:
Verificar na inserção e alteração da tabela de cobrança se a data de pagamento for informada e a mesma for maior que a data do vencimento, o sistema deverá calcular os juros.
Drop trigger tg_calcularjuros
go
create trigger tg_calcularjuros
 on tb_cobranca
 for insert, update as
 
 declare @DataVencimento datetime
 declare @DataPagamento datetime
 declare @Juros money
 declare @ValorJuros money
 declare @ValorCobranca money
 declare @NumeroCobranca int
 
 select @DataVencimento = DataVencimento, @DataPagamento = DataPagamento,
 @ValorCobranca = ValorCobranca,
 @NumeroCobranca = NumeroCobranca from inserted
 if (@DataPagamento <> '') 
 begin
 if (@DataPagamento > @DataVencimento)
 begin
 select @Juros = PercentualJuros from tb_parametros
 select @ValorJuros = @ValorCobranca * (@Juros / 100)
 if (@ValorJuros = 0) 
 begin
 print 'Valor do Juros igual a zeros'
 ROLLBACK tran 
 end
 else 
 begin 
		 update tb_cobranca set Juros = @ValorJuros
 where NumeroCobranca = @NumeroCobranca
 end
 end
 end
go
Exemplo 3
Essa trigger realiza uma validação no campo DataNascimento, impossibilitando que seu valor seja nulo se a pessoa é uma pessoa física.
create trigger TG_ValidaDataNascimento
	on Pessoa
	after insert, update
as
begin
	declare @IdTipoPessoa int
	declare @DataNascimento date
	select @IdTipoPessoa = IdTipoPessoa
		, @DataNascimento = DataNascimento
	from inserted
	if @IdTipoPessoa = 1 and @DataNascimento is null 
	begin
		rollback tran
		RAISERROR ('Data de nascimento é obrigatório para 
			Pessoa Física.',16 ,1)
	end
end
Exemplo 4
Essa trigger atualiza a carga horária do curso a partir da carga horária das disciplinas.
alter trigger TG_AtualizarCargaHorariaCurso
	on Disciplina
	after insert,update,delete
as
begin
	begin try
		declare @IdCurso smallint
		declare @CargaHorariaNova decimal(10,2)
		declare @CargaHorariaAntiga decimal (10,2)
		if (select count(*) 
			from inserted
			where CargaHoraria is not null) > 0
		begin
			select @CargaHorariaNova = CargaHoraria,
				@IdCurso = IdCurso
			from inserted
			update Curso 
			set CargaHoraria = CargaHoraria + @CargaHorariaNova
			where IdCurso = @IdCurso
		end
		if (select count(*) 
			from deleted
			where CargaHoraria is not null) > 0
		begin
			select @CargaHorariaAntiga = CargaHoraria,
				@IdCurso = IdCurso
			from deleted
			update Curso 
			set CargaHoraria = CargaHoraria -@CargaHorariaAntiga
			where IdCurso = @IdCurso
		end
	end try
begin catch
	rollback tran 
	raiserror('Erro ao atualizar a carga horária',16,1)
end catch
end
Cadastrar erros no SQLServer
sp_addmessage @msgnum = 50001, @severity = 16, 
 @msgtext = N'Favor manter o erro na tela e entrar em contato com o setor de TI, informe o número %s.', 
 @lang = 'us_english'
RAISERROR (50001, 16, 10, @Texto)
Stored Procedure
Stored procedures são trechos de códigos Transact SQL armazenados no banco de dados. Os procedimentos são semelhantes a procedimentos em outras linguagens de programação no sentido de que podem:
Aceitar parâmetros de entrada;
Conter instruções de programação que executam operações no banco de dados, inclusive chamar outros procedimentos;
Retornar um valor de status (apenas do tipo int) para indicar êxito ou falha.
As stored procedures são armazenadas em uma forma pré-processada. Após a execução de uma stored procedure, o código pré-processado definido em sua criação é recuperado do banco de dados e compilado, permanecendo em memória.
O SQL Server provê uma extensa biblioteca de stored procedures de sistemas, identificadas pelo prefixo “sp_”. Existem 4 categorias de stored procedures de sistema
Procedures de Catálogo: retornam informações sobre os objetos do banco de dados
Procedures Estendidas: permitem a execução de processos externos ao SQL Server
Procedures de Replicação de Dados: permitem o controle de bancos de dados distribuídos
Procedures do SQL Executive: permitem controlar e agendar eventos e alertas para serem executados automaticamente
Stored procedures de sistema podem ser executadas a partir de qualquer banco de dados.
Exemplos:
sp_databases: retorna informações sobre os bancos de dados
sp_tables: informações sobre tabelas
 nome_do_objeto: retorna informações sobre as colunas de uma tabela ou visão
sp_pkeys nome_da_tabela e sp_fkeys nome_da_tabela: informações sobre chaves primárias e estrangeiras
sp_stored_procedures: informações sobre as stored procedures 
sp_depends nome_do_objeto: mostra as dependências entre os objetos
sp_rename nome_do_objeto, novo_nome: altera o nome de um objeto
sp_help: retorna informações sobre o objeto especificado.
Stored procedures podem ser criadas através do comando
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name
[ { @parameter data_type }
[ OUT | OUTPUT | [READONLY]
]
AS { [ BEGIN ] sql_statement [ END ] }
procedure_name: nome do procedimento;
@parameter: um parâmetro declarado no procedimento. Especifique um nome de parâmetro usando o sinal “@” no primeiro caractere;
data_type: tipo de dado do parâmetro;
OUT e OUTPUT: indica que o parâmetro é um parâmetro de saída. Usado para retornar valores ao chamador do procedimento;
READONLY: indica que o parâmetro não pode ser modificado no corpo do 
procedimento (somente leitura);
AS: especifica as ações do procedimento.
OBSERVAÇÃO: A instrução SET NOCOUNT ON desativa as mensagens do SQL Server que são enviadas após a execução de comandos INSERT, UPDATE, DELETE e SELECT.
Vantagens na utilização de SPs
Separação dos códigos SQL da linguagem de programação: isso normalmente já não ocorre, pois são utilizados framewoks de mapeamento objeto-relacional, como o Entity Framework ou o Hibernate.
Modularidade: passamos a ter o procedimento divido das outras partes do software. Basta alterarmos as operações da SPpara termos as modificações por toda a aplicação e, portanto, encapsulando regras de negócio.
Criação de um repositório unificado dos códigos SQL.
Aumento de performance das aplicações, visto que ao ser executada pela primeira vez, uma SP é compilada e os resultados armazenados em cache.
Diminuição do tráfego da rede, já que o procedimento é executado no próprio banco de dados..
Poderíamos apontar como desvantagem o acoplamento a um SGBD, visto que se for necessário adotar um outro produto, as SPs devem ser reescritas.
Exemplo 1
-- Para criar uma SP
CREATE PROCEDURE SP_ListaPessoa AS
SELECT Nome, DataNasc
FROM TB_Pessoa
-- Para executar uma SP
exec SP_ListaPessoa
Para remover uma stored procedure, utilize o comando
DROP PROCEDURE
DROP PROCEDURE nome
Exemplo 2
Esse procedimento permite inserir dados na tabela pessoa e na tabela aluno ou 
professor dependendo do valor da variável “Tipo”.
alter procedure SP_CriarAlunoProfessor
	(@Nome as varchar(100),
	@CPFCNPJ as varchar(24),
	@DataNascimento date,
	@Usuario varchar(20),
	@Senha varchar(50),
	@Tipo char(1) ) 
as
begin
	declare @Mensagem varchar(max)
	set nocount on
	begin try
		insert into Pessoa(IdTipoPessoa,Nome,CPFCNPJ,DataNascimento,
		Usuario,Senha)
		values (1,@Nome,@CPFCNPJ,@DataNascimento,@Usuario,@Senha)
		if @Tipo = 'P'
		begin
			insert into Professor (IdPessoaProfessor)
			values (IDENT_CURRENT( 'Pessoa' ))
		end
		else if @Tipo = 'A'
		begin
			insert into Aluno (IdPessoaAluno)
			values (IDENT_CURRENT( 'Pessoa' ))
		end
	end try
	begin catch
		select @Mensagem = 'Erro ao salvar: '+ERROR_MESSAGE()
		raiserror(@Mensagem,16,1)
	end catch
end
Para executar um procedimento utilize o comando EXEC ou EXECUTE, e passe 
os parâmetros necessários para a execução do procedimento
exec SP_CriarAlunoProfessor 'Paulo','123.223.233-23','1982-02-02','paulo','123','A'
Exemplo de Store Procedure com valor de retorno
create proc stpPopula @qtdInserido int out, @qtdAInserir int
as 
begin
set @qtdInserido = 10
end
Execução:
declare @qtd int
exec stpPopula @qtd out,null
print 'Valor de sp: '
print @qtd
Valor impresso na tela:
Funções
User functions, ou funções de usuário, é um recurso que permite ao usuário criar suas próprias funções para auxiliar na busca da informação. As user functions podem receber um ou mais parâmetros, e não aceitam parâmetro de output.
Funções definidas pelo usuário não podem ser usadas para executar ações que modificam o estado de banco de dados e podem ser invocadas em uma consulta.
As funções de usuário podem ser de três tipos 
Scalar Function: são funções que retornam um valor simples, como em muitas linguagens de programação.
In-Line Table-valued Function: obrigatoriamente tem que retornar valores em forma de tabelas.
Multi-statement Table-valued Function: retorna valores em forma de tabela, mas dentro da função a variável criada para retornar a tabela pode ser atualizada.
Funções escalares podem ser executadas usando uma instrução EXECUTE como procedimentos armazenados. Por questões de desempenho, recomenda-se:
Usar scalar functions complexas em resultados pequenos.
Usar Multi-statement functions ao invés de stored procedures para retornar tabelas.
Usar In-line functions para criar views parametrizadas.
Exemplos:
Scalar Function: retorna um único valor
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] parameter_data_type 
[ READONLY ] } ]
)
RETURNS return_data_type
[ AS ]
BEGIN 
function_body 
RETURN scalar_expression
END
Drop Function Trim
go
CREATE FUNCTION Trim(@Campo VARCHAR(200))
RETURNS VARCHAR(200)AS
BEGIN
 RETURN RTRIM(LTRIM(@Campo))
END
go
select dbo.Trim(Nome)
from TB_Pessoa
--- Formatar CPF -----
Drop function formatarCPF
go
create function formatarCPF(@cpf bigint)
 returns char(14) as
begin
 declare @retorno varchar(14)
 declare @cpfstring varchar(11)
 
 select @cpfstring = case 
 when len(convert(char(11),@cpf)) = 11 then convert(char(11),@cpf)
 when len(convert(char(11),@cpf)) = 10 then '0' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 9 then '00' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 8 then '000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 7 then '0000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 6 then '00000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 5 then '000000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 4 then '0000000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 3 then '00000000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 2 then '000000000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 1 then '0000000000' + convert(char(11),@cpf)
		 when len(convert(char(11),@cpf)) = 0 then '00000000000' + convert(char(11),@cpf)
 when len(convert(char(11),@cpf)) is null then '00000000000' end
 set @retorno = substring(@cpfstring,1,3) + '.' + substring(@cpfstring,4,3) + '.' + substring(@cpfstring,7,3) + '-' + substring(@cpfstring,10,2)
 return @retorno
end
select id_pessoa,cpf,dbo.formatarCPF(cpf) from tb_pessoa
In-Line Table-valued Function: retorna valores em forma de tabela.
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] parameter_data_type 
[ READONLY ] } 
]
)
RETURNS TABLE
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
drop function RetornaPessoa
go
create function RetornaPessoa(@Nome varchar(100))
Returns Table
AS
RETURN ( SELECT Nome , dbo.formatarCPF(cpf) as CPF FROM TB_Pessoa
WHERE nome like dbo.Trim(@Nome) + '%')
select * from RetornaPessoa('b')
Multi-statement Table-valued Function: retorna valores em forma de tabela, porém, permite a modificação dos dados da tabela que será retornada.
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] parameter_data_type 
[READONLY] } 
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ AS ]
BEGIN 
function_body 
RETURN
END
drop function TipoFuncao
go
CREATE FUNCTION TipoFuncao ( @CODIGO INT)
RETURNS @TAB_RET TABLE ( COD INT , NOME VARCHAR(20) )
AS
BEGIN
if @Codigo = 1 
begin
 INSERT INTO @TAB_RET VALUES(@CODIGO,'Retorno com 1')
end
if @Codigo = 2 
begin
 INSERT INTO @TAB_RET VALUES(@CODIGO,'Retorno com 2')
end
RETURN
END
SELECT * FROM TipoFuncao(1)
SELECT * FROM TipoFuncao(2)
Cursores
Cursores permitem a realização de operações sobre linhas individuais de um resultado.
A utilização de cursores somente é necessária quando a utilização de comandos orientados a conjuntos não é suficiente. Por questões de desempenho, use cursores com cuidado, apenas quando necessário.
Um cursor pode ser declarado através do comando DECLARE … CURSOR
DECLARE nome [INSENSITIVE] [SCROLL] CURSOR
FOR consulta_sql
[FOR {READ ONLY | UPDATE [OF lista_colunas]]
INSENSITIVE: na abertura do cursor, o resultado é armazenado em uma tabela temporária, ou seja, as modificações posteriores a sua abertura não serão conhecidas.
SCROLL: todas as operações de movimentação poderão ser realizadas, não somente as definidas pelo padrão ANSI/92 (movimentação à frente).
READ ONLY: não permite atualizações utilizando o cursor 
UPDATE [OF colunas]: permite atualizações em todas (comportamento padrão) ou somente algumas colunas.
Monta e disponibiliza o resultado do cursor, sintaxe:
OPEN nome do cursor
@@CURSOR_ROWS indica o número de linhas que atenderam a sua consulta.
O Comando FETCH realiza a movimentação em um cursor, permitindo percorrê-lo linha-a-linha, sintaxe:
FETCH [[NEXT | PRIOR | FIRST | LAST |
ABSOLUTE n | RELATIVE n] FROM] nome_do_cursor
[INTO @variável1, @variavel2, ...].
A Variável @@FETCH_STATUS é utilizada para indicar o estado da movimentação:
@@FETCH_STATUS = 0: movimentação realizado com sucesso
@@FETCH_STATUS =-1: cursor está na linha inicial ou final
@@FETCH_STATUS = -2: a linha recuperada não é válida (foi modificada) em um cursor não INSENSITIVE.
NEXT: move para a próxima linha do cursor ou para a primeira, se o cursor foi recém-aberto.
PRIOR: move para a linha anterior.
FIRST e LAST: move para a primeira e última linhas, respectivamente.
ABSOLUTE n: move para a linha de posição n no cursor (se for positivo, a contagem inicia na primeira linha, se negativo, na última).
RELATIVE n: move para n linhas para frente (positivo) ou para trás (negativo) a partir da posição atual
PRIOR, FIRST, LAST, ABSOLUTE n e RELATIVE n só podem ser realizados com cursores SCROLL.
INTO @variavel1[, @variavel2…]: permite associar cada coluna do cursor a uma variável declarada.
Cada variável listada no comando FETCH deverá estar relacionada a uma coluna do cursor.
A variável deve possuir o mesmo tipo da coluna, não sendo realizadas conversões implícitas.
Um cursor pode ser fechado através do comando CLOSE, sintaxe:
CLOSE nome_do_cursor
Um cursor fechado mantém as estruturas de dados criadas através do comando DECLARE CURSOR, ou seja, pode ser reaberto através do comando OPEN.
Um cursor pode ser desalocado, ou seja, ter eliminadas as estruturas de dados criadas através DEALLOCATE nome_do_cursor.
Cursor: Exemplo 1
-- Para criar uma SP utilizando um cursor
drop procedure SP_ListaPessoa
go
CREATE PROCEDURE SP_ListaPessoa AS
Begin
	declare @Nome varchar(100)
	declare @DataNasc datetime
	declare @msg varchar(200)
	Declare CursorLista cursor
	 for
		SELECT Nome, DataNasc
		FROM TB_Pessoa
	 FOR READ ONLY
	OPEN CursorLista
	FETCH NEXT FROM CursorLista INTO @Nome, @DataNasc 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @msg = ' O nome do cliente é : ' + LTRIM(@Nome) + ' a data é ' + convert(char(10),@DataNasc,103)
		PRINT @msg
		FETCH NEXT FROM CursorLista INTO @Nome, @DataNasc 
	END
	DEALLOCATE CursorLista
END
-- Executar a SP
exec SP_ListaPessoa
O exemplo abaixo, disponível em http://www.sqlserverdicas.com/2010/12/cursores-exemplo-basico-de-utilizacao.html, utiliza-se um cursor para executar a chamada da procedure sp_helptext para cada procedure de um banco de dados. As procedures foram obtidas através de uma consulta na view ROUTINES do Schema INFORMATION SCHEMA.
DECLARE @schemaName VARCHAR(30)
 , @procName VARCHAR(30)
 , @fullName VARCHAR(60)
-- Cursor para percorrer os nomes dos objetos
DECLARE cursor_objects CURSOR FOR
 SELECT
 ROUTINE_SCHEMA
 , ROUTINE_NAME
 FROM
 INFORMATION_SCHEMA.ROUTINES
 WHERE
 ROUTINE_TYPE = 'PROCEDURE'
-- Abrindo Cursor para leitura
OPEN cursor_objects
-- Lendo a próxima linha
FETCH NEXT FROM cursor_objects INTO @schemaName, @procName
-- Percorrendo linhas do cursor (enquanto houverem)
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @fullName = @schemaName + '.' + @procName
 EXEC sp_helptext @fullName
 -- Lendo a próxima linha
 FETCH NEXT FROM cursor_objects INTO @schemaName, @procName
END
-- Fechando Cursor para leitura
CLOSE cursor_objects
-- Desalocando o cursor
DEALLOCATE cursor_objects 
Algumas considerações:
Um cursor deve estar sempre associado a uma consulta, especificada ao declarar o cursor. 
O comando FETCH popula as variáveis recebidas como parâmetro com os valores da próxima linha da consulta a ser lida. O número de variáveis passadas como parâmetro deve ser igual ao número de colunas retornadas na consulta associada ao cursor. 
A variável global @@FETCH_STATUS retorna o resultado da última operação FETCH executada por um cursor na conexão.
O status 0 significa que o comando FETCH retornou uma linha, qualquer outro resultado significa que não houve linha retornada.
Um cursor desalocado não pode mais ser reaberto.
Cursores são estruturas relativamente lentas se comparadas ao desempenho de consultas do banco. O uso descuidado dessa ferramenta pode causar sérios problemas de performance.
Dica: Analise também o exemplo disponível em http://imasters.com.br/artigo/3650/sql-server/utilizando-cursores?trace=1519021197&source=single
Tabelas temporárias
Tabelas Temporárias são criadas no database TempDB e podem ser classificadas em Locais e Globais:
Tabelas Temporárias Locais são criadas com o prefixo "#" e possuem visibilidade restrita para a conexão responsável por sua criação; outras conexões não "enxergam" a tabela.
Tabelas Temporárias Globais são criadas com o prefixo "##" e são visíveis por todas as conexões
Sintaxe:
CREATE TABLE #nomeTable
 (
 nomecampoA varchar(80),
 nomecampoB money
 )
Tabelas temporárias são muito utilizadas quando precisamos reunir vários registros de várias tabelas em uma única seleção e exibi-las em uma aplicação qualquer (p.ex.: Delphi, Visual Studio, ASP.NET, etc.). .
É fundamental para aplicações cliente/servidor onde vários usuários estão acessando aquela procedure ao mesmo tempo.
Aqui vai um exemplo prático da utilização de tabelas temporárias locais. É importante saber que neste exemplo a tabela temporária só existirá enquanto a procedure está sendo executada, após a execução da mesma ela é automaticamente excluída. Aproveitei para colocar um exemplo utilizando  o CASE do Sql Server.
CREATE PROCEDURE TesteTabelaTemporaria
as
create table #tmpTotalPage
 (mes smallint null,
 totalmes smallint null,
 mediames decimal(9, 3) null )
Insert into #tmpTotalPage ( mes, totalmes, media) 
(select DATEPART(MONTH,data) as Mes, count(*) as TotalAcessos, null
 from Acessos AS AC 
INNER JOIN CadastroTB AS C ON AC.idcad = C.idCad
group by DATEPART(MONTH,data)) 
select mes, totalmes, 
media = case
 when mes = 1 then totalmes / 31 
 when mes = 2 then totalmes / 28 
 when mes = 3 then totalmes / 30 
 when mes = 4 then totalmes / 31 
 when mes = 5 then totalmes / 30 
 when mes = 6 then totalmes / 31 
 when mes = 7 then totalmes / 30 
 when mes = 8 then totalmes / 31 
 when mes = 9 then totalmes / 30 
 when mes = 10 then totalmes / 31 
 when mes = 11 then totalmes / 30 
 when mes = 12 then totalmes / 31 
end 
from #tmpTotalPage
CORREA, Fábio. Tabelas temporárias no Sql Server. Disponível em http://www.devmedia.com.br/tabelas-temporarias-no-sql-server/2610#ixzz3l9uuXsKE
Exercício
Criar uma tabela de cobrança com os campos: IdCobranca, DataVencimento, DataPagamento, ValorCobranca, ValorJuros
Criar uma StoredProcedure para:
Ler a tabela de cobrança 
Para cada linha verificar se a data de pagamento é maior que a data de vencimento, se for maior, chamar uma função para calcular os juros. 
O cálculo dos juros é o seguinte: Valor da cobrança * 2% + (0,05 * número de dias em atraso).
Atualizar a tabela de cobrança com o valor dos juros calculado.
Para efetuar a leitura e cálculo do juros deverá ser criado um cursor.
ÍNDICE – CONCEITOS
Table scan
Pesquisa todas as páginas começando do inicio da tabela, e extraindo o pedido da consulta.
Estrutura de dados que recebe como entrada uma propriedade de registro (por exemplo, um valor de um ou mais campos) e encontra os registros com essa propriedade rapidamente;
Um índice permite localizar um registro sem ter que examinar mais que uma pequena fração dos registros possíveis; 
O(s) campo(s) cujos valores o índice se baseia formam a chave de pesquisa;
Índices são, portanto, estruturas de dados auxiliares cujo único propósito é tornar mais rápido o acesso a registros baseado em certos campos, chamados campos de indexação, podem ser:
Arquivos ordenados (sort files)
Índice primário - baseado na chave de ordenação do arquivo.
Índice de agrupamento (clustering) - baseado no campo de ordenação não chave de um arquivo.
Arquivos não ordenados (heap files)
Índice secundário - baseado em qualquer campo não ordenado de um arquivo.
Cada índice precisa ser atualizado sempre que um registro é inserido ou excluído do arquivo
Por que utilizamos índices?
tens ordenados
Menortamanho
Localização facilitada
Avaliar
Tempo de Acesso: Tempo de acesso: o tempo gasto para encontrar um item dos dados em particular, ou um conjunto de itens, usando a técnica em questão; 
Tempo de inserção: o tempo gasto para incluir um novo item de dados. Esse valor inclui o tempo gasto para encontrar o lugar correto para incluir o novo item de dados e, também, o tempo gasto para atualizar a estrutura de índice. 
Tempo de exclusão: o tempo gasto para excluir um item dos dados, esse valor inclui o tempo gasto para encontrar o item a ser excluído e, também, o tempo gasto para atualizar a estrutura de índice. 
Espaço adicional: o espaço adicional ocupado por uma estrutura de índice. Desde que a quantidade de espaço adicional seja razoável, geralmente, compensa sacrificar o espaço para obter um desempenho melhor;
Índices Cluster 
Impõem uma organização na própria página de dados 
Fica classificado de acordo com a composição da Chave 
Cada tabela só pode ter um índice Cluster, que normalmente é a chave primária.
Índices Não-Cluster 
Possuem estrutura própria, mantendo-se vinculados às páginas de dados pela utilização de ponteiros. 
Utiliza árvore B-Tree.
No caso do SQL Server da Microsoft ele permite a existência de até 249 índices não-cluster. O índice não-cluster não altera a ordem dos dados Cada linha contém um ponteiro 
Índices únicos/não-únicos 
Determina se valores duplicados são permitidos na tabela 
Os índices no SQL Server não são únicos por padrão 
Melhora significativamente a desempenho do Índice (porque não são necessárias novas buscas) 
Índices de múltiplas colunas 
Reduzem o número de índices das tabelas 
Podem ser cluster ou não-cluster 
No SQL Server da Microsoft eles podem conter de 2 a 16 colunas
Opções na Criação do Índice 
Fill factor (fator de preenchimento) 
A opção fill factor determina qual a porcentagem de uma página de dados deve ser preenchido com o índice e quanto deve ser mantido em branco, reservado para inclusões e alterações. Por exemplo, se utilizarmos o fill factor igual a 80%, então o SQL Server irá apenas preencher 80% de cada página com os índices. Se alterarmos ou incluirmos dados, o SQL consegue reorganizar os índices de uma maneira mais rápida, pois ele tem 20% de espaço em branco em cada página de dados para poder.
Quanto maior o fill factor maior será o tempo entre manutenções do índice do banco de dados.
O fill factor ideal de um índice será aquele que causar menos fragmentação para um determinado período de analise.
Se o banco for utilizado somente para consulta, então a melhor coisa a se fazer é colocar o fill factor 100%, pois não há necessidade de deixar espaço em branco nas páginas de índices, já que eles jamais serão reorganizados
O percentual definido no fill factor será propagado para os níveis intermediários da árvore B-Tree 
Colunas para se indexar
PK´s
FK´s
Colunas que se acessam por ranges (BETWEEN, > < )
Colunas que se usa para sort order
Colunas que se usa para grouping ou agregações
Colunas com muitos acesso na cláusula where
Colunas para não se indexar
Coluna que você raramente referencia numa consulta
Colunas com alta cardinalidade como por exemplo:
Masculino e Feminino
Colunas com Ntext , Image, Text
Tipos de Índices Ordenados
Índice denso: sequência de blocos contendo apenas as chaves dos registros e os ponteiros para os próprios registros - Índice denso = (chave-ponteiro, registro);
Índice esparso: usa menos espaço de armazenamento que o índice denso ao custo de um tempo um pouco maior para localizar um registro dada a sua chave - índice esparso = (chave-ponteiro, blocos de dados)
Índices Primários
Modelo simples;
Arquivo classificado (com dados) + arquivo de índices;
Par (chave de pesquisa, ponteiro);
Tuplas são classificadas pela chave primária;
Útil quando a chave de pesquisa é a chave primária;
Índices clustering
Os registros de um arquivo são fisicamente ordenados segundo um campo que não seja um campo-chave. Não obrigatoriamente possuem um valor único 
É também um arquivo ordenado com dois campos; o primeiro é do mesmo tipo do campo clustening do arquivo de dados e o segundo é um ponteiro para o bloco de dados.
Índices Secundários
O campo de indexação é um campo não ordenado do arquivo de dados. Podem haver muitos índices secundários para um mesmo arquivo.
Vantagem: otimizar as consultas
Índice multinível: “índice de índice”
Primeiro nível: índice secundário sobre arquivo de dados
Demais níveis: índice primário sobre o índice do nível anterior e assim sucessivamente até que no último nível o índice ocupe apenas um bloco ou página.
Implementado com estrutura de dados em árvore de busca.
Comando de criação de índice
Índices são criados através do comando CREATE INDEX
Sintaxe:
CREATE [UNIQUE] {CLUSTERED|NONCLUSTERED} INDEX nome
[WITH
[FILLFACTOR = n]
[[,] IGNORE_DUP_KEY]
[[,] {SORTED_DATA|SORTED_DATA_REORG}]
[[,] {IGNORE_DUP_ROW|ALLOW_DUP_ROW}]]
[ON SEGMENT nome_do_segmento]
Create Index IX_BP_Pessoa ON bp_pessoa (IndPes,NomPes ) 
Go
Índices podem ser removidos através do comando DROP INDEX
Sintaxe:
DROP INDEX nome_do_índice
Drop index IX_BP_Pessoa ON bp_pessoa 
Go
Exercícios
Fazer download do Banco AdventureWorks: http://msftdbprodsamples.codeplex.com/releases/view/93587
Copiar tabela
 IF EXISTS (SELECT * FROM sys.tables WHERE OBJECT_ID = OBJECT_ID('Person.Person_Teste'))
 DROP TABLE Person.Person_Teste;
 GO
 
 SELECT * INTO Person.Person_Teste FROM Person.Person;
 GO
Verificar existência do índice
IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Person.Person_Teste') AND name = 'Name_Index')
 DROP INDEX Person.Person_Teste.Name_Index;
 
 IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Person.Person_Teste')
 AND name = 'IX_Person_LastName_FirstName_MiddleName')
 DROP INDEX Person.Person_Teste.IX_Person_LastName_FirstName_MiddleName; 
Habilitar apresentação de estatística
SET STATISTICS io ON
SET STATISTICS time ON
Consulta sem índice
SELECT *
 FROM Person.Person_Teste where LastName = 'Brown';
 GO
Estatísticas mostradas:
SQL Server parse and compile time: 
 CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
 CPU time = 15 ms, elapsed time = 25 ms.
(92 row(s) affected)
Table 'Person_Teste'. Scan count 1, logical reads 3807, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 SQL Server Execution Times:
 CPU time = 16 ms, elapsed time = 303 ms.
Criando índice não clusterizado
 CREATE NONCLUSTERED INDEX Name_Index
 ON Person.Person_Teste (LastName);
 GO 
Refaça a consulta da letra g e aponte as estatísticas e os operadores utilizados na 
0,299353
Criando índice clusterizado
IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Person.Person_Teste') AND name = 'Name_Index')
 DROP INDEX Person.Person_Teste.Name_Index;
 
 CREATE CLUSTERED INDEX Name_Index
 ON Person.Person_Teste (LastName);
 GO
Consulta envolvendo as duas colunas de nome, com uma cláusula LIKE.
SELECT FirstName, LastName
 FROM Person.Person_Teste 
 where FirstName like '%Jo%' and LastName = 'Brown';
 GO
Criação do índice não-clusterizado nas colunas de nome e sobrenome.
IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID('Person.Person_Teste') AND name = 'Name_Index')
 DROP INDEX Person.Person_Teste.Name_Index;
 
 CREATE NONCLUSTERED INDEX Name_Index
 ON Person.Person_Teste (FirstName, LastName);
 GO
Consultando o percentual de fragmentação do índice
SELECT getdate(), @@servername, db_name(db_id()), object_name(B.Object_id), B.Name, avg_fragmentation_in_percent,page_Count,fill_factor
FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null) A
join sys.indexes