Wednesday, March 07, 2007

Max limit of Varchar, nvarchar, varbinary datatypes ...

As you would be knowing by now in SQL Server 2005 they have enhanced varchar, nvarchar & varbinary as varchar(max), nvarchar(max) & varbinary(max). So now they can hold upto 2^31-1 bytes (it would come to approx 2 GB).

That said, don't think that just because MAX has been introduced you can declare those datatypes for any arbitary number. If you want to specify a limit yourself for those datatype still the maximum is 8000 characters. When you think that you need to store content whose lenght would be more than 8000 characters then you need to declare it as MAX.

For example, Try executing the below code snippet

Create table testVarchar
(
fName varchar(8001)
)
Go

It would throw an error something like this: The size (8001) given to the column 'fName' exceeds the maximum allowed for any data type (8000).

1 comment:

Anonymous said...

Thanks for the information, but does it allocate memory dynamically of required size...if i use MAX ?