Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

VARCHAR/NVARCHAR (N) vs (MAX)

Jan 12 2008 7:05AM by Jacob Sebastian   

Prior to SQL Server 2005, it was hard dealing with large string values. NVARCHAR and VARCHAR data types had a limitation of 8000 bytes (VARCHAR(8000) and NVARCHAR(4000)). To store large values, most people used TEXT columns and others broke the value into multiple rows. Dealing with TEXT columns  was not easy. You cannot declare a variable of type TEXT. So reading information from a TEXT column was hard. Further, most of the string functions that we use regularly, do not support TEXT data type.

Life became easier with SQL Server 2005, when it introduced VARCHAR(MAX) and NVARCHAR(MAX) data types. VARCHAR(MAX) and NVARCHAR(MAX) can now store values up to 2 GB.

What does that mean? Can I declare a variable or column as VARCHAR(9000)?

The answer is NO. You can either declare a variable as VARCHAR(8000) or VARCHAR(MAX). But none in between. This leads to the question: "How do we restrict the length of the field then"?  This can be achieved by adding a CHECK constraint.

 

-- let us create a table

CREATE TABLE Customers (

    CustomerID INT,

    -- Other fields,

    Notes VARCHAR(MAX))

GO

 

-- let us add the check constraint

ALTER TABLE Customers

ADD CONSTRAINT CustomerNoteLength

CHECK (DATALENGTH(Notes) <= 9000)

GO

 

-- expect an error

INSERT INTO Customers( CustomerID, Notes )

SELECT 1, REPLICATE( 'a', 9001 )

 

/*

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): (1 row(s) affected)

!!!!!! NO ERROR !!!!!!!!

*/

 

Well, we expected an error. But the insert statement did not produce an error. Why? Did SQL Server make a mistake? Why did it accept a value which is longer than 9000 characters?

Let us check the length of the data we just stored:

 

SELECT LEN(Notes) FROM Customers

 

/*

--------------------

8000

*/

Oh...NO! Am I getting crazy?

The column has a restriction of 9000 characters. Our insert statement with 9001 characters successfully ran. The column has 8000 characters stored in it. All the statements conflict with each other.

Here is what happened. The REPLICATE function was expected to produce 9001 characters. But it returned only 8000 characters. All string functions that takes VARCHAR data type assumes the variable to be VARCHAR(8000) unless one of the parameters is VARCHAR(MAX). (in case of NVARCHAR, it is 4000). So, here is how we could generate a string of 9001 characters.

 

INSERT INTO Customers( CustomerID, Notes )

SELECT 3, REPLICATE( CAST('a' AS VARCHAR(MAX)), 9000 )

 

SELECT LEN(Notes) FROM Customers WHERE CustomerID = 3

/*

--------------------

9000

*/

Let us see if the CHECK constraint we created works or not.

 

INSERT INTO Customers( CustomerID, Notes )

SELECT 1, REPLICATE( CAST('a' AS VARCHAR(MAX)), 9001 )

/*

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian): Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CustomerNoteLength". The conflict occurred in database "master", table "dbo.Customers", column 'Notes'.

TOSHIBA-USER\SQL2005(TOSHIBA-USER\Jacob Sebastian):

The statement has been terminated.

*/

Yes, it is working!

Storage of NVARCHAR(MAX)/VARCHAR (MAX) values

VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)  and XML data types are called Large Value Types. SQL Server stores each record in a storage location called a 'data row'. The size of a row is 8060 bytes. When you store a Large Value Type to a column, if the total size of the row (including your Large Value Type) value is less than 8060 bytes, the value is stored "in row". That means the value is stored in the same data row where other values of the same record are stored. If the Large Value Type is larger, it is stored "out of row" which means that the data is stored in another location and a pointer to the stored location will be added in the data row. Reading or writing values "out of the row" will need some additional processing and hence is not as fast as "in row" operations.

VARCHAR(MAX)/NVARCHAR(MAX) columns are internally handled as TEXT columns. Though we can work with them as regular strings, under the covers there is some TEXT processing happening. Another important point to note about VARCHAR(MAX)/NVARCHAR(MAX) columns is that, you cannot create an index on those columns.

 

See Also

Points to remember while Using Large Value Types [VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) and XML]
When should I use large Value Types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))

 

Tags: TSQL, Large Value Types, VARCHAR(MAX),


Jacob Sebastian
1 · 100% · 32225
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"VARCHAR/NVARCHAR (N) vs (MAX)" rated 5 out of 5 by 3 readers
VARCHAR/NVARCHAR (N) vs (MAX) , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]