Large-Value datatypes in SQL SERVER 2005


To store more data > 8196 bytes in a column of a SQL Server Table, we used to use text or ntext datatype in SQL SERVER 2000. But, In SQL SERVER 2005, a new specifier is indtroduced. It is called max. This specifier expands the storage capabilities of varchar, nvarchar and varbinary data types. varchar(max), nvarchar(max) and varbinary(max) are collectively known as large-value data types in SQL SERVER 2005. They can store up to  [(2^31)-1] bytes of data.

If we compare, the new datatypes of SQL Server 2005 will map to older SQL Server datatypes as shown in following table:

SQL Server 2005 Types          SQL Server 2000 Types

varchar(max)                         text*

nvarchar(max)                       ntext*

varbinary(max)                      image

Use varchar(max), nvarchar(max), and varbinary(max) data types instead of text, ntext, and image data types becuase they will not be supported in upcoming versions of SQL SERVER.