Implicit Conversion performance hurt and how to solve it

SQL Server will perform Implicit Data Type Conversion to convert values from the original data type to another data type, in order to be able to make a comparison on two expressions of different types, when the query didn't explicitly specified the conversion on either side. SQL Server decides which side to apply the implicit conversion based on the Data Type Precedence which lower type will be converted to upper type. These hidden conversions can hurt performance, especially if it has to apply row-by-row. Mostly it will make the query engine to ignore any useful index and so will do table scanning.

Let's do an experiment to show how the problem happens and the solution of it. Here I use the StackOverflow2013 sample database's User table, but I altered the DisplayName column in it to type varchar, as varchar is a lower precedence data type. Then I created an index on this column. Let's see a query that will perform implicit conversion:
DECLARE @p Nvarchar(40);
SET @p = 'Community';
SELECT * FROM Users WHERE DisplayName = @p;

The input parameter @p is Nvarchar type, which is higher precedence than the column type varchar, so the CONVERT_IMPLICIT function was applied on the table column. In this case, the query is not SARGABLE and so it cannot do index seek, the query performs bad. A yellow band on the left-most root SELECT operator also indicates there's an implicit type conversion warning.

Let's try to solve it by giving the same type to the parameter:
DECLARE @p varchar(40);
SET @p = 'Community';
SELECT * FROM Users WHERE DisplayName = @p;

Now the query becomes sargable and can do index seek.
Another solution is to do an explicit conversion on the scalar value:
DECLARE @p Nvarchar(40);
SET @p = 'Community';
SELECT * FROM Users WHERE DisplayName = CAST(@p AS varchar(40));

It can get the same good result.

Implicit conversions hurt performance, especially when the implicit conversion causes SQL Server to perform it row-by-row on a table. If we found an implicit conversion in our query, the best solution is to put an explicit conversion somewhere else. For instance, if we have a scalar value, rather than allowing SQL Server to convert the column to match the type of the scalar, we can put an explicit conversion to change the scalar to match the type of the column. This allows SQL Server to choose index seek, and carry out the query with fewer overall resources.