Why bother setting the size parameter of a command object parameter?

Question

Our data access layer uses command objects to communicate with sql server.

In most cases I've hard-coded the field size (that matches the column size in sql server) into the command param builder.

Such as:

SqlParameter param = new SqlParameter("@name", NVarChar, 4000);

What's the advantage to specifying a value here (4000 in this example) versus just leaving it as 0? It's a pain when you have to recompile when a column size changes.

Answer 1

Is actually quite important. Identical requests issued with different parameter length end up as different queries in the procedure cache. In time, this leads to cache pollution an over-active compilation events. This issue is one of the major design flaws in how both Linq2SQL and the EF providers were implemented, eg. see How Data Access Code Affects Database Performance. Also see Query performance and plan cache issues when parameter length not specified correctly for a related problem.

Answer 2

There is no performance or execution-time advantage - size is inferred if it is not explicitly passed:

The Size is inferred from the value of the dbType parameter if it is not explicitly set in the size parameter.

I guess you could say that by explicitly declaring the size of the parameter to match the size of the field in SQL Server you are better informing readers of your code as to the limits of the data model. How useful that may be is in the eye of the beholder.