Why are `nvarchar` parameters faster than other types for 'text' `SqlCommand` commands?
<h2>Overview</h2>

<p>This question is a more specific version of this one:</p>

<ul>
<li><a href="https://stackoverflow.com/questions/22008319/performance-hit-when-passing-argument-of-c-sharp-type-int64-into-t-sql-bigint-st">sql server - performance hit when passing argument of C# type Int64 into T-SQL bigint stored procedure parameter </a></li>
</ul>

<p>But I've noticed the same performance hit for other data types (and, in fact, in my case I'm not using any <code>bigint</code> types at all).</p>

<p>Here are some other questions that <em>seem</em> like they should cover the answer to this question, but I'm observing the opposite of what they indicate:</p>

<ul>
<li><a href="https://stackoverflow.com/questions/4624811/when-should-sqldbtype-and-size-be-used-when-adding-sqlcommand-parameters">c# - When should "SqlDbType" and "size" be used when adding SqlCommand Parameters? - Stack Overflow</a></li>
<li><a href="https://stackoverflow.com/questions/293311/whats-the-best-method-to-pass-parameters-to-sqlcommand?rq=1">.net - What's the best method to pass parameters to SQLCommand? - Stack Overflow</a></li>
</ul>

<h2>Context</h2>

<p>I've got some C# code for inserting data into a table. The code is itself data-driven in that some other data specifies the target table into which the data should be inserted. So, tho I <em>could</em> use dynamic SQL in a stored procedure, I've opted to generate dynamic SQL in my C# application.</p>

<p>The command text is always the same for row I insert so I generate it once, before inserting any rows. The command text is of the form:</p>

<pre><code>INSERT SomeSchema.TargetTable ( Column1, Column2, Column3, ... )
VALUES ( SomeConstant, @p0, @p1, ... );
</code></pre>

<p>For each insert, I create an array of <code>SqlParameter</code> objects.</p>

<p>For the '<code>nvarchar</code>' behavior, I'm just using the <code>SqlParameter(string parameterName, object value)</code> constructor method, and not setting any other properties explicitly.</p>

<p>For the 'degenerate' behavior, I was using the <code>SqlParameter(string parameterName, SqlDbType dbType)</code> constructor method and also setting the <code>Size</code>, <code>Precision</code>, and <code>Scale</code> properties as appropriate.</p>

<p>For both versions of the code, the value either passed to the constructor method or separately assigned to the <code>Value</code> property has a type of <code>object</code>.</p>

<p>The '<code>nvarchar</code>' version of the code takes about 1-1.5 minutes. The 'degenerate' or 'type-specific' code takes longer than 9 minutes; so 6-9 times slower.</p>

<p>SQL Server Profiler doesn't reveal any obvious culprits. The type-specific code is generating what would seem like better SQL, i.e. a dynamic SQL command whose parameters contain the appropriate data type and type info.</p>

<h2>Hypothesis</h2>

<p>I suspect that, because I'm passing an <code>object</code> type value as the parameter value, the ADO.NET SQL Server client code is casting, converting, or otherwise validating the value before generating and sending the command to SQL Server. I'm surprised tho that the conversion from <code>nvarchar</code> to each of the relevant target table column types that SQL Server must be performing is so much faster than whatever the client code is doing.</p>

<h2>Notes</h2>

<p>I'm aware that <code>SqlBulkCopy</code> is probably the best-performing option for inserting large numbers of rows but I'm more curious why the '<code>nvarchar</code>' case out-performs the 'type-specific' case, and my current code is fast enough as-is given the amount of data it routinely handles.</p>