Super Sizing Columns in SQL Server

Fast Food is no longer the only thing that comes with a Super Size option.  Since SQL Server 2005, columns can also be Super Sized due to the introduction of the MAX Specifier.  In previous versions of SQL Server, if an application allowed for the storage of string data that would exceed 8000 bytes, the only option available was to use the TEXT or NTEXT data type. By using either one of these data types, common operators were unable to be used, meaning that tasks such as searching and updating data was a complex process.  With the introduction of the MAX Specifier it is now possible to work with large objects in SQL Server in ways that were previously not possible.

With the introduction of the MAX Specifier there is no longer a need to perform the complex manipulation of large objects that requires the use of the TEXTPTR operator to determine a the pointer to the value before using a set of specialized commands.  The following examples show the complexity involved with working with the TEXT and NTEXT data types as a result of having to use a different set of operators.  The example below illustrates how to find the first 10 characters for the pr_info column in the pub_info table:

DECLARE @ptrval varbinary(16);

SELECT @ptrval = TEXTPTR(pr_info)
FROM  pubs.dbo.pub_info
WHERE pub_id = '0736'

READTEXT pub_info.pr_info @ptrval 0 10;

Whereas the following example illustrates how to update the first 10 characters for the pr_info column which is a TEXT data type:

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info)
FROM pubs.dbo.pub_info
WHERE pub_id = '0736'

UPDATETEXT pub_info.pr_info @ptrval 0 4 'This'

The introduction of the MAX Specifier in SQL Server 2005 provides the ability for variable length columns that previously were limited to 8000 bytes to store large amounts of data.  The introduction also means that the NTEXT, TEXT and IMAGE data types are candidates for not being supported in future versions of SQL Server.  Hence, it is recommended that the MAX Specifier is used for the storage of large amounts of data.