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.

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.

[ @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.

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)