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.