SQL Server 2000 added functionality that lets you decide whether and when you want SQL Server to store small chunks of LOB data on the data pages. You can set the threshold for small at the time you enable this option. You must enable the option for each table separately by using the stored procedure sp_tableoption. The following example enables the text in row option for a table called texttable and lets you store as much as 300 bytes of LOB data in the data row:
EXEC sp_tableoption texttable, 'text in row', 300
Any LOB data in texttable that exceeds 300 bytes is stored outside the data row.
No data movement occurs as an immediate result of enabling the text in row option. However, after you turn on this option, whenever you insert or update a row containing less than 300 bytes of LOB data, SQL Server moves the LOB data from the special pages into the table row. Consequently, a very large table could see a lot of activity. When you disable the text in row option by running the sp_tableoption procedure and supplying a size limit of 0, the movement of LOB data back to external pages happens immediately.
The code in Listing 1 uses a simple table to demonstrate when the data movement occurs. The script builds a table with five columns, including one text column, and populates the table with one row. Running sp_spaceused shows that the table has four pages reserved, with three used for data. After the code enables the text in row option, the space-usage information doesn't change, but when the code updates the row, the report changes to show that only three pages are reserved for the table. Because the text data has moved into the row, the table no longer needs the additional text page. I won't discuss the exact meaning of the values that sp_spaceused shows; what's important is at what point the number of pages changes. Finally, you can see that when the code disables the text in row option, the number of pages in the table immediately increases to four because the data has moved off the row and back onto its own page.
Storing small LOB columns in the data rows can dramatically increase how efficiently SQL Server can manage your data. The more LOB data you can put in the data rows, the greater increase you'll see. However, keep in mind that if you're storing large chunks of LOB data in a data row, the row has less room for other data, so you'll need to limit the number of columns or the size of the data types you use in the other columns.
Remember, when determining how to store your LOB data—whether in separate files, in special pages in the database, or in the data rows themselves—there's no one right answer for everyone in every circumstance. Understanding the way SQL Server manages LOB data can help you make your decision, but there's no substitute for testing the various possibilities to see which method is best for you.
End of Article
Prev. page
1
[2]
next page -->
