Datos consecutivos
Los tipos de valores pequeños y medianos (varchar(max), nvarchar(max), varbinary(max) y xml) así como los tipos de datos de objetos grandes (LOB) (text, ntext y image) pueden almacenarse en una fila de datos. Este comportamiento se controla mediante dos opciones del procedimiento almacenado del sistema sp_tableoption: la opción large value types out of row para tipos de valores grandes, y la opción text in row para tipos de objetos grandes. Estas opciones resultan especialmente útiles en las tablas en las que los valores de cualquiera de estos tipos de datos se suelen leer o escribir en una unidad y la mayoría de las instrucciones que hacen referencia a la tabla utilizan este tipo de datos. Según las características de utilización o carga de trabajo, puede que el almacenamiento de datos consecutivos no sea útil.
.gif "Nota importante") Importante |
|---|
La opción text in row se quitará en una versión futura de SQL Server. Evite la utilización de la opción text in row en los nuevos trabajos de programación y planee modificar las aplicaciones que actualmente la utilizan. Se recomienda almacenar los datos de gran volumen con los tipos de datos varchar(max), nvarchar(max) o varbinary(max). Para controlar el comportamiento consecutivo y no consecutivo de estos tipos de datos, utilice la opción large value types out of row. |
A menos que el valor de la opción text in row sea ON o un límite consecutivo específico, las cadenas text, ntext o image serán cadenas de caracteres o binarias grandes (hasta 2 gigabytes) almacenadas fuera de una fila de datos. La fila de datos sólo contiene un puntero de texto de 16 bytes que apunta al nodo raíz de un árbol compuesto de punteros internos. Estos punteros asignan las páginas en las que se almacenan los fragmentos de la cadena. Para obtener más información acerca del almacenamiento de las cadenas text, ntext o image, vea Usar datos text e image.
Puede establecer una opción text in row para las tablas que contengan columnas de tipos de datos LOB. También puede especificar un límite para la opción text in row entre 24 y 7.000 bytes.
De igual forma, a menos que la opción large value types out of row se active, las columnas de tipo varchar(max), nvarchar(max), varbinary(max) y xml se almacenan, si es posible, dentro de la fila de datos. En tal caso, SQL Server Database Engine (Motor de base de datos de SQL Server) intentará adaptar el valor específico si puede; de lo contrario, lo sacará de la fila. Si el valor de large value types out of row es ON, se almacenarán los valores de forma no consecutiva y sólo se almacenará un puntero de texto de 16 bytes en el registro.
Nota
El almacenamiento consecutivo máximo para los tipos de datos de valores grandes está establecido en 8.000 bytes si el valor de large value types out of row es OFF. A diferencia de la opción text in row, no se puede especificar el límite consecutivo para las columnas de la tabla.
Cuando se configura una tabla para almacenar tipos de valores grandes o tipos de datos de objetos grandes directamente en la fila de datos, los valores de columna reales serán consecutivos si se cumple alguna de las condiciones siguientes:
La longitud de la cadena es menor que el límite especificado para las columnas text, ntext y image
Hay suficiente espacio disponible en la fila de datos para almacenar la cadena.
Cuando se almacena en la fila de datos un tipo de valor grande o un valor de columna de tipo de datos de objetos grandes, el Motor de base de datos no tendrá acceso a una página independiente o a un conjunto de páginas para leer o escribir la cadena de caracteres o binaria. Esto hace la lectura y escritura de cadenas consecutivas tan rápida como la lectura y escritura de cadenas varchar, nvarchar o varbinary de tamaño limitado. De forma parecida, cuando se almacenan los valores de forma no consecutiva, el Motor de base de datos realiza una escritura o lectura de página adicional.
En el caso de los tipos de datos de objetos grandes, si la cadena es mayor que el límite de la opción text in row o que el espacio disponible en la fila, el conjunto de punteros que se almacenarían en el nodo raíz del árbol de punteros se almacenará en la fila. Los punteros se almacenan en la fila si se cumple alguna de las condiciones siguientes:
El espacio necesario para almacenar los punteros es menor que el límite especificado para la opción text in row.
Hay suficiente espacio disponible en la fila de datos para almacenar los punteros.
Mover los punteros desde el nodo raíz hasta la fila permite al Motor de base de datos no tener que utilizar un nodo raíz. Esta capacidad puede eliminar el acceso a páginas al leer o escribir la cadena. De este modo se aumenta el rendimiento.
Cuando se utilizan nodos raíz, se almacenan como uno de los fragmentos de cadena en una página LOB y pueden contener hasta cinco punteros internos. El Motor de base de datos necesita 72 bytes de espacio en la fila para almacenar cinco punteros para una cadena consecutiva. Si no hay suficiente espacio en la fila para almacenar los punteros cuando el valor de la opción text in row es ON o el valor de large value types out of row es OFF, puede que el Motor de base de datos tenga que asignar una página de 8 K para almacenarlos. Si la longitud de datos del valor supera los 40.200 bytes, se necesitarán más de cinco punteros consecutivos. En este caso, sólo se almacenarán 24 bytes en la fila principal y se asignará una página de datos adicional al espacio de almacenamiento de LOB.
Cuando se almacenan cadenas de gran tamaño en la fila, se almacenan de manera similar a las de longitud variable. El Motor de base de datos ordena las columnas en orden decreciente según el tamaño e inserta los valores de forma no consecutiva hasta que las columnas restantes caben en la página de datos (8 K).
Habilitar y deshabilitar la opción large value types out of row
Puede habilitar la opción large value types out of row para una tabla utilizando sp_tableoption de la forma siguiente:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
Si especifica OFF, el límite consecutivo para las columnas varchar(max), nvarchar(max), varbinary(max) y xml se establece en 8.000 bytes. Sólo se almacenará un puntero raíz de 16 bytes de forma consecutiva y el valor se almacenará en el espacio de almacenamiento de LOB. Se recomienda establecer esta opción en ON para las tablas en las que la mayoría de las instrucciones no hagan referencia a las columnas de tipos de valores grandes. El almacenamiento de estas columnas de forma no consecutiva implica que se pueden incluir más filas en cada página, con lo que se reduce el número de operaciones de E/S necesarias para recorrer la tabla.
Si se establece el valor OFF para esta opción, es posible que se almacene un gran número de cadenas en la propia fila, lo que reducirá el número de filas de datos que puede contener cada página. Si la mayoría de las instrucciones que hacen referencia a la tabla no tienen acceso a las columnas varchar(max), nvarchar(max), varbinary(max) o xml la disminución de las filas en una página puede incrementar el número de páginas que deben leerse para procesar consultas. Al reducir las filas por página, puede aumentar el número de páginas que será necesario recorrer si el optimizador no encuentra ningún índice utilizable.
También puede utilizar sp_tableoption para deshabilitar la opción no consecutiva:
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
Si 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 convertirán inmediatamente .Se cambiará el almacenamiento de las cadenas a medida que se actualicen con posterioridad. Los nuevos valores que se inserten en una tabla se almacenarán según la opción de tabla seleccionada.
Para examinar el valor de la opción large value types out of row de una tabla específica, consulte la columna large_value_types_out_of_row de la vista de catálogo sys.tables. Esta columna es 0 si la tabla no tiene la opción large value types out of row habilitada y 1 si los tipos de valores grandes se guardan fuera de la fila.
Habilitar y deshabilitar la opción text in row
Puede habilitar la opción text in row para una tabla utilizando sp_tableoption de la forma siguiente:
sp_tableoption N'MyTable', 'text in row', 'ON'
Si lo desea, puede especificar un límite máximo, de 24 a 7.000 bytes, para la longitud de una cadena text, ntext e image que se puede almacenar en una fila de datos:
sp_tableoption N'MyTable', 'text in row', '1000'
Si especifica ON en lugar de un límite específico, el límite predeterminado será de 256 bytes.Este valor predeterminado permite obtener las mayores ventajas de rendimiento de la opción text in row. Aunque en general no se debe establecer el valor por debajo de 72, tampoco se debe seleccionar un valor demasiado alto. Esto es válido especialmente para las tablas en las que la mayor parte de las instrucciones no hacen referencia a columnas text, ntext e image o en las que hay varias columnas text, ntext y image.
Si establece un límite text in row de gran tamaño y se almacena un gran número de cadenas en la propia fila, se reducirá significativamente el número de filas de datos que puede contener cada página. Si la mayoría de las instrucciones que hacen referencia a la tabla no tienen acceso a las columnas text, ntext o image, la disminución de filas en una página puede incrementar el número de páginas que deben leerse para procesar las consultas. Al reducir las filas por página, puede aumentar el tamaño de los índices y el número de páginas que será necesario recorrer si el optimizador no encuentra un índice utilizable. El valor límite predeterminado de text in row de 256 es suficientemente grande para garantizar que las cadenas pequeñas y los punteros de texto raíz puedan almacenarse en las filas, pero no tanto como para que disminuya el número de filas por página lo suficiente para que se note en el rendimiento.
La opción text in row se define automáticamente en 256 para las variables con el tipo de datos de tabla y para las tablas devueltas por una función definida por el usuario que devuelven una tabla. Esta configuración no puede modificarse.
También puede utilizar sp_tableoption para desactivar la opción especificando el valor de opción OFF o 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
Para examinar el valor de la opción text in row de una tabla específica, vea la columna text_in_row_limit de la vista de catálogo sys.tables. El valor de esta columna será 0 si la tabla no tiene activada la opción text in row, o bien un valor superior a 0 si se ha establecido el límite consecutivo.
Efectos del uso de la opción text in row
A continuación se enumeran los efectos de la opción text in row:
Después de habilitar la opción text in row, puede utilizar las instrucciones TEXTPTR, READTEXT, UPDATETEXT o WRITETEXT para leer o modificar partes de un valor text, ntext o image almacenado en la tabla. En las instrucciones SELECT, puede leer una cadena text, ntext o image completa, o bien utilizar la función SUBSTRING para leer partes de la cadena. Todas las instrucciones INSERT o UPDATE que hacen referencia a la tabla deben especificar cadenas completas y no pueden modificar sólo una parte de una cadena text, ntext o image.
Al habilitar por primera vez la opción text in row, las cadenas text, ntext o image existentes no se convertirán inmediatamente en cadenas consecutivas. Sólo se convertirán en cadenas consecutivas si se actualizan con posterioridad. Las cadenas text, ntext o image insertadas después de haber habilitado la opción text in row se insertarán como cadenas consecutivas.
Deshabilitar la opción text in row puede ser una operación de registro de larga duración. La tabla se bloquea y todas las cadenas consecutivas text, ntext e image se convierten en cadenas text, ntext e image normales. La duración de la ejecución del comando y la cantidad de datos modificados depende del número de cadenas text, ntext e image que deben convertirse de cadenas consecutivas en normales.
La opción text in row no afecta al funcionamiento del proveedor OLE DB para SQL Server Native Client ni del controlador ODBC de SQL Server Native Client, salvo para acelerar el acceso a los datos text, ntext y image.
Las funciones de imagen y texto de DB-Library, como dbreadtext y dbwritetext, no pueden utilizarse en una tabla después de habilitar la opción text in row.