Why does a query slow down drastically if in the WHERE clause a constant is replaced by a parameter (having the same value)?

As Martin suggested in a comment under the question, the problem is that SQL server does not push down properly the predicate from the WHERE clause - see the link in his comment.

I ended up with creating a user defined table-valued function and use it with the CROSS APPLY operator for creating the view.

Let's see the solution itself.

User Defined Table-valued Function

CREATE FUNCTION [dbo].[TestFunction] (@Id INT)RETURNS TABLE ASRETURN (    WITH    Hierarchy (Id,  ParentId, Data, Depth)    AS(    SELECT Id, ParentId, NULL AS Data, 0 AS Depth FROM Test Where Id = @Id    UNION ALL    SELECT h.Id, t.ParentId, COALESCE(h.Data, t.Data), Depth + 1 AS Depth        FROM Hierarchy h            INNER JOIN Test t ON t.Id = h.ParentId    )    SELECT * FROM Hierarchy)

View

CREATE VIEW [dbo].[TestView]ASSELECT t.Id, t.ParentId, f.Data, f.DepthFROM    Test AS t    CROSS APPLY TestFunction(Id) as f

Query with constant

SELECT * FROM TestView WHERE Id = 69

Query with parameter

DECLARE @Id INTSELECT @Id = 69SELECT * FROM TestView WHERE Id = @Id

The query with the parmater executes basically as fast as the query with the constant.

Thank You Martin and for the others as well!

For your second Query try using the OPTIMIZE FOR or OPTION(RECOMPILE) query hint to see if that forces it to recomplile based on the provided parameter value.

You should use a plan guide to freeze the plan you want.