There are multiple overloads for sqlcommand.Parameters.Add(...) function. Some of them do not take size, and some do. What sizes are set by default? Is it safe not to set parameter sizes? Does it affect performance?
+1
A:
From SqlParameter.Size Property doco
If not explicitly set, the size is inferred from the actual size of the specified parameter value.
There's an awesome reply by Peter Wone on 293315 that describes more detail what that actually means
Dan F
2009-05-24 12:32:03
Thanks. The link that you provided was very useful.
Roma
2009-05-24 13:12:18
No worries. I learnt a thing or two reading Peter's explanation, so I should thank *you* for asking the question :-)
Dan F
2009-05-24 13:33:38
+1
A:
It is possible to send a parameter that is too large for the database datatype from .Net code to SQL Server. If you set the size in creating the parameter in your .Net code, that will throw an exception if a value is sent that is too large. So you'll know sooner and won't waste the database call.
Alternately, you might want to set a max number of characters for certain text fields. The database might permit last names of 400 characters, but you only want varchar 100.
The parameter Nullable property is similar. It's optional, but if the parameter cannot be null in the sproc call, making it not nullable in your .Net code will highlight the problem sooner.
DOK
2009-05-24 12:32:49
"Alternately, you might want to set a max number of characters for certain text fields. The database might permit last names of 400 characters, but you only want varchar 100." - in this case if you pass a string with more than 100 characters (and less than 400, so that it will be accepted by DB), it'll be clipped and you may lose some data.
Roma
2009-05-24 13:13:45