Artículo
09/29/2010

sp_tableoption (Transact-SQL)

Establece los valores de las opciones de las tablas definidas por el usuario. Se puede utilizar sp_tableoption para controlar el comportamiento consecutivo de las tablas con columnas varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, o columnas de gran tamaño definidas por el usuario.

Importante
La característica text in row se quitará en una versión futura de SQL Server. Para almacenar datos de valores de gran tamaño, se recomienda utilizar los tipos de datos varchar(max), nvarchar(max) y varbinary(max).

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_tableoption [ @TableNamePattern = ] 'table' 
          , [ @OptionName = ] 'option_name' 
          ,[ @OptionValue =] 'value'

Argumentos

[ @TableNamePattern =] 'table'
Es el nombre completo o no completo de una tabla de base de datos definida por un usuario. Si se proporciona un nombre de tabla completo, incluido el nombre de la base de datos, el nombre de la base de datos debe ser el nombre de la base de datos actual. No se pueden establecer opciones para varias tablas al mismo tiempo. table es nvarchar(776) y no tiene valor predeterminado.

[ @OptionName = ] 'option_name'
Es un nombre de opción de tabla. option_name es varchar(35) y el valor predeterminado es NULL. option_name puede ser uno de los valores siguientes.

Valor	Descripción
table lock on bulk load	Cuando está deshabilitado (valor predeterminado), hace que los procesos de carga masiva en tablas definidas por el usuario obtengan bloqueos de fila. Cuando está habilitado, hace que los procesos de carga masiva en tablas definidas por el usuario obtengan un bloqueo de actualización masiva.
insert row lock	No se admite en SQL Server 2008. La estrategia de bloqueo de SQL Server consiste en el bloqueo de fila con posible promoción al bloqueo de página o de tabla. Esta opción no afecta al comportamiento de bloqueo de SQL Server y sólo está incluida para mantener la compatibilidad con los scripts y procedimientos existentes.
text in row	Cuando es OFF o 0 (deshabilitado, valor predeterminado), no cambia el comportamiento actual, y no existe ningún BLOB almacenado en fila de manera consecutiva. Cuando @OptionValue es ON (está habilitado) o bien se especifica un valor entero comprendido entre 24 y 7000, las cadenas text, ntext o image nuevas se almacenan directamente en la fila de datos. Todos los BLOB existentes (objetos binarios grandes: datos de tipo text, ntext o image) se cambiarán a formato text in row al actualizarse el valor del BLOB. Para obtener más información, vea la sección Notas.
large value types out of row	1 = Las columnas varchar(max), nvarchar(max), varbinary(max) y xml, y las columnas de gran tamaño de tipo definido por el usuario (UDT) de la tabla se almacenan de manera no consecutiva mediante un puntero de 16 bytes que señala a la raíz del objeto. 0 = Los valores varchar(max), nvarchar(max), varbinary(max) y xml, y los de gran tamaño de tipo definido por el usuario se almacenan directamente en la fila de datos, hasta un límite de 8.000 bytes y siempre que el valor pueda caber en el registro. Si el valor no cabe en el registro, se almacena un puntero en la fila, de manera consecutiva y el resto se almacena de forma no consecutiva en el espacio de almacenamiento de LOB. El valor predeterminado es 0.
vardecimal storage format	Cuando es TRUE, ON o 1, la tabla designada se habilita para el formato de almacenamiento vardecimal. Cuando es FALSE, OFF o 0, la tabla no se habilita para el formato de almacenamiento vardecimal. El formato de almacenamiento vardecimal se puede habilitar únicamente cuando la base de datos se ha habilitado para dicho formato con sp_db_vardecimal_storage_format. Para obtener información acerca del formato de almacenamiento vardecimal, vea Almacenar datos decimales como longitud variable. Esta opción requiere el Service Pack 2 de SQL Server 2005. El formato de almacenamiento Vardecimal sólo está disponible en las ediciones Enterprise, Developer y Evaluation de SQL Server. En SQL Server 2008 y versiones posteriores, todas las bases de datos de los usuarios están habilitadas para el formato de almacenamiento vardecimal. En SQL Server 2008 y versiones posteriores, el formato de almacenamiento vardecimal ha quedado en desuso. En su lugar, use la compresión de fila. Para obtener más información, vea Crear tablas e índices comprimidos. El valor predeterminado es 0.

[ @OptionValue =] 'value'
Indica si option_name está habilitado (TRUE, ON o 1) o deshabilitado (FALSE, OFF o 0). value es de tipo varchar(12) y no tiene valor predeterminado. value no distingue entre mayúsculas y minúsculas.

Para la opción text in row, los valores válidos son 0, ON, OFF o un entero comprendido entre 24 y 7000. Si value es ON, el valor predeterminado del límite es 256 bytes.

Valores de código de retorno

0 (correcto) o número de error (error)

Notas

sp_tableoption se puede utilizar únicamente para definir valores de opción de tablas definidas por el usuario. Para mostrar las propiedades de tabla, utilice OBJECTPROPERTY.

La opción text in row en sp_tableoption puede habilitarse o deshabilitarse sólo en tablas que contengan columnas de texto. Si la tabla no contiene una columna de texto, SQL Server genera un error.

Cuando se habilita la opción text in row, el parámetro @OptionValue permite a los usuarios especificar el tamaño máximo que debe almacenarse en una fila de un BLOB. El valor predeterminado es 256 bytes, pero los valores pueden oscilar entre 24 y 7.000 bytes.

Las cadenas text, ntext e image se almacenan en la fila de datos si se cumplen las condiciones siguientes:

La opción text in row está habilitada.
La longitud de la cadena es menor que el límite especificado en @OptionValue
Hay suficiente espacio disponible en la fila de datos.

Cuando se almacenan cadenas BLOB en la fila de datos, la lectura o escritura de cadenas text, ntext o image puede ser tan rápida como la lectura o escritura de cadenas de caracteres y binarias. SQL Server no necesita obtener acceso a páginas independientes para leer o escribir la cadena BLOB.

Si una cadena text, ntext o image es mayor que el límite especificado o que el espacio que hay disponible en la fila, lo que se almacena en la fila son punteros. Las condiciones para almacenar las cadenas BLOB en la fila siguen siendo válidas aunque debe haber espacio suficiente para almacenar los punteros en la fila de datos.

Los punteros y cadenas BLOB almacenados en la fila de una tabla se tratan de forma parecida a las cadenas de longitud variable. SQL Server sólo utiliza el número de bytes necesario para almacenar la cadena o el puntero.

Las cadenas BLOB existentes no se convierten inmediatamente cuando text in row se habilita por primera vez. Las cadenas sólo se convierten cuando se actualizan. De la misma manera, cuando se aumenta el límite de la opción text in row, las cadenas text, ntext o image ya existentes en la fila de datos no se convertirán para atenerse al nuevo límite hasta el momento de su actualización.

[!NOTA]

Para deshabilitar la opción text in row o reducir el límite de la opción será necesario realizar la conversión de todos los BLOB; el proceso puede ser largo en función del número de cadenas BLOB que deban convertirse. La tabla se bloquea durante el proceso de conversión.

Una variable de tabla, incluida una función que devuelve una variable de tabla, tiene habilitada de forma automática la opción text in row con un valor predeterminado para inline limit de 256. Esta opción no puede modificarse.

text in row admite las funciones TEXTPTR, WRITETEXT, UPDATETEXT y READTEXT. Los usuarios pueden leer partes de un BLOB con la función SUBSTRING(), pero debe recordarse que los punteros de texto consecutivos tienen límites de duración y de número distintos del resto de punteros de texto. Para obtener más información, vea Administrar datos ntext, text e image.

Para cambiar una tabla de un formato de almacenamiento vardecimal de nuevo al formato de almacenamiento decimal normal, la base de datos debe estar en modo de recuperación SIMPLE. Si se cambia el modo de recuperación, se interrumpirá la cadena de registro para las copias de seguridad; por lo tanto, debe crear una copia de seguridad completa de la base de datos después de quitar el formato de almacenamiento vardecimal de una tabla.

Permisos

Para ejecutar sp_tableoption se requiere el permiso ALTER en la tabla.

Ejemplos

A. Almacenar datos xml fuera de la fila

En el siguiente ejemplo se especifica que los datos xml de la tabla HumanResources.JobCandidate se almacenen de forma no consecutiva.

USE AdventureWorks;
GO
EXEC sp_tableoption 'HumanResources.JobCandidate', 'large value types out of row', 1;

B. Habilitar el formato de almacenamiento vardecimal en una tabla

En el ejemplo siguiente, se modifica la tabla Production.WorkOrderRouting para almacenar el tipo de datos decimal en el storage formatvardecimal.

USE master;
GO
-- The database must be enabled for vardecimal storage format
-- before a table can be enabled for vardecimal storage format
EXEC sp_db_vardecimal_storage_format 'AdventureWorks', 'ON';
GO
USE AdventureWorks;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting', 
   'vardecimal storage format', 'ON';