Armazenando dados decimais como comprimento variável
Os tipos de dados decimal e numeric normalmente são armazenados no disco como dados de comprimento fixo. O tipo de dados numeric é funcionalmente equivalente ao tipo de dados decimal. No SQL Server 2005 Service Pack 2 (SP2) e nas versões posteriores, os tipos de dados decimal e numeric podem ser armazenados como uma coluna de comprimento variável usando o formato de armazenamento vardecimal. Esse formato de armazenamento está disponível somente nas edições Enterprise, Developer e Evaluation do SQL Server.
.gif "Observação") Observação |
|---|
Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam. Em vez disso, use a compactação ROW e PAGE. Para obter mais informações, consulte Criando tabelas e índices compactados. |
| Observação |
|---|
Vardecimal é um formato de armazenamento, não um tipo de dados. |
O formato de armazenamento vardecimal pode reduzir consideravelmente o tamanho do armazenamento dos dados, mas aumenta um pouco a sobrecarga da CPU. O formato de armazenamento vardecimal é aplicado no nível da tabela. Ou seja, não é possível armazenar algumas colunas decimal de uma tabela no formato de armazenamento vardecimal e não de outras. Os dados decimal continuam sendo um tipo de dados exato.
Quando o formato de armazenamento vardecimal for habilitado em uma tabela, os dados decimal serão armazenados nas páginas de dados, índices e logs do formato de armazenamento vardecimal. A alteração do formato de armazenamento é uma operação offline. A tabela que está sendo modificada fica bloqueada para uso exclusivo durante a operação e fica indisponível para acesso de leitura ou gravação simultâneo.
Implementação do formato de armazenamento vardecimal
Dependendo da precisão da coluna (1 a 38), o armazenamento de um valor decimal consome entre 5 e 17 bytes. Quando uma tabela não está usando o formato de armazenamento vardecimal, cada entrada na tabela consome o mesmo número de bytes de cada coluna decimal definida, mesmo que o valor de uma linha seja 0, NULL ou algum outro valor que possa ser expresso em um número menor de bytes, como o número 3. Quando a tabela é armazenada no formato de armazenamento vardecimal, as colunas decimal de cada linha consomem apenas o espaço exigido para conter o número fornecido, mais 2 bytes de sobrecarga. O resultado sempre ficará entre 5 e 20 bytes. Isso inclui os 2 bytes de sobrecarga para armazenar o deslocamento do valor. No entanto, valores nulos e zeros são tratados especialmente e ocupam somente 2 bytes.
Se a tabela não tiver colunas de comprimento variável, haverá uma sobrecarga adicional de 2 bytes por linha para armazenar o número de colunas de comprimento variável. Se a tabela já possuir pelo menos uma coluna de comprimento variável, não haverá sobrecarga adicional.
A tabela a seguir mostra o número de bytes necessário para armazenar dados decimais no formato fixo regular e o número máximo de bytes exigido para armazenar dados decimais no formato de armazenamento vardecimal. Quando uma tabela é armazenada no formato fixo, é usado o valor listado para cada linha. Se a tabela for armazenada no formato de armazenamento vardecimal, o número de bytes exigido por muitos valores será menor do que o listado.
Precisão da coluna |
Tamanho de decimal fixo original (bytes) |
Área máxima de dados vardecimais (bytes) |
Sobrecarga para armazenar deslocamento (bytes) |
Armazenamento vardecimal máximo usado (bytes) |
|---|---|---|---|---|
1-3 |
5 |
3 |
2 |
5 |
4-6 |
5 |
4 |
2 |
6 |
7-9 |
5 |
5 |
2 |
7 |
10-12 |
9 |
6 |
2 |
8 |
13-15 |
9 |
8 |
2 |
10 |
16-18 |
9 |
9 |
2 |
11 |
19 |
9 |
10 |
2 |
12 |
20-21 |
13 |
10 |
2 |
12 |
22-24 |
13 |
11 |
2 |
13 |
25-27 |
13 |
13 |
2 |
15 |
28 |
13 |
14 |
2 |
16 |
29-30 |
17 |
14 |
2 |
16 |
31-33 |
17 |
15 |
2 |
17 |
34-36 |
17 |
16 |
2 |
18 |
37-38 |
17 |
18 |
2 |
20 |
Usando o formato de armazenamento vardecimal
Você pode usar o formato de armazenamento vardecimal quando estiver tentando resolver os seguintes problemas:
Falta espaço em disco.
O acesso ao disco (E/S) gera um afunilamento no desempenho do sistema.
É necessário ter um alto nível de precisão para alguns dados, mesmo que muitos dos valores sejam pequenos, NULL ou 0 (como uma tabela em um data warehouse com uma coluna decimal que possui muitas linhas contendo valores 0 ou inteiro).
A alteração do formato de armazenamento da tabela exige o armazenamento da tabela (índice clusterizado ou heap) para ser recriada. Se o índice clusterizado de uma tabela contiver uma coluna decimal, todos os índices não clusterizados também deverão ser recriados porque contêm os valores de chave clusterizada. Se o índice clusterizado não tiver uma coluna decimal, os índices não clusterizados serão recriados se contiverem uma coluna decimal. Se a tabela for um heap (ou seja, não tiver um índice clusterizado), todos os índices não clusterizados terão de ser recriados para apontar para os novos locais de linha no heap.
A recriação da tabela para habilitar ou desabilitar o formato de armazenamento vardecimal pode exigir mais do que o dobro do espaço de armazenamento total da tabela original. Se a tabela não contiver colunas decimal ou numeric, a habilitação do formato de armazenamento vardecimal será apenas uma operação de metadados. Haverá muita atividade de log quando a tabela e os índices forem recriados.
O formato de armazenamento vardecimal pode ser usado para tabelas somente leitura e tabelas para leitura/gravação. O aumento do armazenamento deve ser equilibrado com o uso adicional de CPU necessário para converter o formato de armazenamento das linhas sempre que elas forem acessadas. Além disso, a gravação em uma tabela que usa o formato de armazenamento vardecimal pode reduzir o desempenho devido a um número maior de divisões de página.
Limitações do formato de armazenamento vardecimal
As seguintes restrições são aplicadas:
Requer SQL Server 2005 SP2 ou versões posteriores.
O formato de armazenamento vardecimal não pode ser habilitado nos bancos de dados do sistema: mestre, modelo, msdb, tempdb ou distribuição. Quando uma consulta classifica dados armazenados em formato de armazenamento vardecimal, eles são classificados no tempdb em um estado decimal fixo. Geralmente, os dados irão exigir um espaço significativamente maior no tempdb do que o espaço ocupado pela tabela de origem do formato de armazenamento vardecimal no banco de dados de origem.
O formato de armazenamento vardecimal não pode ser aplicado a exibições, exibições indexadas, índices XML e índices de texto completo. No entanto, as tabelas subjacentes a esses objetos podem usar o formato de armazenamento vardecimal.
Tabelas internas, como as tabelas de metadados e notificação, não podem usar o formato de armazenamento vardecimal.
Funções com valor de tabela não podem usar o formato de armazenamento vardecimal.
A coluna numeric armazenada em uma tabela no formato de armazenamento vardecimal também não pode ser criptografada.
Não há suporte para partições heterogêneas (ou seja, partições com formato decimal fixo e de armazenamento vardecimal).
Novas tabelas criadas a partir da tabela com formato de armazenamento vardecimal usando a sintaxe Transact-SQL SELECT … INTO… não herdam o formato de armazenamento vardecimal.
Não é possível alterar os estados de formato de armazenamento vardecimal de bancos de dados habilitados para espelhamento de banco de dados. É necessário remover o espelhamento de banco de dados para habilitar o formato de armazenamento vardecimal no banco de dados. No entanto, não é necessário remover o espelhamento de banco de dados quando tabelas individuais são habilitadas ou desabilitadas para o formato de armazenamento vardecimal.
O SQL Server deve poder garantir que todas as atualizações tenham êxito e que a tabela possa sempre retornar ao formato decimal fixo. Portanto, uma tabela não pode ser alterada para o formato de armazenamento vardecimal se a sobrecarga adicional fizer com que uma linha existente exceda 8060 bytes ou o valor de um índice existente exceda 900 bytes.
ObservaçãoA diferença entre o formato de armazenamento vardecimal e o armazenamento de texto variável (varchar) é que o SQL Server permite a criação de uma linha que poderá exceder 8060 bytes se todas as colunas variáveis não ultrapassarem o tamanho máximo. O SQL Server impõe o limite de 8060 bytes para inserção ou atualização de texto. O SQL Server não permite a criação de um conjunto de colunas decimais que possa exceder o limite de 8060 bytes para uma linha. Esse limite é imposto quando o formato de tabela é alterado para armazenamento vardecimal.
Durante a transferência de um banco de dados usando o método de desanexar e anexar do Assistente para Copiar Banco de Dados, haverá falha na operação de anexação se o Mecanismo de Banco de Dados de destino não for SQL Server 2005 SP2 ou versão posterior. O método do SQL Server Management Objects criará o banco de dados e as tabelas sem usar o formato de armazenamento vardecimal. O banco de dados e as tabelas poderão ser alterados para o formato vardecimal depois da transferência, caso o Mecanismo de Banco de Dados seja SQL Server 2005 SP2 ou versão posterior.
Backup e recuperação, espelhamento de banco de dados, sp_attach_db e envio de logs
Backup e recuperação, espelhamento de banco de dados, sp_attach_db e envio de logs funcionam corretamente com o formato de armazenamento vardecimal; no entanto, para incluir um banco de dados que usa o formato de armazenamento vardecimal, cada instância do SQL Server deve ser atualizada para o SQL Server 2005 SP2, no mínimo. Por exemplo, não é possível restaurar o backup de log de um banco de dados habilitado para o formato de armazenamento vardecimal em um banco de dados que não está habilitado, ou fazer o espelhamento de um banco de dados habilitado para o formato de armazenamento vardecimal em um banco de dados que não está habilitado, nem anexar um banco de dados habilitado para o formato de armazenamento vardecimal de um SQL Server 2005 SP2 em uma versão anterior do SQL Server. Se você restaurar um backup completo de um banco de dados habilitado para o formato de armazenamento vardecimal para um banco de dados que não está habilitado para o formato de armazenamento vardecimal, ele será habilitado para esse formato.
Quando uma tabela é alterada para o formato de armazenamento vardecimal, a cadeia de backup de logs continua válida e o banco de dados pode ser restaurado aplicando o último backup completo mais uma cadeia de logs válida. Para evitar que a criação de backups seja inválida, altere o banco de dados para o modelo de recuperação simples antes de modificar qualquer tabela para remover o formato de armazenamento vardecimal. Depois de remover o formato de armazenamento vardecimal das tabelas, crie um backup de banco de dados completo.
Usando o formato de armazenamento vardecimal com espelhamento de banco de dados
Os procedimentos a seguir apresentam as etapas para usar o formato de armazenamento vardecimal com espelhamento de banco de dados.
Para usar o formato de armazenamento vardecimal com espelhamento de banco de dados
A instância principal e o parceiro do espelho devem estar, no mínimo, na versão SQL Server 2005 SP2.
Se você estiver usando o espelhamento de banco de dados, remova-o e exclua o parceiro do espelho. Para obter mais informações, consulte Como remover espelhamento de banco de dados (Transact-SQL).
Habilite o formato de armazenamento vardecimal no banco de dados principal (se o banco de dados estiver na versão SQL Server 2005) e confirme se o banco de dados principal está usando o modelo de recuperação completa.
Estabeleça o espelhamento de banco de dados usando backups completos e de logs do banco de dados principal. Para obter mais informações, consulte Como estabelecer uma sessão de espelhamento de banco de dados usando a Autenticação do Windows (Transact-SQL).
Modifique tabelas individuais para usar o formato de armazenamento vardecimal.
| Observação |
|---|
Não é necessário remover o espelhamento de banco de dados para alterar o formato de armazenamento de tabelas individuais. |
Para remover o formato de armazenamento vardecimal
Modifique as tabelas do banco de dados principal para remover o formato de armazenamento vardecimal.
Remova o espelhamento de banco de dados.
Configure o banco de dados principal com o modo de recuperação simples. Isso separa a cadeia de logs.
Se o banco de dados estiver na versão SQL Server 2005, desabilite o formato de armazenamento vardecimal no banco de dados principal.
Descarte o banco de dados do parceiro do espelho.
Altere o banco de dados principal para o modo de recuperação completa.
Faça backup do banco de dados principal e restabeleça o espelhamento de banco de dados.
Efeitos do formato de armazenamento vardecimal em operações de replicação
A replicação funcionará como sempre nas tabelas que usam o formato de armazenamento vardecimal, com algumas considerações:
Os tipos de dados decimal armazenados no formato de armazenamento vardecimal são convertidos no formato decimal fixo para transferência durante a replicação. O banco de dados de distribuição não pode ser habilitado para o formato de armazenamento vardecimal. Portanto, os dados não serão armazenados no formato de armazenamento vardecimal se estiverem armazenados em tabelas de replicação no banco de dados de distribuição. No assinante, os registros do log são aplicados normalmente.
Uma tabela no formato de armazenamento vardecimal pode ser replicada para uma tabela de formato decimal fixo e uma tabela de formato decimal fixo pode ser replicada para uma tabela no formato de armazenamento vardecimal.
O processo de criação de tabela relacionado a uma nova assinatura não cria tabelas usando o formato de armazenamento vardecimal. Isso possibilita a replicação, independentemente do nível do service pack do Mecanismo de Banco de Dados ou do estado habilitado do formato de armazenamento vardecimal do banco de dados de assinatura. A tabela de assinatura poderá ser habilitada para o formato de armazenamento vardecimal no assinante depois que a tabela for criada ou pela modificação dos scripts de criação antes de sua aplicação.
A tabela a seguir descreve os requisitos de script para vários assinantes.
Assinante |
Script |
|---|---|
SQL Server 2000 ou SQL Server versão 7.0 |
Podem ser usados scripts para criação de tabela sem modificação. |
SQL Server 2005, o banco de dados não está marcado para o formato de armazenamento vardecimal. |
Podem ser usados scripts para criação de tabela sem modificação. |
SQL Server 2005, o banco de dados está marcado para o formato de armazenamento vardecimal, mas você não deseja habilitar o formato de armazenamento vardecimal para a tabela de assinantes. |
Podem ser usados scripts para criação de tabela sem modificação. |
SQL Server 2005, o banco de dados está marcado para o formato de armazenamento vardecimal e você deseja habilitar o formato de armazenamento vardecimal para a tabela de assinantes do SQL Server 2005. |
Os scripts para criação de tabela podem ser modificados para habilitar o formato de armazenamento vardecimal no banco de dados e nas tabelas. Ou o banco de dados e as tabelas de assinantes podem ser habilitados usando os procedimentos armazenados descritos na seção “Habilitando o formato de armazenamento vardecimal” a seguir. |
Considerações adicionais
Veja a seguir uma lista de considerações adicionais que você não deve ignorar ao trabalhar com o formato de armazenamento vardecimal:
O formato de armazenamento vardecimal não afeta as operações de importação e exportação em massa (bcp).
A função DATALENGTH não detecta o formato de armazenamento vardecimal e retorna o número de bytes que seria armazenado no formato decimal fixo.
Em raras ocasiões, o formato de armazenamento vardecimal impedirá que o SQL Server use um plano de consulta mais adequado para dados decimais fixos.
O formato de armazenamento vardecimal pode ser usado com qualquer nível de compatibilidade de banco de dados.
Se a tabela não tiver colunas do tipo de dados decimal ou numeric quando o tipo sp_tableoption for executado, os metadados da tabela serão alterados para indicar que a tabela usa o formato de armazenamento vardecimal. Quando novas colunas decimal forem adicionadas posteriormente, elas serão armazenadas no formato de armazenamento vardecimal. Não são necessárias técnicas especiais para adicionar ou remover colunas de uma tabela que está usando o formato de armazenamento vardecimal.
Habilitando o formato de armazenamento vardecimal
Para habilitar ou alterar o formato de armazenamento vardecimal, são necessárias as seguintes permissões:
Para habilitar o formato de armazenamento vardecimal em um banco de dados, é necessário ALTER DATABASE no servidor.
Para habilitar uma tabela para o formato de armazenamento vardecimal, é necessária a permissão ALTER na tabela.
Antes de habilitar o formato de armazenamento vardecimal, verifique primeiro se a tabela será reduzida quando esse formato for habilitado. Quando a precisão da coluna definida for necessária na maioria das linhas, a sobrecarga associada ao formato de armazenamento vardecimal poderá ser maior que a economia e resultar em uma tabela maior. Para calcular a redução de tamanho de linha antes de modificar a tabela, use o procedimento armazenado sp_estimated_rowsize_reduction_for_vardecimal. Se você decidir alterar o formato de armazenamento da tabela, habilite o banco de dados para o formato de armazenamento vardecimal e, em seguida, habilite as tabelas individuais para esse formato. Os bancos de dados do SQL Server 2008 não precisam ser habilitados para o formato de armazenamento vardecimal.
Em um banco de dados SQL Server 2008, você pode habilitar o formato de armazenamento vardecimal para tipos de dados decimal usando procedimentos armazenados ou o SQL Server Management Studio:
Execute sp_db_vardecimal_storage_format para habilitar o formato de armazenamento vardecimal no banco de dados (se a instância do SQL Server for SQL Server 2005, SP 2) e, em seguida, execute sp_tableoption para habilitar o formato de armazenamento vardecimal nas tabelas apropriadas.
No Management Studio, use a Página de Opções de Propriedades do Banco de Dados para habilitar o formato de armazenamento vardecimal no banco de dados. Use sp_tableoption para alterar uma tabela para o formato de armazenamento vardecimal.
| Observação |
|---|
A partir do SQL Server 2008, todos os bancos de dados são habilitados para o formato de armazenamento vardecimal. |
Identificando tabelas no formato de armazenamento vardecimal
Para determinar quais tabelas de um banco de dados estão usando o formato de armazenamento vardecimal, use a função OBJECTPROPERTY e pesquise a propriedade TableHasVarDecimalStorageFormat.
O exemplo a seguir retornará 1 se a tabela Production.WorkOrderRouting estiver usando o formato de armazenamento vardecimal, caso contrário, retornará 0.
USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'),
'TableHasVarDecimalStorageFormat') ;
GO
O exemplo a seguir pesquisa no banco de dados AdventureWorks todas as tabelas que usam o formato de armazenamento vardecimal.
USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects
WHERE OBJECTPROPERTY(object_id,
N'TableHasVarDecimalStorageFormat') = 1 ;
GO
Problemas ao remover o formato de armazenamento vardecimal
Para remover o formato de armazenamento vardecimal de uma tabela, ela deve ser recriada no formato decimal fixo. Isso pode aumentar significativamente o espaço em disco usado pela tabela. Se não houver espaço em disco suficiente, ocorrerá falha na operação. Nesse caso, para desabilitar o formato de armazenamento vardecimal, certifique-se de que haja espaço em disco suficiente disponível para o SQL Server. A operação de expansão também exige espaço temporário para armazenar os dados nos formatos vardecimal e regular. Se a tabela expandida couber no espaço em disco disponível após a expansão, mas ocorrer falha na expansão apenas devido à falta espaço em disco temporário, será possível expandir os dados de maneira incremental copiando linhas da tabela a uma nova tabela não expandida.
Para remover o formato de armazenamento vardecimal de um banco de dados imediatamente após ele ter sido modificado, descarte o banco de dados e, em seguida, restaure-o a partir de um backup feito antes do formato de armazenamento vardecimal ter sido habilitado.
Ao mover um banco de dados de uma edição do SQL Server 2005 Enterprise, Developer ou Evaluation para outra edição ou para uma versão anterior do SQL Server, abra primeiro o banco de dados usando uma das edições exigidas, remova o formato de armazenamento vardecimal e migre o banco de dados. Sempre ocorrerá falha ao anexar um banco de dados que contém o formato de armazenamento vardecimal a um servidor inadequado.