Query Cost when filtering with varchar vs nvarchar Recently the application I'm working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the predicate of the query. Apparently the data type of the column is varchar, but the query is passing in nvarchar, hence the warning.
Out of curiosity, I try to see how much difference would passing the correct data type improves query performance by checking the execution plan. I got the following result:

To my surprise, when I'm using the matching data type varchar, the query cost is double compare to using nvarchar, even though it seems like the second query is not using the most efficient execution plan.
So is the relative query cost to be trusted, or is there something I missed?
Here's the execution plan.
If even the actual execution plan is not reliable, what is a better way to compare the execution cost of 2 queries?
This is due to a Cardinality Estimate issue. Even though your first execution plan (when filtering on a VARCHAR) estimates returning more rows (when you hover over the clustered index seek operation), it's the correct estimate that matches your actual number of rows returned.
The second execution plan (when filtering on NVARCHAR) under-estimates the number of rows it believes will be returned which results in a less optimal execution plan, but even worse, it'll request less server resources (memory and CPU) than it actually needs to be allocated to process the query. This will cause the query to run slower since effectively the actual number of rows being returned is the same in both cases (same amount of data) but the query optimizer estimates only half the number of rows are being returned than what actually is.
The under-estimation of the number of rows being returned is due to the IMPLICIT_CONVERSION Warning which you can see if you hover over the SELECT operator of the second execution plan. This generally happens when the data types in your JOIN, WHERE, or HAVING clauses are of different data types than the fields they're being compared against, forcing the SQL engine to perform a cast operation. This messes with the statistics the server stores about the cardinality of the fields being compared to in the predicates, and ultimately results in a cardinality estimate issue such as the one you're currently facing.