Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 203

LargeValueDataTypes
A large value data type will hold values larger 8 KB. With SQL Server 2005 Microsoft has created three
new data types for large values: VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX). These
new types are positioned to replace the older TEXT, NTEXT, and IMAGE types.
The new types are generally easier to work with than their older counterparts. Anything you can do with
a regular varchar, nvarchar, or varbinary column can be done with the max variation.
Storage Considerations
For both the older and newer groups of large value data types it is possible to control where the values
are stored. The data can either be stored in the data row or outside the row. Knowing which option to
use is difficult to determine. To make matters more confusing the default behavior is different for the old
and new types. The old types default to storing the data outside the data row, and the new types default
to storing the data in the data row.
When storing data outside the data row, a two-part scheme is used. First, a 16-byte pointer is stored
with the row on the data page. Second, this pointer redirects SQL Server to additional data pages, which
contain the actual data.
When first creating tables with these data types, it's recommended to start out by storing these values
outside the data row. In order to force Large Value Data Types to be stored outside the data row use the
following commands.
For VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), & XML data types
sp_tableoption ' tablename ', 'large value types out of row', 'ON'
For TEXT, NTEXT, & IMAGE data types
sp_tableoption 'MyTable', 'text in row', 'OFF'
Note that it's not possible to control this on a column-by-column basis. Once you disable in-row storage,
it's disabled for all columns of the same large value data type family. There are two reasons for starting
out this way. First, putting these values outside the data row allows the data row to remain relatively
small, which in turn allows the data page to hold more rows. Second, SQL Server's management of in-row
data can become quite complex. Without going into great detail, SQL Server must handle the possibility
that the data won't fit into a data row. This means that SQL Server has to revert to the two-part scheme
described above. Also, in the event that the data is updated to a shorter value that would fit into the
row, SQL Server will shuffle that data back into the data row. The downside of storing the data outside
the data row is that retrieval performance suffers. The additional overhead of getting the data by using
the pointers can get expensive.
Once a table has been created and is in use consider two aspects when determining where to store large
value data. First, take into account the percentage of rows storing data outside the data row. Second,
consider the likelihood that this data will be queried. Table 8-3 will serve as a guide.
UsingtheUniqueidentifierDataType
Using the uniqueidentifier data type requires a deeper understanding than most other data types. First,
you need a good definition for them. BOL defines the uniqueidentifier as, ''A globally unique identifier