What size do you use for varchar(MAX) in your parameter declaration?

Question

I normally set my column size when creating a parameter in ADO.NET.

But what size do I use if the column is of type VARCHAR(MAX)?

cmd.Parameters.Add("@blah", SqlDbType.VarChar, ?????).Value = blah;

score 335 · Accepted Answer · answered Jun 10 '09 at 01:11

335

In this case you use -1.

answered Jun 10 '09 at 01:11

7

is there any performance drawback to setting all parameter lengths to -1, so I dont have to maintain a db-matching list? – Andrew Bullock Jul 23 '14 at 16:02
1

Varchar(max) is treated identically to varchar(8000) for values less than 8000 bytes. For larger values the field is treated as a "text" field (aka a "CLOB"). This can affect query plan optimization and the efficiency of retrieving rows with larger values in this column, as the data is stored "out-of-row" requiring an extra lookup. – KeithS Mar 31 '15 at 17:55
Use nvarchar(max) in sql and define length -1 with SqlDbType.NVarchar in c# – Romil Kumar Jain Jun 30 '16 at 09:23
If it wasn't for the answer by Sam Meshesha below - I would have missed your answer. Your answer might get more votes if you put a sample line of code formatted as code. – qxotk Apr 02 '20 at 17:04

score 71 · Answer 2 · edited Apr 18 '13 at 15:45

71

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";

edited Apr 18 '13 at 15:45

Lennart

answered Apr 18 '13 at 15:27

Sam Meshesha

Eric Draven · Answer 3 · 2016-10-05T14:41:52.730

6

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";

edited Oct 05 '16 at 14:41

answered Oct 05 '16 at 14:20

Eric Draven

score 2 · Answer 4 · answered Sep 27 '19 at 11:24

2

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.

answered Sep 27 '19 at 11:24

Alberto Tromba

I seem to be getting an exception for an `SqlParameter` whose size I do not set, even though I do assign a value - however, I am not using that overload of `Add`, but create the `SqlParameter` instance myself. Does the overload of `Add` you are using already initialize `Size` to something maybe? – O. R. Mapper Oct 29 '20 at 08:31
This is bad because it breaks the query plan optimizations. Having the same parameter with different sizes makes impossible to reuse the cached informations for the same query text forcing Sql Server to recalculate the execution plan. see also the comments on the Igor Macedo's answer – Steve Nov 15 '22 at 10:16

score 0 · Answer 5 · answered May 16 '17 at 13:47

0

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";

answered May 16 '17 at 13:47

Igor Macedo

5

This can have negative effects on your SQL server due to how the Execution Plan is calculated. – yaakov Aug 11 '19 at 06:08
I found this not to be the case when using an output parameter. It result in the following error ` Exception:String[2]: the Size property has an invalid size of 0.` To fix this, use Size = -1 see https://stackoverflow.com/questions/21087950/how-to-create-nvarcharmax-sqlparameter-in-c – Michael K Oct 28 '19 at 16:42
2

As I have found out today this is actually bad as SQL server then has to recompile the plan each time. In our case it had 60,000 plans cached for a simple INSERT as we did not know this. – MrPurpleStreak Mar 31 '21 at 10:28

5 Answers5