This chapter is from the book
This chapter is from the book
This chapter is from the book
Large Value Data Types
In SQL Server 2000 (and 7) the maximum size for VARCHAR and VARBINARY was 8,000 and for NVARCHAR 4,000. If you had data that potentially exceeded that size, you needed to use the TEXT, NTEXT, or IMAGE data types (known as Large Object data types, or LOBs). This was always a hassle because they were hard to work with, in both retrieval and action statements.
This situation changes in SQL Server 2005 with the introduction of the MAX specifier. This specifier allows storage of up to 231 bytes of data, and for Unicode it is 230 bytes. When you use the VARCHAR(MAX) or NVARCHAR(MAX) data type, the data is stored as character strings, whereas for VARBINARY(MAX) it is stored as bytes. These three data types are commonly known as Large Value data types. The following code shows the use of these data types in action.
CREATE TABLE largeValues ( lVarchar VARCHAR(MAX), lnVarchar NVARCHAR(MAX), lVarbinary VARBINARY(MAX) )
We mentioned earlier that LOBs are hard to work with. Additionally, they cannot, for example, be used as variables in a procedure or a function. The Large Value data types do not have these restrictions, as we can see in the following code snippet, which shows a Large Value data type being a parameter in a function. It also shows how the data type can be concatenated.
CREATE FUNCTION dovmax(@in VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN — supports concatenation RETURN @in + '12345' END
SQL Server's string handling functions can be used on VARCHAR(MAX) and NVARCHAR(MAX) columns. So instead of having to read in the whole amount of data, SUBSTRING can be used. By storing the data as character strings (or bytes), the Large Value data types are similar in behavior to their smaller counterparts VARCHAR, NVARCHAR, and VARBINARY, and offer a consistent programming model. Using the Large Value data types instead of LOBs is recommended; in fact, the LOBs are being deprecated.
When we first came across the enhanced size of the VARCHAR data type in SQL Server 7 (from 256 to 8,000), we thought, "Great, we can now have a table with several VARCHAR columns with the size of 8,000 instead of a text column." You probably know that this doesn't work, because in SQL Server 7 and 2000, you cannot have a row exceeding 8,060 bytes, the size of a page. In SQL Server 2005 this has changed as well, and a row can now span several pages.