MIC PERNAMBUCO
SEGUNDA FASE / S2B
Banco de Dados
Turma: Manhã / FIR
Recife-PE
www.micpernambuco.com.br
 Existem
muita tarefas que devem ser feitas em um
banco de dados, com o passar do tempo
chegamos a classificá-las e distribuí-las em grupos,
mas por onde começar?






Backup
Restore
Concorrência
Profiler
SQL Server Agent
SQL Server Configuration Manager
A
principal tarefa de um DBA é garantir que os
dados não só vão ser inseridos, mas também que
estes dados permaneçam existindo
 Independente do sistema de contingência o
Backup é indispensável
 Devemos entender como a empresa trabalha e
qual o tamanho do banco de dados para definir a
melhor política de backup
 Fazer testes periódicos para garantir que o
processo de backup não está com problemas
 Comando

de backup:
BACKUP DATABASE { database_name |
@database_name_var } TO <backup_device> [ ,...n ] [
<MIRROR TO clause> ] [ next-mirror-to ] [ WITH {
DIFFERENTIAL | <general_WITH_options> [ ,...n ] } ] [;]
 Facilitando,



exemplo:
BACKUP DATABASE ADVENTUREWORKS
TO DISK=“C:\SQL\AW.BAK”
WITH FORMAT, COMPRESSION, STATS=10
 Backup
completo
Cria uma cópia de todo o banco de dados.
 Backup diferencial
Cria uma cópia do que foi modificado em relação
ao último backup completo.
 Backup do log de transações
Cria uma cópia do log de transações do banco,
sendo possível, ao DBA, restaurar o banco de
dados posteriormente até determinada transação.
Somente Full
Sunday
Monday
Tuesday
Full e Log
Sunday
Monday
 Importante
recurso para recuperação da
informação em caso de desastre
 Apenas retornamos uma database a partir do
backup full (completo)
 O processo de restore deve ser feito com
frequência no ambiente de teste, para se evitar
falhas em um possível desastre, checando a
consistência do processo
 Sempre que um restore é feito, a database fica
indisponível para acesso dos usuários

Comando de Restore:
 RESTORE DATABASE { database_name |
@database_name_var } [ FROM <backup_device> [
,...n ] ] [ WITH {
[ RECOVERY | NORECOVERY
| STANDBY =
{standby_file_name |
@standby_file_name_var }
] |,
<general_WITH_options> [ ,...n ]
|,
<replication_WITH_option> | ,
<change_data_capture_WITH_option>
|,
<service_broker_WITH
options>
| , <point_in_time_WITH_options—
RESTORE_DATABASE>
} [ ,...n ] ] [;]
Simplificando:
 RESTORE DATABASE ADVENTUREWORKS
 FROM DISK=“C:\SQL\AW.BAK”
 WITH NORECOVERY
A
concorrência é muito importante ser
observada, quando temos muita
concorrência e as aplicações não foram
preparadas para isso acabamos com
problemas de performance. Como
observar?

Observar os metadados do SQL Server, usando:



Stored procedures
Dynamic Management Views
SQL Server Management Studio
 Quais


nossas principais opções?
sp_who e sp_who2
sys.dm_tran_locks
 Ter
o máximo controle sobre abertura e
fechamento das transações nas aplicações
 Minimizar os erros de codificação
 Otimizar ao máximo suas consultas
 Em
casos extremos podemos acabar com a
conexão que está bloqueando as transações:


KILL (ID da conexão)
Exemplo: KILL 53
O
SQL Server Agent tem uma ferramenta
que auxilia na execução de processos
automatizados
 Onde podemos agendar as tarefas para
serem executadas.
Tarefas agendadas
Log das tarefas
 Iniciando
o serviço do SQL Server por meio da
ferramenta de configuração de startup
 Escolha do usuário que irá executar o serviço
 Listando
os primeiros n valores com TOP n
 Usando
funções agregadas
 Noções
básicas sobre a cláusula GROUP BY
 Gerando
valores agregados em conjuntos
de resultados
 Usando
as cláusulas COMPUTE e
COMPUTE BY
 Lista
apenas os n primeiros registros de um
conjunto de resultados
 Especifica o intervalo de valores na cláusula
ORDER BY
 Retorna correspondências se WITH TIES for
usada
Exemplo 1
USE northwind
SELECT TOP 5 orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
GO
Exemplo 2
USE northwind
SELECT TOP 5 WITH TIES orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
GO
Funções agregadas
Descrição
AVG
Média de valores em uma expressão numérica
COUNT
Número de valores em uma expressão
COUNT (*)
Número de registros selecionados
MAX
Maior valor da expressão
MIN
Menor valor da expressão
SUM
Valores totais em uma expressão numérica
STDEV
Desvio padrão de todos os valores
STDEVP
Desvio padrão da população
VAR
Variância estatística de todos os valores
VARP
Variância estatística de todos os valores da
população
A
maioria das funções agregadas ignora
valores nulos
 A função COUNT (*) conta todos os registros
com valores nulos
USE northwind
SELECT COUNT (*)
FROM employees
GO
Exemplo 1
USE northwind
SELECT COUNT(reportsto)
FROM employees
GO
Exemplo 2
 Usando
a cláusula GROUP BY
 Usando a cláusula GROUP BY com a
cláusula HAVING
USE northwind
SELECT productid, orderid
,quantity
FROM orderhist
GO
productid
orderid
USE northwind
SELECT productid
,SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid
GO
quantity
1
1
5
1
1
10
2
1
10
2
2
25
3
1
15
3
2
30
productid
Apenas os
registros que
satisfazem às
condições da
cláusula WHERE
serão agrupados
total_quantity
1
15
2
35
3
45
productid
2
total_quantity
35
USE northwind
SELECT productid
,SUM(quantity) AS total_quantity
FROM orderhist
WHERE productid = 2
GROUP BY productid
GO
USE northwind
SELECT productid, orderid
,quantity
FROM orderhist
GO
productid
orderid
quantity
USE northwind
SELECT productid, SUM(quantity)
AS total_quantity
FROM orderhist
GROUP BY productid
HAVING SUM(quantity)>=30
GO
1
1
5
1
1
10
2
1
10
2
35
2
2
25
3
45
3
1
15
3
2
30
productid
total_quantity
 Usando
a cláusula GROUP BY com o
operador ROLLUP
 Usando a cláusula GROUP BY com o
operador CUBE
 Usando a função GROUPING
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH ROLLUP
ORDER BY productid, orderid
GO
productid
orderid total_quantity
Descrição
NULL
NULL
95
1
NULL
15
1
1
5
1
2
10
2
NULL
35
2
1
10
2
2
25
Valores de detalhe de productid 2, orderid 1
3
NULL
45
Resume apenas os registros de productid 3
3
1
15
Valores de detalhe de productid 3, orderid 1
3
2
30
Valores de detalhe de productid 3, orderid 2
Total geral
Resume apenas os registros de productid 1
Valores de detalhe de productid 1, orderid 1
Valores de detalhe de productid 1, orderid 2
Resume apenas os registros de productid 2
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid
GO
productid
orderid total_quantity
Descrição
NULL
NULL
95
Total geral
NULL
1
30
Resume todos os registros de orderid 1
O operador CUBE NULL
produz dois
1
valores de
1
resumo a mais
que o operador
1
ROLLUP
2
2
65
Resume todos os registros de orderid 2
NULL
15
Resume apenas os registros de productid 1
1
5
Valores de detalhe de productid 1, orderid 1
2
10
Valores de detalhe de productid 1, orderid 2
NULL
35
Resume apenas os registros de productid 2
2
1
10
Valores de detalhe de productid 2, orderid 1
2
2
25
Valores de detalhe de productid 2, orderid 2
3
NULL
45
Resume apenas os registros de productid 3
3
1
15
Valores de detalhe de productid 3, orderid 1
3
2
30
Valores de detalhe de productid 3, orderid 2
USE northwind
SELECT productid, GROUPING (productid)
,orderid, GROUPING (orderid)
,SUM(quantity) AS total_quantity
FROM orderhist
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid
GO
productid
1 representa os valores de resumo
na coluna anterior
0 representa os valores de detalhe na
na coluna anterior
NULL
NULL
NULL
1
1
1
2
2
2
3
3
3
orderid
1
1
1
0
0
0
0
0
0
0
0
0
NULL
1
2
NULL
1
2
NULL
1
2
NULL
1
2
total_quantity
1
0
0
1
0
0
1
0
0
1
0
0
95
30
65
15
5
10
35
10
25
45
15
30
COMPUTE
COMPUTE BY
USE northwind
SELECT productid, orderid
,quantity
FROM orderhist
ORDER BY productid, orderid
COMPUTE SUM(quantity)
GO
USE northwind
SELECT productid, orderid, quantity
FROM orderhist
ORDER BY productid, orderid
COMPUTE SUM(quantity) BY productid
COMPUTE SUM(quantity)
GO
productid
orderid
quantity
productid
orderid
quantity
1
1
5
1
1
5
1
2
10
1
2
10
2
1
10
2
1
10
2
2
25
2
2
25
3
1
15
3
2
30
3
1
15
95
3
2
30
sum
sum
sum
15
35
sum
45
sum
95
Indexe colunas freqüentemente agregadas
Evite usar funções agregadas com colunas que
contêm valores nulos
Use a cláusula ORDER BY para garantir a classificação do
conjunto de resultados
Use o operador ROLLUP sempre que possível
Use a cláusula COMPUTE ou COMPUTE BY para testar
seus aplicativos
 Recuperando
dados usando a instrução SELECT
 Filtrando dados
 Formatando conjuntos de resultados
 Como as consultas são processadas
 Como as consultas são colocadas em cache
automaticamente
 Considerações sobre o desempenho
 Usando
a instrução SELECT
 Especificando colunas
 Usando a cláusula WHERE para especificar
registros
A
lista de seleção especifica as colunas
 A cláusula WHERE especifica a condição que
restringe a consulta
 A cláusula FROM especifica a tabela
Sintaxe parcial
SELECT [ALL | DISTINCT] <lista_de_seleção>
FROM {<origem_da_tabela>} [,…n]
WHERE <condição_de_pesquisa>
USE northwind
SELECT employeeid, lastname, firstname, title
FROM employees
GO
employeeid
lastname
firstname
title
1
Davolio
Nancy
Sales Representative
2
Fuller
Andrew
Vice President, Sales
3
Leverling
Janet
Sales Representative
4
Peacock
Margaret
Sales Representative
5
Buchanan
Steven
Sales Manager
6
Suyama
Michael
Sales Representative
7
King
Robert
Sales Representative
8
Callahan
Laura
Inside Sales Coordinator
9
Dodsworth
Anne
Sales Representative
USE northwind
SELECT employeeid, lastname, firstname, title
FROM employees
WHERE employeeid = 5
GO
employeeid
5
lastname
Buchanan
firstname
Steven
title
Sales Manager
 Usando
operadores de comparação
 Usando comparações de seqüências
 Usando operadores lógicos
 Recuperando um intervalo de valores
 Usando uma lista de valores como critérios de
pesquisa
 Recuperando valores desconhecidos
Exemplo 1
USE northwind
SELECT lastname, city
FROM employees
WHERE country = 'USA‘
GO
lastname
Davolio
Fuller
Leverling
Peacock
Callahan
city
Seattle
Tacoma
Kirkland
Redmond
Seattle
USE northwind
SELECT companyname
FROM customers
WHERE companyname LIKE '%Restaurant%‘
GO
companyname
GROSELLA-Restaurante
Lonesome Pine Restaurant
Tortuga Restaurante
Exemplo 1
USE northwind
SELECT productid, productname, supplierid, unitprice
FROM products
WHERE (productname LIKE 'T%' OR productid = 46)
AND (unitprice > 16.00)
GO
productid
14
29
62
productname
Tofu
Thüringer Rostbratwurst
Tarte au sucre
supplierid
6
12
29
unitprice
23.25
123.79
49.3
USE northwind
SELECT productname, unitprice
FROM products
WHERE unitprice BETWEEN 10 AND 20
GO
productname
Chai
Chang
Aniseed Syrup
Genen Shouyu
Pavlova
Sir Rodney’s Scones
…
unitprice
18
19
10
15.5
17.45
10
…
Exemplo 1
Exemplo 1
USE northwind
SELECT companyname, country
FROM suppliers
WHERE country IN ('Japan', 'Italy')
GO
companyname
Tokyo Traders
Mayumi’s
Formaggi Fortini s.r.l.
Pasta Buttini s.r.l.
country
Japan
Japan
Italy
Italy
USE northwind
SELECT companyname, fax
FROM suppliers
WHERE fax IS NULL
GO
companyname
Exotic Liquids
New Orleans Cajun Delights
Tokyo Traders
Cooperativa de Quesos ‘Las Cabras’
…
fax
NULL
NULL
NULL
NULL
…
 Classificando
dados
 Ocultando registros duplicados
 Alterando nomes de colunas
 Usando literais
Exemplo 1
USE northwind
SELECT productid, productname, categoryid, unitprice
FROM products
ORDER BY categoryid, unitprice DESC
GO
productid
38
43
2
…
63
8
61
…
productname
Cote de Blaye
Ipoh Coffee
Chang
…
Vegie-spread
Northwoods Cranberry Sauce
Sirop d'érable
…
categoryid
1
1
1
…
2
2
2
…
unitprice
263.5000
46.0000
19.0000
…
43.9000
40.0000
28.5000
…
Exemplo 1
USE northwind
SELECT DISTINCT country
FROM suppliers
ORDER BY country
GO
country
Australia
Brazil
Canada
Denmark
Finland
France
Germany
Italy
Japan
Netherlands
Norway
Singapore
Spain
Sweden
UK
USA
USE northwind
SELECT firstname AS First, lastname AS Last
,employeeid AS 'Employee ID:'
FROM employees
GO
First
Nancy
Andrew
Janet
Margaret
Steven
Michael
Robert
Laura
Anne
Last
Davolio
Fuller
Leverling
Peacock
Buchanan
Suyama
King
Callahan
Dodsworth
Employee ID:
1
2
3
4
5
6
7
8
9
USE northwind
SELECT firstname, lastname,
'Identification number:', employeeid
FROM employees
GO
firstname
Nancy
Andrew
Janet
Margaret
Steven
Michael
Robert
Laura
Anne
lastname
Davolio
Fuller
Leverling
Peacock
Buchanan
Suyama
King
Callahan
Dodsworth
employeeid
Identification Number: 1
Identification Number: 2
Identification Number: 3
Identification Number: 4
Identification Number: 5
Identification Number: 6
Identification Number: 7
Identification Number: 8
Identification Number: 9