Question:

Explain varchar(MAX), nvarchar(MAX) and varbinary(MAX) in SQL Server 2005.

Answer:

In SQL Server 2000 (and former versions as well), it was needed to use text, ntext or image data types to store huge data since these data types are stored in a collection of 8 KB data pages that are seperate from the data pages that store the other data in the same table.

BLOB is a generic term that refers to text, ntext and image data types.

BLOBs are difficult to maintain. They are unable to be used with string functions like REPLACE, SUBSTRING and CHARINDEX. READTEXT, WRITETEXT and UPDATETEXT have to be used for the similar purposes. BLOBs can't be used as variables in a procedure or a function either.

To avoid maintenance difficulty with BLOBs, SQL Server 2005 introduced varchar(MAX), nvarchar(MAX) and varbinary(MAX). These data types can hold the same amount of data that BLOBs can hold, which is 2 GB; and they are stored in the same type of data pages used for other data types.

When data in a MAX data type exceeds 8 KB, an over-flow page is used and SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types.

You can declare variables of MAX data types inside a stored procedure or function, and even pass them as variables. You can also use them inside the string functions.

BLOBs will be removed in the future versions of Microsoft SQL Server.