Parameterised queries–don’t use AddWithValue

I’ve just had another run in with the SQL query optimiser. Here is my tale of woe.
I had a very simple parameterised query. Something like this

select * from People where ID10 = @idnumber

However when I looked at the SQL execution plan it looked like this

The thing to notice here is that it is doing an index scan. This made no sense to me since the ID10 column is indexed and so I should be seeing an index seek.

Using SQL profiler confirmed that this query was taking nearly half a second on our production server, which was way too slow.

This is the query as recorded by SQL profiler after being executed by the C# code
exec sp_executesql N'select * from People where ID10=@id', N'@id nvarchar(10)', N'1001010001'

And here is the table

What was strange is that when I executed the SQL without a parameter

  exec sp_executesql N'select id10 from People where ID10=''1001010001'''

I got this execution plan

An index seek, exactly what I wanted. This query took between 1 and 10ms, so more than 400 times faster!

After much searching I finally found the answer;

This parameterised query works perfectly, it uses an index seek

exec sp_executesql N'select id10 from People where ID10=@id', N'@id varchar(10)', '1001010001'

The difference? One letter… This query passes the ID number as a varchar not an nvarchar. Since the index is on an varchar, passing in a nvarchar means that there will be an index scan not a seek. I have no idea why SQL does not first convert to a varchar and then do a scan, but it does not…

The culprit in the C# code was this line
cmd.Parameters.AddWithValue( "id", idNumber )

The AddWithValue forces .net to infer the type you are passing in and since all strings in .net are unicode the parameter is sent as an nvarchar.

Know this, the fix was trivial

cmd.Parameters.Add( "id", SqlDbType.VarChar, 10 ).Value = idNumber,

Here the type and length are specified explicitly so the query is correct and SQL uses an index scan.

So in summary don’t use AddWithValue