Implementação de Projeto Físico

Prévia do material em texto

Prática 1 
 
1- Criando o Projeto Físico a partir do Projeto Lógico (Modelo Lógico) 
 
1.1 Objetivo 
 
Ao final desta prática o aluno deverá ser capaz de: 
 
• A partir de um modelo lógico fazer a implementação do Projeto Físico; 
• Criar e manipular o banco de dados de acordo com o DER.(uso de comandos da 
DDL e DML). 
 
1.2 Exercícios 
 
 1.2.1 Exercício 1: Criar um novo Banco de Dados 
 1º. Passo: Crie um novo banco de dados com o nome de “biblioteca”, de acordo com o 
modelo lógico da [Figura 1], use os comandos SQL. 
País
Usuário
Exemplar
Editora
Autor
ObraTem
Autoria
é
Pertence
Possui
Tipo Usuário
Empréstimo
N
1
N
N
1
N
N 1
N
1
N
N
@CodUsu
NomUsu
SexoUsu
TipUsu
@TipUsu
DesTipUsu
CodObr
NumExe
CodUsu
DatIni
DatFim
DatDev
@
@CodEdi
NomEdi
CodObr
NumExe
CodEdi
ValorExe
@
CodObr
CodAut
@CodObr
NomObr
@
@CodPai
NomPai
@CodAut
NomAut
CodPai
 
Figura 1: Projeto Lógico – Modelo Lógico DER 
 2º. Passo: Crie as tabelas de acordo com os tipos definidos a seguir: 
Tabela: Usuario 
Nome do Atributo Tipo Restrição 
CodUsu Smallint NOT NULL 
Nom Usu Char(80) NOT NULL 
SexoUsu Char(01) NOT NULL 
TipUsu Smallint NOT NULL 
 
Tabela: TipoUsuario 
 2 
Nome do Atributo Tipo Restrição 
TipUsu Smallint NOT NULL 
DesTipUsu Char(20) NOT NULL 
 
Tabela: Pais 
Nome do Atributo Tipo Restrição 
CodPais Smallint NOT NULL 
NomPais Char(30) NOT NULL 
 
Tabela: Autoria 
Nome do Atributo Tipo Restrição 
CodObr Smallint NOT NULL 
CodAut Smallint NOT NULL 
 
Tabela: Autor 
Nome do Atributo Tipo Restrição 
CodAut Smallint NOT NULL 
NomAut Char(80) NOT NULL 
CodPai Smallint NOT NULL 
 
Tabela: Obra 
Nome do Atributo Tipo Restrição 
CodObr Smallint NOT NULL 
NomObr Char(80) NOT NULL 
 
Tabela: Exemplar 
Nome do Atributo Tipo Restrição 
CodObr Smallint NOT NULL 
NumExe Smallint NOT NULL 
CodEdi Smallint NOT NULL 
ValorExe Float NOTNULL 
 
Tabela: Empréstimo 
Nome do Atributo Tipo Restrição 
CodObr Smallint NOT NULL 
NumExe Smallint NOT NULL 
CodUsu Smallint NOT NULL 
DatIni Date NOT NULL 
DatFim Date NOT NULL 
DatDev Date NULL 
 
Tabela: Editora 
Nome do Atributo Tipo Restrição 
CodEdi Smallint NOT NULL 
NomEdi Char(50) NOT NULL 
 
 3 
1.2.2 Exercício 2: Inserir dados no banco de dados 
1º. Passo: Incluir os dados de acordo com as tabelas a seguir: 
 
Tabela: Autor 
CodAut NomAut CodPai 
1 Ramez Elmasri 1 
2 Shamkant B. Navathe 1 
3 Henry F. Kort 2 
4 Abraham Silberchatz 3 
5 Valduriez Patrick 3 
6 Nívio Ziviani 4 
7 Marcos Viana Villas 5 
 
Tabela: Obra 
CodObr NomObr 
1 Sistemas de Banco de Dados Fundamentos e Aplicações 
2 Sistemas de Banco de Dados 
3 Princípios de Sistemas de Banco de Dados Distribuído 
4 Projeto de Algoritmos com Implementação em C e Pascal 
5 Estrutura de Dados 
 
Tabela: Autoria Tabela: Pais 
CodObr CodAut CodPai NomPai 
1 1 1 Canadá 
1 2 2 Portugal 
2 3 3 França 
3 4 4 Brasil 
3 5 5 Argentina 
4 6 
5 7 
 
Tabela: Editora Tabela: TipoUsuario 
CodEdi NomEdi TipUsu DesTipUsu 
1 LTC 1 Aluno 
2 Campus 2 Professor 
3 FTD 3 Funcionário 
4 Atlas 
5 Bookman 
 
Tabela: Exemplar 
CodObr NumExe CodEdi ValorExe 
1 1 1 99,00 
2 1 2 100,00 
3 1 3 50,00 
4 1 4 45,00 
4 2 4 50,00 
5 1 5 110,00 
5 2 5 110,00 
5 3 5 120,00 
 
Tabela: Usuário 
CodUsu NomUsu SexoUsu TipUsu 
 4 
1 Viviane Cristina Dias F 2 
2 André da Silva M 1 
3 Marcelo Andrade M 1 
4 Márcia Duarte F 1 
5 Joaquim Reis M 1 
6 Ana Maria Silva F 1 
7 Ana Luiza da Silva F 1 
8 Analuiza da Silva F 1 
9 Maria Ana dos Santos F 1 
10 Ana Marta Souza F 2 
11 Márcia Ana F 2 
12 Carla Rodrigues F 1 
13 Francisco Diniz M 2 
 
Tabela: Empréstimo 
CodObr NumExe CodUsu DatIni DatFim DatDev 
1 1 1 10/01/2003 20/01/2003 19/01/2003 
1 1 1 10/02/2003 20/02/2003 15/02/2003 
2 1 1 12/01/2003 23/01/2003 NULL 
3 1 2 08/01/2003 15/01/2003 16/01/2003 
3 1 3 16/01/2003 20/01/2003 22/01/2003 
3 1 4 23/01/2003 28/01/2003 25/01/2003 
3 1 4 28/01/2003 05/02/2003 NULL 
4 1 5 12/01/2003 19/01/2003 20/01/2003 
4 1 6 20/01/2003 28/01/2003 27/01/2003 
4 2 7 20/01/2003 28/01/2003 28/01/2003 
 
 5 
Prática 2 
 
2- Processamento de Consultas 
 
2.1 Objetivo 
 
Ao final desta prática o aluno deverá ser capaz de: 
• Compreender o processo de processamento de consultas; 
• Verificar o Tuning (Utilizar o Plano de Execução das Queries - Execution Plan); 
• Verificar as estatísticas 
 
2.2 Desenvolvimento 
O SQL Sever possui uma ferramenta para análise de performance: Plano de Execução Gráfico de 
Queries (Execution Plan). O Execution Plan corresponde a uma seqüência de etapas empregadas 
pelo SQL Server no processo de execução de queries. Se a query foi executada recentemente, o 
plano estará presente no cache do banco e será reutilizado; caso contrário será gerado um novo 
plano para a query em questão. 
 
No plano de execução gráfico, cada etapa do processamento da query é representada por um objeto 
diferente. O roteiro da execução é determinado por um conjunto de setas que ligam esses objetos; 
assim pode-se facilmente percorrer por todas as etapas. O componente do SQL Server responsável 
pela construção do plano é chamado de otimizador; e é ele quem decide que índices serão 
utilizados, o modelo interno de join, se serão criadas tabelas temporárias para processar o join, entre 
outras decisões (como vimos na aula teórica). 
 
Entendendo o Execution Plan 
A leitura do Execution Plan deve ser feita da direita para a esquerda, e de cima para baixo. Cada 
objeto sinaliza uma operação distinta, e existem setas indicando o caminho. 
 
 
Figura 2: Execution Plan 
 
Tabela 1: Tabela de Símbolos 
Símbolo Descrição 
 
indica que está sendo efetuada uma varredura sequencial na tabela Orders (=Clustered 
Index Scan) , tendo por base o índice cluster PK_Orders. 
 
indica que após o processo de varredura sequencial na tabela Orders , o resultado da 
seleção será apresentado ao cliente. 
 
representa um processo de Index Seek, e indica que a busca do empregado para 
employeeId=9 foi uma busca pontual, realizada com o auxílio de um índice não-cluster. 
 
Esse processo de busca da página de dados à partir de ponteiros localizados na estrutura 
do índice é conhecido por bookmark lookup. 
 6 
De acordo com a consulta efetuada na Tabela Orders [Figura 3], : 
 
Figura 3: Avaliação de execução de outra consulta 
 
Outras Análises 
Através do Execution Plan verifica-se que a consulta teve um alto custo para a execução da Query 
(Cost 100%) [Figura 4], ou seja, muito processamento para a busca da informação, isto pode estar 
acontecendo devido a uma falta de índices ou estatísticas desatualizadas. 
 
Figura 4: Informações do Plano de Execução 
O Medidor - Estimated Row Count: 999, 
• significa que o SQL Esperava desta tabela o retorno 999 registros. Essas informações 
são de armazenadas pelas estatísticas. 
O Medidor – Row Count: 2.499 
• porém na realidade está retornando muito mais registros do que o esperado. 
 
Neste caso, precisamos atualizar as estatísticas, que podem estar sofrendo interferências de algumas 
operações como: 
• Muitas alterações em índices; 
• Adição e remoção de muitos registros; 
 
2.3 Exercícios 
 
2.3.1 Exercício 1: Verificando o Plano de execução da Consulta 
1º. Passo: Inserir as Editoras: ‘Pearson’ e ‘Saraiva’. 
 
2º. Passo: Selecionar o código e o nome da editora que ainda não possui um exemplar cadastrado. 
Utilize o operador IN. 
 
3º. Passo: Verifique o plano de execução da Consulta. 
 
 7 
4º. Passo: Selecionar o código e o nome da editora que ainda não possui um exemplar cadastrado. 
Utilize o operador EXISTIS. 
 
Responda: 
a) Em termos de otimização de consultas aconteceu alguma 
diferença? 
 
4º. Passo: Certifique que as estatísticas estão atualizadas: Atualize a tabela Editora 
Sintaxe: UPDATE Statistics | [With FullScan] 
 
 
2.3.2 Exercício2: Verificando as Estatísticas 
1º. Passo: Mude o modo de exibição para Texto. Menu Query → Results in Text 
2º. Passo : Comando para “Ligar” as estatísticas. 
Sintaxe: Set Statistics IO ON 
 
Execute as consultas verificando os resultados em estatísticas: 
• Logical Reads: Informa o números de páginas lidas em memória. 
• Physical Reads: Número de páginas lidas em disco. Se as páginas requeridas por um 
comando não estão em memória, devem ser lidas do disco para a memória. 
• Read Ahead Reads: páginas lidas por antecipação. O SQL lê páginas adicionais para 
efeito de otimização, mantendo-as em cachê para agilizar sua utilização por outras 
queries. 
• Scan Count : Número de vezes que a tabela foi acionada. Dependendo da maneira como 
escrevemos a query, o mesmo pelo modelo do join, uma mesma tabela pode ser acessada 
repetidas vezes. 
 
2.3.3 Exercício 3: Verificando o plano de Consulta para as Consultas: 
 
a) Selecionar o nome dos usuários que já fizeram empréstimo da Obra “Sistemas de Banco de 
Dados”. Faça 2 consultas, a primeira passando por todas as tabelas, e a segunda excluindo a 
tabela exemplar. 
 
b) Altere a consulta anterior utilizando o distinct. 
 
c) Selecionar a quantidade de empréstimo feita por cada usuário. 
 
d) Selecionar o nome dos usuários que fizeram mais de um empréstimo na biblioteca 
 8 
Prática 3 
 
3- Consultas 
 
3.1 -Objetivo 
 
Ao final desta prática o aluno deverá ser capaz de: 
 
• Usar os operadorares: UNION, UNION ALL, INTERSECT, EXCEPT; 
• Criar uma VIEW a partir de uma tabela; 
• Criar uma VIEW a partir de múltiplas tabelas; 
• Criar uma VIEW a partir de uma VIEW; 
 
3.2 -Desenvolvimento 
 
Operador UNION ALL 
O operador UNION ALL é usado para combinar os resultados de duas instruções SELECT que 
incluem linhas duplicadas as mesmas regras que se aplicam a UNION se aplicam a UNION ALL. Os 
operadores UNION e UNION ALL são praticamente iguais, só que um retorna linhas de dados 
duplicados e outro não. 
Sintaxe: 
SELECT Coluna1[,Coluna2] 
FROM Tabela1[,Tabela2] 
[WHERE] 
UNION ALL 
SELECT Coluna1[,Coluna2] 
FROM Tabela1,[Tabela2] 
[WHERE] 
 
 
Operador INTERSECT (Alguns SGBDs não têm suporte a esse operador) 
O operador INTERSECT é usado para combinar duas instruções SELECT, mas retorna somente as 
linhas da primeira instrução SELECT que sejam idênticas a uma linha da segunda instrução 
SELECT. Exatamente como ocorre com o operador UNION, as mesmas regras se aplicam quando se 
usa o operador INTERSECT. 
Sintaxe: 
SELECT Coluna1[,Coluna2] 
FROM Tabela1[,Tabela2] 
[WHERE] 
INTERSECT 
SELECT Coluna1[,Coluna2] 
FROM Tabela1,[Tabela2] 
[WHERE] 
 
Operador EXCEPT (Interbase não tem suporte a esse operador) 
O operador EXCEPT combina duas instruções SELECT e retorna linhas da primeira instrução 
SELECT que não são retornadas pela segunda instrução SELECT. Novamente, as mesmas regras 
que se aplicam ao operador UNION se aplicam ao operador EXCEPT. 
Sintaxe: 
SELECT Coluna1[,Coluna2] 
FROM Tabela1[,Tabela2] 
[WHERE] 
EXCEPT 
 9 
SELECT Coluna1[,Coluna2] 
FROM Tabela1,[Tabela2] 
[WHERE] 
 
VIEW(Visões) 
Do ponto de vista do negócio, visões são elementos estratégicos que normalmente limitam o poder 
de acesso a informações. Do lado técnico, uma visão é uma tabela virtual: uma tabela resultante de 
uma consulta efetuada sobre uma ou mais tabelas. Em outras palavras parece uma tabela e age 
como uma tabela no que diz respeito ao usuário. 
 
Uma VIEW(Visão) na verdade é uma composição de uma tabela na forma de uma consulta 
predefinida. 
 
As VIEWs (Visões) suprem várias funções. Podem por exemplo, substituir consultas longas e 
complexas por algo mais fácil de ser entendido e manipulado. Visões também são elementos de 
segurança, a partir do momento que conseguem limitar o acesso dos usuários a determinados grupos 
de informações armazenadas em um banco de dados. 
 
Uma Visão não replica dados, ou seja, não gera uma cópia dos dados armazenados em outras 
tabelas. Quando os dados das tabelas são atualizados, as visões automaticamente refletem essas 
mudanças. 
 
WITH CHECK OPTION 
WITH CHECK OPTION é uma opção da instrução CREATE VIEW. O objetivo de WITH CHECK 
OPTION é assegurar que todos os Updats e Inserts satisfaçam às condições na definição da visão. 
Se eles não satisfizerem às condições, o Update ou Insert retornará um erro. 
 
Exercícios 
 
1) Selecionar o nome dos usuários que leram obras do autor Navathe. Faça a consulta usando join 
utilizando todas as tabelas. Verifique o plano de execução da consulta. 
 
2) Altere a consulta 1) resolva por subconsulta – utilize IN 
Responda: 
a) Em termos de Processamento da Consulta aconteceu alguma 
diferença? 
 
3) Altere a consulta 2) resolva por subconsulta – utilize EXISTS 
Responda: 
a) Em termos de Processamento da Consulta aconteceu alguma 
diferença? 
 
4) Selecionar o nome dos usuários que já leram Obras do autor Navathe e do autor Korth. 
 
5)Crie uma View ‘VUsuario’ com todos os dados da tabela usuário. 
 
6) Apresente em SQL as editora que possuem uma quantidade de obras menor que a média da 
quantidade de Obras de cada Editora. 
 
10)Verifique os dados da View VUsuario. Verifique os dados da tabela usuário. 
 
Responda: 
O que você observa? 
 10 
 
 
11) Essa View VUsuario é atualizável? Insira o seguinte registro na View Vusuario: 
(14,’testeviewusu’,'F',2) 
Responda: 
Selecione os dados da View e depois da tabela usuario. Observe os dados. 
 
12)Crie uma View ‘VNomUsuario’com o nome do usuario da tabela usuario. 
 
13)Insira o seguinte registro na View VNomUsuario: 
(15,’testeviewnomusu’,'F',2) 
 
Responda: 
O que aconteceu? Por quê? 
 
15) Crie uma View ‘VFeminino’ com todos os atributos da tabela usuario, cujo sexo é igual a 
feminino. 
 
16)Insira o registro a seguir na View VFeminino: 
(16,’testeviewfeminino’,'M',2) 
Responda: 
O que aconteceu? Por quê? 
 
17)Insira o registro a seguir na View Vfeminino 
(17,’testeviewfeminino2’,'F',2) 
 
18)Insira o registro a seguir na tabela usuario: 
(18,’testeviewfeminino3’,'F',2) 
 
19) Crie uma View ‘VFemProf’ que tenha todos os atributos da tabela usuário, que são professores 
e cujo sexo é feminino. 
Observe as dependências da tabela usuário e da View VFemProf. 
 
20)Insira o registro a seguir na View VfemProf: 
(19,’testeviewfemProf’,'F',2) 
Responda: 
O que aconteceu? Por quê? 
 
21)Insira o registro a seguir na tabela usuário: 
(19,'testeviewfemProf','F',2) 
 
 
 
 
 
 
 
 
 
 
22)Criar as Views de acordo com a estrutura proposta na figura5.Observe as dependências em 
propriedades. 
 
VUsuario 
VVUsuFem VVUsuMas 
VVNomUsu
Fem 
VVNomUsu
Mas 
Figura5: Demonstrativo da hierarquia das Views 
 11 
22.1)Criar uma View ‘VVUsuFem’ com todos os atributos da Vusuario, que sejam do sexo 
feminino. 
 
22.2)Criar uma View ‘VVUsuMas’ com todos os atributos da Vusuario, que sejam do sexo 
Masculino. 
 
22.3)Criar uma View ‘VVNomUsuFem’ com o nome do usuário da VVUsuFem. 
 
22.4)Criar uma View ‘VVNomUsuMas’ com o nome do usuário da VVUsuMas. 
 
23) Criar uma View ‘VFemChkOption’ com todos os atributos da tabela usuário cujo sexo é 
feminino, use a opção ‘With Check Option’. 
 
24)Insira o registro a seguir na View VFemChkOption: 
(20,'testeVFemChkOption','M',2) 
Responda: 
O que aconteceu? Por quê? 
 
25)Insira o registro a seguir na View VFemChkOption 
(20,'testeVFemChkOption','F',2) 
 12 
Prática 4 
 
4- Índices 
 
4.1 Objetivo 
 
Ao final desta prática o aluno deverá ser capaz de: 
 
• Criar índices para o Banco de Dados Biblioteca. 
 
4.2 Desenvolvimento 
A criação de índices é uma ferramenta poderosa para projetistas de Banco de Dados. Um índice 
é uma estrutura auxiliar que melhora o desempenho das consultas. O SQL-Server tem dois tipos 
de índices: Clustered Index e Nonclustered Index. 
 
4.2.1 ClusteredIndex 
Um Clustered Index ordena os dados de acordo com os valores do campo onde o índice está 
sendo ordenado. 
Ao criar um Clustered Index é necessário considerar: 
• Cada tabela pode conter apenas um Clustered Index. 
• A ordem física das linhas das tabelas (Leaf Nodes) e das linhas do índice (Non-Leaf 
Nodes) é a mesma. Caso queira utilizar um Clustered Index e um Nonclustered Index 
na mesma tabela, deve-se criar o Cluestered Index primeiro, uma vez que isso 
modifica a ordem dos registros na tabela. 
• Os valores da chave em um Clustered Index devem ser únicos. Isto é possível com a 
utilização da palavra UNIQUE, na criação do índice. Se não utilizarmos a palavra 
UNIQUE o SQL-Server adicionará um identificador interno, o qual será o único para 
cada chave do índice. Este identificador interno é de 8 bytes e é somente para uso do 
SQL-Server, não podendo seus valores serem acessados pelo usuário através de um 
comando SELECT. 
• O espaço ocupado por um Clustered Index é cerca de 5% do tamanho da tabela. Este 
percentual varia, dependendo da coluna que está sendo indexada. 
• Durante o processo de criação do índice o SQL-Server ocupara, temporariamente, 
espaço em disco. A criação de um Clustered Index requer cerca de 1,2 vezes o 
tamanho da tabela que está sendo indexada. Este espaço é liberado após a criação do 
índice. Caso o Banco de Dados não permita crescimento automático dos arquivos 
que o compõem e não exista este espaço disponível, o índice não poderá ser criado. 
4.3.2 Nonclustered Index 
Este tipo de índice cria uma estrutura separada das páginas da tabela. A ordem dos registros na 
tabela não é alterada com a criação de um Nonclustered Index. Com isso, os registros ficam 
armazenados em uma ordem aleatória. 
Observações sobre Nonclustered Index: 
• Nonclustered Index é o default para a criação de índices no SQL-Server. 
• Podemos ter um máximo de 249 Nonclustered Indexes por tabela. 
• A partida da versão 2005 do SQL-Server foi incluída uma novidade ao Nonclustered 
Index. É possível ampliar a funcionalidade de um Nonclustered Index, através da 
inclusão de colunas que não são chave, como parte das páginas Leaf do índice. Esta 
opção pode melhorar consideravelmente o desempenho das consultas, 
principalmente em casos onde todas as colunas do comando T-SQL fazem parte do 
Nonclustered Index, sejam estas colunas do tipo chave ou não. Isso ocorre porque, 
sendo todas as colunas de uma consulta parte do índice, bastará o SQL-Server 
pesquisar diretamente no índice, o que é muito mais rápido, do que, após localizar o 
 13 
registro no índice, ainda ter que percorrer a página de dados para a qual aponta o 
valor encontrado no índice. 
 
4.2.3 FillFactor Option 
Ao criarmos um Clustered Index, os dados da tabela são armazenados em páginas de dados de 
acordo com a ordem dos valores da coluna indexada. Ao inserirmos novos registros na tabela, 
estes precisam ser inseridos na ordem definida pelo campo que compõe o índice. 
 
Nestas situações pode acontecer de registros terem que ser deslocados para outras páginas para 
que o registro que está sendo inserido seja colocado na ordem correta. Esse procedimento 
também acontece quando utilizamos um Nonclustered Index, ao adicionarmos ou alterarmos os 
registros da tabela. 
 
Ao criarmos um índice podemos especificar um valor para a opção FillFactor. Este valor define 
um percentual a ser deixado em branco, sem registros gravados, em cada página de dados. O 
objetivo deste espaço em branco é agilizar as operações de Inserção, alteração e inclusão de 
dados, evitando que tenham que ser feitas movimentações de registros entre páginas. Com isso 
obtém-se um melhor desempenho nestas operações. O valor do FillFactor é um percentual que 
pode variar de 0 a 100%, um valor 100% significa que as páginas de dados serão ocupadas 
completamente. O valor 100% deve ser utilizado apenas se a tabela for usada somente para 
consulta. 
4.2.4 DBCC – Database Consistency Checker 
No SQL-Server temos uma série de comandos para manutenção e otimização de tabelas e 
índices, comandos conhecidos como “DBCC”. A maioria desses comandos é utilizada para 
verificação de consistência física e lógica de um banco de dados e de seus elementos, tais como 
tabelas e índices. Em muitos situações, o comando, além de fazer a verificação, é capaz de 
corrigir problemas encontrados. 
 
3.3 Exercícios 
3.3.1 Exercício 1: Entendendo a definição do índice 
 1º. Passo: Duplo clique na opção de índice a [Figura 5] será exibida: 
 
 14 
Figura 5: Tela Visualização e inclusão dos índices 
 2º. Passo: Duplo Clique no índice na tabela Pais, a tela será exibida [Figura 6]: 
 
Figura 6: Tela de Edição do Índice. 
Responda: Qual é o tipo do índice? 
As informações dos índices também podem ser visualizadas através do comando digitado no SQL 
SQL Server Management Studio: 
Sintaxe: exec sp_helpindex 
 
exec sp_helpindex Pais ou sp_helpindex Pais 
 
3.3.2 Exercício 2: Criando um Nonclustered Index Simples com o SQL Server Management 
Studio 
 1º. Passo: Clique com o botão direito na Tabela Pais, escolha a opção Indexes →New Index, 
a [Figura 5] , será exibida. 
 
 2º. Passo: Dê o nome para o índice de IndexNome e marque a coluna NomPais, que será a 
coluna referência para o índice [Figura 7]. 
 15 
 
Figura 7: Tela de Inclusão do Índice na Tabela Pais. 
 3º. Passo: Clique no botão ADD, para selecionar a coluna. 
 
 4º. Passo: Clique no botão OK. Observe que o índice foi criado de acordo com a 
especificação. 
 
 5º. Passo: Verifique a especificação do índice criado com o comando a seguir: 
 sp_helpindex Pais 
 
 6º. Passo: Selecione todos os dados da Tabela Pais. 
 
3.3.3 Exercício 3: Criando um Nonclustered Index com T-SQL 
 1º. Passo: Crie um índice do tipo Nonclustered para o Nome da Editora na Tabela 
Editora. 
Sintaxe: Create Nonclustered Index On() 
 
 2º. Passo: Verifique se o índice foi criado 
sp_helpindex Editora 
 
3.3.4 Exercício 4: Excluir um Índice com T-SQL 
 1º. Passo: Excluir o índice IndexNome da Tabela Pais 
Sintaxe: Drop index . 
 
 2º. Passo: Verifique se o índice foi excluído 
sp_helpindex Pais 
 16 
 
3.3.5 Exercício 5: Manipulando as propriedades do Índice com T-SQL 
 1º. Passo: Crie um índice com o Nome IndexNomePais na Tabela Pais para o atributo 
NomPais, do tipo Nonclustered e a ordenação DESC. 
Sintaxe: 
Create Nonclustered Index On 
( ); 
 
 2º. Passo: Selecione os dados da Tabela Pais. 
 
 3º. Passo: Excluir o IndexNomePais da Tabela Pais. 
 
 4º. Passo: Crie um índice com o Nome IndexNomePais na Tabela Pais para o atributo 
NomPais, do tipo Nonclustered e a ordenação ASC. 
 
 5º. Passo: Selecione os dados da Tabela Pais. 
 
 6º. Passo: Execute o 3º. Passo. Crie um índice para a coluna NomPais como UNIQUE. 
 
 7º. Passo: Inserir o País - Brasil com o código 6. 
Responda: 
a) Ocorreu erro? Por quê? 
 
2.3.3 Exercício 1: Verificando as Estatísticas usando Índices 
1º. Passo: Mude o modo de exibição para Texto. Menu Query → Results in Text 
2º. Passo : Comando para “Ligar” as estatísticas. 
Sintaxe: Set Statistics IO ON 
 
Execute as consultas verificando os resultados em estatísticas: 
• Logical Reads: Informa o números de páginas lidas em memória. 
• Physical Reads: Número de páginas lidas em disco. Se as páginas requeridas por um 
comando não estão em memória, devem ser lidas do disco para a memória. 
• Read Ahead Reads: páginas lidas por antecipação. O SQL lê páginas adicionais para 
efeito de otimização, mantendo-as em cachê para agilizar sua utilização por outras 
queries. 
• Scan Count : Número de vezes que a tabela foi acionada. Dependendo da maneira como 
escrevemos a query, o mesmo pelo modelo do join, uma mesma tabela pode ser acessada 
repetidas vezes. 
 
2º. Passo : Verifiquese existe um índice do tipo NonCluster para a coluna Nome da Editora na 
Tabela Editora. Caso não exista crie esse índice. 
 
3º. Passo : Execute o comando: 
Sintaxe: dbcc show_statistics(,) 
 
O Ponteiro do índice é representado pela coluna CodEdi que é o índice cluster da 
tabela. Portanto a coluna CodEdi foi anexada a tabela à estrutura da tabela na forma de ponteiro. 
 
3.3.6 Exercício 6: Reconstrução de Índices com T-SQL 
Um índice é gravado fisicamente dentro do banco de dados em uma página de índices (Index Page). 
As páginas de índice são criadas seqüencialmente através de ponteiros que indicam o local da 
próxima página. Quando campos-chave são alterados e conseqüentemente o índice, as páginas de 
índices ficam fragmentadas. É aconselhável que se reconstrua o índice com o comando Create 
 17 
Index e a opção With Drop_Existing, que aproveita a ordem dos dados já existentes para acelerar o 
processo. 
 
 1º. Passo: Reconstruir o índice com o Nome – IndexNome, na Tabela Pais para o atributo 
NomPais, esse índice é do tipo Nonclustered. Utilize o comando Create Index com a opção With 
DROP_Existing. 
Sintaxe: 
Create Nonclustered Index On 
(NomeColuna) With DROP_Existing 
 
3.3.7 Exercício 7: Usando a Opção - FillFactor Option 
 1º. Passo: Crie um índice para o atributo NomAut na Tabela Autor, use a opção With 
FillFactor 65%. 
Sintaxe: 
Create Nonclustered Index On 
(NomeColuna)With FillFactor = 
 
Create Nonclustered Index IndexNomeAutor On Autor(NomAut)With 
FillFactor = 65 
 
3.3.8 Exercício 8: Criando um Nonclustered Index Composto com T-SQL 
 1º. Passo: Crie um índice composto do tipo Nonclustered para o Código da Obra e Código 
da Editora na Tabela Exemplar. 
Sintaxe: Create Nonclustered Index On 
{,',,[]) 
 18 
Prática 5 
 
5 - Criação de um atributo com numeração automática 
 
5.1 Objetivo 
 
Ao final desta prática o aluno deverá ser capaz de: 
a. Gerar um atributo como autoincremento 
 
5.2 Desenvolvimento 
A grande maioria dos SGBDs populares possuem o recurso de auto numeração para colunas, como é o caso 
das SEQUENCES para Oracle, ou da propriedade AUTO_INCREMENT para o MySQL. 
 
O SQL Server disponibiliza a propriedade IDENTITY para as colunas do tipo inteiro, permitindo a criação 
de uma coluna auto numerável. 
 
Algumas condições devem ser observadas para trabalhar com esta propriedade: 
• Apenas colunas do tipo INT, TINYINT, SMALLINT e BIGINT podem ser auto numeráveis 
• Somente uma única coluna pode ser auto numerável em uma tabela 
A propriedade IDENTITY é utilizada para atributos (campos/colunas) das tabelas nas funções CREATE 
TABLE e ALTER TABLE, e tem como finalidade incrementar um valor a cada nova inserção. 
Sintaxe: 
IDENTITY [ (início , incremento ) ] 
• Início: Valor a ser utilizado para o primeiro valor inserido na coluna. 
• Incremento: Valor a ser incrementado a cada nova inserção. 
Exemplo para criação de uma tabela utilizando um campo auto incremento: 
Create Table RamoAtividade2 
( CodRamAti smallint Identity(1,1) not null, 
DesRamAti varchar(100) not null, 
Constraint pk_RamoAtividade2 primary key(CodRamAti) 
); 
Nesse exemplo a tabela RamoAtividade possui dois atributos, sendo CodRamAti a chave primária e tendo 
seu primeiro valor válido igual a 1 e se auto incrementando de 1 em 1, ou seja, primeiro registro igual a 1, o 
segundo igual a 2 e assim sucessivamente. 
A inserção em tabelas que possuem campos auto incrementos deve-se suprimir da sintaxe INSERT os 
mesmos, conforme exemplo a seguir: 
Insert into RamoAtividade2 (DesRamAti) values ('Agrícola'); 
Insert into RamoAtividade2 (DesRamAti) values ('Mineração'); 
Insert into RamoAtividade2 values ('Papelaria'); 
 19 
Observe que o atributo CodRamAti foi suprimido, devido a funcionalidade de auto incremento, assim os 
valores serão gerados automaticamente conforme suas configurações. 
O que acontece quando um comando de exclusão de dados é executado? 
Delete from RamoAtividade2 where CodRamAti = 3; 
insert into RamoAtividade2 values ('Gráfica'); 
Para saber o valor atual do IDENTITY, basta usar o seguinte comando: 
SELECT IDENT_CURRENT(''); 
SELECT IDENT_CURRENT('RamoAtividade2'); 
Portanto, em uma nova instrução INSERT, o registro gerado, terá o valor de acordo com a sequência, ou 
seja, descartando o valor que foi excluído. 
Supondo que em um determinado momento torna-se necessário inserir um novo registro, porém com valor 
retroativo ao IDENTITY, ou seja, um valor que já foi inserido e deletado. Nos exemplos esse valor é o “3”. 
Utilizando a situação descrita é possível “desligar” o IDENTITY em uma instrução de INSERT. 
Sintaxe: 
SET IDENTITY_INSERT (,) VALUES (,) 
SET IDENTITY_INSERT OFF 
 
SET IDENTITY_INSERT RamoAtividade2 ON 
INSERT INTO RamoAtividade2(CodRamAti,DesRamAti) VALUES (3,'Química') 
SET IDENTITY_INSERT RamoAtividade2 OFF 
Essa forma de execução do comando, o SQL Server não gera o valor de auto incremento para o atributo 
CodRAmAti, permitindo que o usuário informe manualmente o valor para o mesmo. (ON: desabilita a 
funcionalidade do auto incremento, OFF: habilita a funcionalidade). 
Suponha que ocorra uma exclusão de todos os dados da tabela: 
Delete from RamoAtividade2; 
Insert into RamoAtividade2 values ('Industrial'); 
Observe que mesmo com a exclusão de todos os registros da tabela, ao fazer a inclusão de um novo registro a 
sequência para o auto incremento continua de onde parou. 
Em caso de necessidade é possível “reiniciar” a sequência de auto incremento. 
DBCC Checkident( , reseed, 0); 
DBCC Checkident( RamoAtividade2, reseed, 0); 
Insert into RamoAtividade2 (DesRamAti) values ('Agrícola'); 
Insert into RamoAtividade2 (DesRamAti) values ('Mineração'); 
Insert into RamoAtividade2 values ('Papelaria'); 
A sequência foi alterada para 0, logo a próxima inserção terá valor igual a “1”. Não é obrigado iniciar a 
sequência do 0, podendo alterar esse valor conforme as necessidades vigentes. 
9.2.1 Definindo um atributo como identity via interface gráfica 
 20 
Em designer crie uma tabela RamodeAtividade3, com o atributo CodRamAti como identity, mude na janela 
propriedades “Especificação de Identidade” -> É identidade para SIM. 
 
Figura: Visão propriedades da Tabela. 
9.2.2 Diferença entre IDENTiTY e SEQUENCE 
Uma das diferenças entre SEQUENCE e IDENTITY está no fato de que as SEQUENCES são acionadas 
sempre quando forem necessárias, sem dependência de tabelas e campos no banco, onde pode ser chamada 
diretamente por aplicativos. 
 
Outra diferença está que nas SEQUENCES, nós podemos obter o novo valor antes de usá-lo em um 
comando, diferente do IDENTITY, onde não podemos obter um novo valor. Além disso, com o IDENTITY 
não podemos gerar novos valores em uma instrução UPDATE, enquanto que com SEQUENCE, já podemos. 
 
Com SEQUENCES, podemos definir valores máximos e mínimos, além de podemos obter mais valores em 
sequencia de um só vez, utilizando para isso a procedure SP_SEQUENCE_GET_RANGE, onde então é 
permitido atribuirmos os valores individuais para aumentar então o desempenho no uso da SEQUENCE. 
 
Uma das grandes utilidades em IDENTITY está no fato de podermos trabalhar com o mesmo na utilização 
de TRANSAÇÕES de INSERT, pois, só iremos gerar um próximo valor a partir do momento que o comando 
for executado, ou seja, que a transação for aceita,ao contrário de uma SEQUENCE, que uma vez chamado 
seu próximo valor, mesmo que ocorra um erro de transação, o valor é alterado. 
 
- MINVALUE e MAXVALUE: delimitam o limite da SEQUENCE com seu respectivo valor máximo e 
mínimo. Caso o valor não seja inserido, será atribuído o valor do DataType escolhido. 
 
- INCREMENT: define em quantos números serão incrementadas as sequencias. No exemplo da imagem 
acima, será realizado o incremento de 1 em 1. 
 
- CYCLE: A propriedade do objeto CYCLE permite começar novamente um ciclo a partir do momento que a 
propriedade MINVALUE e MAXVALUE for atingida. 
 
- CACHE: pelo fato de desempenho, o SQL Server pré-aloca os números sequencias pela propriedade 
CACHE, sendo que o valor padrão para esta é 15, significando que valores de 1 a 15 serão disponibilizados 
na memória a partir do último valor armazenado em cache. 
 
Create table Localidade2 
 21 
( 
 CodLoc smallint not null, 
 NomLoc varchar(100) not null, 
 Constraint pk_Localdiade2 primary key(CodLoc) 
) 
Os objetos SEQUENCES não estão vinculados a tabela. Este vinculo irá ocorrer, por exemplo, se utilizando 
de PROCEDURES. 
CREATE SEQUENCE dbo.SEQCodLoc 
 START WITH 1 
 INCREMENT BY 1 
 NO CACHE 
 NO CYCLE 
 
Em seguida é necessário criar um procedimento. 
 
Create Proc PRC_Sequencia 
 ( 
 @NomLoc varchar(100) 
 ) 
 AS 
 BEGIN 
 INSERT INTO Localidade2 
 values( NEXT VALUE FOR dbo.SEQCodLoc, @NomLoc ) 
 END 
 
exec PRC_Sequencia 'Belo Horizonte'; 
 
 Select * from localidade2; 
 
 exec PRC_Sequencia 'São Paulo'; 
9.3 Inserindo Dados em uma tabela proveniente de uma consulta (Combinação de Insert e Select) 
Insert into RamoAtividade2 (DesRamAti) Select DesRamAti from RamoAtividade; 
9.4 Combinação de Update e Select 
Pode-se combinar o comando SELECT e UPDATE. No exemplo, os contratos de menor valor receberão um 
aumento de 10%. 
UPDATE Contrato 
SET ValTotCon = ValTotCon * 1.1 
WHERE VAlTotCon = (SELECT MIN(ValTotCon) FROM Contrato) 
Utilização do SELECT como valor para o UPDATE 
UPDATE Contrato 
SET ValTotCon = (SELECT MAX(ValTotCon) FROM Contrato) 
WHERE NumCon = 1 
9.5 Diferença entre primary key e unique 
Após a escolha entre os atributos que são candidatos a serem chave primária, aquele que será a chave 
primária, os demais atributos com a característica de ser único deverão se definidos como UNIQUE. 
Create Table Empregado 
( 
 22 
 MatEmp smallint identity not null, 
 NomEmp varchar(200) not null, 
 CPFEmp smallint unique not null, 
 Constraint pk_Emp primary key(MatEmp) 
) 
Execute os comandos: 
Insert into empregado values( 'Viviane Cristina Dias',11); 
 
Select * from Empregado; 
 
Insert into empregado values( 'Ana Maria Silva Duarte',11);