RM Programação SQL Server

Todos os direitos autorais reservados pela RM SISTEMAS.
Proibida a reprodução total ou parcial, bem como a armazenagem em sistema de

recuperação e a transmissão, de qualquer modo ou por qualquer outro meio, seja
este eletrônico, mecânico, de fotocópia, de gravação, ou outros, sem prévia
autorização por escrito da proprietária.
O desrespeito a essa proibição configura uma apropriação indevida dos direitos
intelectuais e patrimoniais da RM SISTEMAS.
Aos infratores se aplicam as sanções previstas nos artigos 122 e 130 da Lei
nº 5.988 de 14 de dezembro de 1973.
Programação SQL
no ambiente
CORPORE RM
DEPARTAMENTO DE TREINAMENTO
Fundada em 1986, a RM Sistemas, que faz parte da TOTVS S.A., grupo que
atua no desenvolvimento e comercialização de softwares de gestão
empresarial integrada e na prestação de serviços relacionados, se tornou, ao
longo de suas duas décadas de história, uma das principais empresas de
sistemas de ERP do mercado brasileiro. Com matriz em Belo Horizonte, filiais
em São Paulo (SP), Rio de Janeiro (RJ), Curitiba (PR), Uberlândia (MG), Porto
Alegre (RS) e Vitória (ES), a RM Sistemas está presente em todo o país por
meio de mais de 40 canais de distribuição. Além disso, mantém unidades em
Portugal.
A Linha CORPORE RM apresenta sistemas integrados de gestão empresarial

ágeis, leves e seguros. São avançados softwares compostos por aplicativos
que compartilham uma base de dados única, dispõem de uma poderosa
ferramenta de BI e oferecem ainda recursos em suas ferramentas .NET, que
permitem a clientes, fornecedores e colaboradores o acesso a informações e
o controle de processos mesmo fora do ambiente físico da empresa, via
internet, com total segurança. Abrange processos de controladoria, finanças,
automação comercial, recursos humanos, produção, manutenção,
assistência técnica, CRM, e-commerce, ASP e business intelligence. Além
disto, possui soluções específicas para os segmentos de educação, saúde,
obras e projetos e indústrias.
SERVIÇOS ON LINE
Através do Site RM Sistemas você tem acesso a serviços exclusivos como:
informações financeiras e cadastrais, emissão de 2ª via de nota fiscal e
boleto bancário, consulta e ocorrências, envio de base, senhas e download
de novas versões, log de acessos e fórum de discussão. Tudo isso com total
segurança.
Cadastre-se agora mesmo no menu cliente on-line

www.rm.com.br
Programação SQL no
ambiente CORPORE RM
Versão: 7.20
ÍNDICE
Visão Geral do CORPORE RM ERP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
MÓDULO VISÃO GERAL DA PROGRAMAÇÃO DO SQL SERVER . . . . . . . . . . . . .5
Identificando camadas lógicas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Ferramentas de programação do SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Linguagem de programação Transact-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Nível de registro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16
MÓDULO BANCOS DE DADOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Funcionamento do log de transações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21
Configurando opções de banco de dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23
Monitorando e expandindo um log de transações . . . . . . . . . . . . . . . . . . . . . .29
Reduzindo um banco de dados ou arquivos . . . . . . . . . . . . . . . . . . . . . . . . . . .30
MÓDULO TIPOS DE DADOS E TABELAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
Criando tipos de dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
Criando e descartando tipos de dados definidos pelo usuário . . . . . . . . . . . . . .35
Criando tabelas - Informações Gerais . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36
Criando e descartando uma tabela . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38
MÓDULO IMPLEMENTANDO A INTEGRIDADE DE DADOS . . . . . . . . . . . . . . . . .43
Tipos de Integridade de dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43
Definindo restrições . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Criando restrições . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45
Usando padrões e regras . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
MÓDULO IMPLEMENTANDO VIEWS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55
Introdução às views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55
Criando views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
Alterando e Descartando views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57
Modificando dados através de views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60
Otimizando o desempenho com o uso de views . . . . . . . . . . . . . . . . . . . . . . . .60
MÓDULO PROCEDIMENTOS ARMAZENADOS . . . . . . . . . . . . . . . . . . . . . . . . . .63
Definido procedimentos armazenados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Processamento inicial de procedimentos armazenados . . . . . . . . . . . . . . . . . . .64
Criando, executando e modificando procedimento armazenado . . . . . . . . . . . .67
Executando procedimentos armazenados . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69
Alterando e descartando procedimentos armazenados . . . . . . . . . . . . . . . . . . .70
Recompilando explicitamente procedimentos armazenados . . . . . . . . . . . . . . .74
Tratando mensagens de erro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .75
Considerações sobre o desempenho . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .77
MÓDULO FUNÇÕES DEFINIDAS PELO USUÁRIO . . . . . . . . . . . . . . . . . . . . . . . .79
Estabelecendo funções definidas pelo usuário . . . . . . . . . . . . . . . . . . . . . . . . .79
Criando uma função definida pelo usuário . . . . . . . . . . . . . . . . . . . . . . . . . . . .79
Restrições às funções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .80
Criando uma função com vinculação de esquemas . . . . . . . . . . . . . . . . . . . . . .81
Definindo permissões para funções definidas pelo usuário . . . . . . . . . . . . . . . .81
Alterando e descartando funções definidas pelo usuário . . . . . . . . . . . . . . . . .81
Práticas recomendadas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .84
MÓDULO IMPLEMENTANDO DISPARADORES . . . . . . . . . . . . . . . . . . . . . . . . . .86
Introdução a disparadores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .86
Uso de disparadores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .87
Considerações sobre o uso dos disparadores . . . . . . . . . . . . . . . . . . . . . . . . . .88
Definindo disparadores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89
Alterando e descartando disparadores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .91
Como funcionam os disparadores aninhados . . . . . . . . . . . . . . . . . . . . . . . . . .94
MÓDULO PROGRAMANDO EM VÁRIOS SERVIDORES . . . . . . . . . . . . . . . . . . . .99
Introdução às consultas distribuídas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Acessando dados remotos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Configurando um ambiente de servidor vinculado . . . . . . . . . . . . . . . . . . . . .101
Estabelecendo segurança de servidor vinculado . . . . . . . . . . . . . . . . . . . . . . .102
ANEXO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
Visão Geral do CORPORE RM ERP
CORPORE RM Departamento de Treinamento RM Sistemas

4 Programação SQL Server Reprodução Proibida
MÓDULO VISÃO GERAL DA PROGRAMAÇÃO DO
SQL SERVER
Identificando camadas lógicas
A arquitetura de aplicativos empresariais contém camadas lógicas. As camadas
são compostas de uma camada de apresentação de dados, uma camada lógica de
aplicativo é uma camada de serviços de dados.
Camada de apresentação de dados

A camada de apresentação de dados também é chamada de serviços de usuário
e permite que os usuários procurem e manipulem dados. Os dois principais tipos
de cliente são os aplicativos personalizados do Microsoft Windows® e os
navegadores da Web. Essa camada usa os serviços fornecidos pela camada lógica
de aplicativo.
Camada lógica de aplicativo

Essa camada contém a lógica do aplicativo que define regras e processos. Ela
permite uma escalonabilidade; em vez de vários clientes acessarem diretamente
um banco de dados (com cada cliente requerendo uma conexão separada), os
clientes podem se conectar a serviços comerciais que, por sua vez, se conectarão
aos servidores de dados.
Os serviços comerciais podem ser componentes personalizados ou aplicativos e

serviços integrados, como os serviços da Web. A lógica de aplicativo também
pode conter componentes que utilizam serviços de transação, serviços de
mensagens ou serviços de gerenciamento de objetos e conexões.
Camada de serviços de dados

Os serviços de dados incluem a lógica de acesso a dados e o armazenamento de
dados. Esses serviços podem incluir procedimentos armazenados do SQL Server
para o gerenciamento do tráfego de dados de integridade no servidor de banco de
dados.
Criando camadas físicas

Você pode inserir fisicamente as camadas lógicas em um ambiente distribuído de
várias maneiras. Embora todas as camadas lógicas possam existir em um
computador, é comum distribuí-las em um modelo de duas camadas ou de várias
camadas.
Esse procedimento permite a implementação da lógica, das regras comerciais e do

processamento onde eles são mais eficazes.
Departamento de Treinamento RM Sistemas CORPORE RM
Reprodução Proibida Programação SQL Server 5
Usando um modelo de duas camadas
Ao usar este modelo, você poderá localizar a apresentação e a lógica do aplicativo
no cliente e os serviços de dados em um servidor. Podendo também localizar a
lógica do aplicativo nos procedimentos armazenados no servidor. Existe ainda a
possibilidade de uma solução mista em que a lógica do aplicativo seja dividida
entre o cliente e o servidor.
As estruturas de duas camadas são menos comuns do que as de várias camadas,

devido à crescente popularidade dos aplicativos da internet. Elas não são
escalonáveis e podem apresentar uma manutenção mais difícil do que a
manutenção das estruturas de várias camadas.
Usando um modelo de várias camadas

O modelo de várias camadas, também conhecido como modelo de três camadas,
permite a distribuição da lógica nos aplicativos. As regras comerciais podem ser
separadas do cliente ou do banco de dados. Quando esse modelo é aplicado à
internet, é possível dividir os serviços de apresentação entre um cliente navegador
e um servidor web do Microsoft Internet Information Services (IIS); o servidor Web
formata as páginas da web exibidas pelo navegador.
O modelo de várias camadas é escalonável para grandes bases de clientes e vários

aplicativos. Além disso, você pode dividir a carga de trabalho entre vários
computadores. Um modelo de várias camadas é fácil de ser gerenciado pois
permite isolar uma alteração em uma regra comercial sem afetar as outras. Além
disso, uma página ASP (Active Server Page) em um servidor web, atualiza
automaticamente todos os clientes.
Acessando dados
As tecnologias Microsoft permitem o acesso a dados da empresa através de um
grande número de clientes existentes ou personalizados que usam a interface de
programação de acesso a dados.
Usando clientes existentes

Você pode usar aplicativos clientes existentes para acessar dados do SQL Server.
A lógica de recuperação de dados faz parte do aplicativo cliente (O Microsoft
Office inclui o Microsoft Excel). Quando esses aplicativos fazem parte de uma
solução de várias camadas, eles são utilizados principalmente para serviços de
apresentação. Entretanto, você também pode usá-los para a lógica do aplicativo e
para serviços de dados.
Esses aplicativos permitem que os usuários procurem dados do servidor e

executem consultas ad hoc. Você pode usá-los para recuperar dados do SQL
Server ou como um cliente em uma estrutura de várias camadas. Você também
pode usar o Office como um ambiente de desenvolvimento para criar aplicativos
de acesso a dados.
O Access e o Excel são exemplos de clientes existentes que oferecem uma ampla
funcionalidade. Você também pode usar os clientes existentes que oferecem
somente serviços da apresentação, como um navegador que se comunica com IIS.
Criando clientes personalizados
Você pode criar clientes personalizados usando uma interface de programação de
acesso e um ambiente de desenvolvimento.
Fornecendo acesso universal a dados

Os clientes personalizados podem precisar acessar várias fontes de dados
diferentes na empresa. O Microsoft Data Access Components (MDAC) é uma
interface que permite uma comunicação com várias fontes de dados. Você pode
usar os seguintes componentes do MDAC para facilitar a comunicação:
OLE DB
Um conjunto de interfaces do Component Services (Serviços de
componentes) que fornecem acesso uniforme aos dados armazenados em
diversas fontes de informações. O OLE DB (banco de dados OLE) permite
o acesso às fontes de dados relacionais e às fontes de dados que não são
relacionais.
Microsoft Active X® Data Objects (ADO)

Uma interface de programação de aplicativo (API, application programming
interface) de fácil utilização para qualquer fornecedor de dados OLE DB.
Você pode usar ADO em uma grande variedade de cenários de aplicativos
de acesso a dados. O OLE DB e o ADO permitem criar componentes de
dados que usam os serviços integrados fornecidos pelo Component
Services. O ADO permite:
Abrir e manter conexões;

Criar consultas ad hoc;
Executar os procedimentos armazenados no SQL Server;
Recuperar resultados e usar cursores;
Armazenar em cache os resultados do cliente;
Atualizar os registros do banco de dados;
Fechar conexões.
Ferramentas de programação do SQL Server

SQL Query Analyzer
Efetua a sintaxe de codificação por cores automaticamente

Várias janelas de consulta
Visualizações personalizáveis de conjuntos de resultados
Planos gráficos de execução
Executa partes de scripts
SQL Query Analyzer

Você pode usar o SQL Query Analyzer para executar instruções de consulta e
resultados simultaneamente. Também pode usá-lo para escrever, modificar e
salvar scripts Transact-SQL. O SQL Query Analyzer fornece os seguintes recursos:
Marcação personalizada dos elementos da sintaxe. Quando você escreve

uma consulta, o SQL Query Analyzer realça as palavras-chave, as
seqüências de caracteres de linguagem; é possível personalizar a exibição
desses elementos;
Várias janelas de consulta, cada qual com uma conexão própria;
Visualizações personalizáveis de conjuntos de resultados. Você pode exibir

os resultados na forma de conjunto de resultados padrão ou em uma grade
para manipulá-los da mesma forma que uma tabela;
Planos gráficos de execução que descrevem o modo como o SQL Server

executa a consulta. É possível exibir o plano de execução otimizado a
sintaxe;
Capacidade de executar partes de um script. Você pode solucionar panes
de um script; o SQL Server executará somente essas partes;
Utilitário osql
Utilitário de linha de comando. O SQL Server oferece várias ferramentas de
programação, incluindo o SQL Query Analyzer (Analizador de consultas do SQL) e
o utilitário osql. O SQL Query Analyzer é um aplicativo baseado no Windows, e o
osql é um utilitário que pode ser executado a partir de um prompt de comando.
O utilitário osql permite que você escreva instruções Transact-SQL,
procedimentos do sistema de arquivos de script. Ele utiliza a conectividade aberta
de banco de dados (ODBC, open Database Connectivity) para se comunicar com
o servidor, o utilitário é iniciado diretamente do sistema operacional, através dos
argumentos com diferenciação de maiúsculas/minúsculas listados a seguir.
Uma vez iniciado, o osql aceita as instruções Transact-AQL e as envia para o SQL
Server de forma interativa. O osql formata e exibe os resultados da tela. Use os
comandos Quit ou Exit para sair do osql.
osql –U identificação de logon [-e] [-E] [-p] [-n]

[-d nome_do_banco_de_dados] [-q “consultar ”] [-Q “consultar ”] [-e
fim_do_comando] [-h cabeçalhos] [-w largura_da_coluna] L-s
separador_de_coluna] [-t tempo_limite] [-m nível_de_erros] [-L] [-?] [-r {O | 1}] [-H
nome_da_estação_de_trabalho] [-P senha] [-R] [-S nome_do_servidor] [-1
arquivo_de_entrada] [-o arquivo_de_saída] [-a tamanho_do_pacote] [-b] [-O] [-1
tempo_limite]
Observação:
Nas instruções do osql, os parâmetros fazem diferenciação de
maiúsculas/minúsculas.
A tabela a seguir descreve os argumentos usados com mais freqüência.
Linguagem de programação Transact-SQL
Implementação do padrão 150 ANSI-SQL no SQL Server;

Pode ser executada em qualquer produto compatível com nível de entrada;
Contém funcionalidade exclusiva adicional.

O Transact-SQL é a implementação do padrão International Standards
Organization (ISO) ANSI-SQL de nível de entrada no SQL Server. Os elementos da
linguagem compatíveis com ANSI-SQL no Transact-SQL podem ser executados a
partir de qualquer produto compatível com o ANSI-SQL de nível de entrada. Além
disso, o Transact-SQL também possui outros elementos exclusivos da linguagem.
Importante:
Procure escrever scripts que incluam somente instruções de padrão
ANSI-SQL para aumentar a compatibilidade e a portabilidade do banco
de dados.
Elementos do Transact-SQL
Ao escrever e executar instruções Transact-SQL, você usará diferentes instruções
de linguagem usadas para determinar quem poderá ver ou modificar os dados,
criar objetos no banco de dados, consultar e modificar os dados. Siga as regras de
nomeação de objetos do SQL Server e familiarize-se com as orientações de
nomeação de objetos do banco de dados.
Instruções de Data Control Language
Definem ou alteram permissões:

GRANT
DENY
REVOKE
Por padrão, somente os participantes do cargo sysadmin, dbccreator, db_owner

ou db_securityadmin podem executar.
As instruções de linguagem de controle de dados (DCL, Data Control Language)

são usadas para alterar as permissões associadas a um usuário ou cargo do banco
de dados.
A tabela a seguir descreve as instruções de DCL.
Por padrão, somente os participantes do cargo sysadmin, db_creator, db_owner

ou db_securityadmin podem executar instruções de DCL.
Exemplo:
A seguir permissão ao cargo public para consultar a tabela Products
(produtos):
USE corpore
GRANT SELECT ON pfunc to public
Instruções de Data Definition Language
Definem os objetos do banco de dados:
CREATE tipo_de_objeto nome_do_objeto;

ALTER tipo_de_objeto nome_do_objeto;
DROP tipo_de_objeto nome_do_objeto.

As instruções de linguagem de definição de dados (DDL, Data Definition
Language) definem o banco de dados, criando banco de dados, tabelas e tipos de
dados definidos pelo usuário. Elas também podem ser usadas para gerenciar os
objetos do banco de dados. Examine a seguir algumas instruções de DDL:
CREATE tipo_de_objeto nome_do_objeto;

ALTER tipo_de_objeto nome_do_objeto;
DROP tipo_de_objeto nome_do_objeto.
Por padrão, somente os participantes dos cargos sysadmin, dbcreator, db_owner

ou db_ddladmin podem executar instruções de DDL. Em geral, recomenda-se não
conceder permissão para outras contas criarem objetos do banco de dados.
Se os usuários criarem seus próprios objetos nos bancos de dados, cada

proprietário do objeto será obrigado a conceder as permissões apropriadas a cada
usuário desses objetos. Isso provoca uma sobrecarga administrativa e deve ser
evitado. Restringir as permissões de instruções a esses cargos também evita
problemas relacionados à propriedade de objetos, que podem ocorrer quando um
proprietário é descartado de um banco de dados ou quando o proprietário de um
procedimento armazenado ou view não possui as tabelas subjacentes.
Se várias contas de usuários criarem objetos, os cargos sysadmin e db_owner

poderão usar a função SETUSER para representar outros usuários ou o
procedimento armazenado do sistema “sp_changeobjectowner” para alterar o
proprietário de um objeto.
Exemplo:
O script a seguir cria uma tabela chamada “tbsalario” no banco de
dados corpore. Ela inclui as colunas chapa, nome e salario.
USE corpore
CREATE TABLE tbsalario
(chapa VARCHAR(20),nome VARCHAR (100),salario MONEY)
Instruções de Data Manipulation Language
Operam com os dados contidos no banco de dados:
SELECT;
INSERT;
UPDATE;
DELETE.
As instruções de DML (Data Manipulation Language, linguagem de manipulação

de dados) operam com os dados contidos no banco de dados. Ao utilizar
instruções de DML, você pode alterar dados ou recuperar informações.
As instruções de DML são:
SELECT;
INSERT;
UPDATE;
DELETE.
Por padrão, somente os participantes dos cargos sysadmin, db_creator,

db_owner, db_datawriter e db_datareader podem executar instruções de DML.
Exemplo:
Recupera a chapa, nome e salário dos funcionários contidos no
banco de dados corpore.
SELECT chapa,nome,salario FROM corpore.dbo.pfunc
Nomes de objetos do SQL Server

O SQL Server fornece uma série de regras de nomeação padrão para
identificadores de objetos e um método de uso de delimitadores para
identificadores que não sejam padrão. Sempre que possível, procure atribuir
nomes aos objetos usando os caracteres de identificadores padrão.

Identificadores padrão
Os identificadores padrão podem conter de 1 a 128 caracteres, inclusive letras,
símbolos (,@ou#) e números. Não são permitidos espaços incorporados.
Observe as seguintes regras quanto ao uso de identificadores:
O primeiro caracter deve ser um caractere alfabético, de a-z ou A-Z;
Após o primeiro caractere, os identificadores podem incluir letras,

números ou símbolo @, S,# e ou;
Os nomes de identificadores que começam com um símbolo têm

utilizações especiais;
Um identificador que inicie com o símbolo de arroba (@) indica um

parâmetro ou variável local;
Um identificador que inicie com um sinal de tralha (#) indica um

procedimento ou uma tabela temporária;
Um identificador eu inicie com um sinal de tralha duplo (##) indica um

objeto temporário global.
Observação:
Os nomes de objetos temporários não devem ter mais de 116
caracteres, incluindo os sinais (#) ou (##), porque o SQL Server atribui
um sufixo numérico interno aos objetos temporários.
Identificadores delimitados
Se um identificador atender a todas as regras de formato de identificadores,
poderá ser utilizado com ou sem delimitadores. Se um identificador não atender a
uma ou mais regras de formato de identificadores, deverá ser sempre delimitado.
Os identificadores delimitados podem ser usados nas seguintes situações:
Quando nomes contiverem espaços incorporados;

Quando forem utilizados palavras reservadas como nomes de objetos ou
parte de nomes de objetos;
Os identificadores agrupados são delimitados por colchetes ([ ]):
SELECT * FROM [Blanks In Table Name]
Observação:
É possível utilizar sempre os delimitadores agupados,
independentemente do status da opção SET QUOTED_IDENTIFIER.
Os identificadores entre aspas são delimitados por aspas duplas (“”):
SELECT * FROM “Blanks in Table Name”
Atenção:
Só é possível usar identificadores entre aspas com a opção SET
QUOTED_IDENTIHER ativada.
Orientações de nomeação
Manter a exclusividade dos nomes de objetos e usuários.

As orientações de nomeação de objetos do banco de dados são
importantes para identificar o tipo de objeto e facilitar a solução de
problemas ou de depuração.
Exemplo:
Evite criar uma tabela Saies (Vendas) e um cargo saies dentro do
mesmo banco de dados.

Ao nomear objetos do banco de dados, você deve:
Quando possível, usar nomes significativos.

Por exemplo, no caso de uma coluna que contenha os nomes dos cliente,
você poderia denomina-la como Chr_name_of_Customer
(Caractere_Nome_do_cliente). O prefixo Chr no nome da coluna indica o
tipo de dado character.
Manter nomes curtos
Exemplo:
Embora o nome de coluna Chr_Name_Of_Customer seja significativo, é
possível abrevia-lo para Name (Nome) ou Chr (Caractere_Nome).
Usar convenções de nomeação simples e claras.

Descubra o que funcionará melhor na sua situação e seja coerente. Evite
convenções de nomeação muito complexas porque elas podem dificultar a
memorização. Por exemplo, retirem as vogais se o nome de um objeto
precisar ficar parecido com uma palavra-chave (como um procedimento
armazenado de backup chamado Bckup).
Escolher um identificador que diferencie o tipo de objeto, principalmente

quando usar views e procedimentos armazenados.
Os administradores de sistemas costumam confundir views com tabelas,
um descuido que pode gerar problemas imprevisíveis. Por exemplo, criar
uma view que associe duas tabelas, você poderá denomina-la como
SoldView.
Outros elementos de linguagem
Alguns elementos adicionais da linguagem Transact_SQL são variáveis locais,

operadores, funções, elementos de linguagem de controle de fluxo e comentários.
Variáveis locais
Definidas pelo usuário com a instrução DECLARE;

Recebem valores através de uma instrução SET ou SELECT.
Variáveis são elementos da linguagem com valores atribuídos. Você pode usar
variáveis locais no Transact-SQL. Defina uma variável local em uma instrução
DECLARE e atribua um valor inicial a ela usando a instrução SET ou SELECT. Use
a instrução SET quando o valor desejado for conhecido. Use a instrução SELECT
quando tiver de pesquisar o valor desejado em uma tabela.
Após estabelecer o valor da variável, você poderá usá-lo na instrução, no lote ou

no procedimento em que ele foi declarado. Um lote é um conjunto de instruções
Transact-SQL submetidas e executadas como um grupo. Uma variável local
aparece com um símbolo @ no início do nome.
DECLARE { @variavel_local tipo de dados } [,...n]

SET @nome_da_variável_local = expressão
Em seguida, ele imprime as duas variáveis.
USE corpore
DECLARE @v_name varchar (20)
DECLARE @v_chapa varchar (20)
SET @v_chapa = '00001'

SELECT @v_name = nome
FROM corpore.dbo.pfunc
WHERE chapa= @v_chapa
PRINT @v_name + ' -- ' + @v_chapa
GO

Operadores
Operadores são símbolos que efetuam cálculos matemáticos, concatenações de
seqüências de caracteres e comparações entre colunas, constantes e variáveis. É
possível combiná-los e usá-los em pesquisas. Quando forem combinados, a
ordem em que esses operadores serão processados pelo SQL Server adotará como
base uma precedência predefinida.
Sintaxe parcial:
{ constante | nome_da_coluna | função | (subconsulta)}

[ { operador_aritmético | operador de sequência_de_caracteres|
AND | OR | NOT}
Constante | nome_da_coluna | função | (subconsulta)}...]
Tipos de operadores
O SQL Server oferece suporte para quatro tipos de operadores: aritméticos, de
comparação, de concatenação de seqüências de caracteres e lógicos.
Aritmético
Os operadores aritméticos efetuam cálculos com colunas ou constantes
numéricas, o Transact-SQL oferece suporte para operadores multiplicativos,
incluindo multiplicação (*), divisão (1) e módulo (%) – o resto inteiro de uma divisão
de inteiros – e operadores aditivos, de adição (+) e subtração (-).
Comparação
Os operadores de comparação comparam duas expressões. É possível efetuar
comparações entre variáveis, colunas e expressões semelhantes. A tabela a seguir
define os operadores de comparação no Transact-SQL.
Concatenação de seqüências de caracteres

O operador de concatenação de seqüências de caracteres (+) concatena valores de
seqüência. As funções de seqüência tratam de todas as outras manipulações de
seqüência.
Lógico
Os operadores lógicos AND, OR e NOT conectam condições de pesquisa em
cláusulas WHERE.
Níveis de precedência de operadores

Se você utilizar vários operadores (lógicos ou aritméticos) para combinar lógicos é
diferente em expressões, o SQL Server processará os operadores na respectiva
ordem de outras linguagens de precedência, o que pode afetar o valor resultante.
O SQL Server processa os operadores na respectiva ordem de outras linguagens

de precedência, o que pode afetar o valor resultante.

A tabela a seguir mostra a programação do nível de precedência dos operadores
(os níveis variam do nível mais alto para o nível mais baixo).
O SQL Server trata primeiramente da expressão com o agrupamento mais interno.

Além disso, se todos os operadores aritméticos em uma expressão
compartilharem o mesmo nível de precedência, a ordem será da esquerda para a
direita.
Funções
O transact-SQL fornece várias funções que retornam informações. As funções

recebem os parâmetros de entrada e retornam valores que podem ser utilizados
em expressões. A linguagem de programação Transact-SQL fornece três tipos de
funções: agregada, escalar e conjunto de registros.
Funções agregadas
Operam sobre um conjunto de valores,mas retornam um único valor de resumo.
Exemplos:
1) Calcula a média da coluna UnitPrice para todos os produtos da tabela
Products (Produtos).
SELECT AVG (salario) FROM pfunc
Funções escalares
Operam sobre um valor individual e retornam um único valor. É possível utilizar
essas funções sempre que uma expressão for válida. Você pode agrupar as
funções escalares nas categorias da tabela a seguir.

2) Este exemplo de função de metadados retoma o nome do banco de
dados em uso no momento.
SELECT DB.NAME ( ) AS ‘Database
Funções de conjunto de registros

Podem ser usadas como referências de tabela em uma instrução Transact_SQL.
Exemplo:
1) O exemplo a seguir executa uma consulta distribuída para recuperar
informações da tabela EMP (Funcionário)
SELECT *
FROM OPENQUERY
(OracleSvr, ‘SELECT ENAME, EMPNO
FROM SCOTT.EMP’)
Elementos de linguagem de controle de fluxo

Nível de instrução
Blocos BEGIN...END;
Blocos IF...ELSE;
Construções com WHILE.
IF USER_NAME( ) < > 'dbo'

BEGIN
RAISERROR ('Usuário diferente de DBO', 10, 1)
RETURN
END
ELSE
DBCC CHECKDB (corpore)
Os seguintes elementos de linguagem permitem que você controle o fluxo da

lógica em um script:
Blocos BEGIN. . END

Estes elementos de linguagem incluem uma série de instruções Transact-
SQL para sejam tratadas pelo SQL Server como uma unidade.
Blocos IF. .ELSE
Estes elementos especificam que o SQL Server deve executar a primeira
alternativa se determinada condição for verdadeira. Caso contrário, o SQL
Server deverá executar a segunda alternativa.
Construções WHILE
Estes elementos executam uma instrução várias vezes, enquanto a
condição especificada for verdadeira. As instruções BREAK e CONTINUE
controlam a operação das instruções dentro de um loop WHILE.
Exemplo:
Neste exemplo nos verificamos se o cliente possui algum lançamento
para que o nome possa ser alterado.
USE corpore
IF EXISTS (SELECT codcfo FROM flan

WHERE codcfo='12345')
PRINT '***** cliente possui lancamentos ******'
ELSE
BEGIN
UPDATE fcfo SET nome='LOJA DO JOAO' WHERE
codcfo='12345'
PRINT 'NOME ALTERADO COM SUCESSO!'
END
Nível de registro
Expressão CASE
O Transact-SQL contém vários elementos de linguagem que controlam o
fluxo da lógica em uma instrução. Ele também contém a expressão CASE
que permite usar uma lógica condicional em uma registro de cada vez, em
uma instrução SELECT ou UPDATE.
Uma expressão com CASE lista atributos atribui um valor a cada atributo e testa
cada um deles. Se a expressão retorna um valor verdadeiro, a expressão com
CASE retomará o valor na cláusula WHEN. Se a expressão for falsa e você tiver
especificado uma cláusula ELSE, o SQL Server retomará o valor na cláusula ELSE.
Exemplo:
Qualificamos a classificação da quantidade conforme a informado no
item da nota fiscal.
SELECT idprd,'Classifição da Quantidade'=

CASE
WHEN (quantidade<=10) THEN 'BAIXA'
WHEN (quantidade BETWEEN 11 AND 100) THEN 'MEDIA'
ELSE
'ALTA'
END
FROM
corpore.dbo.titmmov
Comentários
Comentários são seqüências de texto não executáveis inseridas em instruções
para descrever a ação executada pela instrução ou para desativar uma ou mais
linhas da instrução. Eles podem ser usados de duas maneiras, em linha com uma
instrução ou como um bloco.
Comentários em linha
É possível criar comentários usando dois hífens (- -) para isolá-los da instrução. O
Transact-SQL ignora o texto posicionando à direita dos caracteres de comentário.
Esses caracteres de comentário também podem ser usados para desativar linhas
de uma instrução.
Exemplo:
SELECT
idprd,MAX(quantidade) AS Maior_quantidade --Maior quantidade
FROM
corpore.dbo.titmmov
GROUP BY idprd
Comentários em bloco
É possível criar blocos de comentários de várias linhas, inserindo um caractere de
comentário (1*) no início do texto do comentário, digitando o comentário e
concluindo-o com um caractere de encerramento de comentário (1*).
Use esse caractere indicativo para criar uma ou mais linhas de comentários ou
cabeçalhos de comentários(texto descritivo que documenta as instruções
subseqüentes). Geralmente, os cabeçalhos incluem o nome do autor, as datas de
criação e da última modificação do script, informações sobre a versão e uma
descrição da ação executada pela instrução.
Observação:
Você pode inserir a instrução GO nos comentários em bloco.

Exemplo:
/*
Esta query retorna a maior quantidade vendida por produto
da tabela titmmov
*/
SELECT idprd,MAX(quantidade) AS Maior_quantidade --Maior

quantidade
FROM
corpore.dbo.titmmov
GROUP BY idprd
Observação:
Insira comentários em um script inteiro para descrever as ações
executadas pelas instruções. Esse recurso é importante principalmente
se outros usuários também precisarem examinar ou implementar o
script.
Maneiras de executar as instruções Transact-SQL
É possível executar as instruções Transact-SQL construindo instruções de forma

dinâmica e usando lotes, scripts e transações. Também é possível usar a
linguagem de marcação extensível (XML, Extensible Markup Language) para
apresentar dados na páginas da Web.
Construindo instruções de forma dinâmica
Use EXECUTE com literais e variáveis de seqüência de caracteres;

Use quando for necessário atribuir o valor da variável em tempo de
execução;
As variáveis e tabelas temporárias permanecerão somente durante a
execução.
É possível criar instruções de forma dinâmica, simultaneamente à execução de um

script pelo SQL Server.
Para criar uma instrução de forma dinâmica, use a instrução EXECUTE com uma
série de literais e variáveis de seqüências que são resolvidas em tempo de
execução. As instruções construídas dinamicamente são úteis quando você deseja
que SQL Server atribua o valor da variável durante a execução da instrução.
Por exemplo, você pode criar uma instrução dinâmica que executa a mesma ação
em uma série de objetos de banco de dados.
Sintaxe:
EXECUTE ( { @variável_de_sequência |
‘sequência_do_tsql’} + [ { @variável_de_sequência |
‘sequência_do_tsql’}...)}
As opções são definidas de forma dinâmica, e as variáveis e tabelas temporárias

criadas dinamicamente permanecerão somente durante a execução pelo SQL
Server.
Considere os seguintes fatos sobre a instrução EXECUTE:
Executa instruções compostas de seqüências de caracteres dentro de um

lote Transact-SQL. Por serem literais de seqüência, certifique-se de
adicionar espaços nos locais corretos para assegurar uma concatenação
também correta;
Permite a inclusão de um literal de seqüência, uma variável local de

seqüência ou uma concatenação de ambos;
Todos os itens pertencentes a uma seqüência de EXECUTE devem consistir

em dados de caracteres; para utilizar a instrução EXECUTE, converta todos
os dados numéricos;
Não é possível usar funções para criar a seqüência a ser executada;

É possível criar dinamicamente quaisquer instruções Transact-SQL
válidas, inclusive funções;
É possível aninhar as instruções EXECUTE.
Exemplo:
Consultaremos a coluna nome na tabela “tprd” no banco “corpore”,
lembrando que o nome do banco e tabela foram informados na
instrução.
DECLARE @banco VARCHAR(30)

DECLARE @tabela VARCHAR(40)
SET @banco='CORPORE'
SET @tabela='TPRD'
EXECUTE
('USE ' + @banco + ' SELECT nome FROM '+@tabela)
Usando lotes
Você também pode submeter uma ou mais instruções em um lote.
Uma ou mais instruções Transact-SQL submetidas em conjunto

Os lotes podem executados de forma interativa ou como parte de um
script. Um script pode incluir mais de um lote de instruções Transact-SQL.
Definir um lote com a instrução GO

Use uma instrução GO para indicar o final de um lote. GO não é uma
instrução transact-SQL universalmente aceita; somente o SQL Query
Analyzer e o utilitário osql a aceitam. Os aplicativos baseados nas APls de
ODBC ou OLEDB geram um erro de sintaxe quando tentam executar uma
instrução GO.
Como o SQL Server processa lotes

O SQL Server otimiza, compila e executa em conjunto as instruções
contidas em um lote. Contudo, as instruções não são executadas
necessariamente como uma unidade de trabalho recuperável. O escopo
das variáveis definidas pelo usuário é limitado a um lote, de modo que não
é possível fazer referência a uma variável depois de uma instrução GO.
Observação:
Se ocorrer um erro de sintaxe em um lote, nenhuma das instruções
contidas nesse lote será executada. A execução iniciará no lote seguinte.
Instruções que você não pode combinar em um lote

O SQL Server deve executar determinadas instruções para a criação de
objetos em lotes próprios dentro de um script, devido ao modo como os
objetos são definidos. Cada uma das instruções a seguir é definida através
da inclusão de um cabeçalho de definição de objetos seguido da palavra-
chave AS (indicando a existência de uma ou mais instruções a seguir). As
definições de objetos são delimitadas pela instrução GO.
O SQL Server reconhece o fim da definição do objeto ao atingir a

instrução GO
CREATE PROCEDURE;
CREATE VIEW;
CREATE TRIGGER;
CREATE RULE;
CREATE DEFAULT.
Usando scripts
Contêm instruções salvas;

Podem ser escritos em qualquer editor de texto;
Salve usando a extensão de nome de arquivo.sql;
São executados no SQL Query Analyzer ou no utilitário osql;
Use para a recriação de objetos de banco de dados ou para executar
instruções várias vezes.

Os scripts representam uma das maneiras mais conhecidas de executar
instruções Transact-SQL. Um script é constituído de uma ou mais instruções
Transact-SQL salvas como um arquivo. É possível escrever e salvar scripts no SQL
Query Analyzer ou em qualquer editor de texto, como o Notepad (bloco de notas).
Salve o arquivo de script usando a extensão de nome de arquivo.sql.
Você pode abrir e executar o arquivo de script no SQL Query Analyzer ou no

utilitário osql (ou em qualquer ferramenta de consulta). Os scripts salvos são
muito mais úteis durante a recriação de banco de dados ou de objetos de dados,
ou quando você usar um conjunto de instruções várias vezes.
Formate as instruções Transact-SQL de modo que os usuários consigam lê-las.

Use recuos para indicar níveis de relacionamentos.
Usando transações
Processadas como um lote;

Integridade de dados garantida;
Alterações no banco de dados são aplicadas juntas ou desfeitas.
Como os lotes, as transações são grupos de instruções submetidas em conjunto.

Contudo, o SQL Server trata as transações como uma unidade de trabalho
individual, e a transação pode ser bem sucedida como um todo. Esse processo
mantém a integridade dos dados. As transações podem ocupar vários lotes.
As transações iniciam uma instrução BEGIN TRANSACTION e terminam com uma

instrução COMMIT TRANSACTION ou ROLLBACK TRANSACTION. Quando uma
transação é confirmada, o SQL Server torna as alterações efetuadas nessa
transação permanentes. Quando uma transação é desfeita, o SQL Server retoma
todos os registros afetados pela transação anterior à transação.
Sintaxe parcial:
BEGIN TRANSACTION
COMMIT / ROLLBACK TRANSACTION
Exemplo:
Estamos alterando o salário de um funcionário e caso ocorra algum
erro na execução será executado o ROLLBACK da transação.
BEGIN TRANSACTION
UPDATE pfunc SET salario=(salario*0.10)+salario
WHERE chapa='00001'
IF @@ERROR <> 0
BEGIN
RAISERROR (' Transação não pode executar com sucesso',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
Usando a Extensible Markup Language(XML)

A XML (Extensible Markup Language, línguagem de marcação extensível) é uma
linguagem de programação que pode ser usada pelos desenvolvedores da Web
para apresentar dados de um banco de dados do SQL Server nas páginas da Web.
Permitindo que um navegador cliente formate dados

Quando a cláusula FOR XML é usada na instrução SELECT, o SQL Server:
Retoma os resultados de uma consulta como uma seqüência de

caracteres;
Retorna os atributos dos dados, por exemplo, os nomes de colunas e
tabelas, como marcas. Em seguida, um navegador cliente pode usar
essas marcas para formatar os dados retomados.
Especificando a opção FOR XML AUTO

Você pode especificar a opção FOR XML, AUTO para retornar os resultados
da consulta em um formato padronizado. Cada tabela da cláusula FROM,
para a qual pelo menos uma coluna é listada na cláusula SELECT, é
representada como um elemento XML. Um elemento inclui tanto os dados
como os atributos que descrevem os dados.

Exemplo:
SELECT nome,salario
FROM pfunc
WHERE salario>100
ORDER BY salario DESC
FOR XML AUTO
Observação:
O SQL Server reordena o conjunto de resultados a fim de agrupar as
colunas por nome de tabela.
Especificando a opção FOR XML RAW

Em alguns casos, os desenvolvedores da Web não desejam a formatação
automática. Você pode especificar a opção RAW para transformar cada
registro do conjunto de resultados em um elemento XML, onde um registro
identificador genérico é usado como a marca de elemento.
Exemplo:
SELECT nome,salário;
FROM pfunc;
WHERE salario>100;
ORDER BY salario DESC;
FOR XML RAW.
Identificando limitações na utilização da cláusula FOR XML

Uma instrução SELECT que contém a cláusula FOR XML reformata a saída
do cliente do SQL Server. Devido a essas alterações, não é possível usar
uma saída de consulta no formato XML como entrada para um
processamento futuro do SQL Server. Não é possível usar a saída
formatada em XML em:
Uma instrução SELECT aninhada;

Uma instrução SELECT INTO;
Uma cláusula COMPUTE BY;
Procedimentos armazenados chamados em uma instrução SELECT;
Uma definição de View ou uma função definida pelo usuário que retoma
um conjunto de registros.
Exercícios
Objetivos
Escrever instruções SELECT básicas;
Executar um script com utilitário osql;
Usar funções do sistema.
Tempo previsto para conclusão deste exercício: 20 minutos
1) Execute uma instrução SELECT que recupere todos os produtos da tabela

TPRD. Os campos ficaram a critério de cada aluno, portanto, para localizar os
nomes das colunas execute antes o procedimento armazenado SP_HELP na
tabela TPRD.
2) Execute o procedimento armazendo sp_who. E verifique se este procedimento

tem como retorno as atividades que estão ocorrendo no servidor.
3) Execute a instrução SELECT @@spid para verificar qual é a sua atividade no

servidor.
4) Execute a instrução SELECT @@version para verificar a versão do SQL Server.
5) Execute a instrução select @@servername para verificar o nome da instance do

SQL Server.
6) Execute a seguinte instrução para recuperar dados do metadados.
Esta instrução tratara todas as colunas que pertencem a uma chave (Primary key
ou foreign Key) da tabela PFUNC.
USE corpore
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE
TABLE_NAME='PFUNC'

MÓDULO BANCOS DE DADOS
Definindo banco de dados
Criar um banco de dados;

O nome do banco de dados;
O tamanho do banco de dados;
Os arquivos nos quais o banco de dados existirá.
É possível definir um banco de dados com a instrução CREATE DATABASE. O

processo de definição de um banco de dados também cria um log de transações
para esse banco de dados. As informações sobre cada banco de dados no SQL
Server estão armazenadas na tabela Sysdatabase (banco de dados do sistema)
no banco de dados “master”. Portanto, você deve usar o banco de dados master
para definir um banco de dados quando utilizar o Transact-SQL.
A definição de um banco de dados é um processo que envolve a especificação do

nome do banco de dados e a designação do tamanho e da localização dos
arquivos do banco de dados. Quando o novo banco de dados é criado, ele é uma
cópia do banco de dados “model”. Quaisquer opções ou configurações existentes
no banco de dados model são copiadas para o novo banco de dados.
Importante:
Você deve fazer um backup do banco de dados master toda vez que
criar, alterar ou descartar um banco de dados.
Funcionamento do log de transações

O SQL Server registra toda transação em um log de transações para manter a
consistência do banco de dados e auxiliar na recuperação. O log é uma área de
armazenamento que controla automaticamente todas as alterações feitas em um
banco de dados. O SQL Server grava as modificações no log em disco conforme
sãs executadas, antes que sejam gravadas no banco de dados.
CREATE DATABASE nome_do_banco

[ON
PRIMARY (NAME='Nome do arqquivo lógico',
FILENAME='nome do arquivo fisico',
SIZE= 'tamanho',
MAXSIZE='tamanho maximo',
FILEGROWTH='incremento de crescimento'
]
[LOG ON
(NAME='Nome do arqquivo lógico',
FILENAME='nome do arquivo fisico',
SIZE= 'tamanho',
MAXSIZE='tamanho maximo',
FILEGROWTH='incremento de crescimento'
]
[COLLATE nome da intercalação]
Quando criar um banco de dados, você poderá definir os seguintes parâmetros:
PRIMARY
Este parâmetro especifica os arquivos do grupo primário. Esse grupo
contém todas as tabelas do sistema do banco de dados. Também contém
todos os objetos não atribuídos a grupos de arquivos do usuário. Todo
banco de dados tem único arquivo de dados primário. O arquivo de dados
primário é o ponto de partida do banco de dados e indica o restante dos
arquivos do banco de dados. A extensão de arquivo recomendado para
arquivos de dados primários é “.mdf.” Se a palavra chave PRIMARY não
for especificada, primeiro arquivo listado na instrução se tornará o arquivo
primário.
FILENAME
Este parâmetro específica o nome do arquivo no sistema operacional e o
caminho para o arquivo. O caminho contido em “nome_do_arquivo” só
deve especificar uma pasta no servidor na qual o SQL Server está
instalado.
SIZE
Este parâmetro especifica o tamanho do arquivo de dados ou log. Você
pode especificar tamanhos em megabytes (MB) (o valor padrão) ou em
quilobytes (KB). O tamanho mínimo é de 512 KB tanto para o arquivo de
dados quanto para o arquivo de log. O tamanho especificado para o
arquivo de dados primário deve ter, pelo menos, o mesmo tamanho do
arquivo primário do banco de dados model. Quando você adicionar um
arquivo de dados ou de log, o valor padrão é de 1 MB.
MAXSIZE
Este parâmetro especifica o tamanho máximo que o arquivo pode atingir.
Você pode especificar tamanhos em megabytes (valor padrão) ou em
quilobytes. Caso nenhum tamanho seja especificado, o arquivo crescerá
até que o disco esteja cheio.
FILEGROWTH
Este parâmetro especifica o incremento de crescimento do arquivo. A
configuração de HLEGROWTH para um arquivo não pode exceder a
configuração de MAXSIZE. Um valor 0 indica nenhum crescimento. O valor
pode ser especificado em megabytes (padrão), em quilobytes ou como
uma porcentagem (%). O valor padrão, caso HLEGROWTH não seja
especificado, é de 10%, e o valor mínimo é de 64 KB (uma extensão). O
tamanho especificado é arredondado até o múltiplo de 64 KB mais
próximo.
COLLATE
Este parâmetro especifica a intercalação padrão para o banco de dados. A
intercalação inclui as regras que governam o uso de caracteres para uma
linguagem ou um alfabeto.
Exemplo:
criando database chamado corpore
CREATE DATABASE corpore

ON
PRIMARY (NAME='corpore_data',
FILENAME='c:\curso\database\corpore_data.mdf',
SIZE= 100Mb,
MAXSIZE=1GB,
FILEGROWTH=10%)
LOG ON
(NAME='corpore_log',
FILENAME='c:\curso\database\corpore_log.ldf',
SIZE= 5MB,
MAXSIZE=200MB,
FILEGROWTH=1MB)
Como o log de transações funciona

O SQL Server registra toda transação em um log de transações para manter a
consistência do banco de dados e auxiliar na recuperação. O log é uma área de
armazenamento que controla automaticamente todas as alterações feitas em um
banco de dados. O SQL Server grava as modificações no log em disco conforme
são executadas, antes que sejam gravadas no banco de dados
O processo de log funciona da seguinte forma:
1) Uma alteração nos dados é enviada pelo aplic ativo.
2) Quando uma alteração é executada, as páginas de dados afetadas são

carregadas do disco para o cache de buffer, desde que as páginas ainda não
tenham sido colocadas no cache de buffer por uma consulta anterior.
3) Cada instrução para alteração de dados é gravada no log conforme é feita. A

alteração é sempre registrada no log e gravada em disco antes que seja feita no
banco de dados. Esse tipo de log é chamado de log de gravação antecipada.
4) De forma repetida, o processo de ponto de verificação grava todas as

transações concluídas no banco de dados em disco.
Se o sistema falhar, o processo de recuperação automática uso o log de transações

para completar todas as alterações confirmadas e reverter quaisquer transações
incompletas.
Marcadores de transação no log são usados durante a recuperação automática

para determinar os pontos de inicio e fim de uma transação. Uma transação é
considerada concluída quando o marcador BEGIN TRANSACTION possui um
marcador COMMIT TRANSACTION associado. As páginas de dados são gravadas
no disco quando ocorre um ponto de verificação.
Configurando opções de banco de dados

Categorias de opção de banco de dados
Opções automáticas;
Opções do cursor;
Opções de recuperação;
Opções do SQL;
Opções de estado.
Após a criação de um banco de dados, você pode definir opções de banco de

dados com a instrução ALTER DATABASE.
Você pode configurar diversas opções de banco de dados, porém, só é possível

defini-las para um único banco de dados de cada vez. Para atingir as opções em
todos os novos bancos de dados, altere o banco de dados model.

A tabela a seguir lista algumas opções usadas com mais freqüência:

Recuperando informações do banco de dados
Determinar propriedades de bancos de dados com a função
DATABASEPROPERTYEX;
Use os procedimentos armazenados do sistema para exibir informações
sobre os bancos de dados e seus parâmetros

Sp_helpdb;
Sphelpdb nome_do_banco_de_dados;
Spspaceused [nomeobj].
Você pode determinar propriedades de bancos de dados com a função

DATABASEPROPERTYEX.
SELECT DATABASEPROPERTYEX. (banco_de_dados , propriedade)
A tabela a seguir lista algumas das propriedades do banco de dados
Collate
IsAnsiNullDefault
IsAnsiNullsEnabled
IsAnsiPaddingEnabled
IsAnsiWamingsEnabled
IsArithmcticAbortEnabled
IsAutoCreateStatjstics
LsAutoShrink
IsAutoUpdateStatitics
IsCloseCursorsOnCommitEnabled
IsFulltextEnabled
IsInStandBy
IsNullConcat
IsQuotedIdentifiersEnabled
IsRecursiveTriggersEnabled
Recovery
Status
Updateability
UserAccess
Version
A tabela a seguir lista os procedimentos armazenados do sistema comumente

usados que exibem informações sobre os bancos dedos e seus parâmetros.
Criando grupos de arquivos

Caso sua configuração de hardware inclua diversas unidades de disco, você pode
alocar objetos e arquivos específicos em discos individuais, agrupando seus
arquivos de banco de dados em grupos de arquivos. Grupos de arquivos são
coleções nomeadas de arquivos. O SQL Server contém um grupo de arquivos
como padrão. É possível criar grupos de arquivos adicionais com a instrução
CREATE DATABASE ou ALTER DATABASE.
Os administradores de sistema também podem fazer backups e restaurar arquivos

individuais ou grupos de arquivos em vez de fazer backup ou restaurar um banco
de dados inteiro. Em banco de dados grandes, é necessário fazer backups de
arquivos ou grupos de arquivos para obter uma estratégia eficiente de backup e
restauração.

Considerações sobre o uso de grupos de arquivos
O uso de grupos de arquivos é uma técnica avançada para o desenvolvimento do
banco de dados, você deve entender por completo a estrutura, os dados, as
transações e as consultas a seu banco de dados para determinar a melhor forma
de posicionar tabelas e índices em grupos de arquivos específicos.
Muitas vezes, o uso do recurso de faixas de disco oferecido pelos sistemas RAID
(Redundant Array of Independent Disks, matriz redundante de discos
independentes) fornece o mesmo ganho de desempenho que você obteria usando
grupos de arquivos sem a carga administrativa adicional de definir e gerenciar
grupos de arquivos.
Observação:
Arquivos de log não são parte de um grupo de arquivos. O espaço de
log é gerenciado separadamente do espaço de dados.
Tipos de grupos de arquivos
O SQL Server oferece os dois tipos de grupos de arquivos a seguir:
O grupo de arquivos primário, que contém as tabelas do sistema no

arquivo de dados primário;
Grupos de arquivos definidos pelo usuário, que são quaisquer grupos

especificados pela palavra chave FILEGROUP.
Designando o grupo de arquivos padrão

Quando você cria um banco de dados. O grupo de arquivos primários torna-se
automaticamente o grupo de arquivos padrão. Esse grupo de arquivos padrão
recebe todas as tabelas, índices e arquivos novos para os quais um grupo de
arquivos não é especificado.
Se o banco de dados contiver mais de um grupo de arquivos, recomenda-se que

você altere o padrão para que seja um dos seus grupos de arquivos definidos pelo
usuário. Isso impede que o grupo de arquivos primário, que contém as tabelas do
sistema, seja ocupado inesperadamente por uma tabela de usuário.
Dimensionando o grupo de arquivos primário padrão

Se o grupo de arquivos padrão continua sendo o grupo de arquivos primário, o
dimensionamento correto desse grupo de arquivos será importante. Se não houver
mais espaço no grupo de arquivos, você não poderá adicionar nenhuma
informação nova às tabelas do sistema.
Se um grupo de arquivos definido pelo usuário ficar sem espaço, apenas os
arquivos de usuário que estiverem especificamente alocados para esse grupo de
arquivos serão afetados.
Exemplo:
Adicionado um grupo de arquivo e adicionado um arquivo de dados
secundário na database corpore.
USER MASTER
ALTER DATABASE corpore

ADD FILEGROUP historicogrupo
go
ALTER DATABASE corpore

ADD FILE
(NAME='historico',
FILENAME='c:\curso\database\historico.ndf',
SIZE= 10 MB)
TO FILEGROUP historicogrupo

Exibindo informações sobre grupos de arquivos
As informações sobre grupos de arquivos estão disponíveis através do uso de
funções como FILE_NAME, FILE_ID, FILE_PROPERTY, FILEGROUP_NAME,
FILEGROUP_ID e FILEGROUP_PROPERTY. Os procedimentos armazenados do
sistema na tabela a seguir exibem informações sobre grupos de arquivos.
Gerenciando banco de dados
À medida que o banco de dados for aumentado ou alterado, você pode expandir
ou reduzir o banco de dados manual ou automaticamente. Quando não precisar
mais de um banco de dados, poderá descartá-lo com todos os arquivos
associados.
Gerenciando o crescimento dos arquivos de dados e de log

Quando os arquivos de dados crescem ou quando a atividade de modificação de
dados aumenta, pode ser necessário expandir o tamanho dos arquivos de dados
ou de log. Gerencie o crescimento do banco de dados usando a instrução ALTER
DATABASE. Você deve estar no banco de dados master para usar a instrução
ALTER DATABASE.
Você pode controlar o tamanho do banco de dados das seguintes formas:
Configurando os arquivos de banco de dados e de log para que aumentem

automaticamente;
Aumentando ou diminuindo manualmente o tamanho atual ou máximo de

arquivos de banco de dados e arquivos de log já existentes;
Adicionando manualmente arquivos secundários de banco de dados e de

log.
Usando o crescimento automático de arquivos

Você pode definir a opção de crescimento automático usando a instrução
ALTER DATABASE ou SQL Server Enterprise Manager para especificar que
os arquivos de banco de dados sejam expandidos automaticamente de
acordo com uma quantidade determinada sempre que necessário. O uso
do crescimento automático de arquivos reduz o número de tarefas
administrativas envolvidas com o aumento manual do tamanho do banco
de dados.
Você pode especificar o tamanho inicial, o tamanho máximo e o

incremento de crescimento de cada arquivo.
Embora seja possível especificar o crescimento do arquivo em megabytes
ou em quilobytes, você deve especificá-lo em porcentagens. Caso não
especifique um tamanho máximo, um arquivo poderá continuar a crescer
até usar todo o espaço disponível em disco.
Quando você usa o crescimento automático com vários arquivos, o SQL

Server usa uma estratégia de preenchimento proporcional em todos os
arquivos de cada grupo de arquivos. A medida que os dados são gravados
no grupo de arquivos, o SQL Server grava uma quantidade de dados
proporcional ao espaço livre de cada arquivo do grupo de arquivos, em vez
de gravar todos os dados no primeiro arquivo até que ele esteja cheio e,
em seguida, gravar no próximo arquivo.
Para obter um desempenho otimizado:
Aloque um tamanho inicial suficiente para o banco de dados e o log para

que não seja preciso ativar o crescimento automático com freqüência;

Defina um tamanho máximo para arquivos de dados se você tiver vários
bancos de dados;
Defina os incrementos de crescimentos do arquivo de dados e de log com

um tamanho suficiente para que não seja preciso ativar o crescimento
automático com freqüência;
ALTER DATABASE banco de dados

ADD FILE 'especificação do arquivo'
TO FILEGROUP 'nome do grupo de arquivos'
ADD LOG FILE 'especificação do arquivo'
REMOVE FILE 'nome do arquivo logico '[WITH DELETE]
ADD FILEGROUP 'nome do grupo de arquivo'
REMOVE FILEGROUP 'nome do grupo de arquivo'
MODIFY FILE 'especificação do arquivo'
MODIFY NAME 'novo nome do banco de dados'
MODIFY FILEGROUP ' nome do grupo de arquivos'
SET 'especificação de opção' [WITH 'finalização']
COLLATE 'nome da intercalação'
Expandindo arquivo de bancos de dados

Caso você não configure um arquivo já existente para crescimento
automático, ainda assim é possível aumentar seu tamanho. Atribuir em
valor igual a zero para o incremento de crescimento indica que ele não
crescerá automaticamente.
Adicionando arquivos secundários de banco de dados

É possível criar arquivos secundários de banco de dados para expandir o
tamanho de um banco de dados. Use arquivos secundários para colocar
arquivos de dados em discos físicos distintos quando você não usar o
recurso de faixas de disco dos sistemas RAID.
ALTER DATABASE corpore MODIFY FILE

(NAME='corpore_data',size=110MB)
Monitorando e expandindo um log de transações
Quando um banco de dados é ampliado ou quando a atividade de modificação de

dados aumenta, você pode precisar expandir o log de transações.
Monitorando o log
Planeje cuidadosamente de forma que você não fique com espaço insuficiente no
log. Monitorar o log de forma regular ajuda você a determinar o momento ideal
para expandi-lo.
Importante:
Caso seu log de transações fique sem espaço, SQL Server não será
capaz de registrar as transações e não permitirá alterações em seu
banco de dados.
É possível monitorar o log de transações com SQL Server Enterprise Manager, a

instrução DBCC SQLPERF (LOGSPACE) ou System Monitor (Monitor do sistema)
do Microsoft Windows.
Você pode monitorar o log de transações de banco de dados individuais usando

os contadores do objeto SQL Server: Database (SQL Server: Banco de dados) no
System Monitor. Esses contadores incluem os que estão listados na tabela a
seguir.

Monitorando situações que produzem atividade intensa no log
Estas são as situações que produzem um aumento de atividade no log de

transações:
Carregamento de informações em uma tabela que possua índices. O SQL

Server registra todas as inserções e alterações feitas no índice. Quando
este carregamento for feito em tabelas e índice, o SQL Server carregará
apenas alocações de extensões;
Transações que executam muitas modificações, como as instruções

INSERT, UPDAT e DELETE, na tabela em uma única transação.
Normalmente, isso ocorre quando a instrução não possui em cláusula
WHERE ou quando essa cláusula é geral demais, resultando em um
grande numero de registros afetados;
A adição ou modificação de dados de texto ou imagem em uma tabela.
Expandindo o log quando necessário

Você pode expandir o log de transações usando o SQL Server Enterprise Manager
ou a instrução ALTER DATABASE.
Reduzindo um banco de dados ou arquivos
Definir a opção de banco de dados autoshrink como verdadeira

Quando há espaço demais alocado, ou quando os registro de espaços diminuem,
você pode reduzir um banco de dados inteiro ou arquivos de dados específicos de
um banco de dados.
Reduzindo um banco de dados inteiro DBCC SHRINKDATABASE (corpore,

25)
É possível reduzir um banco de dados inteiro usando o SQL Server Enterprise
Manager ou executando a instrução SHRINKDATABASE do Database Consistency
Checker (DBCC, verificador de consistência do banco de dados). Isso reduz o
tamanho de todos os arquivos de dados no banco de dados.
O SQL Server reduz arquivos de log usando uma operação de redução com
adiamento e faz isso como se todos os arquivos do log existissem em um conjunto
de logs contíguo. Os arquivos de log são reinicializados quando o log é truncado,
o SQL Server tenta reduzir os arquivos de log truncados para o tamanho mais
próximo possível do tamanho desejado.
DBCC SHRINKDATABASE (nome_do_banco_de_dados [,percentual_desejado] [,

{NOTRUNCATE | TRUNCATEONLY}] )

A tabela a seguir descreve as opções de DBCC SHRINKDATABASE.
Exemplo:
Este exemplo reduz o tamanho de Sample para que o banco de dados
tenha 25% de espaço livre.
DBCC SHRINKDATABASE (corpore, 25)
No exemplo anterior, se o arquivo de banco de dados Sample contiver 6 MB de

dados, o novo tamanho do banco de dados será de 8 MB (6MB de dados: 2MB
de espaço livre).
Atenção:
O SQL Server não reduz um arquivo a um tamanho menor que a
quantidade de espaço ocupada pelos dados. Além disso, ele não reduz
um arquivo além do tamanho especificado pelo parâmetro Size da
instrução CREATE DATABASE.
Reduzindo um arquivo de dados no banco de dados DBCC SHRINKFILE

(corpore, 10)
Você pode reduzir um arquivo de dados em um banco de dados usando o SQL
Server Enterprise Manager ou executando a instrução DBCC SHRINKFILE.
DBCC SHIRINKFILE({nome_arquivo|identificação_do_arquivo}[,tamanho_desejado]
[, { EMPTYFILE | NORTRUCATE | TRUNCATEONLY}] )
A tabela a seguir descrever as opções de DBCC SHRINKFILE.
Exemplo:
Este exemplo reduz o tamanho do arquivo de dados SampleData a 10
MB.
DBCC SHRINKFILE ( corpore_data, 10)

Reduzindo um banco de dados automaticamente
A redução automática não fica ativada por padrão. Ao definir a opção de banco de
dados autoshrink como verdadeira, você pode definir uma opção de banco de
dados para recuperar automaticamente o espaço não utilizado. Também pode
alterar esta opção com o SQL Server Enterprise Manager.
Considere os seguintes aspectos e diretrizes quando reduzir um banco de dados

ou arquivo de dados:
O banco de dados resultante deve ser maior que o banco de dados model
ou que os dados existentes no banco de dados ou arquivo de dados;
Antes de reduzir um banco de dados ou arquivo de dados, você deve fazer

backup do banco de dados, bem como do banco de dados master;
As instruções SHRINKDATABASE e DBCC SHRINKFILE são executadas

com adiamento, portanto, talvez você não veja uma redução imediata no
tamanho do banco de dados ou do arquivo;
A instrução DBCC SHRINKFILE permite que você reduza o tamanho de

banco de dados abaixo do tamanho especificado quando o banco de dados
foi criado ou alterado. Porém, ele nunca será menor que o tamanho que os
dados ocupam.
Descartando um banco de dados
Métodos para descartar um banco de dados SQL Server Enterprise

Manager;
Instrução DROP DATABASE ( DROP DATABASE Northwind, pubs);
Restrições relativas ao descarte de um banco de dados;
Enquanto estiver sendo restaurado;
Quando um usuário estiver conectado a ele;
Quando estiver publicando como parte de uma duplicação;
Ser for um banco de dados de sistema.
Observação:
Você pode descartar um banco de dados quando não precisar mais
dele. Descartar um banco de dados exclui o banco de dados e os
arquivos de disco usados por ele.
Métodos para descartar um banco de dados

Você pode descartar banco de dados executando a instrução DROP DATABASE .
DROP DATABASE nome_do_banco_de_dados [,..n]
Exemplo:
DROP DATABASE Northwind
Restrições relativas ao descarte de um banco de dados

As seguintes restrições dizem respeito ao descarte de banco de dados. Você não
pode descartar:
Um banco de dados que esteja sendo restaurado;

Um banco de dados que esteja aberto para leitura ou gravação por
qualquer usuário;
Um banco de dados que esteja publicando qualquer uma das tabelas como
parte de uma replicação do SQL;
Um banco de dados de sistema.

Exercícios
Objetivo
Criar um banco de dados;

Alterar opções do banco de dados;
Descartar um banco de dados.
1) Abra e examine o script cria_banco.sql.

2) Execute o script cria_banco.sql.
3) Crie um banco de dados conforme dados informados abaixo:
4) Execute o comando sp_helpdb no database que acabou de criar e examine o

resultado.
5) Abra e examine o script alterando_database.sql.
6) Execute o script alterando_database.sql
7) Drop o database criado pelo script cria_banco.sql.

MÓDULO TIPOS DE DADOS E TABELAS
Criando tipos de dados
Antes de criar uma tabela, você deve definir os tipos de dados para a tabela. Os
tipos de dados especificam os tipos de informações (caracteres, números ou
datas) que uma coluna pode conter, bem como a maneira como os dados são
armazenados. O SQL Server também permite tipos de dados definidos pelo
usuário que se baseiam em tipos de dados do sistema.
Tipos de dados fornecidos pelo sistema

Numérico:
Inteiro;
Numérico exato;
Numérico aproximado;
Monetário.
Data e hora;
Caractere e caractere Unicode;
Binário;
Outros.
Os tipos de dados definem o valor do campo permitido para cada coluna. O SQL
Server fornece vários tipos de dados diferentes. Alguns tipos de dados comuns
possuem vários tipos de dados associados do SQL Server. Escolha os tipos de
dados apropriados para otimizar o desempenho e poupar espaço em disco.
Categorias de tipos de dados fornecidos pelo sistema

A tabela a seguir faz o mapeamento de tipos de dados comuns para os tipos de
dados fornecidos pelo sistema SQL Server. A tabela inclui sinônimos de tipos de
dados para manter a compatibilidade com o ANSI(American National Standards
Institute, instituto nacional de padronização americano).

Tipos de dados numéricos exatos e aproximados
O modo como você planeja usar um tipo de dados determinará a escolha de um
tipo de dados numérico exato ou numérico aproximado.
Tipos de dados numéricos exatos

Os tipos de dados numéricos exatos permitem que você especifique exatamente a
escala e a precisão que utilizara. Por exemplo, você pode especificar três dígitos a
direita do ponto decimal e quatro a esquerda. Uma consulta sempre retorna
exatamente o que você inseriu. O SQL Server oferece suporte a dois tipos de dados
numéricos exatos para compatibilidade ANSI: decimal e numeric.
Em geral, você usaria tipo de dado numérico exatos em aplicações financeiras nas
quais os dados devem ser representados de forma consistente e para realizar
consultas nessa coluna.
Tipos de dados numéricos aproximados

Os tipos de dados numéricos aproximados armazenam os dados com a maior
precisão possível. Por exemplo, a fração 1/3 é representada em um sistema
decimal com 0,33333 (repetição). O numero não pode ser armazenado com
precisão, portanto, e armazenado uma aproximação.
O SQL Server oferece suporte a dois tipos de dados aproximados: float e real. Se
estiver arredondando números ou efetuando verificações de qualidade entre os
valores, evite usar tipos de dados numéricos aproximados.
Criando e descartando tipos de dados definidos pelo usuário

Criando
EXEC SP_ADDTYPE EMAIL, ‘NVARCHAR(20)’, NULL
Descartando
EXEC SP_DROPTYPE EMAIL
Os tipos de dados definidos pelo usuário se baseiam em tipos de dados fornecidos

pelo sistema. Eles permitem aprimorar ainda mais os tipos de dados para
assegurar a consistência durante o trabalho com elementos de dados comuns em
tabelas ou bancos de dados diferentes. Um tipo de dado definido pelo usuário é
especificado para uma determinado banco de dados.
Criando um tipo de dados definido pelo usuário

O procedimento armazenado do sistema sp_addtype cria tipos de dados definidos
pelo usuário.
S P _ A D D T Y P E { t i p o } , [ t i p o _ d e _ d a d o s _ d o _ s i s t e m a ] [ , [ ‘ N U L L’ | ’ N O T
NULL’]][,’nome_do_proprietario’]
Descartando um tipo de dados definido pelo usuário

O procedimento armazenado do sistema sp_droptype exclui tipos de dados
definidos pelo usuário da tabela do sistema systypes. Um tipo de dados definido
pelo usuário não poderá ser descartado se tabelas ou outros objetos de banco de
dados fizerem referencia a ele.
SP_DROPTYPE [‘tipo’]

Diretrizes para especificar tipos de dados
Se o comprimento da coluna variar, use um dos tipos de dados variáveis;

Use tinyint de forma apropriada;
Para tipos de dados numéricos, use decimal;
Use o tipo de dado money para unidade monetária;
Não use os tipos de dados aproximados float e real como chaves primarias.
Considere as diretrizes a seguir para selecionar tipos de dados e equilibrar o

tamanho de armazenamento com os requisitos:
Se o comprimento da coluna for variável, use um dos dados variáveis. Por

exemplo, se tiver uma lista de nomes,você poderá defini-la como varchar
em vez de char (fixo);
Se possuir uma cadeia de livrarias em rápido crescimento e tiver

especificado o tipo de dados tinyint para o identificador de loja no banco
de dados, você terá problemas quando decidir abrir a loja de numero 256;
Para tipos de dados numéricos, o tamanho e o nível de precisão

necessário ajudam a determinar sua escolha. Em geral, use decimal;
Use o tipo de dado money para unidade monetária;
Não use os tipos de dados aproximados float e real como chaves primárias.
Como os valores desses tipos de dados não são precisos, não convém usá-
los em comparações.
Criando tabelas - Informações Gerais

Após definir os tipos de dados para sua tabela, você poderá criar tabelas,
adicionar e descartar colunas e gerar valores de colunas.
Como o SQL Server organiza dados em registros
Um registro de dados consiste em um cabeçalho de registro e uma parte de

dados. É importante entender os elementos da parte de dados de cada registro
para estimar com precisão o tamanho de uma tabela.
Cabeçalho de registro
O cabeçalho de registro de 4 bytes contém informações sobre as colunas no
registro de dados, como um ponteiro que indica o local do final da parte de dados
fixos do registro e a existência ou não de colunas de tamanho variável no registro.
Parte de dados
A parte de dados de um registro pode conter os seguintes elementos:
Dados de tamanho fixo

Os dados de tamanho fixo são inseridos na página antes dos dados de
tamanho variável. Um registro vazio de dados de tamanho fixo ocupa o
mesmo espaço que um registro preenchido com dados de tamanho fixo.
Uma tabela contendo somente colunas de tamanho fixo armazena sempre
o mesmo numero de registros em uma página.
Bloco nulo
Um bloco nulo representa um conjunto de bytes de tamanho variável. Ele
consiste em dois bytes, que armazenam o numero de colunas, seguidos de
um bitmap nulo que indica se cada coluna individual é nula. O tamanho de
um bitmap nulo é igual a um bit por coluna, arredondando para o byte
mais próximo. Uma a oito colunas requerem um bitmap de 1 byte. Nove a
dezesseis colunas requerem um bitmap de 2 bytes.
Bloco variável
Um bloco variável consiste em dois bytes que descrevem o número de
colunas de tamanho variável presentes. Uma quantidade adicional de dois
bytes por coluna aponta para o final de cada coluna de tamanho variável.
O bloco variável será omitido se não houver nenhuma coluna de tamanho
variável.
Dados de tamanho variável

Os dados de tamanho variável são inseridos na página após o bloco
variável. Um registro vazio de dados de tamanho variável não ocupa
espaço. Uma tabela com colunas de tamanho variável pode ter alguns
registros longos ou vários registros curtos.
Dica:
Sempre que possível, mantenha compacto o comprimento do registro
para permitir o ajuste de mais registro em uma página. Isso reduzirá
I/O e aumentará a taxa de acertos no cache do buffer.
Os tipos de dados do tamanho variável podem ser armazenados como uma

coleção de paginas ou em registros de dados.
Organizando dados text, ntext e image

Eles são:
O tipo de dado text, pode conter 2.147.483.647 caracteres. O tipo de

dados text não-Unicode não pode ser usado para variáveis ou parâmetros
nos procedimentos armazenados;
O tipo de dado ntext, pode conter no máximo 230 -1(1.073.741.823)

caracteres ou 231bytes, que corresponde a 2.127.483.647 bytes de dados
Unicode de tamanho variável. O sinônimo SQL-92 para ntext é o National
Text;
O tipo de dado image, que pode conter de 0 a 2.147.483.647 bytes de

dados binários.
Como os tipos de dados text, ntext e image normalmente são extensos, o SQL
Server armazena-os fora dos registros. Um ponteiro de 16 bytes no registro de
dados para uma estrutura raiz que contém os dados. A estrutura raiz de texto
forma o nó da árvore B, que aponta para o blocos de dados. Caso haja mais de 32
quilobytes (KB) de dados, os nós intermediários da árvore B serão adicionados
entre o nó raiz e os blocos de dados.
Isso permitirá uma rápida navegação na árvore B, iniciando no meio de uma

seqüência. No caso dos tipos de dados text, ntext e image com conteúdo de
tamanho pequeno a médio, o SQL Server permitirá o armazenamento de valores
no registro de dados, e não em uma estrutura de arvore B separada. Use a opção
“text in row” (Texto do registro) para fazer essa especificação.
Você também pode definir o limite da opção, com intervalo variando entre 24 a
7.000 bytes. Para ativar a opção “text in row” para uma tabela, use o
procedimento armazenado do sistema “sp_tableoption”. Este exemplo define a
opção “text in row” para “ON”, através do procedimento armazenado do sistema
“sp_tableoption”, e especifica que no máximo 1000 caracteres de text, ntext e
image serão armazenados na página de dados.
EXEC SP_TABLEOPTION N'PFUNC' 'TEXT ½ row ' 1000

Criando e descartando uma tabela
Ao criar uma tabela, você deve especificar o nome da tabela, os nomes das
colunas e os tipos de dados das colunas. Os nomes das colunas devem ser
exclusivos para uma tabela, mas o mesmo nome de coluna pode ser usado em
diferentes tabelas dentro do mesmo banco de dados. Você deve especificar um
tipo de dados para cada coluna.
Criando uma tabela

Considere os fatos a seguir quando criar tabelas no SQL Server. Você pode ter até:
Dois bilhões de tabelas por banco de dados;

1.024 colunas por tabela;
8060 bytes pode registro (esse comprimento maximo aproximado nao se
aplica aos tipos de dados image,text e ntext).
Intercalação de coluna
O SQL Server oferece suporte ao armazenamento de objetos com diferentes
intercalações no mesmo banco de dados. As intercalações separadas do SQL
Server podem ser especificadas no nível de coluna, de modo que cada coluna de
uma tabela obtenha uma intercalação diferente.
Especificando NULL ou NOT NULL

Você pode espeficicar na definição da tabela se devem ser permitidos valores
nulos em cada coluna. Se você não especificar NULL ou NOT NULL, o SQL Server
fornecerá a característica de NULL ou NOT NULL de acordo com o padrão do nível
de sessão ou de banco de dados. Entretanto, esses padrões podem mudar;
portanto, não confie neles. NOT NULL é o padrão do SQL Server.
CREATE TABLE nome_da_tabela
Nome_da_coluna_tipo_de_dados [COLLATE<nome_da_intercalação>]
{NULL| NOT NULL]
Nome_da_coluna AS expressão_de_coluna_calculada
[,...n]
Exemplo:
Cria a tabela dbo.Vendedor, especificando as colunas da tabela, um
tipo de dados para cada coluna e se a coluna permite valores nulos.
CREATE TABLE dbo.vendedor

(id_vendedor INT IDENTITY (1,1) NOT NULL,
Nome VARCHAR(100) NOT NULL,
Email VARCHAR(100) NOT NULL,
Comissao MONEY NULL )
Colunas calculadas
A coluna calculada é uma coluna virtual que não está fisicamente armazenada na
tabela. O SQL Server usa uma fórmula criada pelo usuário para calcular o valor
dessa coluna usando outras colunas da mesma tabela. A utilização de um nome
de coluna calculada em uma consulta pode simplificar a sintaxe da consulta.

Descartando uma tabela
A ação de descartar uma tabela remove a definição da tabela e todos os dados,
bem como as especificações de permissões para essa tabela. Antes de descartar
uma tabela, você deve remover quaisquer dependências entre a tabela e outros
objetos. Para exibir as dependências existentes, execute o procedimento
armazenado do sistema sp_depends.
DROP TABLE nome_da_tabela [,..n]
Adicionando e descartando uma coluna
Adicionar e descartar colunas são duas maneiras usadas para modificar tabelas.
ALTER TABLE tabela

{ | [ALTER COLUMN nome_da_coluna]
| {ADD
{<definição_de_coluna>: :=
Nome_da_coluna tipo_de_dados
{ [NULL NOT NULL]
| DROP COLUMN nome_da_coluna} [,...n]
Adicionado uma coluna

O tipo de informações que você especifica quando adiciona uma coluna é
semelhante ao fornecido quando você cria uma tabela.
Exemplo:
Adiciona uma coluna que permite valores nulos.
ALTER TABLE vendedor ADD endereço VARCHAR(100) NULL
Descartando uma coluna

As colunas descartadas são irrecuperáveis. Portanto, esteja seguro de que deseja
remover uma coluna antes de fazê-lo.
Exemplo:
Descarta uma coluna de uma tabela.
ALTER TABLE vendedor DROP COLUMN comissão
Observação:
Todos os índices e restrições que se baseiam em uma coluna devem
ser removidos antes de descartar a coluna.
Gerando valores de colunas
Usando a propriedade Identity;

Usando a função NEWID e tipo de dados uniqueidentifier.
Diversos recursos permitem que você gere valores de colunas: a propriedade

“Identity”, a função “NEWID” e o tipo de dados “uniqueidentifier”.
Usando a propriedade Identity
Requisitos para utilização da propriedade Identity:
Somente uma coluna de identidade é permitida por tabela;

Use com o tipo de dados integer, numeric ou decimal.
Recuperando informações sobre a propriedade Identity:
Use IDENT_SEED e IDEN_INCR para informações sobre definição;

Use @@identity para determinar o valor mais recente.
Gerenciando a propriedade Identity
Você pode usar a propriedade Identity para criar colunas (referidas como colunas
de identidade) que contêm valores seqüências gerados pelo sistema que
identificam cada registro inserido em uma coluna em uma tabela. Um coluna de
identidade é usada com freqüência em valores de chave primaria.
Fazer com que o SQL Server forneça automaticamente valores de chaves pode
reduzir custos e melhorar o desempenho. Esse recurso simplifica a programação,
mantém os valores da chave primaria curtos e reduz gargalos em transações do
usuário.
CREATE TABLE tabel a

(nome_da_coluna tipo_de_dados
[IDENTITY [(valor_inicial,incremento)]] NOT NULL)
Considere os requisitos a seguir para a utilização da propriedade Identity:
Somente uma coluna de identidade é permitida por tabela;
Ela deve se usada com o tipo de dados integer(int,bigint,smallint ou

tinyint), numeric ou decimal. Os tipos de dados numeric e decimal devem
ser especificados com uma escala 0;
Ela não pode ser atualizada;
Você pode usar a palavra–chave IDENTITYCOL no lugar do nome da coluna

em uma consulta. Esse procedimento permitirá que você faça referência a
coluna da tabela que contém a propriedade Identity sem que precise sabe
o nome coluna;
Ela não permite valores nulos.
É possível recuperar as informações sobre a propriedade Identity de diversas

maneiras:
Duas funções do sistema retornam informações sobre a definição de uma

coluna de identidade: IDENT_SEED (retorna o valor inicial) e IDENT_INCR
(retorna o valor do incremento);
È possível recuperar dados a partir das colunas de identidade usando a
variável global @@identity, que determina o valor do último registro
inserido em uma coluna de identidade durante uma sessão;
SCOPE_IDENTITY retoma o último valor de IDENTITY inserido em uma

coluna de identidade no mesmo escopo. O escopo é um procedimento
armazenado, um disparador, uma função ou um lote;
IDENT_CURRENT retorna o último valor de identidade gerado para uma

tabela especificada em qualquer sessão e qualquer escopo.
É possível gerenciar a propriedade Identity de diversas maneiras:
Você pode permitir que valores explícitos sejam inseridos na coluna de

identidade de uma tabela definindo a opção IDENTITY_INSERT para ON.
Quando a opção IDENTITY_INSERT estiver definida para ON, as
instruções INSERT deverão fornecer uma valor;

Para verificar e, possivelmente, corrigir o valor de identidade atual de uma
tabela, você poderá usar a instrução “DBCC CHEKIDENT”. DBCC
CHECKIDENT permite comparar o valor de identidade atual como o valor
máximo na coluna de identidade.
Exemplo:
Cria uma tabela com três colunas, CODIGO (identificação do cliente)
NOME (Nome do cliente) e EMAIL (E-mail do cliente). A propriedade
Identity é usada para incrementar o valor automaticamente em cada
registro adicionado à coluna CODIGO. O valor inicial é definido como
10, e o valor incremento é 10. Os valores na coluna seriam 10,20,30 e
assim por diante. O uso de 10 como um valor incremento permite que
você insira posteriormente entre os valores.
CREATE TABLE dbo.cliente

(código INT IDENTITY (10,10) NOT NULL,
Nome VARCHAR(50) NOT NULL,
Email VARCHAR(40) NULL)
Usando a função NEWID e o tipo de dados uniqueidentifier
Esses recursos são usados em conjunto;

Use valores globalmente exclusivos;
Use com a restrição DEFAULT.
CREATE TABLE dbo.fornecedor

(id_for UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
nome VARCHAR(100) NOT NULL)
O tipo de dados uniqueidentifier e a funcao NEWID são dois recursos usados em

conjunto. Use-se quando os dados de várias tabelas forem intercalados em uma
tabela maior e quando for necessário manter exclusividade entre todos os
registros:
O tipo de dados uniqueidentifier armazena um numero de identificação

exclusivo com uma seqüência binária de 16 bytes. Esse tipo de dados é
usado para armazenar um identificador global exclusivo (GUI);
A função NEWID cria um número identificador exclusivo que pode

armazenar um GUID usando o tipo de dados uniqueidentifier;
O tipo de dados uniqueidentifier não gera automaticamente novas

identificações para os registros inseridos como a propriedade Identity. Para
obter os novos valores de uniqueidentifier, defina uma tabela com a
restrição DEFAULT que especifica a função NEWID. Ao usar uma instrução
INSERT, especifique também a função NEWID.
Exemplo:
A coluna de identificação do cliente da tabela Cliente foi criada com um
tipo de dados uniqueidentifier, com um valor padrão, gerado pela
função NEWID. Um valor exclusivo para a coluna CODIGO
(Identificação do cliente) será gerado para cada registro novo existente.
CREATE TABLE cliente

(codigo UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID( ),
nome NVARCHAR(100) NOT NULL)
Exercícios
Objetivos
Criar tipos de dados definidos pelo usuário

Criar tabelas
Adicionar e descartar tabelas
Gerar scripts a partir de um banco de dados

1) Abra o Database Engine Query selecione o database CORPORE.
2) Abra o script cria_tipo_dados.sql e examine o script.
3) Execute o script cria_tipo_dados.sql.
4) Utilizando a sintaxe do SQL 2005 crie os seguintes tipos de dados:
5) Abra e examine o script cria_tabela.sql.
6) Execute o script cria_tabela.sql.
7) Crie no banco de dados CORPORE as seguintes tabelas definindo os nomes de

colunas e seus respectivos tipos de dados conforme quadro abaixo:
Tabela: TBL_CLIENTE
Tabela: TBL_EMPRESA
8) Abra e examine o script drop_tabela.sql.
9) Execute o script drop_tabela.sql.
10) Abra e examine o script alter_table_tabela.sql.

11) Execute o script alter_table_tabela.sql.
12) Adicione a coluna email na tabela TBL_CLIENTE utilizando o tipo de dados

Email criado por você no exercício 4.
13) Descarte a tabela TBL_CLIENTE.

MÓDULO IMPLEMENTANDO A INTEGRIDADE DE
DADOS
Tipos de Integridade de dados
Uma etapa no planejamento de um banco de dados é decidir a melhor maneira de
se impor a integridade de dados. A integridade de dados refere-se à consistência
e à precisão dos dados que estão armazenados em um banco de dados. Os
diferentes tipos de integridade de dados são apresentados a seguir.
Integridade de domínio
A integridade de domínio (ou de coluna) especifica um conjunto de valores de
dados que são válidos para uma coluna e determina se serão permitidos valores
nulos. Esse tipo de integridade é geralmente imposto através do uso de uma
verificação de validade e também pode ser imposto restringindo-se o tipo, o
formato ou a faixa de valores possíveis de dados que será permitida em uma
coluna.
Integridade de entidade
A integridade de entidade (ou de tabela) requer que todos os registros de uma
tabela tenham um identificador único, conhecido como o valor da chave primaria.
A possibilidade de alterar o valor da chave primaria ou de excluir o registro inteiro
dependerá do nível de integridade requerido entre a chave primaria e todas as
outras tabelas.
Integridade Referencial
A integridade referencial garante que o relacionamento entre as chaves primárias
(na tabela referenciada) e as chaves externas (nas tabelas de referência) sejam
sempre mantidos. Não será possível excluir um registro de uma tabela
referenciada nem alterar a chave primária de um registro se uma chave externa
fizer referência ao registro, a menos que uma ação em cascata seja permitida.
Você pode definir relacionamentos de integridade referencial na mesma tabela ou
entre tabelas distintas.
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 restrições, padrões e
regras.

Na integridade declarativa, você define os critérios aos quais os dados devem
atender como parte da definição de um objeto, e o SQL Server garantirá
automaticamente a conformidade dos dados com esses critérios. O método
preferencial de implementação da integridade básica dos dados é usar a
integridade declarativa.
Considere os seguintes fatos sobre esses métodos:
A integridade é exposta como parte da definição do banco de dados

através do uso de restrições declarativas definidas diretamente em tabelas
e colunas;
Implementa a integridade declarativa usando restrições, padrões e regras.
Integridade de dados procedural
Critérios definidos em script;

O script impõe;
Implemente a integridade procedural usando disparadores e
procedimentos armazenados.
Na integridade procedural, você escreve scripts que definem os critérios aos quais
os dados devem atender e impor os critérios. Limite o uso da integridade
procedural às exceções e à lógica comercial mais complexa.
Exemplo:
Use esse tipo de integridade quando desejar uma exclusão em cascata.
Os fatos a seguir aplicam-se à integridade procedural:
A integridade procedural pode ser implementada no cliente ou no servidor

com o uso de outras ferramentas e linguagens de programação;
Implemente a integridade procedural usando disparadores e

Definindo restrições
As restrições são o método preferencial para se impor a integridade de dados. Esta

seção aborda como determinar o tipo de restrição que será usado, o tipo de
integridade de dados que cada tipo de restrição impõe e como definir restrições.
Determinando o tipo de restrição que será usado

As restrições são um método baseado no padrão ANSI para impor a integridade
de dados. Cada tipo de integridade domínio, entidade e referencial é imposto com
tipos diferentes de restrições. As restrições garantem que valores validos sejam
inseridos nas colunas e que os relacionamentos entre as tabelas sejam mantidos.

A tabela a seguir descreve os diferentes tipos de restrições:
Criando restrições
As restrições são definidas usando-se a instrução CREATE TABLE ou ALTER

TABLE.
Você pode adicionar restrições a uma tabela que já contenha dados e colocá-las
em uma única ou em várias colunas:
Se a restrição se aplicar a uma única coluna, ela será chamada de

“restrição em nível de coluna”;
Se uma restrição fizer referência a várias colunas, ela será chamada de
“restrição em nível de tabela”, mesmo que não faça referência a todas as
colunas da tabela.
CREATE TABELA nome_da_tabelas
({<definição_de_coluna>
| <restrição_de_tabela>} [,...n])
<definição_de_coluna> ::= {nome_da_coluna tipo_de_ dados}

[[ DEFAULT expressão_constante]
[<restrição_de_coluna>] [,..n]
<restrição_de_coluna> ::=
[CONSTRAINT nome_da_restricao]
\ [ { PRIMARY KEY | UNIQUE}
[CLUSTERED | NONCLUSTERED]]
| [ [FOREIGN KEY]
REFERENCES tabela_referenciada [(coluna_referenciada)]
[ON DELETE { CASCADE | NO ACTION}]
[ON UPDATE { CASCADE | NO ACTION}]]
CHECK ( expressao_logica)

<restricao_de_tabela> ::=
{ [ { PRIMARY KEY | UNIQUE}
[CLUSTERED | NOCLUSTERED]
(COLUNA [ASC | DESC] [,..N]) } ]
| FOREIGN KEY
[ ( coluna [,..n])]
REFERENCES tabela_referenciada [(coluna_referenciada [,...
.n])]
[ON DELETE {CASCADE | NO ACTION}]
[ON UPDADE {CASCADE | NO ACTION}]
CHECK (condições_de_pesquisa)}
Exemplo:
Cria a tabela “Produtos”, define colunas e define restrições em nível de
coluna e de tabela.
CREATE TABLE dbo.Produto

(
Id_produto int identity (1,1) not null.
Nome nvarchar(30) not null,
DataCompra datetime not null CONSTRAINT DF_Produto_Data

DEFAULT (GETDATE()),
Valor money not null CONSTRAINT DF_Produto_Valor DEFAULT (0),
Fornecedor varchar (20) null,
CONSTRAINT PK_Produto PRIMARY KEY (Id_produto),
CONSTRAINT FK_produto_fornecedor FOREIGN KEY

(Fornecedor)
REFERENCES dbo.fornecedor (codigo) on UPDADE

CASCADE,
CONSTRAINT CK_Produtos_valor CHECK (valor>0)

)
Considerações sobre o uso de restrições

Considere os seguintes fatos ao implementar ou modificar restrições:
Você pode criar, alterar e descartar restrições sem precisar descartar e

recriar uma tabela;
Você deve criar uma lógica de verificação de erros em seu aplicativo e nas
transações para verificar se uma restrição foi violada;
O SQL Server verificar os dados existentes quando você adicionar uma

restrição a uma tabela.
Você deve especificar nomes para as restrições ao criá-las, pois o SQL Server
fornece nomes complexos, gerado pelo sistema. Os nomes devem ser exclusivos
para o proprietário do objeto do banco de dados e seguir a regras definidas para
os identificadores do SQL Server. Para obter ajuda sobres restrições, execute o
procedimento armazenado do sistema “sp_helpconstraint” ou “sp_help”, ou
consulte as views do esquema de informações, como check_constraints,
referential_constraints e table_constraints.
Tipos de restrições
Restrições DEFAULT
Aplicam-se apenas a instruções INSERT;

Apenas uma restrição DEFAULT por coluna;

Não podem ser usadas como propriedade IDENTITY ou o tipo de dados
rowversion;
Permitem algum valores fornecidos pelo sistema.
A restrição DEFAULT insere um valor em uma coluna quando nenhum

valor e especificado em uma instrução INSERT. As restrições DEFAULT
impõem a integridade de domínio.
DEFAULT expressão-constante
Exemplo:
Adicionar uma restrição DEFAULT que insere o valor NÃO
INFORMADO na tabela cliente se um contato não for fornecidor.
USE corpore
GO
ALTER TABELA dbo.cliente
ADD
CONSTRAINT DF_cliente_contato DEFAULT 'NÃO INFORMADO' FOR
CONTATO
GO
Observação:
Ela permite que alguns valores fornecidos pelo sistema USER,
CURRENT_USER, SESSION_USER, SYSTEM_USER ou
CURRENT_TIMESTAMP sejam especificados em vez de valores
definidos pelo usuário. Esses valores fornecidos pelo sistema podem
ser úteis para oferecer um registro dos usuários que inseriram dados.
Restrições CHECK
São usadas com as instruções INSERT e UPDATE;

Podem fazer referência a outras colunas na mesma tabela;
Não podem:
Ser usadas com o tipo de dados rowversion
Conter subconsultas.
A restrição CHECK restringe a valores específicos os dados que os usuários

podem inserir em uma determinada coluna. As restrições CHECK
assemelham-se às cláusulas WHERE, pois permitem especificar as
condições sob as quais os dados serão aceitos.
CHECK ( expressão_lógica)
Exemplo:
Adiciona uma restrição CHECK para garantir que uma data de
nascimento esteja em conformidade com um intervalo aceitável de
datas.
USE corpore
GO
ALTER TABLE dbo.vendedor ADD
CONSTRAINT CK_NASCIMENTO
CHECK (dtnascimento >'01-01-1900' AND dtnascimento<GETDATE())
GO
Observação:
Se algum dado violar a restrição CHECK, você poderá executar a
instrução DBCC CHECKCONSTRAINTS para retornar os registros
responsáveis pela violação.
Restrições PRIMARY KEY
Apenas uma restrição PRIMARY KEY por tabela;

Os valores devem ser exclusivos;
Não são permitidos valore nulos;
Cria um índice exclusivo nas colunas especificadas.

A restrição PRIMARY KEY define uma chave primária em uma tabela que
identifica de modo exclusivo um registro.
Ela impõe a integridade de entidade.
PRIMARY KEY [CLUSTERED | NONCLUSTERED]
{ ( coluna [,…n) }
Exemplo:
Adiciona uma restrição que específica que o valor da chave primária da
tabela Fornecedor é a identificação do cliente e indica que será criado
um índice sem agrupamento para impor a restrição.
USE corpore
GO
ALTER TABLE dbo.Fornecedor
ADD
CONSTRAINT PK_Fornecedor
PRIMARY KEY NONCLUSTERED (codigo)
GO
Observação:
Ela cria um índice exclusivo nas colunas especificadas. Você pode
especificar um índice de agrupamento ou um outro qualquer (o índice
de agrupamento será o padrão se ele ainda não existir).
O índice criado para uma restrição PRIMARY KEY não pode ser
descartado diretamente. Ele será descartado quando você descartar a
restrição.
Restrições UNIQUE
Permitem um valor nulo;

Permitem várias restrições UNIQUE em uma tabela;
Definidas com uma ou mais colunas;
Impostas com um índice exclusivo.
A restrição UNIQUE especifica que dois registros de uma coluna não

podem ter o mesmo valor. Essa restrição impõe a integridade de entidade
com um índice exclusivo.
A restrição UNIQUE é útil quando você já tem uma chave primária, como
o número de funcionário, mas deseja garantir que outros identificadores,
como o numero da carteira de motorista.
UNIQUE [CLUSTERED | NONCLUSTERED]
{ ( coluna [,…n) }
Exemplo:
Cria uma restrição UNIQUE no nome da empresa na tabela Fornecedor
USE corpore
GO
ALTER TABLE dbo.Fornecedor
ADD
CONSTRAINT U_nome
UNIQUE NONCLUSTERED (NOME)
GO
Observação:
E possível aplicar a restrição UNIQUE a uma ou mais colunas que
devam ter valores exclusivos, mas que não sejam a chave primária de
uma tabela.

Restrições FOREIGN KEY
Devem fazer referencia a uma restrição PRIMARY KEY ou UNIQUE;

Fornecem uma integridade referencial de uma várias colunas;
Não criam índices automaticamente;
Os usuários devem ter as permissões SELECT ou REFERENCES em
tabelas referenciadas.
A restrição FOREIGN KEY impõe a integridade referencial. Ela define uma

referência a uma coluna que contenha uma restrição PRIMARY KEY ou
UNIQUE na mesma ou em outra tabela.
FOREIGN KEY [ ( coluna[,…n)]
REFERENCES tabela_referenciada [ (
coluna_referenciada [,…n])]
Exemplo:
Usa uma restrição FOREIGN KEY para garantir a identificação do cliente
na tabela PEDIDO associada a uma identificação válida na tabela
“Cliente”.
USE CURSO
GO
ALTER TABLE dbo.Pedido
ADD CONSTRAINT FK_pedido_cliente
FOREIGN KEY (Id_cliente)
REFERENCES dbo.Cliente (Id_cliente)
GO
Observação:
Você só poderá usar a cláusula REFERENCES sem a cláusula FOREIGN
KEY ao fazer referência a uma coluna da mesma tabela.
Integridade referencial em cascata
A restrição FOREIGN KEY inclui uma opção CASCADE que permite que qualquer
alteração em um valor de coluna que defina uma restrição UNIQUE ou PRIMARY
KEY seja propagada automaticamente para o valor da chave externa.
Essa ação é referenciada como integridade referencial em cascata. As cláusulas

REFERENCES das instruções CREATE TABLE e ALTER TABLE oferecem suporte às
cláusulas ON DELETE e ON UPDADE. Essas cláusulas permitem especificar a
opção CASCADE ou ON ACTION.
[FOREIGN KEY [ ( coluna[,…n)]
REFERENCES tabela_referenciada [ (
coluna_referenciada [,…n])].
[ON DELETE { CASCADE | NO ACTION }]
[ON UPDATE { CASCADE | NO ACTION
}]

A instrução NO ACTION especifica que qualquer tentativa feita por chaves
externas de excluir ou atualizar uma chave referenciada em outras tabelas gera
um erro e a alteração é cancelada. NO ACTION é o padrão. Se a instrução
CASCADE for definida e um registro for alterado na tabela pai, o registro
correspondente será alterado na tabela referenciada.
Considere os seguintes fatos ao aplicar a opção CASCADE:
É possível combinar as ações CASCADE e NO ACTION em tabelas que

tenham relacionamentos referenciais ente si. Se o SQL Server encontrar a
ação NO ACTION, terminará e desfará as ações relacionadas à ação
CASCADE. Quando uma instrução DELETE gera uma combinação entre as
ações CASCADE e NO ACTION, todas as ações CASCADE são aplicadas
antes de o SQL Server verificar se existe alguma ação NO ACTION;
A ação CASCADE não pode ser especificada para nenhuma coluna de

chave externa ou primária definida com a coluna rowversion.
Desativando restrições
Por motivos de desempenho, às vezes recomenda-se a desativação de restrições.
É mais eficiente, por exemplo permitir o processamento de operações em lote
grandes antes de ativar restrições. Esta seção descreve como desativar a
verificação de restrições, independentemente de você estar criando uma nova
restrição ou desativando uma existente.
Desativando a verificação de restrições nos dados existentes
Só é possível desativar as restrições CHECK e FOREIGN KEY;

Use a opção WITH NOCHECK quando adicionar uma nova restrição;
Use a opção WITH NOCHECK se dados existentes não forem alterados;
E possível alterar dados existentes antes de adicionar restrições.
Quando você define uma restrição em uma tabela que já contenha dados, o SQL
Server verifica os dados automaticamente para assegurar que eles atendam aos
requisitos da restrição. No entanto, você pode desativar a verificação de restrições
nos dados existentes ao adicionar uma restrição à tabela.
Considere as seguintes diretrizes ao desativar a verificação de restrições nos

dados existentes.
Só é possível desativar as restrições CHECK e FOREIGN KEY. Outras

restrições devem ser descartadas e depois adicionadas novamente;
Para desativar a verificação de restrições ao adicionar uma restrição

CHECK ou FOREIGN KEY a uma tabela que já tenha contenha dados, inclua
a opção WITH NOCHECK na instrução ALTER TABLE;
Use a opção WITH NOCHECK se os dados existentes não forem alterados.

Os dados deverão estar em conformidade com as restrições CHECK se
forem atualizados;
Certifique-se de que seja apropriado desativar a verificação de restrições.

Você poderá executar uma consulta para alterar os dados existentes antes
de decidir adicionar uma restrição.
ALTER TABLE tabela

[WITH CHECK | WITH NOCHECK]
ADD C CONSTRAINT nome_da_restricao]
[FOREIGN KEY [ ( coluna[,…n)]
REFERENCES
tabela_referenciada[(coluna_referenciada [,…n])]
[CHECK (condicoes_de_pesquisa)]
Exemplo:
Adiciona uma restrição FOREIGN KEY que verifica se todos os produtos
estão associados com um fornecedor valido. A restrição não e imposta
nos dados existentes no momento em que é adicionada.
USE corpore
GO
ALTER TABLE dbo.produto

WITH NOCHECK
ADD CONSTRAINT FK_produto_fornecedor
FOREIGN KEY (id_fornecedor)
REFERENCES dbo.Fornecedor(codigo)
GO
Desativando a verificação de restrições ao carregar novos dados

É possível desativar a verificação das restrições CHECK e FOREIGN KEY existentes
para que todos os dados modificados ou adicionados à tabela não sejam
verificados em relação à restrição.
Para evitar os custos associados a verificação de restrições, você poderá desativar

as restrições quando:
Tiver certeza de que os dados obedecem as restrições;

Deseja carregar dados que não obedeçam as restrições. Posteriormente,
você poderá executar consultas para alterar os dados e, depois, reativar as
restrições.
Para ativar uma restrição que tenha sido desativada, será necessário executar
outra instrução ALTER TABLE que contenha uma cláusula CHECK ou CHECK ALL.
ALTER TABLE tabela

{CHECK | NOCHECK} CONSTRAINT
{ALL | restricao [,…n]}
Este exemplo desativa a restricao FK_produto_fornecedor. Ela poderá ser reativada

através da execução de outra instrução ALTER TABLE com a cláusula CHECK.
USE CURSO
GO
ALTER TABLE dbo.produto
NOCHECK
CONSTRAINT FK_produto_fornecedor
Go
Para saber se uma restrição está ativada ou desativada em uma tabela, execute o
procedimento armazenado do sistema SP_HELP ou use a propriedade
CnstlsDisabled na função OBJECTPROPERTY.
Usando padrões e regras

Como os objetos independentes , eles:
São definidos uma vez;

Podem ser ligados a uma ou mais colunas ou aos tipos de dados definidos
pelo usuário.
Padrões e regras são objetos que podem estar ligados a uma ou mais colunas ou
a tipos de dados definidos pelo usuário, tomando possível defini-los uma vez e
usá-los inúmeras vezes. Uma desvantagem do uso de padrões e regras é que eles
não estão em conformidade com o padrão ANSI.
Criando um padrão
Se você não especificar um valor ao inserir os dados, um padrão especificará um
valor à qual o objeto está ligado. Considere estes fatos antes de criar padrões:
Quaisquer regras ligadas à coluna e aos tipos de dados tornam válidos os

valores de um padrão;
Todas as restrições CHECK na coluna devem tornar válido o valor de um

padrão;
Você não poderá criar uma restrição DEFAULT em uma coluna que esteja
definida com um tipo de dados definido pelo usuário se já existir um
padrão ligado ao tipo de dados ou a coluna.
CREATE DEFAULT padrão

As expressão_constante

Ligando um padrão
Após criar um padrão, você deverá ligá-lo a uma coluna ou a tipo de dados pelo
usuário executando o procedimento armazenado do sistema sp_bindefault. Para
desanexar um padrão, execute o procedimento armazenado do sistema
sp_unbindefault.
Criando uma regra

As regras especificam os valores aceitáveis que podem ser inseridos em uma
coluna. Elas garantem que os dados estejam dentro de um intervalo especificado
de valores, correspondam a um determinado padrão ou correspondam às
entradas de uma lista especificada.
Considere estes fatos sobre as regras:
A definição de regra pode conter qualquer expressão que seja válida em

uma cláusula WHERE;
Só poderá haver uma regra ligada a uma coluna ou a um tipo de dados
definido pelo usuário.
CREATE RULE regra

As expressão da_condição
Ligando uma regra

Após criar uma regra, você deverá ligá-la a uma coluna ou um tipo de dados
definido pelo usuário executando o procedimento armazenado do sistema
sp_bindrule. Para desanexar uma regra, execute o procedimento armazenado do
sistema sp_unbindrule.
USE CORPORE
GO
CREATE DEFAULT TELEFONEDEF AS '55-(31)2222-9090'
GO
EXEC SP_BINDEFAULT TELEFONEDEF, 'ppessoa.telefone'
Descartando um padrão ou uma regra

A instrução DROP remove um padrão ou uma regra do banco de dados
DROP DEFAULT padrão [, ...n]

DROP RULE regra [, ...n]
Definido que método de imposição usar

Você deverá considerar a funcionalidade e a sobrecarga ao determinar os métodos
que usará para impor a integridade de dados:
É preferível usar a integridade declarativa para a lógica de integridade

fundamental, como, por exemplo, para impor valores válidos e manter os
relacionamentos entre as tabelas;
Se você desejar manter dados redundantes e complexos que não fazem

parte de um relacionamento de chave primária ou externa, use
disparadores ou procedimentos armazenados.

No entanto, como os disparadores são acionados somente quando ocorre uma
modificação, a verificação de erros acontece depois que a instrução é concluída.
Quando um disparador detecta uma violação, ele deve desfazer as alterações.
Práticas recomendadas
As práticas recomendadas a seguir devem ajudá-lo a implementar a integridade
de dados:
Use restrições, pois estão em conformidade com o padrão ANSI e as

ferramentas de desenvolvimento de terceiros oferecem suporte a elas;
Use a integridade referencial em cascata em vez de disparadores.

Exercícios
Objetivos
Definir e usar instruções DEAFULT e CHECK

Definir e usar restrições PRIMARY KEY e FOREIGN KEY
2) Abra e examine o script cria_default.sql.
3) Execute o script cria_default.sql.
4) Execute o script insere_dados_tbl_venda.sql e verifique o resultado executando

uma instrução SELECT na tabela TBL_CLIENTE.
5) Abra e examine o script cria_check.sql.
6) Execute o script cria_check.sql.
7) Abra e execute o script insere_dados_tbl_clientes1. Após execução execute a

instrução SELECT na tabela e verifique o resultado.
8) Abra e execute o script insere_dados_tbl_clientes2. Qual foi a mensagem e por

que ocorreu a mesma?
___________________________________________________________________________________________
___________________________________________________________________________________________
___________________________________________________________________________________________
_____________________________________________________________________________
9) Abra e examine o script cria_primarykey.sql
10) Execute o script cria_primarykey.sql.
11) Crie uma tabela conforme informações abaixo:
Tabela: TBL_FUNC
12) Abra e examine o script cria_foreignkey.sql.
13) Execute o script cria_foreignkey.sql.
14) Execute o script insere_TBL_VENDA. Por que ocorreu erro?

___________________________________________________________________________________________
___________________________________________________________________________________________
___________________________________________________________________
15) Execute o script insere_tbl_cliente1. O que ocorreu? Quantos registros foram

inseridos?
___________________________________________________________________________________________
___________________________________________________________________________________________
___________________________________________________________________________________________
_____________________________________________________________________________

MÓDULO IMPLEMENTANDO VIEWS
Introdução às views
As views permitem armazenar uma consulta predefinida como um objeto no
banco de dados para o uso posterior. As tabelas consultadas em uma view são
chamadas “tabelas base”. Com algumas exceções, você pode nomear e
armazenar qualquer instrução SELECT como uma view.
Alguns exemplos de views são:
Um subconjunto de registros ou colunas de uma tabela base;

Uma união de duas ou mais tabelas base;
Uma associação de duas ou mais tabelas base;
Um resumo estatístico de uma tabela base;
Um subconjunto de outra view ou alguma combinação de views e tabela
base.
Vantagens das views

As views oferecem diversas vantagens, incluindo focalizar os dados para os
usuários, mascarar a complexidade dos dados, simplificar o gerenciamento de
permissões e organizar dados para serem exportados para outros aplicativos.
Focalizar os dados para os usuários

As views criam um ambiente controlado que permite o acesso de dados
específicos enquanto outros dados ficam ocultos. Dados desnecessários,
confidenciais ou inadequados podem ser deixados fora de uma view. Os usuários
podem manipular a exibição dos dados em uma view da mesma forma que em
uma tabela.
Além disso, com as permissões apropriadas e algumas restrições, eles podem

modificar os dados produzidos pela view.
Mascarar a complexidade do banco de dados

As views isolam do usuário a complexidade do design do banco de dados. Isso
permite que os desenvolvedores alterem o design sem afetar a interação do
usuário com o banco de dados. Além disso, os usuários podem ver uma versão
mais amigável dos dados usando nomes mais fáceis de compreender do que os
nomes abreviados geralmente usados nos bancos de dados.
Consultas complexas, incluindo consultas distribuídas a dados heterogêneos,
também podem ser mascaradas através de views. O usuário consulta a view em
vez de escrever a consulta ou executar um script.
Simplificar o gerenciamento de permissões de usuários

Em vez de conceder permissão para que os usuários consultem colunas
específicas em tabelas base, os proprietários de bancos de dados podem conceder
permissões para que os usuários consultem dados somente através de views. Isso
também protege as alterações no design das tabelas base subjacentes. Os
usuários podem continuar a view sem interrupção.
Melhorar o desempenho
As views permitem que você armazene os resultados de consultas complexas.
Outras consultas podem usar esses resultados resumidos. As views também
permitem o particionamento dos dados. Você pode colocar partições individuais
em computadores separados.
Organizar dados para serem exportados para outros aplicativos

Você pode criar uma view com base em uma consulta complexa que associe duas
ou mais tabelas e, depois, exportar os dados para outro aplicativo para análise
adicional.
Definido views
Esta seção descreve como criar, alterar e descartar views. Ela também aborda
como evitar cadeias de propriedades interrompidas, ocultar definições de views e
obter informações sobre views em seu banco de dados.
Criando views
Criando uma view;
Restrições às definições de views:
Não pode incluir a instrução ORDER BY
Não pode incluir a palavra-chave INTO
Você pode criar views usando o Create View Wizard (Assistente para criação de
views), o SQL Server Enterprise Manage (Gerenciador corporativo do SQL Server)
ou transact-SQL. As views só podem ser criadas no banco de dadosatual.
Quando você cria uma view, o Microsoft® SQL Server verifica a existência de
objetos aos quais a definição da view faz referência. O nome da view deve seguir
as regras dos identificadores. A especificação do nome do proprietário da view é
opcional. Você deve desenvolver uma conservação de nomeação consistente para
fazer a distinção entre views e tabelas.
Exemplo:
Você poderia adicionar a palavra view como sufixo para cada objeto
dde view criado. Isso permite que você diferencie facilmente objetos
semelhantes (tabelas e view) ao consultar a view
INFORMATION_SCEMA.TABLES.
Sintaxe:
CREATE VIEW proprietário.nome_da_view [(coluna [, n])]

[WITH {ENCRYPTION | VIEW METADATA} [, n] ]
AS
Instrução_select
[WITH CHECK OPTION]
Para executar a instrução CREATE VIEW, voce deverá ser participante do cargo de
administradores do sistema (sysadmin), do cargo de proprietário do banco de
dados (db_owner) ou do cargo de administrador de linguagem de definição de
dados (db_ddladmin) ou deverá ter permissão CREATE VLEW. Você também
deverá ter a permissão SELECT em todas as tabelas ou views às quais a view faz
referência.
Para evitar situações em que o proprietário de uma view e o proprietário das

tabelas subjacentes sejam diferentes, recomenda-se que o usuário “dbo” possua
todos os objetos de um banco de dados. Ao criar o objeto, especifique sempre o
usuário dbo como nome do proprietário, caso contrário, o objeto será criado com
o seu nome de usuário como o proprietário do objeto.
Especifique o conteúdo de view usando uma instrução SELECT, com algumas

limitações, as views podem ser tão complexas quanto você desejar:
Você deverá especificar nomes de colunas se:
Quaisquer colunas da view derivam de uma expressão aritmética, de uma

função interna ou de uma constante;

Quaisquer colunas de tabelas que serão associadas compartilharem o
mesmo nome.
Importante:
Quando você cria uma view, é importante testar a instrução SELECT
que define a view para garantir que o SQL Server retome o conjunto de
resultados esperado. Depois de escrever e testar a instrução SELECT e
verificar os resultados, crie a view.
Restrições às definições de views
Ao criar views, considere as restrições a seguir:
A instrução CREATE VIEW não pode incluir as cláusulas COMPUTE BY. A

instrução CREATE VIEW não pode incluir a palavra chave INTO;
A instrução CREATE VIEW só poderá incluir a cláusula ORDER BY se a
palavra chave top for usada;
As views não podem fazer referência a tabelas temporárias;
As views não podem fazer referência a mais de 1.024 colunas;
A instrução CREATE VIEW não pode ser combinada com outras instruções
Transact-SQL em um único lote.
USE corpore
GO
CREATE VIEW dbo.fcfo_flan
AS
SELECT fcfo.nome,SUM(valororiginal)
FROM
fcfo
JOIN
flan
ON
flan.codcolcfo=fcfo.codcoligada and
flan.codcfo=fcfo.codcfo
GROUP BY fcfo.nome
ORDER BY fcfo.nome
Alterando e Descartando views

Alterando views;
Matem permissões atribuídas;
Faz com que as novas opções e instrução SELECT substituam a definição
existente;
Descartando views.
Frequentemente, você altera as views em resposta a solicitações de informações

adicionais feitas pelos usuários ou as alterações na definição da tabela
subjacentes. Você pode alterar uma view descartando-a e criando-a novamente ou
executando a instrução ALTER VIEW.
Alterando views
A instrução ALTER VIEW altera a definição de view, incluindo views indexadas,
sem afetar os disparadores ou procedimentos armazenados dependentes. Isso
permite manter as permissões da view. Essa instrução está sujeita às mesmas
restrições que a instrução CREATE VIEW. Se descartar uma view e criá-la
novamente, você deverá reatribuir permissões a ela.
Sintaxe:
ALTER VIEW proprietário.nome_da_view

[ (coluna [ , ...n ] ) ]
[WITH {ENCRYPTION | SCHEMABINDING|VIEW_METADATA}[,…n] ]
AS
Instrução_select
[WITH CHECK OPTION]

Observação:
Se você usar a opção WHIT CHECK OPTION, WIHT ENCRYPTION,
WITH SCHEMABINDING ou WITH VIEW_METADATA ao criar a view,
deverá incluí-la na instrução ALTER VIEW para manter a
funcionalidade fornecida pela opção.
Exemplo:
ALTER VIEW dbo.fcfo_flan
AS
SELECT fcfo.nome,AVG(valororiginal)
FROM
fcfo
JOIN
flan
ON
GROUP BY fcfo.nome
ORDER BY fcfo.nome
Observação:
Se você definir uma view com uma instrução SELECT * e, depois,
alterar a estrutura das tabelas adjacentes adicionando colunas, as
novas colunas não aparecerão na view. Quando todas as colunas são
selecionadas em uma instrução CREATE VIEW, a lista de colunas é
interpretada apenas quando você cria uma view pela primeira vez.
Para ver as novas colunas na view, você deve altera-la.
Descartando views
Se não precisar mais de uma view, você poderá remover sua definição do banco
de dados executando a instrução DROP VIEW. Ao descartar uma view, você
remove sua definição e todas as permissões atribuídas a ela.
Além disso, se os usuários consultarem qualquer view que faça referência à view
descartada, eles receberão uma mensagem de erro. No entanto, descartar uma
tabela que faça referência a uma view não descartada automaticamente, deverá
ser descartada explicitamente.
Observação:
A permissão para descartar uma view atribuída ao proprietário da view
e é intransferível. Esse é o padrão. No entanto, o administrador do
sistema ou o proprietário do banco de dados pode descartar qualquer
objeto especificando o nome do propritário na instrução DROP VIEW.
Evitando cadeias de propriedades interrompidas

O SQL Server permite que o proprietário do objeto original mantenha o controle
sobre os usuários que estão autorizados a acessar o objeto.
Objetos dependentes com propriedades diferentes

As definições de views dependem dos objetos subjacentes (views ou tabelas).
Essas dependências podem ser consideradas como a cadeia de propriedades. Se
o proprietário de uma view também possuir os objetos subjacentes, ele só terá de
conceder permissão para a view. Quando o objeto é usado, as permissões são
verificadas somente na view.
Para evitar cadeias de propriedades interrompidas, o usuário dbo deve ser

proprietário de todas as views. Quando o objeto é usado, as permissões são
verificadas em cada objeto dependente com um proprietário diferente.
Localizando informações sobre definições de views
Talvez você deseje ver a definição de uma view para altera-la ou entender como
seus dados são derivados das tabelas base.
Localizando definições de views

Você pode localizar informações sobre definições de views com o SQL Server
Enterprise Manager ou consultando as seguintes views de tabela do sistema.

Observação:
INFORMATION_SCHEMA.VIEW_TABLE_USAGE
e INFORMATION_SCHEMA.VIEW_COLUMN_USAGE exibem
informações referentes apenas a seu nome de usuário.
Não disponível se a view foi criada com a opção WITH ENCRYPTION
Para exibir o texto usado para criar uma view, use o SQL Server Enterprise
Manager, consulte INFORATION_SCHEMA.VIEWS ou execute o procedimento
armazenado do sistema sp_helptext com o nome de view como parâmetro.
Sintaxe:
sp_helptext nome_do_objeto
Localizando dependências de views

Para recuperar um relatório das tabelas ou views das quais uma views depende e
dos objetos que dependem de uma determinada view, use o SQL Server Enterprise
Manager ou execute o procedimento armazenado do sistema spdepends.
Você deve exibir as dependências antes de descartar qualquer objeto. Antes de
alterar ou descartar uma tabela, use o procedimento armazenado do sistema
sp_depends para determinar se algum objeto faz referência à tabela.
Sintaxe:
sp_depends nome_do_objeto
Ocultando definições de views

Como os usuários podem exibir a definição de uma view usando o SQL Server
Enterprise Manager, consultando INFORMATION_SCHEMA.VIEWS ou
consultando a tabela do sistema syscomments, talvez você deseje ocultar cenas
definições de views.
Usar a opção WITH ENCYPTION
Voce pode criptografar as entradas da tabela syscomments que contêm o texto da
instrução CREATE VIEW especificando a opção WITH ENCRYPTION na definição
de view. Antes de criptografar uma view, certifique-se de que a sua definição
(script)seja salva em um arquivo. Para descriptografar o texto de uma view, você
deve descarta-la e cria-la novamente ou altera-la e usar a sintaxe original.
Exemplo:
CREATE VIEW dbo.fcfo_flan_max
WITH ENCRYPTION
AS
SELECT fcfo.nome,MAX(valororiginal)
FROM
fcfo
JOIN
flan
ON
GROUP BY fcfo.nome
ORDER BY fcfo.nome
Não excluir entradas da tabela syscomments
Quando as considerações sobre segurança exigem que a definição de view não
fique disponível para os usuários, use a criptografia. Nunca exclua entradas da
tabela syscomments.
Isso impedirá que você use a view também impedirá que o SQL Server a crie
novamente quando você atualizar um banco de dados para uma versão mais
recente do SQL Server.
Modificando dados através de views

As views não mantêm um cópia separada dos dados. Em vez disso, elas mostram
o conjunto de resultados de uma consulta em uma ou mais tabelas base.
Portanto, sempre que você modifica dados em uma view, a tabela base é que é
realmente modificada.
Com algumas restrições, você poderá inserir, atualizar ou excluir livremente dados
de tabelas através de uma view. Em geral, a view deve ser definida em uma única
tabela e não deve incluir funções agregadas ou cláusulas GROUP BY na instrução
SELECT. Especificamente, as modificações feitas com o uso de views:
Não podem afetar mais de uma tabela subjacente

Você pode modificar views derivadas de duas ou mais tabelas, mas cada
atualização ou modificação pode afetar apenas uma tabela.
Não podem ser feitas em certas colunas

O SQL Server não permite que você altere uma coluna que seja o resultado de um
cálculo, como as colunas que contêm valores calculados.
Poderão ocasionar erros se afetarem colunas às quais a view não faz

referência
Por exemplo, você receberá uma mensagem de erro se inserir em uma view um
registro que seja definido em uma tabela com colunas às quais a view não foi
referência e que não permitem NULLs nem contêm valores padrão.
Serão verificadas se a opção WITH CHECK OPTION for especificada na

definição da view.
A opção WITH CHECK OPTION força todas as instruções de modificação de dados
que são executadas na view a obedecer a certos critérios. Esses critérios são
especificados na instrução SELECT que define a view. Se os valores alterados
estivessem fora do intervalo da definição da view, o SQL Server rejeitará as
modificações.
Otimizando o desempenho com o uso de views

Esta seção descreve as considerações sobre o desempenho para o uso de views
e como as views permitem otimizar o desempenho através do armazenamento
dos resultados de consultas complexas e do particionamento dos dados.
Considerações sobre o desempenho

Quando views que associam diversas tabelas e avaliam expressões complexas
são aninhadas dentro de outra view, poderá ser difícil determina a origem de
qualquer problema de desempenho. Portanto, convém considerar a criação de
definições de views separadas, em vez de aninhar views.
Usando views indexadas

Você pode criar índices em views. Uma view indexada armazena o conjunto de
resultados de view no banco de dados. Devido ao tempo rápido de recuperação,
é possível usar views indexadas para melhorar o desempenho de consultas.
Criando uma view indexada

Crie uma view indexada implementando um índice UNIQUE CLUSTERED em uma
view. Os resultados da view são armazenados nas páginas do nível folha do índice
de agrupamento. Depois de criar o índice UNIQUE CLUSTERED, você poderá criar
outros índices nessa view. Uma view indexada reflete automaticamente as
modificações afetadas nos dados das tabelas base. A medida que os dados são
alterados, o índice UNIQUE CLUSTERED é atualizado.

Diretrizes para a criação de views indexadas
O otimizador de consultas determina automaticamente se uma consulta se
beneficiará do uso de uma view indexada. Isso ocorre mesmo que a consulta não
faça referência à view indexada. Como prática geral, deixe que o otimizador de
consultas determine quando usar views indexadas.
Usando o “Index Tuning Wizard” (Assistente para ajuste de índice), você pode
melhorar significativamente sua capacidade de determinar a melhor combinação
de índices e views indexadas para otimizar o desempenho de consultas.
Crie views indexadas quando:
A melhoria de desempenho em termos da maior velocidade na recuperação

dos resultados compensa o maior custo de manutenção;
Os dados subjacentes não são atualizados com freqüência;
As consultas executam uma quantidade significativa de associações e
agregações que processam vários registros ou são executadas
frequentemente por vários usuários.
Restrições à criação de views indexadas

Ao criar views indexadas, considere as restrições a seguir:
O primeiro índice criado em uma view deve ser um índice de agrupamento

exclusivo;
Você deve criar a view com a opção SCHEMABINDING;
A view pode fazer referência a tabelas base, mas não a outras views;
Você deve usar nomes de duas partes para fazer referência a tabelas e
funções definidas pelo usuário;
As conexões subseqüentes devem ter as mesmas configurações de opções
para usar a view indexada.
Observação:
Você deve usar a propriedade IsIndexable da função
OBJECTPROPERTY para certificar-se de que possa indexar uma view.
Usando views para particionar dados

Você pode usar views para particionar dados em vários bancos de dados ou
ocorrências do SQL Server para melhorar o desempenho.
Como o SQL server usa view para particionar dados

Você pode usar o operador de conjuntos UNION em uma view para
combinar os resultados de duas ou mais consultas de tabelas separadas
em um único conjunto de resultados. Esse conjunto de resultados aparece
para o usuário como uma única tabela chamada view particionada. Você
poderá atualizar as views particionadas mesmo que elas façam referência
a várias tabelas.
As views particionadas podem se basear em dados de várias origens

heterogêneas, como servidores remotos, e não apenas de tabelas do
mesmo banco de dados. Isso permite distribuir o processamento do banco
de dados através de um grupo de servidores. O grupo de servidores pode
dar suporte às necessidades de processamento de aplicativos de larga
escala de comércio eletrônico ou de centros de dados corporativos.
Como as views particionadas melhoram o desempenho

Se as tabelas de uma view particionada estiverem em servidores diferentes
ou em um computador com vários processadores, cada tabela envolvida
na consulta poderá ser examinada em paralelo, melhorando o desempenho
da consulta. Além disso, tarefas de manutenção, como a recriação de
índices ou backup de tabelas, poderão ser executadas mais rapidamente.
Observação:
Não é possível criar um índice em uma view particionada. A definição
de view necessária para criar a view indexada permite somente nomes
de duas partes; as views particionadas requerem o uso de nomes de
três ou quatro partes, como nome_ do_servidor.nome_do_banco_de
dados. Nome_do_proprietário.nome_do_objeto.

Exercícios
Objetivos
Criar uma view.

Alterando uma view.
Descartando uma view.
2) Abra e examine o script cria_view.sql.
3) Execute o script cria_view.sql.
4) Execute a instrução select na view criada pelo script cria_view.sql.
5) Crie uma view chamada LISTA_EMPRESA_FILIAL que tenha como retorno o

nome da empresa (GCOLIGADA.NOME) e nome da filial (GFILIAL.NOME).
6) Execute o comando SP_HELPTEXT LISTA_EMPRESA_FILIAL e verifique o

retorno do comando.
7) Altere a view para que traga o código da filial (GFILIAL.CODIGO). Verifique

executando a instrução SELECT.
8) Altere a view de modo que ela seja criada com a opção WITH ENCRYPTION.
9) Execute o comando SP_HELPTEXT LISTA_EMPRESA_FILIAL e verifique o

retorno do comando.
10) Descarte a view LISTA_EMPRESA_FILIAL.

MÓDULO PROCEDIMENTOS ARMAZENADOS
Definido procedimentos armazenados
Coleções nomeadas de instruções Transact-SQL encapsulamento de
tarefas repetitivas;
Cinco tipos (sistema, local, temporário, remoto e estendido);
Aceitam parâmetros de entrada e tornam valores;
Retornam um valor de status para indicar um êxito ou uma falha;
Um procedimento armazenado é uma coleção nomeada de instruções Transact-

SQL que é armazenada no servidor. Os procedimentos armazenados são um
método de encapsulamento de tarefas repetitivas. Eles oferecem suporte para
variáveis declaradas pelo usuário, execução condicional e outros recursos
avançados de programação.
O SQL Server oferece suporte a cinco tipos de procedimentos armazenados:
Procedimentos armazenados do sistema (sp)

Armazenados no banco de dados master, os procedimentos armazenados
do sistema (identificados pelo prefixo “sp”.) fornecem um método eficaz
para recuperar informações das tabelas do sistema. Eles permitem que os
administradores de sistema executem tarefas de administração de banco
de dados que atualizam as tabelas do sistema, mesmo que eles não
tenham permissão para atualizar as diretamente tabelas subjacentes. Os
procedimentos armazenados do sistema podem ser executados em
qualquer banco de dados.
Procedimentos armazenados locais

Os procedimentos armazenados locais são criados em banco de dados de
usuário individuais.
Procedimentos armazenados temporários

Os procedimentos armazenados temporários podem ser locais, com nomes
que começam com um sinal de tralha único (#), ou global, com nomes que
iniciam com um sinal de tralha duplo (##). Os procedimentos armazenados
temporários estão disponíveis em uma sessão de usuário único.
Procedimentos armazenados globais estão disponíveis para todas as
sessões de usuários.
Procedimentos armazenados remotos

O procedimento armazenado remotos é um recurso anterior do SQL Server.
Agora as consultas distribuídas dão suporte a essa funcionalidade.
Procedimentos armazenados estendidos (xp)

Estes procedimentos são implementados como dynamic-link libraries
(DLLs, bibliotecas de vínculos dinâmicos) executadas fora do ambiente do
SQL Server. Os procedimentos armazenados estendidos costumam ser
identificados pelo prefixo “xp_“. Eles são executados de modo semelhante
aos procedimentos armazenados. Os procedimentos armazenados do SQL
Server assemelham-se aos procedimentos de outras linguagens de
programação sob os seguintes aspectos:
Contêm instruções que executam operações no banco de dados,

incluindo a capacidade de chamar outros procedimentos armazenados;
Aceitam parâmetros de entrada;
Retomam um valor de status para um lote ou procedimento armazenado
de chamada para indicar um êxito ou uma falha (e a razão da falha);
Retomam vários valores para o lote ou o procedimento armazenado de
chamada na forma de parâmetros de saídas.

Processamento inicial de procedimentos armazenados
O processamento de um procedimento armazenado inclui a sua criação e, depois,

sua execução pela primeira vez, que coloca o plano de consulta respectivo no
cache. O cache de procedimentos é um conjunto de páginas que contém planos
de execução para todas as instruções Transact-SQL executadas no momento.
O tamanho desse cache varia dinamicamente, de acordo com os níveis de

atividade. O cache de procedimentos está localizado no pool de memória, que é a
unidade principal de memória do SQL Server. Ele contém a maioria das estruturas
de dados que usa a memória no SQL Server.
Criação
Quando um procedimento armazenado é criado, as instruções que ele contém são
analisadas para verificar sua precisão sintática. Depois, SQL Server armazena o
nome do procedimento armazenado na tabela do sistema sysobject (objetos do
sistema) e seu texto na tabela do sistema syscomments (comentários do
sistema), dentro do banco de dados atual.
Será retomando um erro se for encontrado um erro de sintaxe, e o procedimento

armazenado não será criado.
Resolução de nomes com atraso

Um processo chamado resolução de nomes com atraso permite que os
procedimentos armazenados façam referências a objetos que não existem quando
o procedimento é criado. Esse processo proporciona flexibilidade, visto que os
procedimentos armazenados e os objetos aos quais eles fazem referência não
precisam ser criados em uma ordem específica.
Os objetos deverão existir quando o procedimento armazenado for executado. A

resolução de nomes com atraso é executada no momento em que o procedimento
armazenado é executado.
Execução (primeira vez ou recompilação)

Na primeira vez que um procedimento armazenado é executado ou se ele precisar
se recompilado, o processador de consultas o lerá em um processo chamado
resolução.
Certas alterações efetuadas em um banco de dados podem tornar um plano de

execução ineficiente ou inválido, o SQL Server detecta essas alterações e
recompila automaticamente o plano de execução quando uma das situações a
seguir:
Uma alteração estrutural é efetuada em uma tabela ou view à qual a

consulta (ALTER TABLE e ALTER VIEW) faça referência;
Novas estatísticas da distribuição são geradas explicitamente em uma

instrução, como UPDATE STATISTICS, ou automaticamente;

Um índice usado pelo plano de execução é descartado;
Alterações significativas são efetuadas nas chaves (instituição INSERT ou

DELETE) de uma tabela à qual a consulta faça referência.
Otimização
Quando um procedimento armazenado passa com êxito pelo estágio de resolução,
o otimizador de consultas do SQL Server analisa as instruções Transact-SQL do
procedimento e cria um plano que contém o método mais rápido de acesso aos
dados. Para fazer isso, ele leva em consideração:
O volume de dados das tabelas;

A presença e a natureza dos índices da tabela e a distribuição dos dados
nas colunas indexadas;
Os operadores e os valores de comparação usados nas condições da
cláusula WHERE;
A presença de associações e das cláusulas UNION, GROUP BY OU ORDER
BY.
Compilação
A compilação diz respeito ao processo de análise do procedimento armazenado e
de um plano de execução que é armazenado no cache de procedimentos. Esse
cache contém os planos de execução de procedimentos armazenados mais
importantes.
Alguns dos fatores que aumentam o valor de execução incluem:

Tempo necessário para a recompilação (alto custo de compilação);
Uso freqüente.
Processamento subseqüente de procedimentos armazenados
O processamento subseqüente de procedimentos armazenados é mais rápido que

o processamento inicial, pois o SQL Server usa o plano de consulta otimizado
armazenado no cache de procedimentos.
Se as condições a seguir se aplicarem, o SQL Server usará o plano armazenado na

memória para executar a consulta posteriormente:
O ambiente atual é igual aquele em que plano foi compilado

As configurações de servidor, banco de dados e conexão determinam o
ambiente.
Os objetos aos quais o procedimento armazenado faz referência não

precisam de resolução de nomes
A resolução de nomes é necessária quando objetos pertencentes a
usuários diferentes possuem nomes idênticos.

Exemplo:
Se o cargo “saies” for proprietário de uma tabela Product (produto) e
o cargo development for proprietário de uma tabela Product, o SQL
Server deverá determinar qual a tabela deverá ser consultada toda vez
que uma tabela Product for referenciada.
Os planos de execução do SQL Server apresentam dois componentes principais:
Plano de consulta
A maior parte do plano de execução encontra-se nessa estrutura de dados
reentrante, somente leitura, que pode ser usada por inúmeros usuários.
Contexto de execução
Cada usuário que executando a consulta no momento possui essa
estrutura de dados reutilizável, que armazena os dados específicos de sua
execução, como os valores de parâmetros. Se um usuário executar uma
consulta, e uma das estruturas não estiver em uso, ela será reinicializado
com o contexto de novo usuário.
Haverá sempre, no máximo, um plano compilado no cache para cada

combinação exclusiva de procedimento armazenado e ambiente. Poderão
existir vários planos no cache para o mesmo procedimento armazenado se
cada um se destinar a um ambiente diferente.
Os fatores a seguir resultam em diferentes ambientes que afetam as escolhas de

compilação:
Planos compilados paralelos versus seriais;

Propriedade implícita de objetos;
Diferentes opções SET.
Observação:
Para obter mais informações sobre os planos de execução paralelos,
consulte o tópico “Degree of Parallelism” (Grau de paralelismo) no
Books Online (livros on-linee) do SQL Server.
Os desenvolvedores devem selecionar um ambiente para seu aplicativo e usá-lo.

Os objetos cuja resolução de propriedade implícita é ambígua devem usar a
resolução explicita especificando o proprietário do objeto. As opções SET devem
ser consistentes, definidas no início de uma conexão e não podem ser alteradas
depois. Uma vez gerado um plano de execução, ele permanece no cache de
procedimentos. O SQL Server remove os planos antigos e não utilizados do cache
somente quando há necessidade de espaço.
Vantagens dos procedimentos armazenados

Os procedimentos armazenados oferecem inúmeras vantagens. Eles podem:
Compartilhar a lógica do aplicativo com outros aplicativos,

garantindo, dessa maneira, o acesso e a modificação consistente dos
dados
Os procedimentos armazenados podem encapsular as regras de negócios.
As regras ou políticas de negócios encapsuladas nos procedimentos
armazenados podem ser alteradas em um único local. Todos os clientes
podem usar os mesmos procedimentos armazenados para garantir o
acesso e a modificação consistente dos dados;
Proteger os usuários da exposição aos detalhes das tabelas do banco

de dados
Se um conjunto de procedimentos armazenados der suporte para todas as
funções de negócios que os usuários precisam executar, eles nunca
precisarão acessar as tabelas diretamente;
Fornecer mecanismos de segurança

Os usuários podem receber permissão para executar um procedimento
armazenado mesmo que tenham permissão para acessar as tabelas ou
views às quais o procedimento armazenado faz referência;

Melhorar o desempenho
Os procedimentos armazenados implementam várias tarefas como uma
série de instruções Transact-SQL. A lógica condicional pode ser aplicada
aos resultados das primeiras instruções Transact-SQL para determinar
quais instruções Transact-SQL subseqüentes serão executadas. Todas
essas instruções Transact-SQL e a lógica condicional tornam-se parte de
um único plano de execução no servidor;
Reduzir o tráfego de rede

Em vez de enviar centenas de instruções Transact-SQL através de rede, os
usuários podem executar uma operação complexa enviando uma única
instrução, o que reduz o número de solicitações transferidas entre o cliente
e o servidor.
Criando, executando e modificando procedimento armazenado

Esta seção descreve como criar, executar, modificar e descartar procedimentos
armazenados.
Criando procedimentos armazenados
Criados no banco de dados atual com a instrução;

Podem ser aninhados em até 32 níveis;
Use sp_help para exibir informações;
Você poderá criar um procedimento armazenado somente no banco de dados

atual, com exceção de procedimentos armazenados temporários, que são criados
no banco de dados tempdb.
A criação de um procedimento armazenado é semelhante a criação de uma view.

em primeiro lugar, escreva e teste as instruções Transact-SQL que você deseja
incluir no procedimento armazenado. Depois, se receber os resultados esperados,
crie o procedimento armazenado.
Usando CREATE PROCEDURE

Os procedimentos armazenados são criados com instrução CREATE PROCEDURE.
Considere os fatos a seguir ao criá-los:
Os procedimentos armazenados podem fazer referência a tabelas, views e

procedimentos armazenados, bem como as tabelas temporárias;
Se um procedimento armazenado criar uma tabela temporária local, essa

tabela só existirá em função do procedimento e desaparecerá após ele ser
executado;
Uma instrução CREATE PROCEDURE não pode ser combinada com outras
instruções SQL em um único lote;
A definição de CREATE PROCEDURE pode incluir qualquer quantidade e

tipo de instruções Transact-SQL, com exceção das instruções de criação de
objetos a seguir: CREATE DEFAULT, CREATE PROCEDURE CREATE RULE,
CREATE TRIGGER e CREATE VIEW. Outros objetos de banco de dados
podem ser criados em um procedimento armazenado e devem ser
qualificados com o nome do proprietário do objeto;
Para executar a instrução CREATE PROCEDURE, você deverá ser

participante do cargo administradores do sistema (sysadmin), do cargo
proprietário do banco de dados (dbowner) ou do cargo administrador data
defination language (DDL, linguagem de definição de dados)
(db_ddladmin), ou ter permissão CREATE PROCEDURE.
O tamanho máximo de um procedimento armazenado é 128 MB,

dependendo da memória disponível.

Sintaxe parcial:
CREATE PROCEDURE ] nome_do_procedimento [; número]

[ { @parâmetro tipo de dados }
[VARYING] [= padrão] [OUTPUT]
] [ , ...n]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE | ENCRYPTION }]
[ FOR REPLICATION]
AS
Instrução_sql [ …n]
Exemplo:
Atualiza o campo valororiginal na tabela flan.
USE corpore
GO
CREATE PROCEDURE dbo.atualizavalor
AS
UPDATE flan set valororiginal=0 where valoriginal IS NULL
go
Aninhando procedimentos armazenados

Os procedimentos armazenados podem ser aninhados (um procedimento
armazenado chama outro).
As características do aninhamento incluem:
Os procedimentos armazenados podem ser aninhados em até 32 níveis. A

tentativa de exceder esse limite fará com que toda a chamada em cadeia
de procedimentos falhe.
O nível de aninhamento atual é armazenado na função do sistema
@@nestlevel.
Se um procedimento armazenado chamar um segundo procedimento
armazenado, esse poderá acessar todos os objetos criados pelo primeiro,
incluindo as tabelas temporárias.
Os procedimentos armazenados aninhados podem ser recursivo. Por
exemplo, o procedimento armazenado X pode chamar o procedimento
armazenado Y. Enquanto Y está sendo executado, ele pode chamar o
procedimento X.
Exibindo informações sobre procedimentos armazenados

Assim como outros objetos de banco de dados, os procedimentos armazenados
do sistema a seguir podem ser usados para localizar informações adicionais sobre
todos os tipos de procedimentos armazenados: sp_help, sp_helptext e
sp_depends.
Para imprimir uma lista de procedimentos armazenados e nomes de proprietários

do banco de dados, use o procedimento armazenado do sistema
sp_store_procedures. Você também pode consultar as tabelas do sistema
sysobjtecs, syscolnments e sysdepends para obter informações.
Diretrizes para a criação de procedimentos armazenados
Usuário dbo deve ser proprietário de todos os procedimentos

armazenados;
Evite usar o prefixo sp_ ao atribuir nomes a procedimentos armazenados;
Todos os procedimentos armazenados devem assumir as mesmas
configurações de conexão.
Minimize o uso de procedimentos armazenados temporários.
Considere as diretrizes a seguir ao criar procedimentos armazenados:
Para evitar situações em que o proprietário de um procedimento

armazenado e o proprietário das tabelas subjacentes sejam diferentes, é
recomendado que o usuário dbo possua todos os objetos do banco de
dados. Como um usuário poder ser participante de vários cargos,
especifique sempre o usuário dbo como o nome de proprietário ao criar o
objeto. Caso contrario, o objeto será criado tendo o seu nome de usuário
como proprietário;
Você também deve ter as permissões apropriadas em todas as tabelas ou
views às quais o procedimento armazenado faz referência;
Evite situações em que o proprietário de um procedimento armazenado e

o proprietário da tabelas subjacentes sejam diferentes;
Crie cada procedimento armazenado para realizar uma única tarefa.
O SQL Server salva as configurações de SET QUOTED_IDENTIFIER e SET

ANSI_NULLS quando um procedimento armazenado é criado ou alterado. Essas
configurações originais são usadas quando o procedimento armazenado é
executado. Portanto, quaisquer configurações de sessão do cliente para essas
opções SET são ignoradas durantes a execução do procedimento armazenado.
Outras opções SET, como SET ARITHABORT, SET ANSI_WARNINGS e SET
ANSI_PADDINGS, não são salvas quando um procedimento armazenado é criado
ou alterado. Para determinar se as opções ANSI SET foram ativadas quando um
procedimento armazenado foi criado, consulte a função do sistema
OBJECTPROPERTY. As opções SET não devem ser alteradas durante a execução
de procedimentos armazenados.
Executando procedimentos armazenados

Executando um procedimento armazenado isoladamente
EXEC atualizavalor
Executando um procedimento armazenado em uma instrução INSERT

Você pode executar um procedimento armazenado isoladamente ou como parte de
uma instrução INSERT. È necessário ter a permissão EXECUTE no procedimento
armazenado.
Executando um procedimento armazenado isoladamente

Você pode executar um procedimento armazenado emitindo a instrução EXECUTE
junto com o nome do procedimento e quaisquer parâmetros.
[[EXEC [UTE]]
{
[@status_do_retorno=]
nome_do_procedimento [;numero] |
@var_nome_do_procedimento }
}
[[ @parametro ={ valor | @variavel [OUTPUT] | [DEFAULT]]
[,...n]
[WITH RECOMPILE]
Exemplos:
01) A instrução a seguir executa um procedimento armazenado que
lista todos os pedidos vencidos.
EXEC listapedidosvencidos
Executando um procedimento armazenado em uma instrucao INSERT

A instrução INSERT pode preencher uma tabela local com um conjunto de
resultados que é retomado de um procedimento armazenado local ou remoto. O
SQL Server carrega a tabela com dados que são retornados das instruções SELECT
do procedimento armazenado. A tabela já deverá exisitir,e o tipos de dados
deverão concidir.
02) As instruções a seguir criam o procedimento armazenado

Clientes_lanc, que insere clientes na tabela ClientesLanc.
USE curso
go
CREATE PROCEDURE dbo.Clientes_lanc
AS
SELECT
fcfo.codcfo,fcfo.nome,fcfo.valor
FROM
flan

JOIN
fcfo
ON
WHERE
flan.valororiginal>30000.00 and
flan.pagrec=1
ORDER BY
fcfo.valor desc
As instruções a seguir executam o procedimento armazenado

Clientes_lanc
INSERT INTO ClientesLanc
EXEC Clientes_lanc
Os lancamentos a receber que possuem valor maior que 30000.00 são

inseridos na tabela ClientesLanc.
Alterando e descartando procedimentos armazenados
Alterando procedimentos armazenados
Inclua quaisquer opções em ALTER PROCEDURE;

Não afeta os procedimento armazenados aninhados;
Descartando procedimentos armazenados;
Execute o procedimentos armazenado sp_depends para determinar se os
objetos dependem do procedimento;
Os procedimentos armazenados são geralmente modificados em resposta às

solicitações dos usuàrios ou às alterações efetuadas nas definições das tabelas
subjacentes.
Para modificar um procedimento armazenado existente e manter as atribuições de

permissão, use a instrucao ALTER PROCEDURE. O SQL Server substitui a
definição anterior do procedimento armazenado quando ele é alterado com ALTER
PROCEDURE.
Considere os fatos a seguir armazenado que tenha sido criado com

quaisquer opções, como WITH ENCRYPTION, inclua a opção na instrução
ALTER PROCEDURE para manter a funcionalidade fornecida pela opção;
ALTER PROCEDURE altera apenas um único procedimento. Se o seu

procedimento chamar outros procedimentos armazenados, os
procedimentos armazenados aninhados não serão afetados;
A pemissão para executar essa instrução assume, por padrão, os

criadores do procedimento armazenado inicial, os participantes do cargo
do servidor sysadmin e os participantes dos cargos fixos de banco de
dados db_owner e db_ddladmin. Você não pode conceder permissão para
executar ALTER PROCEDURE.
Sintaxe:
ALTER PROC[EDURE] nome_do_procedimento [;numero]

[{ @parametro tipo_de_dados}
[VARYING] [=padrao] [OUTPUT]
] [,...N]
[WITH
{RECOMPILE | ENCRYPTION
| RECOMPILE , ENCRYPTION
}
]
[FOR REPLICATION]
AS
instrucao_sql [...n]

Exemplo:
O exemplo a seguir modifica o procedimento armazenado Clientes_lanc
para selecionar somente os clientes que estao com lançamentos com
valor maior que 35000.00.
USE corpore
GO
ALTER PROCEDURE dbo.Clientes_lanc
AS
SELECT
fcfo.codcfo,fcfo.nome, flan.valororiginal
FROM
flan
JOIN
fcfo
ON
WHERE
flan.valororiginal>35000.00 and
flan.pagrec=1
ORDER BY
flan.valororiginal desc
Descartando procedimentos armazenados

Use a instrução DROP PROCEDURE para remover os procedimentos armazenados
definidos pelo usuário do banco de dados atual. Antes de descartar um
procedimento armazenado, execute o procedimento armazenado sp_depends para
determinar se os objetos dependem do procedimento.
Sintaxe:
DROP PROCEDURE [procedimento] [,...n]
Exemplo:
Este exemplo descarta o procedimento armazenado Clientes_lanc
USE corpore
GO
DROP PROCEDURE dbo.Clientes_lanc
Usando parâmetros em procedimentos armazenados
Usando parametros de entrada;

Executando procedimentos armazenados com parâmetros de entrada;
Retornando valores com parâmetros de saída;
Recompilando explicitamente procedimentos armazenados.
Os parâmetros estendem a funcionalidade dos procedimentos armazenados. Você

pode passar informações para procedimentos armazenados e a partir deles
usando parâmetros. Eles permitem que você use o mesmo procedimento
armazenado para pesquisar um banco de dados várias vezes.
Exemplo:
Você pode adicionar parâmetro a um procedimento armazenado que
pesquisa a tabela de funcionarios para localicar os funcionarios cuja
data de contratação corresponde a uma data espeficicada. Depois você
poderá executar o procedimento toda vez que desejar espeficicar uma
data de contratação diferente.
O SQL Server dá suporte para dois tipos de parâmetros: parâmetros de entrada e

de saída.
Usando parâmetros de entrada
Valide todos os valores de parâmetros de entrada primeiro;

Fornceça valores padrão apropriados e inclua verificações NULL.

Os parâmetros de entrada permitem passar informações para um procedimento
armazenado. Para definir um procedimento armazenado que aceite parâmetros de
entrada, declare uma ou mais variaveis como parâmetros na instrucao CREATE
PROCEDURE.
@parâmetro tipo_de_dados [ = padrão]
Considere os fatos e as diretrizes a seguir ao espeficicar parâmetros:
Todos os valores de parâmetros de entrada devem ser verificados no inicio

de um procedimento armazenado para interceptar primeiro valores
ausentes e inválidos;
Você deve fornecer valores padrâo apropriados para um parâmetro. Se for

definido um padrão, os usuários poderão executar o procedimento
armazenado sem espeficicar um valor para esse parâmetro.
Observação:
Os padrões de parâmetro devem ser constantes ou NULL. Ao
espeficicar NULL como um valor padrão de um parâmetro, você deve
usar =NULL; IS NULL não funcionorá, pois a sintaxe não oferece
suporte à designação ANSI NULL.
O número máximo de parâmetros em um procedimento armazenado é

1.024;
O número máximo de variáveis locais em um procedimento armazenado é

limitado somente pela memória disponível;
Os parâmetros são locais a um procedimento armazenado. Os mesmos

nomes de parâmetros podem ser usados com outros procedimentos
armazenados;
As informações sobre parâmetros são armazenadas na tabela do sistema

syscolumns. O exemplo a seguir cria o procedimento armazenado
turnover, que retorna todas demissões entre as datas especificadas.
USE corpore
go
CREATE PROCEDURE dbo.turnover (@datainicial datetime, @datafinal
datetime)
AS
SELECT
pfunc.chapa,pfunc.nome,pfunc.datademissao
FROM
pfunc
WHERE
pfunc.datademissao between @datainicial and @datafinal
ORDER BY
pfunc.datademissao
Executando procedimentos armazenados com parâmetros de entrada

Você pode definir o valor de um parâmetro passando o valor para o procedimento
armazenado por nome de parâmetro ou por posição. Você não deve misturar
formatos diferentes ao fornecer os valores.
Passando valores por nome de parâmetro

A espeficicação de um parâmetro em uma instrução EXECUTE no formato
@parametro = valor é denominada de passagem por nome de parâmetro. Quando
você passa valores por nome de parâmetros, os valores dos parâmetros podem
ser espeficicados em qualquer ordem, e é possível omitir os parâmetros que
permitem valores nulos ou que possuem um padrão.
O valor padrão de um parâmetro, se definido no procedimento, é usado quando:
Nenhum valor é espeficicado para o parâmetro quando o procedimento

armazenado é executado;

A palavra-chave DEFAULT é especificada como o valor do parâmetro.
Sintaxe:
[, [ EXEC [ UTE ] ]
{
[@status_do_retorno ]
{ nome_do_procedimento [; numero] |
@var_nome_do_procedimento }
}
[ [ @parametro= { valor | @variavel [ OUTPUT ] | [ DEFAULT ] ]
[,..N]
[ WITH RECOMPILE ]
Exemplo:
exec turnover @datainicial='01/01/2006',@datafinal='01/01/2007'
Passando valores por posição

A passagem somente de valores (sem uma referencia aos parâmetros para os
quais eles estão sendo passados) é denominada passage de valores por posição.
Quando você especifica somente um valor, os valores dos parâmetros devem ser
listados na ordem em que estão definidos na instrução CREATE PROCEDURE.
Ao passar valores por posição, você poderá omitir os parâmetros quando existirem
padrões, mas não poderá interromper a sequência. Por exemplo, se um
procedimento armazenado tiver cinco parâmetros, você poderá omitir o quarto e o
quinto parâmetros, mas não poderá omitir o quarto parâmetro e espeficicar o
quinto.
Exemplo:
exec turnover '2006-01-01',2007-01-01’
Retornando valores com parâmetros de saída

Os procedimentos armazenados podem retornaar informações para o cliente ou o
procedimento armazenado de chamada com parâmetros de saida(variáveis
designidas com a palavra-chave OUTPUT). Como o uso de parâmetros de saída ,
quaisquer alterações no parâmetro que resultem da execução do procedimento
armazenado poderão ser retidas, mesmo após sua execução ser concluida.
Para usar um parâmetro de saìda, a palava-chave OUTPUT deverá ser especificada

nas instruções CREATE PROCEDURE e EXECUTE. Se essa palavra-chave for
omitida quando o procedimento armazenado for executado, ele ainda será
executado, mas não retornará um valor.
Os parâmetros de saída apresentam as seguintes caracteristicas:
A instrução de chamada deve conter um nome de variável para receber o

valor de retorno. Não è possível passar constantes;
Você poderá usar a variáveis posteriormente em instruções Transact-SQL
adicionais, no procedimento armazenado de chamada ou no lote;
O parâmetro pode ser qualquer tipo de dados, exceto text ou image;
Eles podem ser espaços reservados para o cursor.
Exemplo:
1) Cria um procedimento armazenado que calcular porcentagem de
determinado valor. Este exemplo usa a instrução SET. No entanto, você
também pode usar a instrução SELECT para concatenar uma
sequência dinamicamente. Uma instrução SET requer que você declare
uma variável para imprimir a sequência.
CREATE PROCEDURE dbo.porcentagem

@valor1 float,
@valor2 float,
@resultado float OUTPUT
AS
SET @resultado=@valor1*@valor2/100
GO

Este lote chama o procedimento armazenado porcentagem e passa os
valores 3000 e 10. Esses valores se tornam variáveis que são
fornecidas na instrução SET.
DECLARE @resposta float

EXECUTE porcentagem 3000,10,@resposta OUTPUT
SELECT 'Valor é',@resposta
Resultado: O parâmetro @resultado é designado com a palavra chave

OUTPUT. O SQL Server imprime o conteúdo da variavel @resultado
quando você executa o procedimento armazenado porcentagem. A
variável do resultado é definida como valor da porcentagem de dois
valores 3000 e 10.
Recompilando explicitamente procedimentos armazenados
Recompile quando:
O procedimento armazenado retorna conjuntos de resultados muitos

variados.
Um novo índice é adicionado a uma tabela subjacente.
O valor do parâmetro é atípico
Recompile usando:
CREATE PROCEDURE...[ WITH RECOMPILE].

EXECUTE... [WITH RECOMPILE].
Sp_recompile.
Os procedimentos armazenados podem ser recompilados explicitamente, mas

você não deve fazer isso com frequência.
Use esse procedimento somente quando:
Os valores de parâmetros são passados para um procedimento

armazenado que retorna de resultado muitos variados;
Um ano índice é adicionado a uma tabela subjacente da qual um

procedimento armazenado poderá se beneficiar;
O valor do parâmetro fornecido é atípico.
O SQL server fornece três métodos para a recompilação explícita de um

procedimento armazenado.
CREATE PROCEDURE... [WITH RECOMPILE]
A instrução CREATE PROCEDURE... [WITH RECOMPILE] indica que o SQL Server

não armazena no cache um plano para esse procedimento armazenado. Em vex
disso, a opção recompia o procedimento armazenado toda vez que ele é
executado.
A instrução a seguir recompila o procedimento porcentagem toda vez que é

executado.
CREATE PROCEDURE dbo.porcentagem

@valor1 float,
@valor2 float,
@resultado float OUTPUT
WITH RECOMPILE
AS
SET @resultado=@valor1*@valor2/100
GO
EXECUTE...[WITH RECOMPILE]

A instrução EXECUTE...[WITH RECOMPILE] cria um novo plano de execução cada
vez que o procedimento é executado, se você especificar WITH RECOMPILE. O
novo plano de execução não é colocado no cache. Use essa opção se o parametro
que você está passando for muito diferente dos que geralmente são passados para
o procedimento armazenado.
Com esse plano otimizado é uma exceção e não a regra, quando a execução for
concluída, você deverá executar novamente o procedimento armazenado com um
parâmetro que seja passado normalmente. Essa opção também será útil se os
dados tiverem sido significativamente alterados desde que o procedimento
armazenado foi compilado pela última vez. Esse exemplo recompila o
procedimento armazenado sp_help no momento em que ele é executado.
Exec sp_help WITH RECOMPILE
Sp_recompile
O procedimento armazenado do sistema sp_recompile recompila o procedimento
armazenado do sistema ou disparador espefificado na próxima vez que ele é
executado. Se o parâmetro @objname especificar uma tabela ou view, todos os
procedimentos armazenados que usam o objeto designado serão recompilados na
próxima vez que forem executados.
Use o procedimento armazenado do sistema sp_recompile com a opção

nome_da_tabela se tiver adicionado um novo índice a uma tabela subjacente a
qual o procedimento armazenado faça referância e se acreditar que o desempenho
do procedimento armazenado poderá melhorar com o novo índice.
Este exemplo recompila todos os procedimentos armazenados ou disparadores

que fazem referência à tabela FCFO.
EXEC sp_recompile fcfo
Observação:
Você pode usar DBCC FREEPROCCACHE para desmarcar todos os
planos de procedimentos armazenados do cache.
Tratando mensagens de erro

Para tomar os procedimentos armazenados mais eficazes, você deve incluir
mensagens de erro que comuniquem o status das transações (êxito ou falha) ao
usuário. Você deverá executar a lógica de negócios e da tarefa, bem como a
verificação de erro, antes de iniciar as transações, e mantê-las curtas. Você pode
usar estratégias de codificação, como verificações de existência, para o
reconhecimento de erros. Quando ocorrer um erro, forneça o máximo de
informações ao cliente. Você pode verificar os elementos a seguir em sua lógica de
tratamento de erros: código de retornos, erros do SQL Server e mensagens de erro
personalizadas.
Instrução RETURN
A instrução RETURN sai de uma consulta ou procedimento armazenado de modo
não condicional. Ela também pode retornar um valor inteiro de status (código de
retorno);
Um valor de retorno 0 indica êxito. Os valores de retorno de 0 a -14 estão em uso

no momento e os valores de retorno de -15 a -99 estão reservados para uso futuro.
Sem um valor de retorno definido pelo usuário não for fornecido, o valor do SQL
Server será usado. Os valores de retorno definidos pelo usuário sempre
prevalecem sobre os fornecidos pelo SQL Server.
Exemplo:
Este exemplo cria o procedimento armazenado Rep_lanc que recupera
informacoes da tabela Flan de determinado lançamento. A instrução
RETURN do procedimento armazenado Rep_lanc retorna o número
total de registros da instrução SELECT para outro procedimento
armazenado.
USE corpore
GO
CREATE PROCEDURE dbo.Rep_lanc
@idlan int

AS
SELECT idlan,valor,datavencimento
FROM flan
WHERE idlan=@idlan
return ( @@ROWCOUNT)
GO
Sp_addmessage
Esse procedimento armazenado permite que os desenvolvedores criem
mensagens de erro personalizadas. O SQL Server trata as mensagens de erro do
sistema e personalizadas da mesma forma. Todas as mensagens são
armazenadas na tabela sysmessages no banco de dados master. Essas
mensagens de erro também podem ser gravadas automaticamente no log de
aplicativos do Windows.
Exemplo:
Este exemplo cria uma mensagem de erro definida pelo usuário que
requer que a mensagem seja gravada no log de aplicativos do
Windows 2000 ou Windows 2003 quando ela ocorre.
EXEC sp_addmessage
@msgnum =500102,
@lang='English',
@severity=10,
@msgtext='Cliente não pode ser deletado.',
@with_log='true'
@@error
Esta função do sistema contém o número do erro referente à instrução Transact-
SQL executada mais recentemente. Ela é limpa e redefinida toda vez que uma
instrução é executada. Um valor igual a O será retomado se a instrução for
executada com êxito. Você poderá usar a função do sistema @@error para detectar
um número de error especifico ou para sair de m procedimento armazenado de
modo condicional.
Exemplo:
USE corpore
GO
ALTER PROCEDURE dbo.atualizavalor
AS
BEGIN TRANSACTION
UPDATE flan set valororiginal=0 where valororiginal IS NULL
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
PRINT 'PROBLEMAS NA ATUALIZAÇÃO'
END
ELSE
PRINT 'REGISTROS ATUALIZADOS '+@@ERROR
COMMIT TRAN
go
Instrução RAISERREOR
A instrução RAISERROR retorna uma mensagem de error definida pelo usuário e
define um sinalizador do sistema para registrar a ocorrência de um erro. Você deve
especificar um nível de gravidade do erro e o estado da mensagem quando estiver
usando a instrução RAISERROR.
RAISERROR permite que o aplicativo recupere uma entrada da tabela do sistema

master..sysmessages (master..mensagens do sistema) ou crie dinamicamente
uma mensagem com as informações de estado e gravidade especificadas pelo
usuário. A instrução RAISERROR pode gravar mensagens de erro no log de erros
do SQL Server e no log de aplicativos do Windows.
Exemplo:
RAISERROR (50010,16,1) WITH LOG

Observações:
A instrução PRINT retorna uma mensagem definida pelo usuário para
o indicador de mensagem do cliente; no entanto, ao contrário da
instrução RAISERROR, ela não armazena o número do erro do sistema
@@error.
Considerações sobre o desempenho
System Monitor do Windows 2003

Objeto: SQL Server: Cache Manager;
Objeto: SQL Statistics.
Você pode usar as ferramentas a seguir para ajudar a identificar a origem dos
problemas de desempenho que podem estar relacionados à execução de
System Monitor do Windows 2003

O System Monitor (Monitor do sistema) do Windows 2003 monitora o uso do
cache de procedimentos, além de muitas outras atividades relacionadas.
Os objetos e contadores a seguir fornecem informações gerais sobre os planos

compilados no cache de procedimentos e o número de recompilações. Você
também pode monitorar uma instância específica, como, por exemplo, procedure
plan.
SQL Profiler
Permite monitorar eventos;

É possível testar cada instrução do procedimento armazenado.
O SQL Profiler (analisador de perfis do SQL) é uma ferramenta gráfica que permite
monitorar eventos, como, por exemplo, quando o procedimento armazenado foi
iniciado ou concluído, ou quando instruções Transact-SQL individuais de um
procedimento armazenado foram iniciadas ou concluídas. Além disso, você pode
monitorar se um procedimento armazenado encontra-se no cache de
procedimentos.
Na fase de desenvolvimento de um projeto, também é possível testar as instruções

do procedimento armazenado, linha a linha, para confirmar se elas funcionam
conforme esperado.
Observação:
Cuidado ao criar procedimentos armazenados aninhados. Esses
procedimentos adicionam um nível de complexidade que poderá tornar
difícil a solução de problemas de desempenho.

Exercícios
Objetivos
Criar um procedimento armazenado.

Alterando um procedimento armazenado.
Descartando um procedimento armazenado.

2) Abra e examine o script cria_procedure1.sql.
3) Execute o script cria_procedure1.sql.
4) Abra e examine o script cria_procedure2.sql.
5) Execute o script cria_procedure2.sql.
6) Crie uma procedure de nome P_VALOR_LANC que tenha como retorno o valor
do lançamento (FLAN. VALORORIGINAL) de determinado lancamento passado
como parametro (FLAN.IDLAN).
7) Crie uma tabela conforme informações abaixo:
CREATE TABLE ZFORNECEDOR (

CODCFO VARCHAR(10),
NOME VARCHAR(100))
8) Crie uma procedure de nome P_INSERE_FORNECEDOR que insira dados na

tabela ZFORNECEDOR a partir de uma instrução SELECT na tabela FCFO
buscando os campos (CODCFO e NOME). Esta procedure deve ter como
parâmetro o campo CODCFO.
9) Altere a procedure P_INSERE_FORNECEDOR para inserir na tabela

ZFORNECEDOR somente os clientes da coligada 1.
10) Descarte a procedure criada no script cria_procedure1.sql.

MÓDULO FUNÇÕES DEFINIDAS PELO USUÁRIO
Visão geral
Este assunto fornece uma visão geral das funções definidas pelo usuário. Ele
explica por que e como usá-las e a sintaxe usada para criá-las.
Depois de concluir este assunto, você será capaz de:
Descrever os três tipos de funções definidas pelo usuário;

Criar e alterar funções definidas pelo usuário;
Criar cada um dos três tipos de funções definidas pelo usuário.
O que é uma função definida pelo usuário?
Funções escalares:
Semelhantes a funções internas.
Funções com valor de tabela e várias instruções:

Conteúdo semelhante a um procedimento armazenado;
Referenciadas como uma view.
Funções com valor de tabela in-line:

Semelhante a uma view com parâmentros;
Retorna uma tabela como resultado de uma única instrução SELECT.
Com o Microsoft SQL Server 2000, você pode criar suas próprias funções para
completar e estender as funções fornecidas pelo sistema (internas). Uma função
definida pelo usuário pode não ter nenhum ou ter vários parâmetros de entrada e
retorna um valor escalar ou uma tabela.
Os parâmetros de entrada podem ser qualquer tipo de dados, exceto timestamp,

cursor ou table. As funções definidas pelo usuário não dão suporte para
parâmetros de saída.
O SQL Server dá suporte para três tipos de funções pelo usuário:
Funções escalares
As funções escalares assemelham-se às funções internas.
Funções com valor de tabela e várias instruções

As funções com valor de tabela e várias instruções retornam uma tabela criada por
uma ou mais instruções Transact-SQL e assemelham-se a um procedimento
armazenado. Diferentemente de um procedimento armazenado, essas funções
podem ser referenciadas na claúsula FROM de uma instrução SELECT como se
fossem uma view.
Funções com valor de tabela in-line

As funções com valor de tabela in-line retornam uma tabela que é o resultado de
única instrução SELECT. Elas assemelham-se às views, mas oferecem maior
flexibilidade do que elas quanto ao uso de parâmetros e estendem os recursos das
views indexadas.
Estabelecendo funções definidas pelo usuário

Esta seção aborda como criar, alterar e descartar uma função definida pelo
usuário. Ela também aborda permissões.
Criando uma função definida pelo usuário

Uma função definida pelo usuário é criada de maneira semelhante a uma view ou
um procedimento armazenado.
Criando uma função

As funções definidas pelo usuário são criadas com a instrução CREATE
FUNCTION. Cada função definida pelo usuário deve ter um nome totalmente
qualificado exclusivo:
(nome_do_banco_de_dados.nome_do_proprietário.nome_da_junção).

A instrução especifica o tipo de dados para os parâmetros de entrada, as
instruções de processamento e o valor retomado por cada tipo de dados.
Sintaxe:
CREATE FUNCTION [nome do proprietario].[nome da função]

([@nome_do_parametro tipo_de_dados_do_pamarametro_escalar])
RETURNS [tipo de dados de retorno escalar]
WITH [opção da função]
AS
BEGIN
corpo da função
RETURN expressao_escalar
END
Exemplo:
USE corpore
GO
CREATE FUNCTION fn_maiorsalario
(@codcoligada int)
RETURNS numeric(15,4)
as
BEGIN
DECLARE @salario numeric (15,4)
SELECT @salario=max(salario) from pfunc where
codcoligada=@codcoligada
RETURN @salario
END
Utilizando a função em uma consulta.
SELECT * FROM pfunc WHERE salario=dbo.fn_maiorsalario(1)
Restrições às funções
As funções não-determinísticas são funções, como GETDATE( ), que podem
retornar valores de resultados diferentes toda vez que são chamadas com o
mesmo conjunto de valores de entrada. Não são permitidas funções não
determinadas pelo usuário.
Funções internas não-determinísticas:
@@ERROR
@@IDENTITY
@@ROWCOUNT
@@TRANCOUNT
APP_NAME
CURRENT_TIMESTAMP
CURRENT_USER
DATENAME
FORMATMESSAGE
GETANSINULL
GETDATE
GetUTCDate
HOST_ID
HOST_NAME
IDENT_INCR
IDENT_SEED
IDENTITY
NEWID
PERMISSIONS
SESSION_USER
STATS_DATE
SYSTEM_USER
TEXTPTR
TEXTVALID
USER_NAME

Criando uma função com vinculação de esquemas
As funções definidas pelo usuário e views às quais a função faz referência

também são vinculadas a esquema;
Os objetos não são referenciados com um nome de duas partes;
A função e os objetos pertencem ao mesmo banco de dados;
Ter a permissão REFERENCE em objetos necessários.
Você pode usar a vínculação de esquemas para vincular a função aos objetos de
banco de dados aos quais ela faz referência. Se uma função for criada com a opção
SCHEMABINDING, os objetos do banco de dados aos quais ela faz referência não
poderão ser alterados (com a instrução ALTER) nem descartados (com uma
instrução DROP).
Uma função só poderá ser vinculada a esquema se as seguintes condições forem

verdadeiras:
Quaisquer funções definidas pelo usuário e views às quais ela faz

referência também forem vinculadas a esquema;
Os objetos aos quais ela faz referência não forem referenciados com um
nome de duas partes no formato proprietário.nome do objeto;
A função e os objetos aos quais ela faz referência pertencem ao mesmo
banco de dados;
O usuário que executou a instrução CREATE FUNCTION tenha a permissão
REFERENCE em todos os objetos de banco de dados aos quais a função faz
referência.
Definindo permissões para funções definidas pelo usuário
Permissão CREATE FUNCTION necessária;

Permissão EXECUTE necessária;
Permissão REFERENCE necessária em tabelas, views ou funções às quais
ela faz referência;
O proprietário da tabela também deve ser proprietário da função para usá-
la na instrução CREATE TABLE ou ALTER TABLE.
Os requisitos de permissão para as funções definidas pelo usuário são

semelhantes aos de outros objetos do banco de dados.
Você deve ter a permissão CREATE FUNCTION para criar, alterar ou

descartar funções definidas pelo usuário;
Os usuários que não são o proprietário devem ter a permissão EXECUTE

em uma função para usá-la em uma instrução Transact-SQL;
Se a função vinculada ao esquema, você deverá ter a permissão

REFERENCE nas tabelas, views e funções às quais ela faz referência. As
permissões REFERENCE podem ser concedidas através da instrução
GRANT para views e funções definidas pelo usuário, bem como para
tabelas;
Se uma instrução CREATE TABLE ou ALTER TABLE fizer referência a uma

função definida pelo usuário em uma restrição CHECK, uma cláusula
DEFAULT ou uma coluna calculada, o proprietário da tabela também
deverá ser proprietário da função.
Alterando e descartando funções definidas pelo usuário

Alterando funções
Mantém permissões atribuídas;

Faz com que a nova definição de função substitua a definição existente.
Modifique uma função definida pelo usuário usando a instrução ALTER

FUNCTION.
USE corpore
GO
alter FUNCTION fn_maiorsalario

(@codcoligada int)
RETURNS numeric(15,4)
as
BEGIN
DECLARE @salario numeric (15,4)
SELECT @salario=min(salario) from pfunc where
codcoligada=@codcoligada
RETURN @salario
END
Descartando funções
A vantagem de alterar uma função em vez de descartá-la é a mesma das views e
dos procedimentos. As permissões da função são mantidas e aplicadas à função
revisada.
Descarte uma função definida pelo usuário usando a instrução DROP FUNCTION.
DROP FUNCTION dbo.fn_maiorsalario
Exemplos:
Funções definidas pelo usuário:
Usando uma função escalar definida pelo usuário;

Exemplo de uma função definida pelo usuário;
Usando uma função com valor de tabela e várias instruções;
Exemplo de função com valor de tabela e várias instruções;
Usando uma função com valor de tabela in-line;
Exemplo de uma função com valor de tabela in-line.
Usando uma função escalar definida pelo usuário
Cláusula RETURNS especifica o tipo de dados;

A função é definida em um bloco BEGIN...END;
O tipo de retorno poderá ser qualquer tipo dado, exceto text, ntext, image,
cursor ou timestamp.
Uma função escalar retorna um valor único do tipo de dados definido em uma
cláusula RETURNS. O corpo da função, definido em um bloco BEGIN...END,
contém a série de instruções Transact-SQL que retomam valor de. O tipo de
retomo poderá ser qualquer tipo de dado, exceto text, ntext, image, cursor ou
timestamp.
Exemplos:
Funções escalar definida pelo usuário
Criando a função;
Chamando a função.
As funções escalares definidas pelo usuário assemelham-se às funções internas.

Após criá-las, você poderá reutilizá-las.
Usando uma função com valor de tabela e várias instruções
BEGIN e END delimitam o corpo da função;

A cláusula RETURNS especifica table como tipo de dados;
A cláusula RETURNS define o nome e o formato da tabela.
A função com valor de tabela e várias instruções é uma combinação de uma view
com um procedimento armazenado. Você pode usar funções definidas pelo
usuário que retornam uma tabela para substituir procedimentos armazenados ou
views.
Uma função com valor de tabela (com um procedimento armazenado) pode usar
uma lógica complexa e várias instruções Transact-SQL para criar uma tabela. Você
pode usar uma função com o valor de tabela na cláusula FROM de uma instrução
Transact-SQL da mesma maneira que usa uma view.

Ao usar uma função com o valor de tabela e várias instruções, considere os fatos
a seguir:
BEGIN e END delimitam o corpo da função;

A cláusula RETURNS especifica table como tipo de dados retornado;
A cláusula RETURNS define o nome e o formato da tabela. O escopo do
nome da variável de retorno é local à função.
Exemplos:
Funções com valor de tabela e várias instruções
• Criando a função;
• Chamando a função.
Você pode criar funções usando várias instruções que executam

operações complexas.
USE CORPORE
GO
ALTER FUNCTION FN_FORNECEDORES
(@ESTADO VARCHAR(20))
RETURNS @FN_FORNECEDORES TABLE
(CODCFO VARCHAR(20) PRIMARY KEY NOT NULL,
NOME VARCHAR (50) NOT NULL)
AS
BEGIN
INSERT INTO @FN_FORNECEDORES
SELECT CODCFO,NOME
FROM
FCFO
WHERE CODETD=@ESTADO AND CODCOLIGADA=1
RETURN
END
Você pode chamar a função em vez de uma tabela ou view.
SELECT * FROM DBO.FN_FORNECEDORES ('MG')
Usando uma função com valor de tabela in-line
Conteúdo da função é uma instrução SELECT;

Não use BEGIN e END;
RETURN especifica table como o tipo de dados;
O formato é definido pelo conjunto de resultados.
As funções in-lines definidas pelo usuário retornam uma tabela e são

referenciadas na cláusula FROM, da mesma maneira que uma view. Ao usar as
funções in-line definidas pelo usuáriio, considere os fatos e diretrizes a seguir:
A cláusula RETURN contém uma única instrução SELECT entre parênteses.

O conjunto de resultados da instrução SELECT constitui a tabela que a
função retorna. A instrução SELECT usada em uma função in-line está
sujeita às mesmas restrições que as instruções SELECT usadas em views;
BEGIN e END não delimitam o corpo da função;
RETURN especifica table como o tipo de dados retornado;
Não é necessário definir o formato de uma variável de retorno porque ele é

definido pelo formato do conjunto de resultados da instrução SELECTna
cláusula RETURN.
Exemplos:
Função com valor de tabela in-line
Criando a função
Chamando a função usando um parâmetro

Você pode usar funções in-line para obter a funcionalidade de views
com parâmetros. Não é possível incluir um parâmetro fornecido pelo
usuário na view quando ela é criada. Normalmente você pode resolver
essa questão fornecendo uma cláusula WHERE ao chamar a view.
No entanto, isso poderá exigir a criação de uma seqüência de

caracteres para execução dinâmica, o que poderá aumentar a
complexidade do aplicativo. Você poderá obter a funcionalidade de
uma view com parâmetros usando uma função com valor de tabela in-
line.
Dica:
As funções in-line podem aumentar significativamente o desempenho
quando usadas com views indexadas. O SQL Server executa
operações complexas de agregação e associação quando o índice é
criado. As consultas subseqüentes podem usar uma função in-line
com um parâmetro para filtrar registros do conjunto de resultados
simplificado armazenado.
Práticas recomendadas
Use funções escalares complexas em conjunto de resultados pequenos;
Use funções de varias instruções em vez de procedimentos armazenados
que retornam tabelas;
Use funções in-line para criar views usando parâmetros;
Use funções in-line para filtrar views indexadas
As práticas recomendadas a seguir devem ajudá-lo a implementar funções

definidas pelo usuário:
Use funções escalares complexas em conjuntos de resultados pequenos.

As funções definidas pelo usuário permitem encapsular um raciocínio
complexo em uma consulta simples, mas, se todos os usuários da função
não compreenderem a complexidade do calculo subjacente, a função
poderá resultar em cálculos demorados que não são visíveis para o
usuário. Não aplique uma agregação complexa em cada participante de
um conjunto grande de resultados;
Use funções de varias instruções em vez de procedimentos armazenados

que retomam tabelas. Escrever procedimentos armazenados que retornam
tabelas como funções de varias instruções definidas pelo usuário poderá
aumentar a eficiência;
Use funções in-line para criar views usando parâmetros. O uso de

parâmetros com funções in-line poderá simplificar as referências a tabelas
e views;
Use funções in-line para filtrar views. O us de funções in-line com views
indexadas poderá aumentar significativamente o desempenho.
Informações adicionais sobre os tópicos a seguir estão disponivies no Books

Online (livros on –line) do SQL Server.

Exercícios
Objetivos
Criar uma função escalar.

Criar uma função com valor de tabelas
2) Abra e examine o script cria_funcao1.sql
3) Execute o script cria_funcao1.sql.
4) Abra e examine o script cria_funcao2.sql.
5) Execute o script cria_funcao2.sql.
6) Crie uma função escalar definida pelo usuário chamado fn_ult_id para trazer o
ultimo IDLAN incluído na tabela FLAN. Ele dever como parâmetro o código da
coligada e como retorno um tipo de dados inteiro.
7) Faça uma instrução SELECT chamando a função fn_ult_id.

MÓDULO IMPLEMENTANDO DISPARADORES
Visão geral
Um disparador é um procedimento armazenado que é executado quando os dados
em uma tabela especifica são modificados. Geralmente, você cria disparadores
para impor a integridade referencial ou a consistência entre dados relacionados
logicamente em tabelas diferentes. Como os usuários não podem ignorar os
disparadores, você pode usá-los para impor regras de negócios complexos que
mantêm a integridade dos dados.
Depois de concluir este assunto, você será capaz de:
Criar um disparador;
Descartar um disparador;
Alterar um disparador;
Descrever como os disparadores funcionam;
Avaliar as considerações de desempenho que afetam o uso de
disparadores.
Introdução a disparadores
O que são disparadores?
Uso de disparadores;
Considerações sobre o uso de disparadores;
Esta seção apresenta disparadores e descreve quando e como usá-los.
O que são disparadores?
Associados a uma tabela;
Chamados automaticamente;
Não podem ser chamados diretamente;
É parte de uma transação.
Um disparador é um tipo especial de procedimento armazenado que é executado

sempre que há uma tentativa de modificar os dados de uma tabela protegida por
ele. Os disparadores estão associados a tabelas especificas.
Associados a uma tabela

Os disparadores são definidos em uma tabela específica, que é
denominada tabela de disparadores.
Chamados automaticamente
Quando há uma tentativa de inserir, atualizar ou excluir dados em uma
tabela, e um disparador tiver sido definido na tabela para essa ação
especifica, ele será executado automaticamente. Ele não poderá ser
ignorado.
Não podem ser chamados diretamente
Ao contrario dos procedimentos armazenados do sistema padrão, os
disparadores não podem ser chamados diretamente e não passam nem
aceitam parâmetros.
É parte de uma transação

O disparador e a instrução que o aciona são tratados como uma única
transação que poderá ser revertida em qualquer ponto do procedimento.
Quando estiver usando disparadores, considere estes fatos e diretrizes
As definições de disparadores podem incluir uma instrução ROLLBACK

TRANSACTION, mesmo que não exista uma instrução BEGIN
TRANSACTION explicita;
Se uma instrução ROLLBACK TRANSACTION for encontrada, a transação

inteira será revertida. Se uma instrução no script do disparador seguir a
instrução ROLLBACK TRANSACTION, a instrução será executada.
Portanto, poderá ser necessário usar um clausula RETURN em uma
instrução IF para impedir o processamento de outras instruções;
Se um disparador que contem uma instrução ROLLBACK TRANSACTION

for acionado em uma transação definida pelo usuário, essa instrução
revertera a transação inteira. Um disparador executado em um lote que
executa uma instrução ROLLBACK TRANSACTION cancela o lote; as
instruções subseqüentes do lote não serão executadas;
Você deve minimizar ou evitar o uso de ROLLBACK TRANSACTION no
código de seu disparador. A reversão de uma transação gera um trabalho
adicional, pois todo o trabalho concluído ate esse ponto na transação
precisa ser desfeito. Isso terá um impacto negativo no desempenho. O
recomendado que as informações sejam verificadas e validadas fora da
transação. Inicie a transação depois que tudo for verificado;
O usuário que chamou o disparador também deve ter permissão para

executar todas as instruções em todas as tabelas.
Uso de disparadores
As alterações em cascata em tabelas relacionadas de um banco de dados
Impor uma integridade de dados mais complexa do que uma restrição

CHECK;
Definir mensagens de erro personalizadas;
Manter dados desnormalizados;
Comparar os estados anteriores e posteriores dos dados que estão sendo
modificados.
Os disparadores são usados com maior eficiência para manter a integridade de

dados de baixo nível, e não para retomar os resultados de consultas. A sua
principal vantagem é que eles podem conter uma lógica de processamento
complexa.
Os disparadores podem colocar em cascata as alterações através de tabelas

relacionadas em um banco de dados, impor a integridade dos dados mais
complexos do que uma restrição CHECK, definir mensagens de erro
personalizadas, manter os dados desnormalizados e comparar os estados
anteriores e posteriores dos dados que sofreram modificações.
Alterações em cascata em tabelas relacionadas de um banco de dados

Você pode usar disparador para atualizações e exclusões em cascata através de
tabelas relacionadas em um banco de dados.
Exemplo:
Um disparador de exclusão na tabela Products (produtos) do banco de
dados Northwind pode excluir os registros correspondentes em outras
tabelas que possuem registros com os mesmos valores de ProductID
(identificação do produto) excluídos.
Um disparador faz isso usando a coluna de chave externa ProductID como uma
forma de localizar registros na tabela Order Details (detalhes do pedido).
Impor uma integridade de dados mais complexa do que uma restrição

CHECK
Ao das restrições CHECK, os disparadores podem fazer referencia a colunas de
outras tabelas. Por exemplo, você poderia colocar um disparador de inserção na
tabela Order Details que verificasse a coluna UnitsInStock (unidades de estoque)
para esse item na tabela de Products.
O disparador determinaria que quando o valor UnitsInStock fosse menor do que

10, a quantidade máxima do pedido seria três itens. Este tipo de verificação faz
referência a colunas em outras tabelas. Fazer referências a colunas em outras
tabelas não é permitido com uma restrição CHECK.
Você pode usar disparadores para impor uma integridade referencial complexa
das seguintes maneiras:
Executando uma ação ou efetuando atualizações ou exclusões em

cascata
A integridade referencial pode ser definida através do uso das restrições
FOREIGN KEY e REFERENCE com a instrução CREATE TABLE. Os
disparadores são úteis para garantir ações apropriadas quando exclusões
ou atualizações em cascata devem ser efetuadas. Se existirem restrições
na tabela de disparadores, elas serão verificadas antes da execução do
procedimento. Se as restrições forem violadas, o procedimento não será
executado.

Criando disparadores de vários registros
Quando mais de um registro é inserido, atualizado ou excluído, você deve
escrever um disparador para manipular vários registros.
Impondo a integridade referencial entre banco de dados.
Definir mensagens de erro personalizadas

Ocasionalmente, sua implementação poderá tirar vantagem de mensagens de erro
personalizadas que indicam o status de uma ação. Usando disparadores, você
poderá chamar mensagens de erro personalizadas predefinidas ou dinâmicas
quando ocorrem certas condições durante a execução de um procedimento.
Manter dados desnormalizados

Os disparadores podem ser usados para manter a integridade de dados de baixo
nível em ambientes de bancos de dados desnormatizados. A manutenção de
dados desnormatizados é diferente da manutenção em cascata, visto que essa
geralmente se refere à manutenção de relacionamentos entre valores de chaves
primárias e externas. Em geral, os dados desnormatizados são valores de dados
projetados, derivados ou redundantes.
Você deverá usar um disparador se:
A integridade referencial exigir algo diferente de uma correspondência

exata, como, por exemplo, a manutenção de dados derivados (vendas do
ano até a data) ou a sinalização de colunas (5 ou N para não para indicar
se um produto está disponível);
Você precisa de mensagens personalizadas e mensagens de erro
complexas.
Observação:
Os dados redundantes e derivados geralmente requerem o uso de
disparadores.
Comparar os estados anteriores e posteriores dos dados que estão sendo

modificados
Os disparadores permitem fazer referência às alterações efetuadas pela instrução

INSERT, UPDATE ou DELETE nos dados. Dessa maneira, é possível fazer
referência aos registros que estão sendo afetados pelas instruções de modificação
contidas no procedimento.
Observação:
As restrições, regras e padrões podem comunicar erros somente
através de mensagens de erro padronizadas do sistema. Se o seu
aplicativo exigir (ou puder tirar vantagem de) mensagens
personalizadas e o tratamento de erros mais complexos, você deverá
usar um disparador.
Considerações sobre o uso dos disparadores

Os disparadores são reativos; as restrições são pró-ativas;
As restrições são verificadas primeiro;
As tabelas podem conter vários disparadores para uma ação;
Os proprietários das tabelas podem designar o primeiro e o ultimo
disparador a ser acionado;
Os proprietários das tabelas devem ter permissão para executar todas as
instruções definidas pelo disparador;
Os proprietários das tabelas não podem criar disparadores AFTER em
views ou tabelas temporárias.
Considere os fatos e diretrizes a seguir ao trabalhar com disparadores:
A maioria dos disparadores são reativos; as restrições e o disparador

INSTEAD OF são pró-ativos.
Os disparadores são executados após a execução de uma instrução
INSERT, UPDATE ou DELETE na tabela em que o procedimento é definido.
Por exemplo, uma instrução UPDATE atualiza um registro de uma tabela e,
depois, o disparador dessa tabela é executado automaticamente. As
restrições são verificadas antes da execução de uma instrução INSERT,
UPDATE ou DELETE.
As restrições são verificadas primeiro
Se existirem restrições na tabela de disparadores, elas serão verificadas
antes da execução do procedimento. Se as restrições forem violadas, o
disparador não será executado.
As tabelas podem conter vários disparadores para ma ação

A Microsoft® SQL Server 1M 2000 permite o aninhamento de vários
disparadores em uma única tabela. Uma tabela poderá conter vários
disparadores definidos para ela. Cada procedimento poderá ser definido
para uma única ou varias ações.
Os proprietários das tabelas podem designar o primeiro e o ultimo

disparador a ser acionado
Quando vários disparadores são colocados em uma tabela, o proprietário
da tabela pode usar o procedimento armazenado do sistema
sp_settringgerorder para especificar os primeiros disparadores a serem
acionados. A ordem do acionamento dos disparadores restantes não pode
ser definida.
Os proprietários das tabelas devem ter permissão para executar todas

as instruções definidas pelo disparador
Somente o proprietário da tabela, os participantes do cargo fixo do servidor
sysadmin e participantes dos cargos fixos do servidor db_owner e
db_ddladmin podem criar e descartar disparadores para essa tabela. Essas
permissões não podem ser transferidas. Alem disso, o criador do
disparador também deve ter permissão para executar todas as instruções
em todas as tabelas afetadas. Se forem negadas permissões para qualquer
parte das instruções Transact-SQL contidas no disparador, a transação
inteira será revertida.
Os proprietários das tabelas não podem criar disparadores AFTER em views ou

tabelas temporárias. No entanto, eles podem fazer referência a views e tabelas
temporárias.
Os proprietários das tabelas podem criar disparadores INSTEAD OF em views e

tabelas; dessa forma, os disparadores INSTEAD OF estendem, em grande parte,
os tipos de atualizações para as quais uma view pode oferecer suporte.
Os disparadores não devem retomar conjuntos de resultados. Assim como os

procedimentos armazenados, os disparadores contêm instruções Transact-SQL e
podem conter instruções que retornam um conjunto de resultados. No entanto, a
inclusão de instruções que retomem valores em disparadores não é recomendada,
pois os usuários ou desenvolvedores não esperam ver nenhum conjunto de
resultados quando uma instrução UPDATE, INSERT ou DELETE é executada.
Os disparadores podem manipular ações com vários registros.
Uma ação INSERT, UPDATE ou DELETE que chama um disparador pode afetar
vários registros. Você poderá:
Processar todos os registros em conjunto; nesse caso, todos os registros

afetados devem satisfazer aos critérios do disparador para que qualquer
ação ocorra;
Permitir ações condicionais. Por exemplo, se desejar excluir três clientes da

tabela Customers (Clientes), você poderá definir um disparador para
garantir que não existam pedidos ativos ou faturas pendentes para cada
cliente excluído. Se um dos três clientes tiver uma fatura pendente, ele não
será excluído, mas os clientes qualificados serão.
Para determinar se vários registros são afetados, use a função do sistema

@@ROWCOUNT.
Definindo disparadores
Esta seção aborda como criar, alterar e descartar disparadores. Ela também aborda
as permissões necessárias e as diretrizes que devem ser seguidas ao se definir
disparadores.

Criando disparadores
Criando disparadores usando a instrução CREATE TRIGGER. A instrução
especifica a tabela em que um disparador é definido, os eventos para os quais ele
é executado e as instruções especificas.
Sintaxe:
CREATE TRIGGER [ proprietário . ] nome_do_disparador

ON [ proprietário . ] nome_da_tabela
[WITH ENCRYPTION ]
{ { FOR | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE }
AS
[ IF UPDATE (nome_da_coluna ) ... ]
[ { AND | OR } UPDATE ( nome_da_coluna ) ... ]
instruções_sql }
Quando uma ação FOR UPDATE é especificada, a cláusula IF UPDATE (

nome_da_coluna ) pode ser usada para enfocar a ação em uma coluna especifica
que é atualizada. Tanto FOR qunato AFTER têm sintaxe igaul criando o mesmo
tipo de disparador, que é acionado depois da ação (INSERT, UPDATE ou DELETE).
Os disparadores INSTEAD OF cancelam a ação do disparador e executam uma

nova ação. Quando você cria um disparador, as informações sobre o mesmo são
inseridas nas tabelas do sistema sysobjects (objetos do sistema) e syscomments
(comentários do sistema). Se um disparador for criado com o mesmo nome que
outro existente, o novo procedimento sobrescreverá o original.
Observação:
O SQL Server não oferece suporte para a adição de disparadores
definidos pelo usuário nas tabelas do sistema; portanto, não será
possível criar disparadores nessas tabelas.
Permissões apropriadas
Os proprietários das tabelas, bem como os participantes dos cargos proprietário
do banco de dados (db_owner) e administradores do sistema (sysadmin), têm
permissão para criar um disparador.
Para evitar situações em que o proprietário de uma view e o proprietário das

tabelas subjacentes sejam diferentes, é recomendado que o usuário dbo seja o
proprietário de todos os objetos de um banco de dados. Como um usuário pode
ser participante de vários cargos, especificamente sempre o usuário dbo como o
nome do proprietário ao criar o objeto.
Caso contrário, o objeto será criado tendo o seu nome de usuário como o
proprietário.
Não pode conter certas instruções

O SQL Server não permite que as instruções a seguir sejam usadas na definição
de um disparador:
ALTER DATABASE;
CREATE DATABASE;
DISKINIT;
DISKRESIZE;
DROP DATABASE;
LOAD DATABASE;
LOAD LOG;
RECOFIGURE;
RESTORE DATABASE;
RESTORELOG.
Para determinar as tabelas com disparadores, execute o procedimento

armazenado do sistema sp_depends <nome_da_tabela>. Para exibir a definição
de um disparador, execute o procedimento armazenado do sistema sp_helptext
<nome_do_disparador>.
Para determinar os disparadores existentes em uma tabela específica e suas

ações, execute o procedimento armazenado do sistema sp_helptrigger
<nome_da_tabela>.

USE corpore
go
CREATE TRIGGER PFUNC_UPDATE_SALARIO
ON PFUNC
FOR UPDATE
AS
BEGIN
DECLARE @SALARIO NUMERIC (15,4)
DECLARE @CHAPA VARCHAR(20)
DECLARE @USUARIO VARCHAR(50)
SELECT @SALARIO=SALARIO FROM INSERTED
SELECT @CHAPA=CHAPA FROM INSERTED
SELECT @USUARIO=USER FROM MASTER.DBO.SYSPROCESSES WHERE
SPID=@@SPID
INSERT INTO PFUNC_AUDI VALUES (@CHAPA,@USUARIO,@SALARIO)
END
A instrução DELETE a seguir aciona o disparador
UPDATE PFUNC SET SALARIO=1000 WHERE CHAPA=’00001’
Alterando e descartando disparadores
Alterando um disparador
Se for necessário alterar a definição de um disparador existente, você poderá
alterá-lo sem precisar descartá-lo.
Altera a definição sem descartar o disparador

A definição alterada substitui a definição do disparador existente pela nova
definição. A ação do procedimento também é alterada.
Por exemplo, se você criar um disparador para INSERT e, depois, alterar a

ação para UPDATE, o disparador alterado será executado sempre que a
tabela for atualizada.
Usando a resolução de nomes com atraso, você poderá fazer referência a

tabelas e views em um disparador que ainda não existe. Se o objeto não
existir quando um disparador for criado, você receberá uma mensagem de
aviso, e o SQL Server atualizará a definição do procedimento
imediatamente.
Sintaxe:
ALTER TRIGGER nome_do_disparador

ON tabela
[ WITH ENCRYPTION ]
{ { FOR { [ , ] [ DELETE ] [ , ] [ INSERT ] }
[ NOT FOR REPLICATION ]
AS
Instrução_sql [ , ... n ] }
|
FOR { [ , ] [ INSERT ] [ , ] [ UPDATE ]
[ NOT FOR REPLICATION ]
AS
IF UPDATE (coluna)
[ { AND | OR | } UPDATE (coluna) [ , ...n ] ]
Instrução_sql [ ... n ] }
}
Exemplo:
USE corpore
go
ALTER TRIGGER PFUNC_UPDATE_SALARIO
ON PFUNC
FOR DELETE
AS
BEGIN

DECLARE @USUARIO VARCHAR(50)
DECLARE @CHAPA VARCHAR(20)
SELECT @CHAPA=CHAPA FROM DELETED
SELECT @USUARIO=USER FROM MASTER.DBO.SYSPROCESSES
WHERE SPID=@@SPID
INSERT INTO PFUNC_AUDI_DEL VALUES (@CHAPA,@USUARIO)
END
Desativando ou ativando um disparador

Você pode desativar ou ativar um disparador especifico ou todos os
disparados de uma tabela. Quando um disparador é desativado, ele
permanece definido para a tabela; no entanto, quando uma instrução
INSERT, UPDATE ou DELETE é executada na tabela, as ações do
procedimento não serão executadas ate que ele seja reativado. Você
poderá ativar ou desativar disparadores na instrução ALTER TABLE.
Sintaxe parcial:
ALTER TABLE tabela

{ ENABLE | DISABLE } TRIGGER
{ ALL I nome_do_disparador [ , ... n ] }
Descartando um disparador
Você poderá alterar ou descartar um disparador. Poderá remover um disparador
descartando-o. Os disparadores são descartados automaticamente sempre que as
tabelas associadas são descartadas.
A permissão para descartar um disparador assume, por padrão, o proprietário da

tabela e é intransferível. No entanto, os participantes dos cargos administradores
do sistema (sysadmin) e proprietário do banco de dados (db_owner) podem
descartar qualquer objeto especificando o proprietário na instrução DROP
TRIGGER.
Sintaxe:
DROP TRIGGER nome_do_disparador
Como funcionam os disparadores
Como funciona um disparador INSERT;

Como funciona um disparador DELETE;
Como funciona um disparador UPDATE;
Como funciona um disparador INSTEAD OF;
Como funcionam os disparadores aninhados;
Disparadores recursivos.
Ao criar disparadores, é importante compreender como eles funcionam. Esta

seção aborda os disparadores INSERT, DELETE, UPDATE, INSTEAD OF, aninhados
e recursivos.
Como funciona um disparador INSERT
1) Instrução INSERT para uma tabela com um disparador INSERT definitivo;

2) Instrução INSERT registrada;
3) Ações de disparador executadas.
Você pode definir um disparador para que seja executado sempre que uma
instrução INSERT inserir dados em uma tabela. Quando um disparador INSERT é
acionado, novos registros são adicionados à tabela de disparadores e à tabela
inserted (inserido). A tabela inserted é uma tabela lógica que armazena uma
cópia dos registros que foram inseridos. Essa tabela contém a atividade de
inserção registrada no log da instrução INSERT.
Ela permite que você faça referência aos dados registrados no log da instrução
INSERT que iniciou a operação. O disparador pode examinar a tabela inserted
para determinar se ou com as suas ações devem ser executadas. Os registros da
tabela inserted sempre são copias de um ou mais registros da tabela de

Toda atividade de modificação de dados (instruções INSERT, UPDATE e DELETE)
é registrada no lo, mas as informações do log de transações não podem ser lidas.
No entanto, a tabela inserted permite que você faça referência às alterações
registradas no log que foram ocasionadas pela instrução INSERT. Depois, você
poderá comparar as alterações com os dados inseridos para verificá-las ou tomar
outra ação. Também é possível fazer referência aos dados inseridos sem precisar
armazenar as informações em variáveis.
Como funciona um disparador DELETE
1) Instrução DELETE para uma tabela com um disparador DELETE definido.

2) Instrução DELETE registrada.
Quando um disparador DELETE é acionado, os registros excluídos da tabela

afetada são colocados em uma tabela especial chamada deleted (excluído). Essa é
uma tabela lógica que armazena uma cópia dos registros que foram excluídos. Ela
permite que você faça referência aos dados registrados no log da instrução
DELETE que iniciou a operação.
Considere os fatos a seguir ao usar o disparador DELETE:
Quando um registro é acrescentado à tabela deleted, ele deixa de existir na

tabela do banco de dados; portanto, a tabela deleted e as tabelas do banco
de dados não apresentam registros em comum;
É alocado espaço da memória para criar a tabela deleted. Essa tabela está
sempre no cache;
Um disparador definido para uma ação DELETE não é executado para a

instrução TRUNCATE TABLE porque TRUNCATE TABLE não é registrada
no log;
Como funciona um disparador UPDATE
1) Instrução UPDATE para uma tabela com um disparador UPDATE definido;

2) Instrução UPDATE registrada com instruções INSERT E DELETE;
Uma instrução UPDATE pode ser analisada em duas etapas: a etapa DELETE que
captura a imagem anterior dos dados e a etapa INSERT que captura a imagem
posterior dos dados. Quando uma instrução UPDATE é executada em uma tabela
que contém um disparador definido, os registros originais (imagem anterior) são
movidos para a tabela deleted e os registros atualizados (imagem posterior) são
inseridos na tabela inserted.
O disparador pode examinar as tabelas deleted e inserted, bem como a tabela

atualizada, para determinar se vários registros foram atualizados e como as ações
do procedimento devem ser executadas.
Você pode definir um disparador para monitorar as atualizações de dados em uma

coluna especifica usando a instrução IF UPDATE. Isso permite que o disparador
isole facilmente a atividade relativa a uma coluna específica.
Ao detectar que a coluna foi atualizada, ele poderá tornar a ação adequada, como,
por exemplo, gerar uma mensagem de erro informado que a coluna não pode ser
atualizada ou processar uma série de instruções com base no valor da coluna
atualizada recentemente.
Sintaxe:
IF UPDATE ( < nome_da_coluna > )
USE corpore
go
CREATE TRIGGER PFUNC_UPDATE
ON PFUNC
FOR UPDATE
AS

IF UPDATE (SALARIO)
BEGIN
EXEC XP_CMDSHELL 'NET SEND /USUARIO ''SALARIO
ALTERADO', NO_OUTPUT
END
Como funciona um disparador INSTEAD OF
1) O disparador INSERTEAD OF pode estar em uma tabela ou view;

2) A ação que inicia o disparador NÃO ocorre;
3) Permite atualizações em views não atualizáveis anteriormente.
Você pode especificar um disparador INSERTEAD OF em tabelas e views. Esse

disparador é executado em vez da ação do disparador original, os disparadores
INSERTEAD OF em views que têm WITH CHECK OPTION definido.
CREATE VIEW CLIENTE AS

SELECT CODCFO,CIDADE FROM FCFO WHERE CIDADE='BELO
HORIZONTE'
UNION
SELECT CODCFO,CIDADE FROM FCFO WHERE CIDADE='SÃO PAULO'
GO
CREATE TRIGGER FCFO_UPDATE

ON
CLIENTE
INSTEAD OF UPDATE AS
DECLARE @CIDADE VARCHAR(50)
SELECT @CIDADE=CIDADE FROM INSERTED
BEGIN
UPDATE FCFO SET CIDADE=@CIDADE
FROM FCFO
JOIN
INSERTED
ON
FCFO.CODCFO=INSERTED.CODCFO
WHERE
FCFO.CODCFO=INSERTED.CODCFO
END
Testando o disparador
UPDATE CLIENTE SET CIDADE='SP' WHERE CODCFO='0000000001'

SELECT CIDADE FROM FCFO WHERE CODCFO='0000000001'
Como funcionam os disparadores aninhados

Qualquer disparador pode conter uma instrução UPDATE, INSERT ou DELETE que
afete outra tabela. Quando o aninhamento está ativado, um disparador que altera
uma tabela poderá ativar um segundo procedimento que, por sua vez, poderá
ativar um terceiro e assim por diante.
O aninhamento é ativado durante a instalação, mas você poderá desativá-lo e

reativá-lo usando o procedimento armazenado do sistema sp_configure.
Os disparadores podem ser aninhados em até 32 níveis. Se um disparador em

uma corrente aninhada inicia um loop infinito, o nível de aninhamento é
ultrapassado. Dessa forma, o disparador termina e reverte a transação. Os
disparadores aninhados podem ser usados para executar funções, como o
armazenamento de uma cópia de backup dos registros que foram afetados por um
disparador a anterior.
Considere os fatos a seguir ao usar disparadores aninhados:
Por padrão, a opção de configuração de disparadores aninhados está

ativada;
Um disparador aninhado não será acionado duas vezes na mesma

transação do disparador; um disparador não se chama em resposta a uma
segunda atualização na mesma tabela no disparador. Por exemplo, se um
disparador modificar uma tabela que, por sua vez, modifique a tabela do
disparador original, o disparador não é acionado novamente;
Como um disparador é uma transação, uma falha em qualquer nível de um

conjunto de disparadores aninhados cancelará a transação inteira, e todas
as modificações de dados serão revertidas. Portanto, você devera incluir
instruções PRINT ao testar seus disparadores para que possa determinar
onde ocorreu a falha.
Verificando o nível de aninhamento

Toda vez que um disparador aninhado é acionado, o nível de aninhamento é
incrementado, o SQL Server oferece suporte para até 32 níveis de aninhamento,
mas você poderá limitar para evitar que o nível máximo de aninhamento seja
ultrapassado. Você pode usar a função @@NESTLEVEL para ver os níveis atuais de
aninhamento.
Determinando se o aninhamento deve ou não ser usado

O aninhamento é um recurso poderoso que pode ser usado para manter a
integridade dos dados de um banco de dados. No entanto, ocasionalmente, talvez
você deseje desativá-lo. Se o aninhamento for desativado, um disparador que
modifica outra tabela não chamará nenhum dos disparadores da segunda tabela.
Use a instrução a seguir para desativar o aninhamento:
Sp_configure ‘ nested triggers ’ , 0
Você pode decidir desativar o aninhamento porque:
Os disparadores aninhados requerem um projeto complexo e bem

planejado. As alterações em cascata podem modificar dados que você não
tinha intenção de alterar;
Uma modificação de dados efetuada em qualquer ponto de uma série de

disparadores aninhados aciona toda a série de procedimentos. Embora
esse recurso ofereça uma poderosa proteção para seus dados, ele poderá
ser um problema caso as suas tabelas devam ser atualizadas em uma
ordem específica.
Você pode criar a mesma funcionalidade com ou sem o recurso de aninhamento;

no entanto, o projeto de seus disparadores será bastante diferente. Ao criar
disparadores aninhados, cada procedimento deverá iniciar somente a próxima
modificação de dados – o projeto deverá se modular. Ao criar disparadores não
aninhados, cada procedimento deverá iniciar todas as modificações de dados que
você deseja que ele faça.

Exemplo:
USE CORPORE
GO
CREATE TRIGGER TPRDUPDATE
ON
TPRD
FOR UPDATE
AS
DECLARE @IDPRD VARCHAR(20)
SELECT @IDPRD=IDPRD FROM DELETED
IF (SELECT count(*) FROM TITMMOV(NOLOCK) WHERE
IDPRD=@IDPRD)<>0
BEGIN
RAISERROR ('PRODUTO POSSUI ITENS REFERENCIADOS', 16, 10)
rollback
END
GO
Disparadores recursivos
Ativando um disparador de modo recursivo;
Tipos de disparadores recursivos;

Recursão direta ocorre quando um disparador aciona e executa uma
ação que faz com que o mesmo disparador seja acionado novamente;
Recursão indireta ocorre quando um disparador aciona e executa em
uma ação que faz com que um disparador em outra tabela seja
acionado;
Determinando se disparadores recursivos devem ou não ser usados.
Qualquer disparador pode conter uma instrução UPDATE, INSERT ou DELETE que
afete a mesma ou outra tabela. Com a opção de disparador recursivo ativada, um
procedimento que altere dados em dados em uma tabela poderá ativar a si mesmo
novamente, em uma execução recursiva. A opção de disparador recursivo está
desativada por padrão quando um banco de dados é criado, mas você pode ativá-
la usando para alterar o banco de dados.
Ativando um disparador de modo recursivo

Use a instrução a seguir para ativar disparadores recursivos:
ALTER DATABASE ClassNorthwind SET RECURSVE_TRIGGERS ON

sp_dboption nome_do_banco_de_dados, ‘recursive triggers’ , True
Observação:
Use o procedimento armazenado do sistema sp_settriggerorder para
especificar um disparador que seja acionado como o primeiro
disparador AFTER, ou o último disparador AFTER. Não há uma ordem
fixa para execução de vários disparadores definidos para um
determinado evento. Cada disparador deve ser independente.
Se a opção de disparador aninhado estiver desativada, a opção de disparador

recursivo também estará, independente da configuração de disparador recursivo
do banco de dados. As tabelas inserted e deleted referentes a um determinado
disparador contêm os registros que correspondem somente à última instrução
UPDATE, INSERT ou DELETE que chamou o disparador.
Até 32 níveis de recursão de disparadores são permitidos. Se qualquer disparador

de um loop recursivo provocar um loop infinito, o nível de aninhamento será
ultrapassado, o procedimento será encerrado e a transação será revertida.
Tipos de disparadores recursivos
Há dois tipos diferentes de recursão:
Recursão direta, que ocorre quando um disparador aciona e excuta uma

ação que faz com que o mesmo disparador seja acionado novamente.

Exemplo:
Um aplicativo atualiza a tabela T1, fazendo com que o disparador Trig1
seja acionado. Trig1 atualiza a tabela T1 novamente, fazendo com que
o disparador Trig1 seja acionado novamente.
Recursão indireta, que ocorre quando um disparador aciona e executa uma

ação que faz com que um disparador em outra tabela seja acionado,
ocasionado subsequentemente uma atualização da tabela original. Isso,
por sua vez, faz com que o disparador original seja acionado novamente.
Exemplo:
Um aplicativo atualiza a tabela T2, fazendo com que o disparador Trig2
seja acionado. Trig2 atualiza a tabela T3 novamente, fazendo com que
o disparador Trig3 seja acionado novamente. Trig3, por sua vez,
atualiza a tabela T2, fazendo com que Trig2 seja acionado novamente.
Determinado se disparadores recursivos devem ou não ser usados

Os disparadores recursivos são um recurso complexo que você pode usar
solucionar relacionamentos complexos, como relacionamentos de auto-referêcia
(também conhecidos como fechamentos transitivos). Nessas situações especiais,
você poderá desejar ativar disparadores recursivos.
Os disparadores recursivos podem ser úteis quando você tiver que manter:
O número de colunas de relatórios na tabela employee quando a tabela

contém uma coluna employee ID (identificação do funcionário) e outra
manager ID (identificação do gerente).
Exemplo:
Suponha que dois disparadores de atualização, trupdateemployee e
trupdate manager, sejam definidos na tabela employee. O
procedimento tr_ipdateemployee atualiza a tabela employee.
Uma instrução UPDATE aciona os disparadores tr_update_employee e

tr_update_manager uma vez. Alem disso, a execução de
tr_update_employee aciona a execução de tr_update_employee novamente
(de modo recursivo) e de tr_update_manager.
Um gráfico para dados de planejamento de produção em que exista uma

hierarquia implícita de planejamento;
Um sistema de controle de montagem no qual partes menores sejam

controladas por suas partes principais.
Considere as diretrizes a seguir antes de usar disparadores recursivos:
Os disparadores recursivos são complexos e precisam ser bem projetados

e completamente testados. Eles requerem um código de lógica de loop
controlado (verificação de término). Caso contrário, o limite de
aninhamento de 32 níveis será excedido;
Uma modificação de dados efetuada em qualquer ponto poderá acionar a

serie de disparadores. Embora permita o processamento de
relacionamentos complexos, isso poderá ser um problema se suas tabelas
tiverem de ser atualizadas em uma ordem específica.

Exercícios
Objetivos
Criar disparadores.
Alterar disparadores.
Descartar disparadores.
2) Abra e examine o script cria_trigger1.sql
3) Execute o script cria_trigger1.sql.
4) Execute o comando abaixo e verifique o resultado
INSERT INTO TMOV

( C O D C O L I G A D A , I D M O V, C O D C F O, C O D C O L C F O, I N T E G R A A P L I C A C A O )
VALUES(1,99994,'C00014',1,1)
5) Abre e examine o script cria_trigger2.sql.
6) Execute o script cria_trigger2.sql.
7) Altere a trigger criada no script cria_trigger2.sql adicionando um condição IF

que somente insira dados na tabela se o funcionário for da coligada 1.
8) Descarte a trigger criada no script cria_trigger1.sql.
9) Crie uma trigger chamada TR_FCFO_DEPEN que verifique no momento da

exclusão de um cliente fornecedor (FCFO) se existe algum registro na tabela FLAN
caso exista ele retorne uma mensagem de erro informando que existem
lançamentos para este código.

MÓDULO PROGRAMANDO EM VÁRIOS
SERVIDORES
Introdução às consultas distribuídas
As consultas distribuídas acessam dados a partir de várias fontes de dados
heterogêneas armazenadas no computador local ou remoto. O SQL Server dá
suporte a consultas distribuídas usando o provedor OLE DB da Microsoft. As
consultas distribuídas fornecem aos usuários do SQL Server acesso a:
Dados distribuídos armazenados em vários computadores que estejam

executando o SQL Server;
Dados heterogêneos armazenados em várias fontes de dados relacionais e
não-relacionais para as quais exista um provedor OLE DB ou um drive de
conectividade aberta de banco de dados (ODBC, Open Database
Connectivity).
Acessando dados remotos

Você pode usar duas técnicas para acessar uma fonte de dados OLE DB a partir
do SQL Server:
Consulta ad hoc
Para acessar dados remotos quando não pretende acessar uma fonte de
dados repetidamente, você pode escrever uma consulta ad hoc com a
função OPENROWSET ou OPENDATASOURCE.
Consulta de servidor vinculado

Para acessar dados remotos repetidamente, você pode usar servidor
vinculado e um nome de objeto dividido em quatro panes. Um servidor
vinculado é uma fonte de dados OLE DB pré-registrada no SQL Server
local, de modo que quando é feita referência a ela, o servidor local saberá
onde procurar os objetos e dados remotos. O uso de servidores vinculados
é um modo eficiente de fornecer associações cruzadas do SQL Server e
outras consultas quando se sabe, com antecedência, que determinadas
fontes de dados deverão estar disponíveis.
Especificando onde processar consultas distribuídas

Ao consultar uma fonte de dados OLE DB, você pode especificar quando processar
a consulta localmente ou em um servidor remoto:
SQL Server local

Por padrão, para servidores vinculados, o SQL Server processa consultas
distribuídas no servidor local;
Fonte de dados OLE DB remota

É possível usar a função OPENQUERY com servidores vinculados para
especificar que o processamento ocorrerá no servidor remoto. Esse
processo chama-se consulta de passagem. Quando você usa a função
OPENROWSET para executar uma consulta ad hoc em uma fonte de dados
remota, essa consulta também é processada remotamente.
Verificando configurações de conexão

Em qualquer sessão que emite consultas distribuídas, as opções ANSI_NULLS e
ANSI_WARNINGS devem estar ativadas. Se você usar ODBC ou SQL Query
Analyzer (Analisador de consultas do SQL) para emitir consultas distribuídas,
essas opções estarão ativadas por padrão. Se usar o utilitário de linha de comando
osql, você deverá definir essas opções explicitamente como ON.
Executando uma consulta ad hoc em uma fonte de dados remota
Use a função OPENROWSET quando você não pretende acessar uma

determinada fonte de dados repetidamente;
Use a função OPENROWSET para acessar dados remotos sem configurar
um servidor vinculado;
Você pode acessar dados ad hoc a partir de fontes remotas usando um provedor
OLE DB. A função OPENROWSET permite que você se conecte e acesse dados a
partir de uma fonte remota sem configurar um servidor vinculado. Use a função
OPENROWSET quando você não pretender acessar uma determinada fonte de
dados repetidamente.

OPENROWSET ( ‘nome_do_provedor’
‘ fonte_de_dados’ ; ‘identificaçao_do_usuario’ ; ‘senha’ |
‘sequencia_do_provedor’},
[catálogo] [esquema] objeto | ‘consulta’} )
A tabela a seguir descreve os parâmetros da função OPENROWSET.
A tabela a seguir lista alguns nomes comuns de provedores OLE DB. Consulte
Books Online (livros on-line) do SQL Server para obter uma lista mais completa de
nomes de proedores OLE DB referentes a várias fontes de dados.

Considere os aspectos e diretrizes a seguir quando executar consultas usando a
função OPENROWSET:
Você deverá fornecer nomes de catálogos e esquemas se a fonte de dados

oferecer suporte a vários catálogos e esquemas (proprietários de banco de
dados e objetos, no caso de SQL Server);
A identificação_do_usuário passada ao provedor OLE DB determina as

permissões associadas à conexão;
É possível usar a função OPENROWSET no lugar de um nome de tabela na

cláusula FROM de uma instrução SELECT.
Configurando um ambiente de servidor vinculado

Para trabalhar com dados a partir de um SQL Server remoto ou outra fonte de
dados OLE DB, você deve estabelecer um servidor vinculado. Um servidor
vinculado é uma fonte de dados OLE DB pré-registrada no SQL Server local de
modo que, quando for feita referência a ela, o servidor local saberá onde os objetos
e dados remotos.
Por que usar servidores vinculados?

Os servidores vinculados consistem em uma maneira de permitir associações
cruzadas do SQL Server e outras consultas quando se sabe, com antecedência,
que determinadas fontes de dados deverão estar disponíveis. O uso de um
servidor vinculado permite que você submeta instruções Transact-SQL
diretamente a um SQL Server remoto. É possível executar essas ações como parte
de uma transação distribuída.
Quando você usar servidores vinculados, considere os aspectos e diretrizes a

seguir:
Você pode acessar dados distribuídos, armazenados em vários SQL Server,

e dados heterogêneos, armazenados em várias fontes de dados relacionais
ou não relacionais;
É possível definir uma fonte que seja SQL Server como um servidor
vinculado se houver um provedor OLE DB disponível para essa fonte;
Se acessar regularmente as informações residentes em outro computador

SQL Server, você deverá definir esse servidor remoto como um servidor
vinculado no computador SQL Server local;
As informações sobre servidores vinculados são armazenadas na tabela do

sistema sysservers (servidores do sistema).
Configurando servidores vinculados

Para configurar servidores vinculados, você deve primeiro estabelecer um vínculo
com uma fonte de dados remota e, em seguida, estabelecer segurança entres os
servidores.
Vinculando a uma fonte de dados remota

Para executar instruções Transact- SQL em um SQL Server remoto ou fonte de
dados OLE DB, você deve estabelecer um vínculo com o servidor ou fonte de
dados.
Você pode estabelecer um vínculo com o SQL Server remoto usando o

procedimento armazenado do sistema sp_addlinkdserver. O procedimento
armazenado do sistema sp_addlinkdserver define um SQL Server remoto no
computador local e especifica o provedor OLE DB.
Sintaxe:
sp_addlinkedserver [@server=] 'servidor'

[,[@srvproduct=]'nome_do_produto']
[,[@provider=]'nome_deo_provedor']
[,[@datasrc=]'fonte_de_dados']
[,[@location=]'local']
[,[@provstr=]'sequência_do_provedor']
[,[@catalog=]'catálogo']

A tabela a seguir descreve os parâmetros do procedimento armazenado do
sistema sp_addlinkdserver.
Conectando a um SQL Server remoto

Se você deseja se conectar a um servidor que esteja executando o SQL Server, os
únicos parâmetros a serem fornecidos são @srvproduct e @server. Não é
necessário especificar @provider,@datasrc,@location,@provstr e @catalog
Exemplos:
01) Este exemplo adiciona o Servidor02, a um computador que
executa o SQL Server, á lista de servidores vinculados disponíveis a
partir do computador SQL Server local.
EXEC sp_addlinkedserver 'servidor02','SQL Server'
Conectando a uma fonte de dados OLE DB

Para se conectar a uma fonte de dados que não seja o SQL Server, especifique os
parâmetros @provider, @datasrc, @localtion, @provstr e @catalog, assim como
@srvproduct e @server ao criar um servidor vinculado.
02) Este exemplo adiciona o servidor Oracle com o nome de OracleRH

à lista dos servidores vinculados disponiveis a partir do computador
local que executa o SQL Server. Este exemplo pressupõe que um alias
SQL*Net de nome ‘OracleDB’ tenha sido criado. Esse alias é usado
para o parametro @datasrc.
EXEC sp_addlinkedserver 'OracleRH','Oracle','MSDAORA','OracleDB'
Estabelecendo segurança de servidor vinculado
O Servidor local deve efetuar login no servidor remoto em nome do

usuário;
Se existir uma senha e conta de login nos dois servidores, será possível
efetuar login no servidor remoto;
Mapeie contas de login e senhas entre os servidores usando
sp_addlinkedsrvlogin;
Usando delegação de conta de segurança, é possível se conectar a vários
servidores com uma autenticação;
Sem a delegação de segurança, mapeie a conta de login local para a conta
de login no servidor vinculado.
Talvez você precise estabelecer segurança entre o servidor local e um servidor

remoto. Ao estabelecer segurança entre servidores SQL Server locais e remotos,
considere os seguintes fatos:
Quando os usuários efetuam login no SQL Server local e executam uma

consulta distribuída, o SQL Server local efetua login no SQL Server remoto
em nome do usuário;

Se existir uma senha e conta de login do usuário nos servidores SQL Server
local e remoto, o SQL Server local poderá usar as credenciais do usuário
para efetuar login no SQL Server remoto. Estabelecer segurança dessa
maneira é útil quando os dois servidores estão usando contas de domínio;
Você pode mapear contas de login e senha entre os servidores SQL Server
local remoto usando o procedimento armazenado do sistema
sp_addlinkdsrvlogin. Ao mapear uma conta local para uma conta de login
remota, você não precisa criar uma conta de login e senha para cada
usuário no SQL Server remoto.
Exemplo:
Um usuário pode efetuar login em um aplicativo cliente que acesse um
SQL Server local, o SQL Server local então acessa o servidor vinculado
em um nome do usuário usando uma conta de login para todos os
usuários finais. A conta de login no servidor vinculado para o qual a
conta de login local é mapeada tem permissão para acessar uma tabela
especifica.
É possível se conectar a vários servidores,e com a alteração de cada

servidor, manter as credenciais de autenticação do cliente original. Esse
procedimento é denominado delegação de conta de segurança. Para usar
essa delegação, todos o servidores deve estar executando o Microsoft
Windows 2003 e os provedores que empregam a interface de suporte de
segurança (SSPI, Security Support Provider Interface). Também será
necessário usar o serviço de diretório do Active Directory;
Se o servidor vinculado não der suporte para a delegação de conta de

segurança, você deverá configurar uma mapeamento de login a partir de
uma conta de login que use a autenticação do Microsoft Windows para
uma conta de login no servidor vinculado. Será necessário estabelecer um
mapeamento de conta para ativar a comunicação de servidor vinculado.
Sintaxe:
SP_ADDLINKEDSRVLOGIN [@rmtsrvname=] 'nome dos servidor remoto'

[,[ @useself=]' credencias ']
[,[ @locallogin=] 'login local']
[,[ @rmtuser =] 'usuário remoto']
[,[ @rmtpassword=] 'senha do usuério remoto ']
Exemplo:
Um usuário conectando localmente no SERVIDOR1 para acessar dados
remotos no servidor SERVIDOR2.
SP_ADDLINKEDSRVLOGIN
@rmtsrvname= 'SERVIDOR2'
@useself=]'FALSE'
@locallogin=] 'RH_SERVIDOR1'
@rmtuser =] 'RH_SERVIDOR2'
@rmtpassword=] '1Q2W3E4R'

Configurando opções de servidor vinculado
É possível definir opções para servidores vinculados usando o procedimento
armazenado do sistema sp_serveroption..
Sintaxe:
SP_SERVEROPTION['servidor'][,'nome da opção'][,'valor da opção']
Segue as opções:
Collation Compatible
A opção Collation Compatible afeta o desempenho da execução de consultas
distribuídas em relação a servidores vinculados. Se essa opção for definida como
TRUE, o SQL Server entenderá que os conjuntos de caracteres e colunas no
servidor remoto são compatíveis com a intercalação do servidor local. Esta opção
permite que o SQL Server envie ao provedor as comparações nas colunas de
caracteres. Se essa opção não for definida, o SQL Server deverá retornar todos os
registros ao servidor local para avaliar as comparações nas colunas de caracteres.
Essa opção só deverá ser definida se a fonte de dados que corresponde ao
servidor vinculado tiver o mesmo conjunto de caracteres e ordem de classificação
do servidor local.
Exemplo:
EXEC SP_SERVEROPTION 'SERVIDOR1','COLLATION
COMPATIBLE','TRUE'
Collation Name e Use Remote Collation
Collation Name
Esta opção especificará o nome da intercalação usada pela fonte de dados remota
se use remote collation for true e a fonte de dados não for uma fonte de dados do
SQL Server. As seguintes condições se aplicam a essa opção.
O nome deve ser uma das intercalações ás quais o SQL Server dá suporte;
Você deve usar essa opção ao acessar uma fonte de dados OLE DB que
não seja do SQL Server, que tenha uma intercalação que corresponda a
uma das intercalações do SQL Server;
O servidor vinculado deve dar suporte a uma única intercalação a ser

usada para todas as colunas nesse servidor.
Use Remote Collation

Determina se o SQL Server usará a intercalação de uma coluna remota ou de um
servidor local.
Se for true, a intercalação de colunas remotas será usada para fontes de

dados do SQL Server, e intercalação especificada em collation name é
usada para fontes de dados do SQL Server;
Se for false, as colunas distribuídas sempre usarão a intercalação padrão

do servidor local. O padrão é false.
Data Access
Ativa e desativa um servidor vinculado para o acesso a consultas distribuídas.
Você só pode usá-la para as entradas de sysserver adicionadas por meio de
sp_addlinkdserver.
RPC
A opção RPC ativa chamadas de procedimento remoto (RPCs, remote procedure
calls) a partir de determinado servidor.
RPC out
A opção RPC out (Saída de RPC) ativa RPCs para determinado servidor.

Executando consulta em um servidor vinculado
Fazendo referência a objetos em servidores vinculados

Ao executar consultas distribuídas, você deve fazer referência a objetos com
nomes de quatro partes no seguinte formato.
Nome_do servidor.Nome_do_catálogo.Nome_do_esquema.Nme_do_objeto
Instruções Transact-SQL permitidas
Ao usar um SQL Server vinculado, é permitido as seguintes instuções Transcat-

SQL
Instrução SELECT com uma cláusula WHERE ou JOIN.

Instruções INSERT, UDPDATE e DELETE.
Instruções Transact-SQL não permitidas
Usar a instrução CREAT, ALTER ou DROP em servidores vinculados.

Portanto, não é possível executar um instrução CREATE TABLE que
contenha uma instrução SELECT INTO. Entretando, é possível usar dados
vinculados como a fonte para as tabelas criadas no servidor local com a
instrução SELECT INTO;
Incluir uma cláusula ORDER BY em uma instrução SELECT, se uma coluna

de objetos grandes em uma tabela vinculada estiver na lista de seleção da
instrução SELECT;
Usar as instruções READTEXT, WRITETEXT e UPDATETEXT.
Exemplos:
Execução de consultas em servidores vinculados.
1) SELECT nome FROM servidor2.corpore.dbo.pfunc WHERE

codcoligada=1
2) SELECT nome,salario INTO tbsalarial

FROM servidor2.corpore.dbo.pfunc WHERE codcoligada=1
Executando consultas de passagem

Ao consultar um servidor vinculado, você poderá espeficicar que o SQL Server
execute uma consulta de passagem. Use a funcção OPENQUERY para executar
consultas de passagem em um servidor vinculado.
Use a função OPENQUERY para executar consultas de passagem em um

servidor vinculado;
Use a função OPENQUERY com uma instrução SELECT em vez de um
nome de tabela;
Use o resultado de uma função OPENQUERY como a tabela de destino de
uma instrução INSERT, UPDATE ou DELETE.
Sintaxe:
OPENQUERY (servidor_vinculado, 'CONSULTA')
Exemplos:
1) SELECT * FROM OPENQUERY
(servidor2,'SELECT IDLAN,CODCFO FROM
corpore.dbo.flan')
2) DELETE FROM OPENQUERY (servidor2,'corpore.dbo.fcfo')

Gerenciando transações distribuídas
Gerenciando transações distribuídas usando o MS DTC Gerenciando transações
distribuídas usando o Component services.
As transações distribuídas coordenam a atividade em vários recursos com uma

única unidade de trabalho. O SQL Server dá suporte às transações distribuídas,
permitindo que os usuários atualizem vários banco de dados do SQL Server e
outras fontes de dados.
Gerenciando transações distribuídas usando o MS DTC

O Microsoft Distributed Transaction Coordinator (MS DTC, coordenador de
transações distribuídas da Microsoft) coordena o as transações distribuídas em
todos os servidores que participam da transação. Esses servidores podem conter
o SQL Server além de componentes de camadas intermediarias. Você pode usar o
MS DTC e a partir de um procedimento armazenado do SQL Server para coordenar
as transações em vários computadores que executam o SQL Server ou entre um
SQL Server e servidores vinculados.
É possível adicionar computadores remotos que executam o SQL Server a uma

transação distribuída. Um procedimento armazenado emite uma instrução BEGIN
DISTRIBUITED TRANSACTION e efetua uma chamada de procedimento
armazenado remoto, que faz referência a um servidor remoto, ou executa uma
consulta distribuída, que faz referência a um servidor remoto ou vinculado.
Gerenciando transações distrubuidas usando o Component Services

Use o Component Services para implantar e gerenciar transações distribuídas, o
mecanismo subjacente é o MS DTC. Os componentes na camada intermediaria
podem participar de uma transação distribuída.
Modificando dados em um servidor vinculado
Transações distribuídas através da
Execução da instrução BEGIN DISTRIBUIED TRANSACTION ou chamada

de funções API a partir de um cliente.
Considere estes fatos:
As instruções BEGIN DISTRIBUTED TRANSACTION não podem ser

aninhadas
ROLLBACK TRANSACTION reverte a transação distribuída inteira
Não há suporte para pontos de preservação
Defina a opção de sessão XACT_ABORT
Quando você deseja modificar os dados em um servidor vinculado, deve executar

uma transação distribuída. È possível executar uma instrução BEGIN
DISTRIBUTED TRANSACTION ou fazer referência às funções API (Application
Programming Interface, interface de programação de aplicativo) em um aplicativo
cliente.
Sintaxe:
BEGIN DISTRIBUTED TRANSACTION [nome_da_transação]
Exemplo:
O exemplo a seguir usa uma transação distribuida para transferir uma
pessoa cadastrada no sevidor1 par ao servidor2 . O procedimento
armazenado P_tpessoa transefere uma pessoa cadastrada na tabela
PPESSOA no database CORPORE no servidor1 para a tabela
candidatos parao banco TALENTO no servidor2.
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
EXEC corpore.dbo.P_tpessoa
COMMIT TRAN

Considere os fatos a seguir quando você trabalhar com transações distribuidas:
Não é possivel aninhar as instruções BEGIN DISTRIBUTED TRANSATION.

O SQL Server detecta tais chamadas, rejeita-as e relata um erro;
Um instrução ROLLBACK TRANSACTION reverte a transação distribuida

inteira;
Não há suporte para pontos de preservação. Se o SQL Server reverter uma

transação distribuida,a transação inteira será revertida do inicio,
independentemente de qualquer ponto de preservação;
Defina a opção de sessão XACT_ABORT ao executar transações

distribuidas ente os servidores entre servidores vinculados. Se uma
instrução Transact-SQL falhar quando a opção de sessão XACT_ABORT for
definida, a transação inteira será revertida. Se essa opção não for definida,
somente a instrução que falho será revertida e o processamento da
transação continuará.

ANEXO
Tre i na m e nt os R M S ist e ma s
CORPORE RM RECU RSOS H UM AN OS Rotinas Anuais 13º Salário - RM Labore
• Cadastros de Eventos e Sindicatos
Folha de Pagamento - RM Labore
• Processamento de 1ª e 2ª Parcelas
• Processo de Admissão • Códigos de Cálculos Envolvidos no Processamento.
• Filtros
• Tabelas Dinâmicas e de Cálculo
• Alterações Globais CORPORE RM ERP
• Processo de Lançamento da Folha
• Emissão de Relatórios Faturamento, Compras, Estoque e Contratos -
• Encerramento da Folha e 13º Salário RM Nucleus
• Férias individuais e Coletivas, Escala de Férias • Cadastros Básicos
• Acerta Período Aquisitivo • Gradze Numerada por produto
• Rescisão, Rescisão Complementar, Rescisão Coletiva • Compras
• GRFC, DIRF, Informe de Rendimentos, RAIS. • Controle de Estoque
• Faturamento
Automação de Ponto - RM Chronus • Cancelamento de Movimento
• Horários, Abonos, Terminais de Coleta, Crachás, Grupo • Contratos
de Descanso, Compensação de Dia Ponte • Ressuprimento.
• Visitantes
• Refeições Contabilidade Gerencial - RM Saldus
• Parâmetros RM Chronus, Coletivos e Funcionários • Cadastros Básicos: Plano de Contas Contábil, Gerencial
• Movimentação: Importação de batidas, Lançamentos e Paralelo, Histórico Padrão
globais, Cancelamento do movimento, Compensação • Implantação do Saldo do Período Anterior
do banco de hora, Digitação de batidas, Lançamento de • Orçamento
eventos para o RM Labore • Manutenção de Lançamentos
• Geração de Relatórios • Agendamento de Lançamentos
• Encerramento do período de apuração do ponto • Emissão dos principais relatórios
• Cálculo por Jornada. • Apuração do Resultado do Exercício
• Liberação do Período.
Gestão do Conhecimento – RM Testis
• Áreas de Conhecimento Escrituração e Controle Fiscal - RM Liber
• Provas
• Cadastros Básicos
• Pessoas
• Lançamentos
• Utilitários e Campos Complementares
• Apuração de Tributos Federais
• Cálculo dos Impostos: ICMS, IRPJ, etc.
Integrações CORPORE RM – foco em RH
• PAT (Programa de Alimentação do Trabalhador)
• Admissão • Rotinas Legais
• Controle de Benefícios • Rotinas Federais
• Quadro de Lotação • Geração das Guias
• Tabelas Salariais • Relatórios Fiscais.
• Controle dos Membros da CIPA
• Controle de Atestados e Apuração de Freqüência Controle Financeiro - RM Fluxus
• Lançamentos Financeiros da Folha de Pagamento
• Geração de RPA para guia INSS
• Clientes e Fornecedores
• Contabilização
• Orçamento
• Manutenção de Lançamentos e Lançamento Padrão
Gestão Recursos Humanos - RM Vitae
• Cancelamento e Baixa de Lançamentos
• Cargos e Salários • Adiantamento e Devolução
• Controle de Lotação • Acordo
• Recrutamento e Seleção • Parcelamento e Utilização de Cheques
• Treinamento • Faturas, Extratos de Caixa e Aplicação Financeira
• Programa de Qualidade de Vida • Cobrança e Pagamento Eletrônico
• Processos Jurídicos • Módulo Caixa.
• Segurança e Medicina do Trabalho: Controle de Exames
Médicos, Controle de Riscos Ambientais, Ocorrência de Controle Patrimonial - RM Bonum
Acidente de Trabalho (CAT), Controle de EPIs, Acidente
de Trabalho, Comissão da CIPA, Proteção Contra
• Inventário
Incêndio, Emissão do Relatório PPP. • Movimentações: Baixa de Bens,Transferência de Bens,
Acréscimos, Conversão, UFIR/REAL, Recálculo para 2ª
Integração RM Labore x RM Saldus Moeda, Tabela de Índices, Alteração da taxa de
• Parâmetros do RM Saldus depreciação
• Histórico Padrão • Relatório de Contabilização
• Plano de Contas Contábeis e de Contas Gerenciais • Razão Auxiliar
• Encargos Contábeis • Instruções Normativas
• Agrupamento de Contas Contábeis e Gerenciais • Integração com RM Saldus e com RM Nucleus
• Histórico de Provisões e Encargos • Liberação de Período.
• Lançamentos Contábeis e Tipos de Contabilização
Integrações CORPORE RM - foco Administrativo
Fórmulas RM Labore (RM Nucleus x RM Fluxus / RM Nucleus x RM Saldus /
RM Fluxus x RM Saldus)
• Fórmulas Aplicadas ao RM Labore
• Fórmulas de Cálculo, de Seleção e de Crítica Global • Parametrização dos Tipos de Movimentos para
• Estrutura de Repetição integração Financeira e Contábil no RM Nucleus
• Tipos de Constantes e de Operações • Parametrização da integração contábil no RM Fluxus
• Estrutura Condicional • Cadastro de Eventos Contábeis
• Funções e Montagem de Fórmulas. • Cadastro dos defaults contábeis.
CO RPO RE RM P RO JET OS - G ES T ÃO DE O BRAS E MÓDU L O IM OB IL IÁ R IA
P R O JE TO S
Gestão Imobiliária – RM SGI
Gerenciamento de Obras e Projetos - RM Solum • Cadastros Básicos
• Elaboração de um Orçamento Completo • Controle de Vendas e Cobrança
• Cotação de preços dos insumos na unidade orçada ou • Contratos
na unidade de compra. • Aditivos
• Cálculo do Custo Horário de Equipamentos e de Mão- • Reajuste de Correção e Distrato
de-obra • Manutenção de Parcelas
• Cronograma Previsto x Real x Medido. • Controle de Aluguéis
• Cronograma Desembolso • Relatórios
• PERT - (Técnica de Avaliação e Revisão de Programas) • Modelos de Cálculo.
• Integração com o MS Project/Excel
• Apropriação de Insumos/Composição MÓDU L O C R M
• Medição de Contratos (Reajuste, Realinhamento,
Retenções / Adiantamentos) Gestão de Atendimento e Relacionamento –
• Aditivos de Contratos RM Agilis
• Medição Via PALM • Atendimentos via RM Agilis
• Concorrência Eletrônica. • Status, Dados e pesquisa de Atendimentos
• Informações de Clientes
C O R P O R E R M E D U C AC I O NA L - G E S T Ã O E S C O L A R • Estatísticas de Suporte
• Financeiro
Gestão Educacional - RM Classis • Atendimento Eletrônico
• Cadastros das Tabelas Relativas à Secretaria e • Marketing Via RM Agilis
Tesouraria • Tipo de Mídia
• Demais Cadastros: Cursos, Disciplinas, Grade • Segmento
Curricular, Período Letivo, Turmas, Alunos, Professores • Script de Telemarketing
• Processos da Secretaria:Rotinas de Matrícula e • Tipo de Ação
Rematrícula, Digitação de Notas, Correção de Gabarito • Catálogo de Canais
de provas, Transferência de Grade Curricular, • Campanhas
Fechamento de Período Letivo, Exame Nacional de • Motivo de Contato Perdido
Cursos • Prospect/Suspect
• Processos da Tesouraria: Rotinas de Geração de • Operações de Telemarketing
Lançamentos Financeiros e Cobrança, Consulta de • Relatórios de Ligações.
lançamentos (Títulos), Cadastro de Planos de
Pagamento, Controle de Bolsas, Agendamento de G ERA DO RES D E R ELAT ÓR I OS E CONS U LT AS À B AS E
Geração de Lançamentos, Exportação no padrão CNAB. CORPORE RM
Controle de Biblioteca - RM Biblios Gerador de Relatórios do CORPORE RM
• Cadastros Básicos • Criação de Relatório buscando dados diretamente da
• Regras de Empréstimos base de dados
• Movimentações de Empréstimos, Devoluções e • Criação de Relatórios utilizando sentença SQL
Reservas • Relacionamento entre as Tabelas
• Controle de Periódicos • Formatação de Relatórios
• Pesquisas Simples e Avançadas no acervo • Criação de relatórios com tipo “Arquivo Bancário”
• Integração RM Biblios x RM Classis e RM Biblios x RM • Utilização de Planilha eletrônica: inserção de dados e
Fluxus. criação de gráficos
• Assistente para Montagem de Relatórios.
C O R P O R E R M P R O D U Ç ÃO
C ONS UL TA S SQL À B A SE C OR P OR E R M + R M B I S
Planejamento e Controle da Produção - RM Factor
• Otimização do uso de recursos físicos e financeiros Consultas SQL
• Avaliação de desempenho e correção de desvios • Introdução Teórica
• Dimensionamento da interferência de variáveis • Banco de dados Relacional
• Ágil fornecimento de informações detalhadas • Estrutura Cliente/Servidor
• Cumprimento de prazos e garantia de qualidade dos • Relacionamento entre tabelas
serviços prestados • Cláusulas: SELECT, FROM, WHERE, GROUP BY, ORDER
• Controle de manutenção preventiva e corretiva BY, HAVING, UNION, OUTER JOIN
• Acompanhamento de ordens de serviço • Consultas com Junção (INNER JOIN)
• Integração com Faturamento e Recursos Humanos • Exemplos de aplicação das Consultas SQL
• Acompanhamento de índices de produtividade / serviços • Exercícios Práticos contemplando consultas aos
• Controle da inadimplência. aplicativos RM Fluxus, RM Saldus, RM Nucleus e RM
Labore.
M Ó D U LO MA N U T E N Ç ÃO
RM Bis
Manutenção e Assistência Técnica - RM Officina • Conceito de BI (Business Intelligence)
• Mão-de-Obra, Fabricantes, Problemas de Manutenção, • Cadastros de Tipos de Cubos
Organograma, Acompanhamento de Processos, • Criação e Edição de Cubos
Objetos de Manutenção, Postos de Atendimento, • Áreas de divisão de um Cubo
Atendentes, Tabelas Auxiliares • Alterando a Análise de Dados
• Operações: Ordem de Serviço, Agendamento de • Gráficos.
Serviço, Contratos de Manutenção, Análise de
Movimentos e Problemas, Atendimento, Lançamento
de Indicadores de Uso, Índices de Acompanhamento, Para visualizar a programação de nossos cursos, acesse
Ocupação de Mão-de-Obra, Desempenho e Controle de w w w.rm .com .b r
pneus.

RM Programação SQL Server

Enviado por

Direitos autorais:

Formatos disponíveis

RM Programação SQL Server

Enviado por

Dados do documento

Direitos autorais

Formatos disponíveis

Compartilhar este documento

Compartilhar ou incorporar documento

Opções de compartilhamento

Você considera este documento útil?

Este conteúdo é inapropriado?

Direitos autorais:

Formatos disponíveis

RM Programação SQL Server

Enviado por

Direitos autorais:

Formatos disponíveis

Todos os direitos autorais reservados pela RM SISTEMAS.

Proibida a reprodução total ou parcial, bem como a armazenagem em sistema de

A Linha CORPORE RM apresenta sistemas integrados de gestão empresarial

Cadastre-se agora mesmo no menu cliente on-line

CORPORE RM Departamento de Treinamento RM Sistemas

Camada de apresentação de dados

Camada lógica de aplicativo

Os serviços comerciais podem ser componentes personalizados ou aplicativos e

Camada de serviços de dados

Criando camadas físicas

Esse procedimento permite a implementação da lógica, das regras comerciais e do

As estruturas de duas camadas são menos comuns do que as de várias camadas,

Usando um modelo de várias camadas

O modelo de várias camadas é escalonável para grandes bases de clientes e vários

Usando clientes existentes

Esses aplicativos permitem que os usuários procurem dados do servidor e

Fornecendo acesso universal a dados

Microsoft Active X® Data Objects (ADO)

Abrir e manter conexões;

Ferramentas de programação do SQL Server

Efetua a sintaxe de codificação por cores automaticamente

SQL Query Analyzer

Marcação personalizada dos elementos da sintaxe. Quando você escreve

Várias janelas de consulta, cada qual com uma conexão própria;

Visualizações personalizáveis de conjuntos de resultados. Você pode exibir

Planos gráficos de execução que descrevem o modo como o SQL Server

osql –U identificação de logon [-e] [-E] [-p] [-n]

A tabela a seguir descreve os argumentos usados com mais freqüência.

Linguagem de programação Transact-SQL

Implementação do padrão 150 ANSI-SQL no SQL Server;

CORPORE RM Departamento de Treinamento RM Sistemas

Instruções de Data Control Language

Definem ou alteram permissões:

Por padrão, somente os participantes do cargo sysadmin, dbccreator, db_owner

As instruções de linguagem de controle de dados (DCL, Data Control Language)

A tabela a seguir descreve as instruções de DCL.

Por padrão, somente os participantes do cargo sysadmin, db_creator, db_owner

Instruções de Data Definition Language

Definem os objetos do banco de dados:

CREATE tipo_de_objeto nome_do_objeto;

Departamento de Treinamento RM Sistemas CORPORE RM

CREATE tipo_de_objeto nome_do_objeto;

Por padrão, somente os participantes dos cargos sysadmin, dbcreator, db_owner

Se os usuários criarem seus próprios objetos nos bancos de dados, cada

Se várias contas de usuários criarem objetos, os cargos sysadmin e db_owner

Instruções de Data Manipulation Language

Operam com os dados contidos no banco de dados:

As instruções de DML (Data Manipulation Language, linguagem de manipulação

As instruções de DML são:

Por padrão, somente os participantes dos cargos sysadmin, db_creator,

SELECT chapa,nome,salario FROM corpore.dbo.pfunc

Nomes de objetos do SQL Server

CORPORE RM Departamento de Treinamento RM Sistemas

Observe as seguintes regras quanto ao uso de identificadores:

O primeiro caracter deve ser um caractere alfabético, de a-z ou A-Z;

Após o primeiro caractere, os identificadores podem incluir letras,

Os nomes de identificadores que começam com um símbolo têm

Um identificador que inicie com o símbolo de arroba (@) indica um

Um identificador que inicie com um sinal de tralha (#) indica um

Um identificador eu inicie com um sinal de tralha duplo (##) indica um