Introdução ao SQL Server – Aula 2.2

Nesta segunda aula veremos como trabalhar com tipos de dados para armazenar Texto e Data.

Data/Hora

Armazenar data no banco de dados requer saber o intervalo de data/hora que se pretende armazenar e também a precisão desejada.

Os tipos DataTime, DataTime2 e SmallDataTime permitem armazenar datas e horas com intervalos específicos.

Datetime (8 bytes) de 1753-01-01 a 9999-12-31
Datetime2 (6-8 bytes) de 0001-01-01 a 9999-12-31
SmallDateTime (4 bytes) de 1900-01-01 a 2079-06-06

Já o tipo DateTimeOffset, permite armazenar datas e horas e também o UTC (fuso horário) da localidade em questão, permitindo cálculos precisos para sistemas que trabalham com mais de um fuso horário.

DateTimeOffset (8-10 bytes) 0001-01-01 a 9999-12-31

Para situações que se deseja somente armazenar data ou somente hora, existem os tipos Date e Time, reduzindo em alguns bytes o tamanho dos dados a serem armazenados.

Date (1-3 bytes) de 0001-01-01 a 9999-12-31
Time (3-5 bytes) de 00:00:00.0000000 a 23:59:59.9999999

Introdução ao SQL Server – Aula 2.1

Nesta segunda aula veremos como trabalhar com tipos de dados para armazenar Texto e Data.

Texto

Armazenar texto no banco de dados pode não ser uma tarefa fácil se não se sabe o tamanho dos textos que deseja armazenar e se não se sabe se o texto terá tamanho variável ou não.

O tipo de dados CHAR, permite armazenar textos com tamanho fixo, exemplo siglas como as de UF (AC, PR, SP, RJ…) e IATA (CWB, GRU, GIG…), de forma a utilizar 1 byte por caractere, podendo ter tamanho fixo para até 8000 caracteres.

Sintaxe: CHAR(n. de caracteres)

O tipo de dados VARCHAR, permite armazenar textos com tamanho variável, exemplo nomes e logradouros, de forma a utilizar 1 byte por caractere e 2 bytes para identificar o “final” do texto, podendo armazenar textos com mais de 8000 caracteres.

Sintaxe: VARCHAR(n. de caracteres), acima de 8000: VARCHAR(MAX)

Também há tipos de dados específicos para armazenar caracteres de outros idiomas, como árabe, hebraico, chinês e coreano, tendo um prefixo N, como NCHAR e NVARCHAR, utilizando 2 bytes para cada caractere, tendo assim tamanho fixo limitado à 4000 caracteres.

Sintaxe: NCHAR(n. de caracteres)

Sintaxe: NVARCHAR(n. de caracteres), acima de 4000: NVARCHAR(MAX)

Introdução ao SQL Server – Aula 1.2

Depois de definido quais as colunas que a tabela terá, se faz necessário definir os tipos de dados que serão utilizados para cada coluna, ex.:

1. Nome do cliente, precisar ser armazenado como “Texto”,
2. Número da rua como “Numérico”,
3. Preço da gasolina como “Numérico”,
4. Data de validade como “Data”…

Para isso, no SQL Server tem tipos de dados para cada uma destas situações:

1. Inteiro (-1, 0, 1, 2, 3, 4, …),
2. Decimal (149.99),
3. Flutuantes (π, 0.12515481…),
4. Monetário ($ 149.99),
5. Data/Hora (01/12/1987),
6. Booleano (Verdadeiro/Falso),
7. Texto

Obs.: Também existem outros tipos com finalidades bem específicas e complexas, como os tipos binários, XML, hierárquicos e geométricos, que não requerem atenção no momento.

Inteiros

Para o primeiro tipo numérico do SQL Server, se tem os tipos inteiros (bigint, int, smallint, tinyint), que possuem capacidade de armazenar números sem casas decimais, de diferentes intervalos.

O tinyint é capaz de armazenar números inteiros de 0 a 255, utilizando um 1 byte. Sendo suficientemente capaz de salvar o DDD de um telefone ou a idade de uma pessoa.

O smallint é capaz de armazenar números inteiros de -32,768 a 32,767, utilizando um 2 byte. Sendo suficientemente capaz de salvar a temperatura em ºC de uma cidade.

O int é capaz de armazenar números inteiros de -2,147,483,648 a 2,147,483,647, utilizando um 4 byte. Sendo suficientemente capaz de salvar um CEP sem os “-”.

O bigint é capaz de armazenar números inteiros de -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807, utilizando um 8 byte. Sendo suficientemente capaz de salvar um CNPJ ou CPF.

Decimais

Para o segundo tipo numérico do SQL Server, se tem os tipos decimais (Decimal ou Numeric), que permitem armazenar números com casas decimais precisas, ou seja, o preço de uma casa 190.000,00, tem 8 dígitos, sendo 2 dígitos após a virgula, que será representado pelo tipo Decimal(8,2), segundo a sintaxe: Decimal(quantidade de dígitos, quantidade de dígitos após a virgula)

Esta precisão requer a utilização de um número um pouco maior de bytes em comparação aos outros tipos numéricos, conforme a relação:

1 a 9 dígitos, 5 bytes.
10-19 dígitos, 9 bytes.
20 a 28 dígitos, 13 bytes.
29 a 38 dígitos, 17 bytes.

Flutuantes

Para o terceiro tipo numérico do SQL Server, os tipos flutuantes (real e float) não requerem da mesma precisão dos tipos decimais, por consequência utilizam menos bytes (de 4 a 8 bytes), e podem armazenar números maiores que os decimais e inteiros, como dízimas periódicas e o valor de π, mas devido a sua falta de precisão, não são adequados para dados monetários.

Monetários

O quarto tipo numérico, existe especificamente para armazenar dados monetários utilizando menos bytes do que os tipos numéricos, conforme abaixo:

Money, para armazenar -922,337,203,685,477.5808 a 922,337,203,685,477.5807 em 8 bytes
SmallMoney, para armazenar – 214,748.3648 a 214,748.3647 em 4 bytes

Booleano

Um “quinto tipo numérico”, existe especificamente para armazenar dados do tipo 1:verdadeiro/0:falso denominado “bit“ , utilizando 1 byte para cada 8 colunas deste tipo, ou seja, 1 coluna bit ocupa 1 byte na tabela, assim como 8 colunas bit ocupam também ocupam 1 byte, e 9 a 16 colunas ocupam 2 bytes, assim por diante.

Introdução ao SQL Server – Aula 1.1

Olá pessoas, faz um bom tempo que não escrevo nada no blog, mas achei interessante disponibilizar alguns assuntos que estou trabalhando em um treinamento de SQL realizado na EuroIT, sendo o primeiro deles um passo-a-passo sobre como criar/normalizar tabelas no SQL Server, que achei interessante compartilhar no blog, devido à abordagem mais prática adotada.

Para criar uma tabela em qualquer banco de dados, é necessário pensar como organizar os registros que serão inseridos nela, para não termos situações onde temos um monte de dados sem uma ordem adequada ou que permita identificar o que esta salvo lá, ex.:

João Batista Queiroz, Travessa da Lapa 5034, Joãozinho, 83792-000, 39382872A, 56877848454-54, 012.216.549-12, M, I, 2006-12-12, 1985-01-01

Para isso requer o uso de colunas para organizar estes dados, ex.:

Nome	Endereço	Apelido	…
João Batista Queiroz	Travessa da Lapa 5034	Joãozinho	…

Também é importante utilizar nomes que realmente especifiquem que dado esta armazenado em cada coluna e nas tabelas, ex.: Coluna Nome da tabela Cliente, para armazenar o nome do cliente.

Obs. 1: Em algumas empresas também se adotam nomenclaturas para as tabelas e as colunas, ex.: T0001_CLIENTE, C0001_NOME, N0001_DDD, N0001_TELEFONE …

Ao criar as colunas, se deve pensar também no trabalho que será necessário para organizar estes dados nestas colunas, como no caso os “endereços”.:

1. Que se pode não ser dividido, tendo toda a informação em uma coluna “endereço”

2. Que se pode dividir nas colunas: logradouro, número, bairro, complemento, CEP, cidade, estado, país…

3. Que se pode dividir em uma forma mais detalhada: tipo de logradouro (rua, avenida, praça…), prefixo do logradouro (coronel, marechal, tenente, presidente, são, santa, santo, novo…), logradouro (Teixeira Martins, José, Maria), sufixo do logradouro (I, II, III…)…

O problema do detalhamento para deixar os dados bem organizados, é o trabalho que será necessário para organizar estes dados em muitos colunas ao ponto de ficar muito complexo e requerer um esforço demasiado em comparação à forma que estes dados serão utilizados pelos usuário.

Obs. 2: Também se frisar a existência de limites de colunas para as tabelas, como 1024 colunas no SQL Server (ref. http://msdn.microsoft.com/en-US/library/ms143432.aspx)

Exemplo de Deadlock no SQL Server

Deadlock acontece quando dois ou mais processos são impedidos de prosseguir pois um estar bloqueando o outro, como exemplo, duas pessoas estão querendo usar o mesmo telefone para ligar para números diferentes, enquanto uma delas não ceder sua vez, nenhuma irá conseguir ligar.

No nível de transações de banco de dados não é muito diferente, teremos duas tabelas em nosso banco de dados:

CREATE TABLE Pagamentos (ID INT PRIMARY KEY)
GO
CREATE TABLE Contas (ID INT PRIMARY KEY)
GO

Agora, imagine que um cliente, o senhor 52, irá abrir uma transação para inserir um registro na tabela de pagamentos:

BEGIN TRAN
GO
INSERT INTO Pagamentos VALUES(1)
GO

E ao mesmo tempo, outro cliente, o senhor 55, irá inserir um registro na tabela de contas:

BEGIN TRAN
GO
INSERT INTO Contas VALUES(1)
GO

Até ai, tudo bem, as transações estão abertas, o senhor 55 e o senhor 52 estão utilizando tabelas totalmente diferentes. Mas o senhor 55 lembra que além de inserir um registro na tabela de contas, ele precisa inserir um registro na tabela de pagamentos na mesma transação, assim ele terá que esperar que o senhor 52 termine a transação dele primeiro.

INSERT INTO Pagamentos VALUES(1)
GO

Por enquanto nada crítico, é só um cliente esperando o outro terminar.

Mas o senhor 52 também inventa de querer inserir um registro na tabela de Contas:

INSERT INTO Contas VALUES(1)
GO

Dai acontece o Deadlock, o senhor 52 esperando o senhor 55 terminar a transação dele, e o senhor 55 esperando o senhor 52. Como se trata de uma Deadlock bem simples, o SQL Server vai rapidamente escolher qual dos dois clientes tem a preferência, e por consequência definirá sua vítima, que é o cliente que terá que morrer para que aquele que possui preferência consiga terminar seu processo.

E assim, no meu cenário, senhor 52 foi brutalmente assassinado (a transação sofre um ROLLBACK pelo próprio SQL Server, de forma que todos os rastros da vítima sejam apagados):

Msg 1205, Level 13, State 47, Line 4
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

E em homenagem a todos os processos que diariamente são vítimas de Deadlock:

|| EDIT 2012-03-29
|| Via: http://pessoalex.wordpress.com/2012/03/29/exemplo-de-deadlock-no-sql-server/

Identificando consultas demoradas com o SQL Server Profiler

Antes de publicar os artigos sobre eventos com SMO (Trace e Events) que serão os temas a serem trabalhados nas próximas semanas, identifiquei a necessidade de mostrar o funcionamento do SQL Server Profiler, antes que alguém tente criar um SQL Server Profiler com o SMO.

O SQL Server Profiler é uma ferramenta normalmente utilizada por DBAs para identificar consultas lentas, diagnosticar problemas com deadlock e desempenho, e gerar cargas de consultas com o Database Engine Tuning Advisor.

Caso você nunca tenha falado de SQL Server Profiler, fique tranquilo, só pense que ele permite capturar eventos do SQL Server, como no caso, execução de consultas e procedures.

Para um exemplo bem simples, vamos executar o “Performance Tools\SQL Server Profiler”:

Ir em “File/New Trace…”:

Conectar a um servidor SQL Server:

E vamos nos deparar com a seguinte tela:

Especifique nesta tela um nome para seu Trace e o template a ser utilizado, no meu caso, o “TSQL_Duration”:

E você também pode definir um arquivo ou tabela para salvar o trace, exemplo:

Na aba “Events Selecion”, será possível definir filtros dos eventos a serem monitorados, assim como adicionar/remover eventos e especificar os dados a serem capturados de cada evento:

Prosseguindo com o botão “Run”, será possível ver em tempo real o que esta sendo executado no SQL Server e o tempo de execução de cada evento:

Ferramenta interessante, não? Então após se divertir com ela e conhecer um pouco mais de cada template, procure aprender como utilizar filtros e ordenação, criar seus próprios templates, assim como identificar recursos desta ferramenta para diagnosticar gargalos de desempenho e consultas lentas, e como trabalhar com ele junto com o Perfmon e o Database Engine Tuning Advisor.

Após utilizar o SQL Server Profiler, não se esqueça de parar o Trace, para não o deixar rodando sem necessidade no seu SQL Server.

Questão de SQL no POSCOMP 2011

E ai pessoas!

Estes dias, meu amigo Vitor Augusto (@VitorBitner) que realizou a pouco tempo o teste do POSCOMP, me encaminhou uma das questões que caíram no teste relacionadas á linguagem SQL, bem divertida por sinal.

Abaixo a questão:

51. Considere a relação a seguir, definida na linguagem SQL padrão.

CREATE TABLE EMPREGADO (
CODIGO NUMBER(4) PRIMARY KEY,
NOME VARCHAR2(10),
SALARIO NUMBER(7,2)
)

Considere também as consultas (C1, C2, C3 e C4) a seguir, expressas na linguagem SQL.

C1:

select NOME from EMPREGADO
where CODIGO in (
(select CODIGO from EMPREGADO)
minus
(select E1.CODIGO from EMPREGADO E1, EMPREGADO E2
where E1.SALARIO < E2.SALARIO)
)

Obs: o operador minus realiza a operação de subtração entre relações.

C2:

select NOME from EMPREGADO
where SALARIO = (select max(SALARIO) from EMPREGADO)

C3:

select NOME from EMPREGADO
where SALARIO >= all (select SALARIO from EMPREGADO)

C4:

select NOME from EMPREGADO
where CODIGO in (
select E1.CODIGO
from EMPREGADO E1, EMPREGADO E2
where E1.SALARIO > E2.SALARIO
)

Com relação às consultas, assinale a alternativa correta.

a) Apenas as consultas C2 e C3 são equivalentes.
b) Todas as consultas são equivalentes.
c) Apenas as consultas C1 e C3 são equivalentes.
d) Apenas as consultas C1 e C4 são equivalentes.
e) Apenas as consultas C1, C2 e C3 são equivalentes.

Visto que não se trata de T-SQL, e não possuímos dados para testes, vamos adequar o código simular o cenário no SQL Server:

CREATE TABLE EMPREGADO (
	CODIGO INT,
	NOME VARCHAR(10),
	SALARIO DECIMAL(7,2),
	CONSTRAINT PK$EMPREGADO PRIMARY KEY (CODIGO)
)
GO

INSERT INTO EMPREGADO
VALUES
(1, 'José', 100.00),
(2, 'Agnaldo', 120.00),
(3, 'Mariana', 120.00),
(4, 'Luiz', 150.00),
(5, 'Flávia', 150.00),
(6, 'Otavio', 150.00),
(7, 'Lucia', 150.00)
GO

--C1

SELECT NOME FROM EMPREGADO WHERE CODIGO in (
	(SELECT CODIGO FROM EMPREGADO)
	EXCEPT
	(SELECT E1.CODIGO FROM EMPREGADO E1, EMPREGADO E2 WHERE E1.SALARIO < E2.SALARIO)
)

--C2 | Resultado igual a C1

SELECT NOME FROM EMPREGADO WHERE SALARIO = (
	SELECT MAX(SALARIO) FROM EMPREGADO
)

--C3 | Resultado igual a C1, C2

SELECT NOME FROM EMPREGADO WHERE SALARIO >= ALL (
	SELECT SALARIO FROM EMPREGADO
)

--C4 | Resultado diferente

SELECT NOME FROM EMPREGADO
WHERE CODIGO IN (
	SELECT E1.CODIGO
	FROM EMPREGADO E1, EMPREGADO E2
	WHERE E1.SALARIO > E2.SALARIO
)

Agora que sabemos quais as respostas corretas (C1, C2, C3), vamos pensar um pouco sobre estas consultas na vida real:

A primeira consulta é muito complexa para obter um resultado simples, além de ser difícil ser otimizada (sim, este JOIN de uma tabela com ela mesma e o EXCEPT assustam).

A terceira consulta não é complexa, mas pode assustar pessoas que não conhecem SQL (ALL e subquery), por outro lado, também não oferece muitas alternativas para ser otimizada.

A segunda é simples e exige pouco esforço para ser otimizada (índice sobre a coluna salário e um include da coluna nome já resolve), logo poderíamos dizer que é a alternativa mais adequada para obter este tipo de resultado.

Fiquem a vontade de comentar ou enviar outras questões!

Referências:

Prova do POSCOMP 2011:
http://www.cops.uel.br/concursos/112_poscomp_2011/11201.PDF

Como recuperar registros duplicados com SQL

Olá pessoas, como a memória falha algumas vezes, ontem me deparei com uma consulta simples, mas demorei um pouco para lembrar como “identificar somente os registros com determinado valor duplicados com SQL”, assim preparei um artigo rápido para exemplificar um cenário e as soluções.

Para termos um problema para tratar, criarei um cenário com uma tabela e vários registros com Ids duplicados:

Para recuperar uma relação somente dos Ids sem contar os duplicados, podemos utilizar um DISTINCT ou um GROUP BY:

Com a ajuda da função COUNT, podemos identificar o número de ocorrências de cada Id:

E com a clausula HAVING, podemos filtrar os Ids por número de ocorrências:

Por fim, com um condicional IN, identificamos os registros com Ids duplicados ou que somente ocorrem uma vez:

De mesma forma que utilizamos a função COUNT neste cenário, podemos utilizar outras funções de agregação para estabelecer filtros na clausula HAVING.

Inserindo dados de stored procedured em tabelas

Bom dia pessoas,

Como não é a primeira vez que perguntam como inserir dados de uma stored procedured ou algum consulta resultante de um EXECUTE / EXEC em uma tabela no SQL Server, vamos aos exemplos.

Primeiro utilizando stored procedured:

DECLARE @T TABLE (
	Nome VARCHAR(MAX)
)

INSERT INTO @T
EXEC sp_helptext 'master.sys.tables'

SELECT * FROM @T

Em seguida, qualquer consulta por meio do comando EXEC:

DECLARE @CONSULTA VARCHAR(MAX)

SET @CONSULTA = 'SELECT object_id, name FROM master.sys.tables'

DECLARE @T TABLE (
	Codigo INT,
	Nome VARCHAR(250)
)

INSERT INTO @T
EXEC (@CONSULTA)

SELECT * FROM @T ORDER BY Nome

Também é possível fazer esta operação com linked server… mas… não precisa de tanto…

Backup no SQL Server, por onde começar?

E ai pessoas!

Normalmente eu evitava falar sobre a parte administrativa do SQL Server, visto eu na minha “inocência” considerava natural que bancos de dados em ambiente de produção tinham rotinas de manutenção e nestas rotinas, atividades regulares de backups. Mas o que julgo ser natural é diversas vezes desconsiderado e só lembrado quando a recuperação dos dados se torna irreversível (uma das situações conhecidas como “DBA HELL”).

Para ninguém dizer que backup é difícil, vamos realizar “manualmente” um backup e seguida demonstrar como restaurar este backup.

No SQL Server Management Studio, escolhemos um banco de dados qualquer:

Clicamos com o botão direito sobre o banco de dados, e em “Tasks” escolhemos o item “Back Up…”:

Na nova janela que irá aparecer, adicionaremos os destinos para o nosso backup, clicando no botão “Add”:

E em seguida informaremos o caminho destino do backup (recomendo utilizar a extensão “.bak” para no nome do arquivo):

Uma opção muito importante quando você quer somente uma cópia do banco de dados, mas não quer interferir em qualquer plano de manutenção com backups que a sua empresa possuir, selecione a propriedade “Copy Only Backup” (isso pode evitar momentos de stress quando for necessário que o DBA da sua empresa precise restaurar o banco de dados):

Agora basta clicar em OK e concluímos nosso backup!

E por fim o arquivo de backup que você pode levar para qualquer lugar, mas lembre de não deixar seus backups dispostos de forma insegura, pois qualquer pessoa que souber restaurar seu backup poderá utilizar estes dados para outros fins.

Backup realizado, e quando houver necessidade de recuperar dados que foram salvos no seu backup como faremos?

Para restaurar um banco de dados a partir de um backup, basta escolher nas “Tasks” o menu “Restore”, a opção “Database…”

Escolher a opção “From device” e em seguida clique no botão “…” na frente desta opção:

Adicione seu arquivo de backup pelo botão “Add”:

Feito isso aperte “OK” e selecione o backup que você deseja restaurar:

A partir deste passo, temos duas opções:

Caso você deseje criar um novo banco de dados a partir deste backup, nomeie seu banco de dados com um nome diferente em “To database” e em seguida clique em OK:

Caso você deseje sobrescrever (ou substituir) um banco de dados já existente, selecione o nome do banco de dados já existente em “To database” e na página “Options” selecione a opção “Overwrite the existing database (WITH REPLACE)” e em seguida clique em OK:

Obs.: Verifique também, se realmente quer restaurar os arquivos do banco de dados nos caminhos informados na grid de “Restore As”.

A partir destes exemplos, você estará preparado para realizar backups e restaurar bancos de dados. Nos próximos artigos tratarei algumas formas de automatização deste processo e algumas ações comuns para restaurar partes dos bancos de dados.