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)
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!