sp_tableoption (Transact-SQL)

Artículo
12/15/2008

Actualizado: 12 de diciembre de 2006

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

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_pattern 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 2005. 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 las secuencias de comandos 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 está en ON (habilitado) o bien se especifica un valor entero de 24 hasta 7000, las nuevas cadenas text, ntext o image 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 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 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.
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 2005.

[ @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 de 24 hasta 7000. Cuando value es on, el límite predeterminado es de 256 bytes.

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 table, incluida una función que devuelve una variable table, 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 se puede cambiar.

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, 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.

Valores de código de retorno

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

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 un formato de almacenamiento vardecimal.

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';

Vea también

Historial de cambios

Versión	Historial
12 de diciembre de 2006	Contenido nuevo: Se agregó la opción de formato de almacenamiento vardecimal que está disponible en el Service Pack 2 de SQL Server 2005.