Novos tipos de dados do SQL 2008: DATETIME2

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

No último post, o primeiro da série que irá tratar dos novos tipos de dados no SQL Server 2008, falei sobre a tão esperada separação da data e hora nos novos tipos de dados DATA e TIME (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html). Neste post vou apresentar uma extensão do tipo de dado DATETIME: DATETIME2.

DATETIME2
O tipo de dado DATETIME2 proporciona um melhor controle na precisão da HORA, além de aumentar o intervalo de datas aceito. Veja nas tabelas a seguir a comparação entre DATETIME2 e DATETIME.

Como exemplo execute o script abaixo:

DECLARE @DataHora3 DATETIME2(3)
SET @DataHora3 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(3)] = @DataHora3

DECLARE @DataHora7 DATETIME2(7)
SET @DataHora7 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(7)] = @DataHora7

-- Resultado abaixo:

DATETIME2(3)
-----------------------
2004-02-27 16:14:00.123

DATETIME2(7)
---------------------------
2004-02-27 16:14:00.1234567

No próximo post vou mostrar o tipo de dado TATETIMEOFFSET.
Até lá,

Landry.

Novos tipos de dados do SQL 2008: Date e Time

Até a versão 2005 o SQL Server disponibilizava apenas dois tipos de dados para data e hora (DATETIME e SMALLDATETIME), armazenando a data sempre junto da hora. Armazenar a data e hora em conjunto gera algumas dificuldades quando a necessidade da aplicação é de apenas manipular a data. Vamos explorar algumas situações que geram dificuldade ao manipular a data junto da hora e depois veremos as novidades no SQL Server 2008.

O script abaixo criar uma tabela de Vendas contendo uma coluna DATETIME e depois inclui algumas linhas.

USE tempdb
go

CREATE TABLE Vendas (
IDVenda int not null,
DataVenda datetime not null,
Cliente char(4000) not null,
TotalVenda decimal(10,2))

-- Inclui duas linhas para utilizar no filtro WHERE das consultas
INSERT Vendas VALUES
(3,'20070303 12:00:00.000','Ana',320.00)

INSERT Vendas VALUES
(3,'20070303 00:00:00.000','Pedro',120.00)

-- Inclui 10.000 linhas
DECLARE @i int
SET @i = 1
WHILE @i <= 10000 BEGIN

INSERT Vendas VALUES
(@i,getdate()-@i,'Cliente ' + ltrim(str(@i)),10.00 + @i)

SET @i = @i + 1
END
go

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)
go

Para retornar todas as vendas de um determinado dia, não importando a hora, você terá certa dificuldade. Utilizando uma comparação simples de data, o SQL Server retornará apenas as vendas de meia noite (hora padrão), como podemos observar na consulta abaixo:

SELECT * FROM Vendas WHERE DataVenda = '20070303'

Uma opção para retornar todas as vendas de um dia é utilizar a função CONVERT, extraindo apenas a porção data da coluna DataVenda e comparando com o dia. O problema desta solução é utilizar função em coluna na cláusula WHERE, inviabilizando o uso de índice (Index Seek) gerando problema de desempenho. A solução é entrar com um intervalo de datas, evitando o uso de função em coluna. Para comparar o volume de I/O vamos utilizar SET STATISTICS IO ON e o plano de execução gráfico.

SET STATISTICS IO ON

SELECT * FROM Vendas
WHERE convert(char(8),DataVenda,112) = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 29
-- Plano de Execução: Index Scan

SELECT * FROM Vendas
WHERE DataVenda >= '20070303' and DataVenda < '20070304'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

O SQL Server 2008, finalmente, disponibiliza tipos de dados onde data e hora ficam separados, são eles DATE e TIME.

Vamos alterar a estrutura da tabela Vendas, criando uma nova coluna HoraVenda do tipo de dado TIME e depois iremos alterar a coluna DataVenda para o tipo de dados DATE.

DROP INDEX Vendas.ix_Vendas_DataVenda

-- Cria a coluna HoraVenda do tipo de dados TIME e
-- preenche com as horas existentes na coluna DataVenda
ALTER TABLE Vendas ADD HoraVenda time null
go
UPDATE Vendas SET HoraVenda = DataVenda

-- Altera a coluna DataVenda para o tipo de dados DATE
ALTER TABLE Vendas alter column DataVenda date not null

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)

Agora ficou mais fácil retornar as vendas de um determinado dia utilizando comparação simples, veja as estatísticas da consulta abaixo.

SELECT * FROM Vendas WHERE DataVenda = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

Até o próximo post.
Landry

Novos tipos de dados do SQL Server 2008

Vou iniciar uma seqüência de artigos sobre os novos tipos de dados no SQL Server 2008, segue abaixo uma breve descrição:

- DATE: armazena somente a data.
- TIME: armazena somente a hora.
- DATETIMEOFFSET: armazena data e hora obedecendo timezone.
- DATETIME2: similar ao DATETIME, porém com precisão variável.
- GEOMETRY: dados espaciais utilizando representação plana da Terra ("Flat Earth").
- GEOGRAPHY: dados espaciais utilizando representação redonda da Terra ("round earth").
- HIERARCHYID: representa hierarquias.

Os primeiros quatro tipos de dados (DATE, TIME, DATETIMEOFFSET, DATETIME2) são tipos regulares do SQL Server, já os três últimos foram desenvolvidos como UDT (User Defined Type) com .NET.

No próximo post vou falar dos dois primeiros tipos de dados: DATE e TIME.
Até lá, Landry.

Reduzindo o espaço ocupado em um Banco de Dados utilizando Vardecimal no SQL Server 2005 SP2

Neste artigo irei mostrar uma novidade no armazenamento do tipo de dados DECIMAL disponível a partir do Service Pack 2 do SQL Server 2005. Este novo recurso pode reduzir o tamanho da linha com consequente redução na ocupação do banco de dados, proporcionando melhor desempenho. Porém a adoção deste novo recurso deverá ser considerado com muito cuidado como veremos neste artigo.

Durante muito tempo o recurso de armazenamento variável ficou restrito aos tipos de dados string (varchar, nvarchar, text e ntext) e binário (varbinary e image), restando aos tipos de dados numéricos o armazenamento em tamanho fixo. O grande benefício do armazenamento variável é a redução do tamanho total da linha proporcionando uma melhor ocupação da página de dados (Data Page 8Kb), com conseqüente redução de I/O e melhora no desempenho.
Se uma coluna NomeCliente for do tipo de dado Varchar(100) o SQL Server só irá ocupar um byte por caractere incluído pelo usuário mais dois bytes do ponteiro que irá indicar o término da coluna dentro da linha. Por exemplo, se o usuário incluir “Maria Lucia” na coluna acima o SQL Server irá alocar 11 bytes referentes a cada caractere do nome e 2 bytes para o ponteiro, somando 13 bytes no total!

Já uma coluna decimal é armazenada na área da linha destinada a colunas de tamanho fixo, ocupando espaço de acordo com a tabela abaixo:

Observando uma coluna Decimal é muito comum termos uma precisão muito maior do que utilizamos na maior parte dos dados, sendo claro o benefício do armazenamento variável na redução do espaço ocupado e conseqüente melhora no desempenho.
Para comprovar a redução do espaço ocupado vamos criar um banco de dados contendo uma tabela com uma coluna Decimal, utilize o script abaixo.

Create database DBvardecimal
alter database DBvardecimal set recovery simple
go
use DBvardecimal
go

Create table TBdecimal (

col1 int identity(1,1),col2 char(500),col3 decimal(22,6))

declare @cont int,@val char(500)
set @cont = 1
while @cont <= 100000 begin

set @val = 'Linha - ' + convert(varchar(20),@cont)

insert TBdecimal (col2,col3) values (@val,12.2)

set @cont = @cont + 1

end

go

Foram incluídas 100.000 linhas na tabela, utilize o script abaixo para retornar o espaço ocupado pela tabela após a inclusão das linhas.

sp_spaceused 'TBdecimal'
-- Linhas: 100000
-- Reservado: 61640 KB
-- Dados: 61600 KB

As 100.000 linhas da tabela estão ocupando 61.600 KB em 7.700 páginas de dados (Data Pages 8KB). Agora vamos habilitar o armazenamento variável para o tipo de dado Decimal utilizando o script abaixo.

EXEC sp_db_vardecimal_storage_format 'DBvardecimal', 'ON'
EXEC sp_tableoption 'TBdecimal', 'vardecimal storage format', 1

O primeiro EXEC habilita o armazenamento Vardecimal no Banco de Dados DBvardecimal utilizando a Stored Procedure sp_db_vardecimal_storage_format e o segundo habilita o armazenamento Vardecimal na tabela TBdecimal. Se a Stored Procedure sp_db_vardecimal_storage_format for executada sem parâmetro ela retorna a lista dos Bancos de Dados na Instância indicando indicando se o armazenamento Vardecimal está habilitado ou não.

Basta rodar novamente a Stored Procedure sp_spaceused e comparar os resultados, veja a diferença na tabela abaixo.

Para determinar se a tabela foi habilitada para o armazenamento Vardecimal utilize a consulta abaixo.

SELECT name, object_id, type_desc FROM sys.objects
WHERE OBJECTPROPERTY(object_id,'TableHasVarDecimalStorageFormat') = 1

A estrutura da linha foi alterada para comportar o tamanho variável no tipo de dados Decimal, por isso o Relational Engine das edições anteriores ao Service Pack 2 não conseguem manipular Bancos de Dados com Vardecimal habilitado.

Se você tentar fazer um Attach pelo Management Studio de um Banco de Dados com Vardecimal habilitado em um SQL Server 2005 SP1 irá receber a seguinte mensagem de erro: “An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

No log da Instância irá aparecer a mensagem: “The database ‘DBvardecimal’ cannot be opened because it is version 612. This server supports version 611 and earlier. A downgrade path is not supported”.

Já no Restore a mensagem de erro será: “The database was backed up on a Server running version 9.00.3042. That version is incompatible with this server, which is running version 9.00.2047…”.

Muito cuidado! Uma vez habilitado o armazenamento Vardecimal não poderemos mais retornar ao Service Pack 1, por isso testem bastante as aplicações antes de adotar o armazenamento Vardecimal.

Até a próximo post,

Landry.