The pointer to each row-overflow column adds 24 bytes of overhead to the row. If you have single columns that might need to store more than 8, bytes, you should use either LOB text, image , or ntext columns or the MAX data types. Like row-overflow data, LOB data is stored in its own set of pages, and the allocation query shows you pages for LOB data as well as pages for regular in-row data and row-overflow data.
Although the default behavior is to store all the LOB data off the data row, SQL Server allows you to change the storage mechanism by setting a table option to allow LOB data to be stored in the data row itself if it is small enough. The byte pointer points to a page or the first of a set of pages where the data can be found. A text, ntext , or image page can hold data from multiple columns and from multiple rows; the page can even have a mix of text, ntext , and image data.
However, one text or image page can hold only text or image data from a single table. Even more specifically, one text or image page can hold only text or image data from a single partition of a table, which should become clear when partitioning metadata is discussed at the end of this chapter. The pages are logically organized in a B-tree structure, so operations starting in the middle of the LOB string are very efficient.
The structure of the B-tree varies slightly depending on whether the amount of data is less than or more than 32 KB. See Figure for the general structure. B-trees were discussed in detail when describing indexes in Chapter 7. Figure A text column pointing to a B-tree that contains the blocks of data.
LOB is used only when referring to the data using the special storage format shown in Figure The term large object is used when referring to any method for storing data that might be too large for a regular data page. If the amount of LOB data is less than 32 KB, the text pointer in the data row points to an byte text root structure. This forms the root node of the B-tree structure. The root node points to the blocks of text, ntext , or image data.
Although the data for LOB columns is arranged logically in a B-tree, both the root node and the individual blocks of data are spread physically throughout LOB pages for the table. The size of each block of data is determined by the size written by an application. Small blocks of data are combined to fill a page. The root structure and the data blocks are interleaved throughout the text and image pages. Each page storing intermediate nodes contains only intermediate nodes for one text or image column in one data row.
You can see this behavior by creating a table with a text column, inserting a value of less than 40 KB and then one greater than 40 KB, and finally examining information returned by sys. The next section discusses varchar MAX in more detail. The first time you select from sys.
The second time after data greater than 40 KB in size is inserted, you should also see PageType values of 4. If you store all your LOB data type values outside your regular data pages, SQL Server needs to perform additional page reads every time you access that data, just as it does for row-overflow pages.
In some cases, you might notice a performance improvement by allowing some of the LOB data to be stored in the data row. The following command enables up to bytes of LOB data to be stored with the regular row data in a table called employee :. Notice that the value is in bytes, not characters. If the data in the LOB field is more than the specified maximum, the row holds the root structure containing pointers to the separate chunks of LOB data. The minimum size of a root structure is 24 bytes, and the possible range of values that text in row can be set to is 24 to 7, bytes.
To determine whether a table has the text in row property enabled, you can inspect the sys. If a 0 is returned, the text in row option is disabled. Now create a table very similar to the one that looks at row structures, but change the varchar column to the text data type. Now use the allocation query to find the basic information for this table and look at the sys. The data page for the in-row data is , and the LOB data is on page The row structure is very similar to the row structure shown in Chapter 6, in Figure , except for the text field itself.
Bytes 21 to 36 are the byte text pointer, and you can see the value starting at offset When the bytes are reversed, it becomes 0x, or decimal, which is the page containing the text data, as you saw in the output in Listing Figure A row containing a text pointer. You have to update the text value to actually force the data movement:.
If you turn off text in row , the LOB data moves immediately back onto its own pages, so you must be sure not to turn this off for a large table during heavy operations. A final issue when working with LOB data and the text in row option is dealing with the situation in which text in row is enabled but the LOB is longer than the maximum configured length for some rows. However, setting the limit to a smaller value is different from disabling the option in two ways. First, some of the rows might still have LOB data that is under the limit, and for those rows, the LOB data is stored completely in the data row.
A root structure is at least 24 bytes long which is why 24 is the minimum size for the text in row limit , and the meaning of the bytes is similar to the meaning of the 24 bytes in the row-overflow pointer.
The main difference is that no length is stored in bytes 12— Instead, bytes 12—23 constitute a link to a chunk of LOB data on a separate page. If multiple LOB chucks are accessed via the root, multiple sets of 12 bytes can be here, each pointing to LOB data on a separate page.
As indicated earlier, when you first enable text in row , no data movement occurs until the text data is actually updated. You must update the actual LOB data first. Also, if a variable-length column needs to grow, it might push LOB data off the page so as not to exceed the 8,byte limit. Growth of variable-length columns always has priority over storing LOB data in the row. If no variable-length char fields need to grow during an update operation, SQL Server checks for growth of in-row LOB data, in column offset order.
If one LOB needs to grow, others might be pushed off the row. Although large data columns using the LOB data types can be stored and managed very efficiently, using them in your tables can be problematic. Prior to SQL Server , you had to decide whether to limit your columns to a maximum of 8, bytes or to deal with your large data columns by using different operators than you used for your shorter columns. Although this functionality is frequently described by referring only to varchar MAX , the MAX specifier can also be used with nvarchar and varbinary.
You can indicate the MAX specifier instead of an actual size when you use one of these types to define a column, variable, or parameter. In general, if the actual length is 8, bytes or less, the value is treated as though it were one of the regular variable-length data types, including possibly overflowing onto row-overflow pages. If the actual length is greater than 8, bytes, SQL Server stores and treats the value exactly as though it were text, ntext , or image.
Because variable-length columns with the MAX specifier are treated either as regular variable-length columns or as LOB columns, no special discussion of their storage is needed. By using the MAX specifier, however, you are indicating that the maximum size should be the maximum the system supports. Because the MAX data types can store LOB data as well as regular row data, you are recommended to use these data types in future development in place of the text, ntext , or image types, which Microsoft has indicated will be removed in a future version.
In the storage engine, each LOB column is broken into fragments of a maximum size of 8, bytes each. This eBook includes the following formats, accessible from your Account page after purchase:. EPUB The open industry format known for its reflowable content and usability on supported mobile devices.
PDF The popular standard, which reproduces the look and layout of the printed page. This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours. Add to cart. About eBook formats. Discover how to: Dig into SQL Server architecture and configuration Use the right recovery model and control transaction logging Reduce query execution time through proper index design Track events, from triggers to the Extended Event Engine Examine internal structures with database console commands Transcend row-size limitations with special storage capabilities Choose the right transaction isolation level and concurrency model Take control over query plan caching and reuse.
Download the sample content. We've made every effort to ensure the accuracy of this book and its companion content. Any errors that have been confirmed since this book was published can be downloaded below.
Download the errata. If you find an error, you can report it to us through our Submit errata page.
0コメント