Default string data type should be VARCHAR

[LK]

We have a database with an indexed VARCHAR column, and we noticed significant performance degradation as the size of the table grew. After some digging, we realized this is caused by the fact that the default string data type is NVARCHAR, which has a higher data type precedence than VARCHAR — this causes SQL server to use the following query plan when performing the query:

This happens because SQL Server must cast every value in the index to an NVARCHAR to perform the comparison.

When we changed the type of the parameter to an mssql.VarChar, we saw the expected query plan and query performance was as expected:

Because SQL Server will always convert up from VARCHAR to NVARCHAR (because of data type precedence), it would make sense for the default string type to be VARCHAR to avoid unintentionally triggering this edge case.