Varchar(N) VS Varchar(Max) from deeper view... Series-I

Prior to SQL 2005, Varchar and NVarchar holds only 8000 bytes, so those days sql programmers used TEXT datatype when their requirement ask to store more than 8000 byte of data.

The problem with this approach was that most of the string function does not work with text datatype. But after the introduction of Varchar(MAX) / NVarchar(MAX) life of programmers became easy to handle long string.

Now the question is how much Varchar(Max) datatype holds, what is the limitation for MA X? The answer is 2GB…

Oh! That nice space J

So can I declare a string variable of length 9000 byte like this Varchar(9000)?

Let’s find out:

create table #tempo

(Id int identity(1,1),

 EmpName varchar(20),

 EmpProfile varchar(9000)

)

Msg 131, Level 15, State 2, Line 5

The size (9000) given to the column 'EmpProfile' exceeds the maximum allowed for any data type (8000).

Answer we found is No; we can’t declare a column with varchar size greater than 8000. You can either declare a variable as VARCHAR(8000) or VARCHAR(MAX). But none in between.

This leads to the question:

If my requirement says to declare field with varchar(9000) size how could I do this?

How do we restrict the length of the field then?

This can be achieved by adding a CHECK constraint.

create table Students (

    student_id int,   

    notes VARCHAR(MAX))

GO

-- then alter the table to add the check constraint

-- which will restrict the the size of notes column

alter table Students

add constraint StudentNoteLength

check (DATALENGTH(notes) <= 9000)

Command(s) completed successfully.

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

The datatype Varchar(MAX), Nvarchar(MAX), Varbinary(MAX)  and XML data types are known as Large Value Types. The maximum size of a data row is 8060 bytes for storing user data. 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.

Coding; Joy what it gives

Post Reference: Vikram Aristocratic Elfin Share