Stack Exchange
SQL query performance degrades when using parameter
This is fast:  
SELECT * 
FROM [Assignable Assessors] 
WHERE ([Certificate Type ID] = 'TAA')

But this is slow:
DECLARE @__targetLevel_4 AS nvarchar(8);
SET @__targetLevel_4 = 'TAA';

SELECT * 
FROM [Assignable Assessors] 
WHERE ([Certificate Type ID] = @__targetLevel_4)

How is that possible?
[Assignable Assessor] is a view that uses contains a lot of "Latest records of a group selects.
[Certificate Type ID] is of type nvarchar(8) and there is no explicit type conversion in the selects.
I'm using Azure SQL database.

Take a look at this https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

Check this https://stackoverflow.com/questions/46852874/date-parameter-causing-clustered-index-scan

Also try with OPTION(RECOMPILE) at the end of your second statement

What about WHERE ([Certificate Type ID] = N'TAA') (notice the N). That is a better representation of the parameterized query as the first query compared against a varchar value and the parameterized query uses an NVarchar. Adding N will make the value nvarchar as well.

What Igor said - the string types in the two queries are different. If the field is varchar instead of nvarchar (Unicode)  the server has to convert the value.  This may force the server to ignore any indexes on the field, if the field has an incompatible collation. How is [Certificate Type ID] defined?

What you're seeing is the ability to use a specific value to look at statistics to create an execution plan when you're using the hard-coded value, 'TAA'. Versus, you're using a local variable (not a parameter) and so instead of looking to the statistics for a specific row count, it uses an average row count. If you put the same code into a stored procedure, where you'll have a parameter instead of a local variable, parameter sniffing (sampling the parameter value and using that against the statistics) will result in a more accurate plan.
To validate all this, capture the execution plans for each of your queries and look at the row counts. Then compare those to the statistics on the column (or index) in question.
