Almacenar datos decimales como longitud variable
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 SQL Server 2005 Service Pack 2 (SP2) 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 solo está disponible en las ediciones Enterprise, Developer y Evaluation de SQL Server.
Nota
Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan. En su lugar, use la compresión ROW y PAGE. Para obtener más información, vea Crear tablas e índices comprimidos.
Nota
Vardecimal es un formato de almacenamiento y no un tipo de datos.
El formato de almacenamiento vardecimal puede reducir de forma considerable 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 es posible almacenar algunas columnas decimal concretas de una tabla en el formato de almacenamiento vardecimal y otras no. 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 solo consumen el espacio que se necesita para contener el número proporcionado, más 2 bytes adicionales. El resultado obtenido oscila siempre entre 5 y 20 bytes. El resultado incluye los 2 bytes adicionales necesarios para almacenar el desplazamiento al valor. Sin embargo, los valores nulos y los ceros se tratan de forma especial y solo 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 vardecimal (bytes) |
Tamaño adicional 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 clúster o montón). Si el índice clúster de una tabla contiene una columna decimal, todos los índices no clúster también se deben crear de nuevo porque incluyen los valores de clave en clúster. Si el índice clúster no tiene una columna decimal, los índices no clúster se vuelven a crear si contienen una columna decimal. Si la tabla es un montón (es decir, no tiene un índice clúster), todos los índices no clúster se deben volver a crear para apuntar a las nuevas ubicaciones de fila en el montón.
Para volver a crear una tabla y habilitar o deshabilitar 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 solo 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 SQL Server 2005 SP2 o versiones posteriores.
El formato de almacenamiento vardecimal no se puede habilitar en las bases de datos de sistema maestra, modelo, msdb, tempdb o distribución. Cuando una consulta ordena datos que están almacenados en formato de almacenamiento vardecimal, 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 completo. 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 con 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 deshabilitan 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 podrá 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 superar los 8060 bytes si todas las columnas variables se establecen en su tamaño máximo. SQL Server exige 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 exige 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 Motor de base de datos de destino no es SQL Server 2005 SP2 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 Motor de base de datos es SQL Server 2005 SP2 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 a SQL Server 2005 SP2, como mínimo. Por ejemplo, no se puede restaurar una copia de seguridad de registros 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 de SQL Server 2005 SP2 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 este formato, la base de datos se habilitará para el formato.
Cuando una tabla se cambia al formato de almacenamiento vardecimal, la cadena de copias de seguridad de registros 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 SQL Server 2005 SP2, como mínimo.
Si ya estaba utilizando la creación de reflejo de la base de datos, quítela 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 (si la base de datos está en SQL Server 2005) y asegúrese de que utiliza el modelo de recuperación completa.
Establezca la creación de reflejo de base de datos mediante las copias de seguridad completas y de registros 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.
Si la base de datos está en SQL Server 2005, deshabilite el formato de almacenamiento vardecimal de la base de datos principal.
Quite la base de datos de asociado de creación de 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 replicación
La replicación 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 replicación. La base de datos distribución 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 replicación en esa base de datos. 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 replicación sea correcta, independientemente del nivel de Service Pack de 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 suscripción se puede habilitar para el formato de almacenamiento vardecimal en el suscriptor una vez creada la tabla o mediante la modificación de los scripts de creación antes de que se apliquen.
La tabla siguiente describe los requisitos de script para diversos suscriptores.
Suscriptor |
Script |
|---|---|
SQL Server 2000 o SQL Server versión 7.0 |
Los scripts 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. |
Los scripts 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. |
Los scripts 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. |
Los scripts de creación de tablas se pueden modificar para habilitar el formato de almacenamiento vardecimal en la base de datos y en las tablas. O bien las tablas y la base de datos de suscriptor 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 del 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á al habilitarlo. Cuando la precisión de la columna definida se requiere para la mayoría de filas, la sobrecarga asociada al 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 las tablas para este formato. No es necesario que las bases de datos de SQL Server 2008 estén habilitadas para el formato de almacenamiento vardecimal.
En una base de datos de SQL Server 2008, puede habilitar el formato de almacenamiento vardecimal para los tipos de datos decimal mediante procedimientos almacenados o SQL Server Management Studio:
Ejecute sp_db_vardecimal_storage_format para habilitar el formato de almacenamiento vardecimal en la base de datos (si la instancia de SQL Server es SQL Server 2005 SP2) 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.
Nota
A partir de SQL Server 2008, todas las bases de datos están habilitadas para el 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 AdventureWorks2008R2 ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'),
'TableHasVarDecimalStorageFormat') ;
GO
En el ejemplo siguiente se buscan en la base de datos AdventureWorks2008R2 todas las tablas que utilizan el formato de almacenamiento vardecimal.
USE AdventureWorks2008R2 ;
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 deshabilitar 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, restáurela a partir de 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, en primer lugar, abra la base de datos con una de las ediciones requeridas, quite el formato de almacenamiento vardecimal y, a continuación, migre 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.