sp_tableoption (Transact-SQL)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Establece los valores de las opciones de las tablas definidas por el usuario. sp_tableoptionse puede usar para controlar el comportamiento en fila de las tablas con columnas de tipo varchar(max),nvarchar(max), varbinary(max), xml, text, ntext, image o de tipo definido por el usuario de gran tamaño.

Importante

La característica de texto de fila se quitará en una versión futura de SQL Server. Para almacenar datos de gran valor, se recomienda usar los tipos de datos varchar(max),nvarchar(max) y varbinary(max).

Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_tableoption
    [ @TableNamePattern = ] N'TableNamePattern'
    , [ @OptionName = ] 'OptionName'
    , [ @OptionValue = ] 'OptionValue'
[ ; ]

Argumentos

[ @TableNamePattern = ] N'TableNamePattern'

Nombre completo o no calificado de una tabla de base de datos definida por el usuario. @TableNamePattern es nvarchar(776), sin ningún valor predeterminado. 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. Las opciones de tabla para varias tablas no se pueden establecer al mismo tiempo.

[ @OptionName = ] 'OptionName'

Un nombre de opción de tabla. @OptionName es varchar(35) y 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	Ya no se admite. Esta opción no tiene ningún efecto en el comportamiento de bloqueo de SQL Server y solo se incluye para la compatibilidad de los scripts y procedimientos existentes.
text in row	Cuando OFF o 0 (deshabilitado, el valor predeterminado), no cambia el comportamiento actual y no hay ningún BLOB en fila. Cuando se especifica y @OptionValue es (habilitado) o un valor entero de 24 a través 7000de , las cadenas de texto, ntext o imagen nuevas se almacenan directamente en la fila de datos.ON Todos los datos BLOB existentes (objeto binario grande: text, ntext o image) se cambian a texto en formato de fila cuando se actualiza el valor BLOB. Para obtener más información, vea la sección Comentarios.
large value types out of row	1 = columnas varchar(max), nvarchar(max), varbinary(max), xml y grandes columnas de tipo definido por el usuario (UDT) en la tabla se almacenan fuera de fila, con un puntero de 16 bytes a la raíz. 0 = los valores varchar(max), nvarchar(max), varbinary(max), xml y UDT grandes se almacenan directamente en la fila de datos, hasta un límite de 8000 bytes y siempre que el valor pueda caber en el registro. Si el valor no cabe en el registro, se almacena un puntero en fila y el resto se almacena fuera de la fila en el espacio de almacenamiento loB. El valor predeterminado es 0. El tipo definido por el usuario (UDT) grande se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores. Use la TEXTIMAGE_ON opción CREATE TABLE para especificar una ubicación para el almacenamiento de tipos de datos de gran tamaño.
formato de almacenamiento vardecimal	Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores. Cuando TRUE, ONo 1, la tabla designada está habilitada para el formato de almacenamiento vardecimal . Cuando FALSE, OFFo 0, la tabla no está habilitada para el formato de almacenamiento vardecimal . El formato de almacenamiento vardecimal solo se puede habilitar cuando la base de datos está habilitada para el formato de almacenamiento vardecimal mediante sp_db_vardecimal_storage_format. En SQL Server 2008 (10.0.x) y versiones posteriores, el formato de almacenamiento vardecimal está en desuso. Use ROW la compresión en su lugar. Para obtener más información, consulte Compresión de datos. El valor predeterminado es 0.

[ @OptionValue = ] 'OptionValue'

Especifica si el @OptionName está habilitado (TRUE, ONo 1) o deshabilitado (FALSE, OFFo 0). @OptionValue es varchar(12), sin ningún valor predeterminado. @OptionValue no distingue mayúsculas de minúsculas.

Para la opción texto de fila, los valores de opción válidos son 0, ON, OFFo un entero de a través 7000de 24 . Cuando @OptionValue es ON, el límite tiene como valor predeterminado 256 bytes.

Valores de código de retorno

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

Comentarios

sp_tableoption solo se puede usar para establecer valores de opción para tablas definidas por el usuario. Para mostrar las propiedades de la tabla, use OBJECTPROPERTY o consulta sys.tables.

La opción texto de fila de sp_tableoption solo se puede habilitar o deshabilitar en tablas que contienen columnas de texto. Si la tabla no tiene una columna de texto, SQL Server genera un error.

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

las cadenas text, ntext o image se almacenan en la fila de datos si se aplican las condiciones siguientes:

• El texto de la fila está habilitado.
• La longitud de la cadena es menor que el límite especificado en @OptionValue.
• Hay suficiente espacio disponible en la fila de datos.

Cuando las cadenas BLOB se almacenan en la fila de datos, leer y escribir las cadenas de texto, ntext o imagen pueden ser tan rápidas como leer o escribir caracteres y cadenas binarias. SQL Server no tiene que tener acceso a páginas independientes para leer o escribir la cadena BLOB.

Si una cadena de texto, ntext o imagen es mayor que el límite especificado o el espacio disponible en la fila, los punteros se almacenan en la fila en su lugar. 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 usa solo el número de bytes necesarios para almacenar la cadena o el puntero.

Las cadenas BLOB existentes no se convierten inmediatamente cuando el texto de la fila está habilitado por primera vez. Las cadenas solo se convierten cuando se actualizan. Del mismo modo, cuando se aumenta el límite de opciones de texto de fila, las cadenas de texto, ntext o imagen que ya están en la fila de datos no se convierten para cumplir el nuevo límite hasta el momento en que se actualizan.

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 se puede cambiar.

La opción 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 cambiar una tabla del formato de almacenamiento vardecimal al formato de almacenamiento decimal normal, la base de datos debe estar en el modelo de recuperación SIMPLE. Al cambiar el modelo de recuperación se interrumpirá la cadena de registros con fines de copia de seguridad, por lo que debe crear una copia de seguridad completa de la base de datos después de quitar el formato de almacenamiento vardecimal de una tabla.

Si va a convertir una columna de tipo de datos LOB existente (text, ntext o image) a tipos de valor de tamaño pequeño a mediano (varchar(max),nvarchar(max)o varbinary(max)) y la mayoría de las instrucciones no hacen referencia a las columnas de tipo de valor grande del entorno, considere la posibilidad de cambiar large_value_types_out_of_row para 1 obtener un rendimiento óptimo. Cuando se cambia el valor de la opción large_value_types_out_of_row, los valores varchar(max),nvarchar(max), varbinary(max)y xml existentes no se convierten inmediatamente. El almacenamiento de las cadenas se cambia a medida que se actualizan más adelante. Los valores nuevos que se inserten en una tabla se almacenan de acuerdo a la opción de tabla vigente. Para obtener resultados inmediatos, realice una copia de los datos y, a continuación, vuelva a rellenar la tabla después de cambiar el valor de large_value_types_out_of_row o actualice cada columna de tipos de valor pequeño a mediano a sí mismo para que el almacenamiento de las cadenas cambie con la opción de tabla en vigor. Conviene regenerar los índices en la tabla después de la actualización o de volver a rellenar para condensar la tabla.

Permisos

Para ejecutar se sp_tableoption requiere ALTER permiso en la tabla.

Ejemplos

A Almacenar datos XML fuera de la fila

En el ejemplo siguiente se especifica que los datos xml de la HumanResources.JobCandidate tabla se almacenan fuera de fila.

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

B. Habilitación del formato de almacenamiento vardecimal en una tabla

En el ejemplo siguiente se modifica la Production.WorkOrderRouting tabla para almacenar el tipo de datos decimal en el 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 'AdventureWorks2022', 'ON';
GO
USE AdventureWorks2022;
GO
EXEC sp_tableoption 'Production.WorkOrderRouting',
   'vardecimal storage format', 'ON';

Contenido relacionado

• sys.tables (Transact-SQL)
• OBJECTPROPERTY (Transact-SQL)
• Procedimientos almacenados del sistema (Transact-SQL)
• Motor de base de datos procedimientos almacenados (Transact-SQL)