MSSQL - Select statement slow on VARCHAR column with index

I recently had an odd experience with a select statement. It was a very simple select statement hitting a unique index on a varchar column. But it was still very slow!

I was using Dapper which I didn't think much about at first. After a while I tried the select directly in management studio, it was not slow there. After a while it hit me, I tried to select it with a unicode string - prefixing the value for the select statement with "N". Bingo! - the SQL now ran slow again. Meaning the SQL that Dapper generates was a unicode string (nvarchar) targetting a ASCII string (varchar) - which creates a conversion with some overhead when running the select statement.

My fix was to make the column NVARCHAR instead of VARCHAR.

Under Ansi Strings and varchar in the dapper documentation. It states that you can use the following syntax to achieve the same without changing the type.

Query<Thing>(
"select * from Thing where Name = @Name", 
new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true }
);