Let’s start with the example. Create table, populate it and create an index on varchar column.

Next, let’s run 2 selects. First one with varchar parameter. Plan is perfect.

Second, let’s use unicode parameter.

Oops – index scan. It does not look like the big deal – who is going to use unicode for varchar field search. But..

We deal with the client applications. Unfortunately, modern development languages implement String as the unicode. And if you don’t specify the parameter type, it would be unicode.

Let’s take a look:

As you can see, .Net application defines @UntypedParam as nvarchar(5). And it produces 21 times more reads. Another thing worth to mention – the size of the parameter matches the string length. As result, if next statement uses “100” as the parameter value, it would be nvarchar(3) – recompilation, potentially additional plan in the cache and so on.

Keep that in mind – always specify parameter type and size at the client application.

Script is available for download here. Source code is available here