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.
sinhashubham95
commented
5 months ago
We have a database with an indexed
VARCHARcolumn, 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 isNVARCHAR, which has a higher data type precedence thanVARCHAR— 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
NVARCHARto 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
VARCHARtoNVARCHAR(because of data type precedence), it would make sense for the default string type to beVARCHARto avoid unintentionally triggering this edge case.