Almacenar datos decimales como longitud variable

Artículo
12/15/2008

Nuevo: 12 de diciembre de 2006

Los tipos de datos decimal y numeric normalmente se almacenan en el disco como datos de longitud fija. El tipo de datos numeric equivale funcionalmente al tipo de datos decimal. En el Service Pack 2 (SP2) SQL Server 2005 y versiones posteriores, los tipos de datos decimal y numeric se pueden almacenar como una columna de longitud variable mediante el formato de almacenamiento vardecimal. El formato de almacenamiento vardecimal sólo está disponible en las ediciones Enterprise, Developer y Evaluation de SQL Server 2005.

[!NOTA] Vardecimal es un formato de almacenamiento y no un tipo de datos.

El formato de almacenamiento vardecimal puede reducir considerablemente el tamaño de almacenamiento de los datos, pero aumenta ligeramente la sobrecarga de la CPU. El formato de almacenamiento vardecimal se aplica en el nivel de tabla. Esto significa que no se pueden almacenar algunas columnas decimal concretas de una tabla en el formato de almacenamiento vardecimal y no otras. Los datos decimal permanecen en un tipo de datos exacto.

Cuando el formato de almacenamiento vardecimal se habilita en una tabla, los datos decimal se almacenan en las páginas de datos, el índice y el registro en formato de almacenamiento vardecimal. El cambio del formato de almacenamiento es una operación que se debe realizar sin conexión. La tabla que se está modificando se bloquea exclusivamente durante toda la operación y no está disponible para la lectura simultánea ni el acceso de escritura.

Implementación del formato de almacenamiento vardecimal

Según la precisión de la columna (1 a 38), el almacenamiento de un valor decimal ocupa entre 5 y 17 bytes. Cuando una tabla no usa el formato de almacenamiento vardecimal, cada entrada de la tabla ocupa el mismo número de bytes para cada columna decimal definida, aunque el valor de una fila sea 0, NULL o el mismo valor que se podría expresar en una cantidad menor de bytes, como el número 3. Cuando una tabla se almacena en el formato de almacenamiento vardecimal, las columnas decimal de cada fila sólo ocupan el espacio necesario para contener el número proporcionado, más 2 bytes de sobrecarga. El resultado obtenido oscila siempre entre 5 y 20 bytes. El resultado incluye los 2 bytes de sobrecarga necesarios para almacenar el desplazamiento al valor. Sin embargo, los valores nulos y los ceros se tratan de forma especial y sólo ocupan 2 bytes.

Si la tabla no tiene columnas de longitud variable, se agrega una sobrecarga adicional de 2 bytes por fila para almacenar el número de columnas de longitud variable. Si la tabla ya tiene como mínimo una columna de longitud variable, no existe sobrecarga adicional.

La siguiente tabla muestra los bytes necesarios para almacenar los datos decimales en el formato fijo normal y el número máximo de bytes necesarios para almacenar los datos decimales en el formato de almacenamiento vardecimal. Cuando una tabla se almacena en el formato fijo, el valor indicado se utilizará para cada fila. Cuando una tabla se almacena en el formato de almacenamiento vardecimal, muchos valores necesitarán menos bytes de los indicados.

Precisión de la columna	Tamaño decimal fijo original (bytes)	Área máxima de datos vardecimales (bytes)	Sobrecarga para almacenar el desplazamiento (bytes)	Almacenamiento vardecimal máximo (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

Usar el formato de almacenamiento vardecimal

El formato de almacenamiento vardecimal se puede usar para intentar resolver los siguientes problemas:

El espacio en disco es insuficiente.
El acceso al disco (E/S) constituye un cuello de botella para el rendimiento del sistema.
Para algunos datos puede disponer de un nivel de precisión alto, aunque la mayoría de valores son pequeños, NULL o 0 (como una tabla en un almacenamiento de datos con una columna decimal que tiene muchas filas que contienen 0 o valores enteros).

Para cambiar el formato de almacenamiento de una tabla es preciso volver a crear el almacenamiento de la tabla (índice agrupado o montón). Si el índice agrupado de una tabla contiene una columna decimal, todos los índices no agrupados también se deben crear de nuevo porque también incluyen los valores clave agrupados. Si el índice agrupado no tiene una columna decimal, los índices no agrupados se vuelve a crear si contienen una columna decimal. Si la tabla es un montón (es decir, no tiene un índice agrupado), todos los índices no agrupados se deben volver a crear para apuntar a las nuevas ubicaciones de fila en el montón.

Para el proceso de volver a crear una tabla para habilitar o inhabilitar el formato de almacenamiento vardecimal, puede ser necesario el doble del espacio de almacenamiento total de la tabla original. Si la tabla no contiene columnas decimal o numeric, la habilitación del formato de almacenamiento vardecimal es únicamente una operación de metadatos. La actividad del registro es muy elevada cuando las tablas y los índices se vuelven a crear.

El formato de almacenamiento vardecimal se puede utilizar para las tablas de sólo lectura y de lectura/escritura. El ahorro de almacenamiento se debe contrarrestar con el uso adicional de la CPU necesario para convertir el formato de almacenamiento de las filas cada vez que se obtiene acceso a ellas. Además, la escritura en una tabla que utiliza el formato de almacenamiento vardecimal puede afectar al rendimiento debido al mayor número de divisiones de página.

Limitaciones del formato de almacenamiento vardecimal

Se aplican las siguientes restricciones:

Requiere el SP2 de SQL Server 2005 o versiones posteriores.
El formato de almacenamiento vardecimal no se puede habilitar en las base de datos de sistema: master, model, msdb, tempdb o distribution. Cuando una consulta ordena datos que están almacenados en formato de almacenamiento vardecimal, los datos se ordenan en tempdb en un estado decimal fijo. Normalmente, los datos necesitarán mucho más espacio en tempdb que el espacio ocupado por la tabla de origen de formato de almacenamiento vardecimal en la base de datos de origen.
El formato de almacenamiento vardecimal no se puede aplicar a las vistas, las vistas indizadas, los índices XML y los índices de texto. Sin embargo, las tablas subyacentes de estos objetos pueden utilizar el formato de almacenamiento vardecimal.
Las tablas internas, como las tablas de metadatos y de notificaciones, no pueden utilizar el formato de almacenamiento vardecimal.
Las funciones de los valores de tabla no pueden utilizar el formato de almacenamiento vardecimal.
La columna numeric que se almacena en una tabla de formato de almacenamiento vardecimal tampoco se puede cifrar.
Las particiones heterogéneas (es decir, particiones de formato decimal fijo y de formato de almacenamiento vardecimal) no se admiten.
Las nuevas tablas que se crean a partir de una tabla de formato de almacenamiento vardecimal mediante la sintaxis SELECT … INTO… de Transact-SQL no heredan el formato de almacenamiento vardecimal.
Las bases de datos que están habilitadas para la creación de reflejo de la base de datos no pueden cambiar los estados de su formato de almacenamiento vardecimal. Para habilitar el formato de almacenamiento vardecimal en una base de datos es preciso quitar la creación de reflejo de la base de datos. Sin embargo, no es necesario quitar la creación de reflejo de la base de datos cuando se habilitan o inhabilitan tablas individuales para el formato de almacenamiento vardecimal.
SQL Server debe poder garantizar que todas las actualizaciones serán correctas y que la tabla siempre puede recuperar el formato decimal fijo. Por lo tanto, una tabla no se puede cambiar al formato de almacenamiento vardecimal si la sobrecarga adicional hace que una fila existente supere los 8060 bytes o un valor de índice existente los 900 bytes.

[!NOTA] El formato de almacenamiento vardecimal se diferencia del almacenamiento de texto variable (varchar) en que SQL Server permite crear una fila que puede exceder los 8060 bytes si todas las columnas variables se establecen en su tamaño máximo. SQL Server fuerza el límite de los 8060 bytes cuando se insertan o actualizan datos de texto. SQL Server no permite crear conjuntos de columnas decimales que puedan sobrepasar el límite de 8060 bytes para una fila. El límite de 8060 bytes se fuerza cuando el formato de la tabla se cambia al almacenamiento vardecimal.
Cuando se transfiere una base de datos mediante el método de separar y adjuntar del Asistente para copiar bases de datos, la operación para adjuntar no será correcta si el Database Engine (Motor de base de datos) de destino no es el SP2 de SQL Server 2005 o posterior. El método Objetos de administración de SQL Server creará la base de datos y las tablas nuevas sin utilizar el formato de almacenamiento vardecimal. La base de datos y las tablas se pueden cambiar al formato vardecimal después de la transferencia si el Database Engine (Motor de base de datos) es el SP2 de SQL Server 2005 o posterior.

Copia de seguridad y recuperación, creación de reflejo de la base de datos, sp_attach_db y trasvase de registros

La copia de seguridad y la recuperación, la creación de reflejo de la base de datos, sp_attach_db y el trasvase de registros funcionan correctamente con el formato de almacenamiento vardecimal. Sin embargo, para incluir una base de datos que utilice el formato de almacenamiento vardecimal, cada instancia de SQL Server se debe actualizar al SP2 de SQL Server 2005 como mínimo. Por ejemplo, no se puede restaurar una copia de seguridad del registro de una base de datos habilitada para el formato de almacenamiento vardecimal en una base de datos que no está habilitada, ni reflejar desde una base de datos habilitada para el formato de almacenamiento vardecimal en una base de datos que no está habilitada, ni adjuntar una base de datos habilitada para el formato de almacenamiento vardecimal del SP2 de SQL Server 2005 a una versión anterior de SQL Server. Si se restaura una copia de seguridad completa de una base de datos habilitada para el formato de almacenamiento vardecimal en una base de datos que no está habilitada para el formato de almacenamiento vardecimal, la base de datos se habilitará para el formato de almacenamiento vardecimal.

Cuando una tabla se cambia al formato de almacenamiento vardecimal, la cadena de copias de seguridad del registro tiene validez y la base de datos se puede restaurar aplicando la última copia de seguridad completa y una cadena de registros válida. Para evitar la creación de copias de seguridad que no sean válidas, cambie la base de datos al modelo de recuperación simple antes de modificar cualquier tabla para quitar el formato de almacenamiento vardecimal. Después de quitar el formato de almacenamiento vardecimal de cualquier tabla, deberá crear una copia de seguridad completa de la base de datos.

Usar el formato de almacenamiento vardecimal con la creación de reflejo de la base de datos

Los procedimientos siguientes describen los pasos para utilizar el formato de almacenamiento vardecimal con la creación de reflejo de la base de datos.

Para utilizar el formato de almacenamiento vardecimal con la creación de reflejo de la base de datos

Actualice la instancia principal y la del asociado de creación de reflejo a la versión de SP2 de SQL Server 2005 como mínimo.
Si ya estaba utilizando la creación de reflejo de la base de datos, quite la creación de reflejo de base de datos y elimine el asociado de creación de reflejo. Para obtener más información, vea Cómo quitar la creación de reflejo de la base de datos (Transact-SQL).
Habilite el formato de almacenamiento vardecimal en la base de datos principal y asegúrese de que utilice el modelo de recuperación completa.
Establezca la creación de reflejo de base de datos mediante las copias de seguridad completas y de registro de la base de datos principal. Para obtener más información, vea Cómo establecer una sesión de creación de reflejo de la base de datos mediante la autenticación de Windows (Transact-SQL).
Modifique las tablas individuales para utilizar el formato de almacenamiento vardecimal.

[!NOTA] No es preciso quitar la creación de reflejo de base de datos para cambiar el formato de almacenamiento de tablas individuales

Para quitar el formato de almacenamiento vardecimal

Modifique las tablas en la base de datos principal para quitar el formato de almacenamiento vardecimal.
Quite la creación de reflejo de la base de datos.
Configure la base de datos principal en el modelo de recuperación simple. Esto interrumpe la cadena de registros.
Inhabilite el formato de almacenamiento vardecimal de la base de datos principal.
Elimine la base de datos de asociado del reflejo.
Vuelva a asignar el modo de recuperación completo a la base de datos principal.
Realice una copia de seguridad de la base de datos principal y vuelva a establecer la creación de reflejo de la base de datos.

Efectos del formato de almacenamiento vardecimal en las operaciones de réplica

La réplica funciona de la forma habitual en las tablas que utilizan el formato de almacenamiento vardecimal, aunque con las siguientes consideraciones:

Los tipos de datos decimal almacenados en el formato de almacenamiento vardecimal se convierten al formato decimal fijo para las transferencias durante la réplica. La base de datos distribution no se puede habilitar para el formato de almacenamiento vardecimal. Por lo tanto, los datos no se almacenan en el formato de almacenamiento vardecimal cuando se almacenan en tablas de réplica en la base de datos distribution. En el suscriptor, las entradas del registro se aplican como siempre.
Una tabla de formato de almacenamiento vardecimal puede replicarse en una tabla de formato decimal fijo, y una tabla de formato decimal fijo puede replicarse en una tabla de formato de almacenamiento vardecimal.
El proceso de creación de tablas relacionado con una suscripción nueva no crea tablas mediante el formato de almacenamiento vardecimal. Esto permite que la réplica sea correcta, independientemente del nivel de Service Pack de Database Engine (Motor de base de datos) o del estado habilitado del formato de almacenamiento vardecimal de la base de datos de suscripción. La tabla de suscricpión se puede habilitar para el formato de almacenamiento vardecimal en el suscriptor una vez creada la tabla o modificando las secuencias de comandos de creación antes de que se apliquen.

La tabla siguiente describe los requisitos de secuencia de comandos para varios suscriptores.

Suscriptor	Secuencia de comandos
SQL Server 2000 o SQL Server versión 7.0	Las secuencias de comandos de creación de tablas se pueden utilizar sin ninguna modificación.
SQL Server 2005, la base de datos no está marcada para el formato de almacenamiento vardecimal.	Las secuencias de comandos de creación de tablas se pueden utilizar sin ninguna modificación.
SQL Server 2005, la base de datos está marcada para el formato de almacenamiento vardecimal, pero la tabla del suscriptor no debe tener el formato de almacenamiento vardecimal habilitado.	Las secuencias de comandos de creación de tablas se pueden utilizar sin ninguna modificación.
SQL Server 2005, la base de datos está marcada para el formato de almacenamiento vardecimal y el suscriptor de SQL Server 2005 debe tener el formato de almacenamiento vardecimal habilitado.	Las secuencias de comandos de creación de tablas se pueden modificar para habilitar el formato de almacenamiento vardecimal en la base de datos y para habilitar el formato de almacenamiento vardecimal de las tablas. O bien la base de datos y las tablas de suscripción se pueden habilitar mediante los procedimientos almacenados descritos en la siguiente sección "Habilitar el formato de almacenamiento vardecimal".

Consideraciones adicionales

A continuación se enumeran las consideraciones adicionales que es preciso tener en cuenta al trabajar con el formato de almacenamiento vardecimal:

El formato de almacenamiento vardecimal carece de operaciones de importación y exportación masiva (bcp) eficaces.
La función DATALENGTH no detecta el formato de almacenamiento vardecimal y devuelve el número de bytes que se almacenaría en el formato decimal fijo.
En contadas ocasiones, el formato de almacenamiento vardecimal impedirá que SQL Server utilice un plan de consultas que era óptimo para los datos decimales fijos.
El formato de almacenamiento vardecimal se puede utilizar con cualquier nivel de compatibilidad de base de datos.
Si una tabla no tiene columnas de tipo de datos decimal o numeric cuando se ejecuta sp_tableoption, los metadatos de la tabla cambian para indicar que la tabla utilice el formato de almacenamiento vardecimal. Cuando más tarde se agreguen columnas decimal nuevas, se almacenarán en formato de almacenamiento vardecimal. No se necesita ninguna técnica especial para agregar o quitar columnas de una tabla que utiliza el formato de almacenamiento vardecimal.

Habilitar el formato de almacenamiento vardecimal

Para habilitar o cambiar el formato de almacenamiento vardecimal se requieren los permisos siguientes:

Para habilitar el formato de almacenamiento vardecimal en una base de datos se necesita el permiso ALTER DATABASE en el servidor.
Para cambiar una tabla al formato de almacenamiento vardecimal se necesita el permiso ALTER en la tabla.

Antes de habilitar el formato de almacenamiento vardecimal, debe comprobar que la tabla se reducirá cuando se habilite el formato de almacenamiento vardecimal. Cuando la precisión de la columna definida se requiere para la mayoría de filas, la sobrecarga asociada con el formato de almacenamiento vardecimal puede ser superior al ahorro obtenido y podría dar como resultado una tabla más grande. Para calcular la reducción del tamaño de fila antes de modificar la tabla, utilice el procedimiento almacenado sp_estimated_rowsize_reduction_for_vardecimal. Si decide cambiar el formato de almacenamiento de la tabla, habilite la base de datos para el formato de almacenamiento vardecimal y, a continuación, habilite tablas individuales para el formato de almacenamiento vardecimal.

Puede habilitar el formato de almacenamiento vardecimal para los tipos de datos decimal utilizando procedimientos almacenados o bien SQL Server Management Studio:

Ejecute sp_db_vardecimal_storage_format para habilitar el formato de almacenamiento vardecimal en la base de datos y, a continuación, ejecute sp_tableoption para habilitar el formato de almacenamiento vardecimal en las tablas adecuadas.
En Management Studio, utilice la página de opciones Propiedades de la base de datos para habilitar el formato de almacenamiento vardecimal en la base de datos. Debe utilizar sp_tableoption para cambiar una tabla al formato de almacenamiento vardecimal.

Identificar las tablas del formato de almacenamiento vardecimal

Para determinar las tablas en una base de datos que utilizan el formato de almacenamiento vardecimal, utilice la función OBJECTPROPERTY y busque la propiedad TableHasVarDecimalStorageFormat.

El ejemplo siguiente devuelve 1 si la tabla Production.WorkOrderRouting utiliza el formato de almacenamiento vardecimal, y 0 si no lo usa.

USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'), 
   'TableHasVarDecimalStorageFormat') ;
GO

En el ejemplo siguiente se buscan en la base de datos AdventureWorks todas las tablas que utilizan el formato de almacenamiento vardecimal.

USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
 WHERE OBJECTPROPERTY(object_id, 
   N'TableHasVarDecimalStorageFormat') = 1 ;
GO

Problemas para quitar el formato de almacenamiento vardecimal

Para quitar el formato de almacenamiento vardecimal de una tabla, la tabla debe haberse creado de nuevo en formato decimal fijo. Esto puede incrementar considerablemente el espacio de disco utilizado por la tabla. Si no hay suficiente espacio disponible, la operación no será correcta. En tal caso, para inhabilitar el formato de almacenamiento vardecimal, debe asegurarse de que hay espacio suficiente para SQL Server en el disco. La operación de ampliación también requiere espacio temporal para almacenar datos en formato vardecimal y en formato normal. Si la tabla ampliada tiene espacio suficiente en el disco después de la ampliación pero no se puede ampliar por falta de espacio en el disco temporal, puede ampliar los datos incrementalmente copiando las filas de la tabla en una nueva tabla sin ampliar.

Para quitar el formato de almacenamiento vardecimal de una base de datos inmediatamente después de haber sido modificada, elimine la base de datos y, a continuación, restaure la base de datos desde una copia de seguridad realizada antes de habilitar el formato de almacenamiento vardecimal en la base de datos.

Cuando mueva una base de datos desde una edición Enterprise, Developer o Evaluation de SQL Server 2005 a otra edición o a una versión anterior de SQL Server, primero debe abrir la base de datos con una de las ediciones necesarias, quitar el formato de almacenamiento vardecimal y, a continuación, migrar la base de datos. Se producirá un error si se adjunta una base de datos que contenga el formato de almacenamiento vardecimal a un servidor no apto.