Menu
The VARCHAR(MAX) data type is similar to the VARCHAR data type in that it supports variable-length character data. VARCHAR(MAX) is different from VARCHAR because it supports character strings up to 2 GB (2,147,483,647 bytes) in length.
The size of the maximum size (m) parameter of a VARCHAR column can range from 1 to 255 bytes. If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. You can store character strings that are shorter, but not longer, than the m value that you specify.
When VARCHAR(MAX) exceeds 8,000 characters, the pointer is stored “in row”, and the string is stored in “LOB” pages.
DO NOT use VARCHAR(MAX) just because it can be. Use it only if the data to be stored can be more than 8,000 bytes.
In this case you use -1.
For those of us who did not see -1 by Michal Chaniewski, the complete line of code:
cmd.Parameters.Add("@blah",SqlDbType.VarChar,-1).Value = "some large text";
The maximum SqlDbType.VarChar size is 2147483647.
If you would use a generic oledb connection instead of sql, I found here there is also a LongVarChar datatype. Its max size is 2147483647.
cmd.Parameters.Add("@blah", OleDbType.LongVarChar, -1).Value = "very big string";
If you do something like this:
cmd.Parameters.Add("@blah",SqlDbType.VarChar).Value = "some large text";
size will be taken from "some large text".Length
This can be problematic when it's an output parameter, you get back no more characters then you put as input.
You do not need to pass the size parameter, just declare Varchar already understands that it is MAX like:
cmd.Parameters.Add("@blah",SqlDbType.VarChar).Value = "some large text";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With