sp_tableoption (Transact-SQL)sp_tableoption (Transact-SQL)

SE APLICA A: síSQL Server (a partir de 2008) síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Establece los valores de las opciones de las tablas definidas por el usuario.Sets option values for user-defined tables. sp_tableoption se puede usar para controlar el comportamiento consecutivo de las tablas con varchar (max), nvarchar (max), varbinary (max), xml, texto, ntext, imagen, o las columnas de tipo definido por el usuario grandes.sp_tableoption can be used to control the in-row behavior of tables with varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, or large user-defined type columns.

Importante

La característica text in row se quitará en una versión futura de SQL ServerSQL Server.The text in row feature will be removed in a future version of SQL ServerSQL Server. Para almacenar datos de valores grandes, se recomienda que utilice el varchar (max), nvarchar (max) y varbinary (max) tipos de datos.To store large value data, we recommend that you use of the varchar(max), nvarchar(max) and varbinary(max) data types.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

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

ArgumentosArguments

[ @TableNamePattern =] 'tabla'[ @TableNamePattern =] 'table'
Es el nombre completo o no completo de una tabla de base de datos definida por un usuario.Is the qualified or nonqualified name of a user-defined database table. 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.If a fully qualified table name, including a database name, is provided, the database name must be the name of the current database. No se pueden establecer opciones para varias tablas al mismo tiempo.Table options for multiple tables can not be set at the same time. tabla es nvarchar(776), no tiene ningún valor predeterminado.table is nvarchar(776), with no default.

[ @OptionName =] 'option_name'[ @OptionName = ] 'option_name'
Es un nombre de opción de tabla.Is a table option name. option_name es varchar (35), no tiene ningún valor predeterminado es null.option_name is varchar(35), with no default of NULL. option_name puede ser uno de los siguientes valores.option_name can be one of the following values.

ValorValue DescripciónDescription
table lock on bulk loadtable 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.When disabled (the default), it causes the bulk load process on user-defined tables to obtain row locks. Cuando está habilitado, hace que los procesos de carga masiva en tablas definidas por el usuario obtengan un bloqueo de actualización masiva.When enabled, it causes the bulk load processes on user-defined tables to obtain a bulk update lock.
insert row lockinsert row lock Ya no se admite.No longer supported.

Esta opción no afecta al comportamiento de bloqueo de SQL ServerSQL Server y solo está incluida para mantener la compatibilidad con los scripts y procedimientos existentes.This option has no effect on the locking behavior of SQL ServerSQL Server and is included only for compatibility of existing scripts and procedures.
text in rowtext 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.When OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row.

Cuando se especifica y @OptionValue es ON (habilitado) o un valor entero comprendido entre 24 y 7000, las nuevas texto, ntext, o imagen las cadenas se almacenan directamente en la fila de datos.When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new text, ntext, or image strings are stored directly in the data row. Todos los BLOB existentes (objetos binarios grandes: texto, ntext, o imagen datos) se cambiarán a formato text in row cuando se actualiza el valor del BLOB.All existing BLOB (binary large object: text, ntext, or image data) will be changed to text in row format when the BLOB value is updated. Para obtener más información, vea la sección Comentarios.For more information, see Remarks.
large value types out of rowlarge value types out of row 1 = varchar (max), nvarchar (max), varbinary (max), xml y se almacenan las columnas de tipo grande definido por el usuario (UDT) en la tabla out of row, con un puntero de 16 bytes en la raíz.1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar (max), nvarchar (max), varbinary (max), xml y valores UDT grandes se almacenan directamente en la fila de datos, hasta un límite de 8.000 bytes y siempre y cuando el valor pueda caber en el registro.0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. 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.If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. El valor predeterminado es 0.0 is the default value.

El tipo grande (UDT) definido por el usuario se aplica a: SQL Server 2008SQL Server 2008 con SQL Server 2017SQL Server 2017.Large user-defined type (UDT) applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

Utilice la opción TEXTIMAGE_ON de CREATE TABLE para especificar una ubicación para el almacenamiento de tipos de datos de gran tamaño.Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types.
Formato de almacenamiento vardecimalvardecimal storage format Se aplica a: desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

Cuando es TRUE, ON o 1, la tabla designada se habilita para el formato de almacenamiento vardecimal.When TRUE, ON, or 1, the designated table is enabled for vardecimal storage format. Cuando es FALSE, OFF o 0, la tabla no se habilita para el formato de almacenamiento vardecimal.When FALSE, OFF, or 0, the table is not enabled for vardecimal storage format. Formato de almacenamiento vardecimal puede habilitarse únicamente cuando la base de datos se ha habilitado para el formato de almacenamiento vardecimal mediante el uso de sp_db_vardecimal_storage_format.Vardecimal storage format can be enabled only when the database has been enabled for vardecimal storage format by using sp_db_vardecimal_storage_format. En SQL Server 2008SQL Server 2008 y versiones posteriores, vardecimal el formato de almacenamiento está en desuso.In SQL Server 2008SQL Server 2008 and later, vardecimal storage format is deprecated. En su lugar, use la compresión de fila.Use ROW compression instead. Para obtener más información, consulte Data Compression.For more information, see Data Compression. El valor predeterminado es 0.0 is the default value.

[ @OptionValue =] 'valor'[ @OptionValue =] 'value'
Es si el option_name está habilitado (TRUE, ON o 1) o deshabilitado (FALSE, OFF o 0).Is whether the option_name is enabled (TRUE, ON, or 1) or disabled (FALSE, OFF, or 0). valor es varchar (12), no tiene ningún valor predeterminado.value is varchar(12), with no default. valor distingue mayúsculas de minúsculas.value is case insensitive.

Para la opción text in row, los valores válidos son 0, ON, OFF o un entero comprendido entre 24 y 7000.For the text in row option, valid option values are 0, ON, OFF, or an integer from 24 through 7000. Cuando valor es ON, el límite predeterminado es 256 bytes.When value is ON, the limit defaults to 256 bytes.

Valores de código de retornoReturn Code Values

0 (correcto) o número de error (error)0 (success) or error number (failure)

ComentariosRemarks

sp_tableoption se puede utilizar únicamente para definir valores de opción de tablas definidas por el usuario.sp_tableoption can be used only to set option values for user-defined tables. Para mostrar las propiedades de tabla, utilice OBJECTPROPERTY.To display table properties, use OBJECTPROPERTY.

La opción text in row en sp_tableoption puede habilitarse o deshabilitarse solo en tablas que contengan columnas de texto.The text in row option in sp_tableoption can be enabled or disabled only on tables that contain text columns. Si la tabla no contiene una columna de texto, SQL ServerSQL Server genera un error.If the table does not have a text column, SQL ServerSQL Server raises an error.

Cuando se habilita la opción text in row, el @OptionValue parámetro permite a los usuarios especificar el tamaño máximo que se almacenará en una fila para un BLOB.When the text in row option is enabled, the @OptionValue parameter allows users to specify the maximum size to be stored in a row for a BLOB. El valor predeterminado es 256 bytes, pero los valores pueden oscilar entre 24 y 7.000 bytes.The default is 256 bytes, but values can range from 24 through 7000 bytes.

texto, ntext, o imagen las cadenas se almacenan en la fila de datos si se cumplen las condiciones siguientes:text, ntext, or image strings are stored in the data row if the following conditions apply:

  • La opción text in row está habilitada.text in row is enabled.

  • La longitud de la cadena es menor que el límite especificado en @OptionValueThe length of the string is shorter than the limit specified in @OptionValue

  • Hay suficiente espacio disponible en la fila de datos.There is enough space available in the data row.

Cuando se almacenan cadenas BLOB en la fila de datos, leer y escribir el texto, ntext, o imagen las cadenas pueden ser tan rápidas como la lectura o escritura de cadenas de caracteres y binarias.When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. SQL ServerSQL Server no necesita obtener acceso a páginas independientes para leer o escribir la cadena BLOB. does not have to access separate pages to read or write the BLOB string.

Si un texto, ntext, o imagen cadena es mayor que el límite especificado o el espacio disponible en la fila, los punteros se almacenan en la fila en su lugar.If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. 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.The conditions for storing the BLOB strings in the row nonetheless apply: There must be enough space in the data row to hold the pointers.

Los punteros y cadenas BLOB almacenados en la fila de una tabla se tratan de forma parecida a las cadenas de longitud variable.BLOB strings and pointers stored in the row of a table are treated similarly to variable-length strings. SQL ServerSQL Server solo utiliza el número de bytes necesario para almacenar la cadena o el puntero. uses only the number of bytes required to store the string or the pointer.

Las cadenas BLOB existentes no se convierten inmediatamente cuando text in row se habilita por primera vez.Existing BLOB strings are not converted immediately when text in row is first enabled. Las cadenas solo se convierten cuando se actualizan.The strings are converted only when they are updated. Del mismo modo, cuando se aumenta el límite text in row opción, el texto, ntext, o imagen cadenas ya está en la fila de datos no se convertirán a ajustarse al nuevo límite hasta el momento en que se actualicen.Likewise, when the text in row option limit is increased, the text, ntext, or image strings already in the data row will not be converted to adhere to the new limit until the time they are updated.

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.Disabling the text in row option or reducing the limit of the option will require the conversion of all BLOBs; therefore, the process can be long, depending on the number of BLOB strings that must be converted. La tabla se bloquea durante el proceso de conversión.The table is locked during the conversion process.

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.A table variable, including a function that returns a table variable, automatically has the text in row option enabled with a default inline limit of 256. Esta opción no puede modificarse.This option cannot be changed.

La opción text in row admite las funciones TEXTPTR, WRITETEXT, UPDATETEXT y READTEXT.The text in row option supports the TEXTPTR, WRITETEXT, UPDATETEXT, and READTEXT functions. 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.Users can read parts of a BLOB with the SUBSTRING() function, but must remember that in-row text pointers have different duration and number limits from other text pointers.

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.To change a table from vardecimal storage format back to the normal decimal storage format, the database must be in SIMPLE recovery mode. 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.Changing the recovery mode will break the log chain for backup purposes, therefore you should create a full database backup after removing the vardecimal storage format from a table.

Si va a convertir una existente LOB columna tipo de datos (text, ntext o image) a los tipos de valores grandes de pequeño a mediano (varchar (max), nvarchar (max), o bien y realice la mayoría de las instrucciones no hacen referencia a las columnas de tipo de valor grande en su entorno, considere la posibilidad de cambiar large_value_types_out_of_row a 1 para obtener un rendimiento óptimo.If you are converting an existing LOB data type column (text, ntext, or image) to small-to-medium large value types (varchar(max), nvarchar(max), or varbinary(max)) , and most statements do not reference the large value type columns in your environment, consider changing large_value_types_out_of_row to 1 to gain optimal performance. Cuando el large_value_types_out_of_row se cambia el valor de opción, existente varchar (max), nvarchar (max), varbinary (max), y los valores xml no se convierten inmediatamente.When the large_value_types_out_of_row option value is changed, existing varchar(max), nvarchar(max), varbinary(max), and xml values are not immediately converted. El almacenamiento de las cadenas se cambia cuando se actualizan ulteriormente.The storage of the strings is changed as they are subsequently updated. Los valores nuevos que se inserten en una tabla se almacenan de acuerdo a la opción de tabla vigente.Any new values inserted into a table are stored according to the table option in effect. Para obtener resultados inmediatos, ya sea realizar una copia de los datos y, a continuación, volver a llenar la tabla después de cambiar el large_value_types_out_of_row establecer o actualizar cada columna de tipos de valores grandes de pequeño a mediano a sí mismo para que el almacenamiento de la las cadenas se cambia con la opción de tabla vigente.For immediate results, either make a copy of the data and then repopulate the table after changing the large_value_types_out_of_row setting or update each small-to-medium large value types column to itself so that the storage of the strings is changed with the table option in effect. Conviene regenerar los índices en la tabla después de la actualización o de volver a rellenar para condensar la tabla.Consider rebuilding the indexes on the table after the update or repopulation to condense the table.

PermisosPermissions

Para ejecutar sp_tableoption se requiere el permiso ALTER en la tabla.To execute sp_tableoption requires ALTER permission on the table.

EjemplosExamples

A.A. Almacenar datos xml fuera de la filaStoring xml data out of the row

El ejemplo siguiente especifica que el xml datos en el HumanResources.JobCandidate tabla almacenarse de manera no consecutiva.The following example specifies that the xml data in the HumanResources.JobCandidate table be stored out of row.

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

B.B. Habilitar el formato de almacenamiento vardecimal en una tablaEnabling vardecimal storage format on a table

En el ejemplo siguiente se modifica el Production.WorkOrderRouting tabla para almacenar el decimal tipo de datos en el vardecimal el formato de almacenamiento.The following example modifies the Production.WorkOrderRouting table to store the decimal data type in the vardecimal storage format.

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 'AdventureWorks2012', 'ON';  
GO  
USE AdventureWorks2012;  
GO  
EXEC sp_tableoption 'Production.WorkOrderRouting',   
   'vardecimal storage format', 'ON';  

Vea tambiénSee Also

sys.tables (Transact-SQL) sys.tables (Transact-SQL)
OBJECTPROPERTY (Transact-SQL) OBJECTPROPERTY (Transact-SQL)
Procedimientos almacenados del sistema (Transact-SQL) System Stored Procedures (Transact-SQL)
Procedimientos almacenados del motor de base de datos (Transact-SQL)Database Engine Stored Procedures (Transact-SQL)