Feature Post

Top

C# data type corresponding to VARCHAR(MAX)

I stumbled into size limitations while specifying VARCHAR(MAX) in C# code.

Following two options can be the C# alternative to VARCHAR(MAX) or NVARCHAR(MAX) that I have tried for SQL Server 2005.

Lets go step by step and verify as well.

STEP 1: Create a table

CREATE TABLE TBL_MAXTEST (LARGE_DATA VARCHAR(MAX))

STEP 2: C# Code
Run the following locs.

2a) Set parameter size equal to minus one (-1)
//Create a brand new object
SqlParameter theParam = new SqlParameter();

//Fill in the public properties
theParam.ParameterName = "@LARGE_DATA";
theParam.SqlDbType = SqlDbType.VarChar;
theParam.Size = -1;
theParam.Value = strLargeData;
theParam.Direction = ParameterDirection.Input;

//Add and enjoy!
cmdSQL.Parameters.Add(theParam);


2b) Or donot specify any column size.
By default the length is assumed to be -1;




STEP 3: Verify data length
Verify the length of the data.

SELECT DATALENGTH(LARGE_DATA) FROM TBL_MAXTEST

The above shall give the length of the data. I have tried it with 7999 bytes, 8000, 8001, and 10000 bytes.

Basically, VARCHAR and NVARCHAR comes in Large-Value Data Types. NVARCHAR supports Unicode characters which means a wider "range" of characters/code pages. And since its Unicode therefore a single character is stored on two bytes; which inherently means NVARCHAR is twice the size of VARCHAR.

While VARCHAR stores one char per byte; and the problem with data types that use one byte to encode each character is that the data type can only represent 256 different characters.

The following table shows the relationship between the large -value data types and their counterparts from earlier versions of SQL Server.

Large-value data types Earlier versions (SQL Server 2000/etc)
varchar(max) text*

nvarchar(max) ntext*

varbinary(max) image



Note that, TEXT, NTEXT, and IMAGE data types are now a deprecated data types.

  • Use char when the sizes of the column data entries are consistent/same.
  • Use varchar when the sizes of the column data entries vary considerably; that is the max and minimum amount of length is known.
  • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
A table can contain a maximum of 8,060 bytes per row; but this restriction does not apply to varchar(max), nvarchar(max), varbinary(max), text, image, or xml columns.

You can use the large-value data types, i.e. the MAX value, to store up to 2^31-1 bytes of data; precisely 2147483648 – 1 bytes; approximately 2 GB.