Performance issues in parameterized queries accessing a view in SQL Server

I am currently facing an issue with parameterized queries in SQL Server that I do not understand where it is rooted in. I broke it down to a simple example: Let's assume a table that holds data about some child entity as well as the parent_id and a corresponding index on the parent_id. The data is accessed based on this parent_id but through a view that, additionally to the table data, holds a column calculating a row_number over all entries partitioned by the parent_id. ### Reproducable setup Create the table, index and view as follows:

CREATE TABLE dbo.test (id BIGINT IDENTITY(1,1), text NVARCHAR(255), parent_id BIGINT);
GO

CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id);
GO

CREATE VIEW dbo.test_view
AS
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) AS row_num
  FROM dbo.test
GO

Now get some data into the table:

DECLARE @i BIGINT = 0
WHILE @i < 200000
BEGIN
    SET @i = @i + 1
    INSERT INTO dbo.test (text, parent_id)
    VALUES ('test 1', @i), ('test 2', @i), ('test 3', @i);
END

### The issue When accessing the data through a parameterized query from the view the SQL Server will do a full scan on the table.

DECLARE @parent_id BIGINT = 123456
SELECT *
FROM dbo.test_view
WHERE parent_id = @parent_id

While when accessing the data directly (without using a parameter) we will get the expected index seek.

SELECT *
FROM dbo.test_view
WHERE parent_id = 123456

### What I have tried Searching different forums, I do not really understand what is happening here. I have found similar issues where the parameter had the wrong data type and thus performance was bad, but this is not an issue in my case. I also read about issues with parameter sniffing but I neither think this is a problem here as I do not access data through stored procedures or functions. Also, when I am accessing the data directly from the table with a parameterized query the issue will not occur. An index seek is done even with the parameters. Same happens when I add the OPTION (RECOMPILE) to the query accessing the view with a parameterized query the SQL Server will end up doing an index seek. ### Question Can someone explain what the issue is here? How come that this is an issue for the view but not for the table itself? Do I really need to get rid of the view calculating this row_number differently during inserts/deletes? ### Setup - SQL Server 2022 v16.0.4165 running in a docker container - Docker image: mcr.microsoft.com/mssql/server:2022-latest The real table has a primary key of course. But it also has a lot more columns then only the text column. Including all of these columns in the index would be a possibility. The issue though is not occurring when selecting from the table itself, so it seems not to be an issue of the index to me. I was not aware that I am running the database in a compatibility mode. In the productive environment I am even getting CardinalityEstimationModelVersion="140". I do not think that I have set it up anywhere on purpose. ### Execution plans - direct select with index seek - parameterized select with full table scan - Full table scan with QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150