I will try them and let you know. I like more the TVF than the function in this case. Right now, view is working fine but I'm facing a new...

No, it's not, because it's not part of a recursive table expression. Somehow it seems that SQL solves that query before and then you can use it.

By the way, this...

No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.

Yes, maybe I didn't understand you completely. I thought you were suggesting to have that outside CTE but as LEFT JOIN in the SELECT FROM CTE.

So, what do you suggest?...

I need it, otherwise I'm getting an aritmethic overflow error, even defined as decimal(38,6)

Sure, here is:

USE [DBMCS_BK]
GO

/****** Object:  View [dbo].[vRecipe]    Script Date: 2/16/2020 9:04:31 PM ******/
DROP VIEW IF EXISTS [dbo].[vRecipe]
GO

/****** Object:  View [dbo].[vRecipe]  ...

Here is the new plan with first option (UNION ALL SELECT NULL, NULL). What's the purpose of the UNION ALL in this case?

https://www.brentozar.com/pastetheplan/?id=S1B7FkdXI

Current view is using your Aggreg_NR CTE. Beside that, I've changed the indexes, that helped a little. I don't like the index spool in current execution plan.

I...

I've replaced a couple of indexes, including some fields in order to avoid key lookups. Now performance looks a bit better, as you can see here:

Still wondering...

-- option 2

or if the aggreg_nr still does not work then replace all the outer applies with

outer apply (select sum(amt) over (order by nr) from dbo.dbdopsk dop where dop.nr =...

Performance is slighty better, however is still slower that UNION ALL version.

Look at SQL Sentry Explorer.

Trying first option with OUTER APPLY. LEFT JOIN is not allowed in the recursive part of a recursive common table expresion.

You cannot use GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expresion. In theory that query looks better, but I...

You cannot use SELECT TOP in a CTE, that's why.

I know I can't rely on those percentages, I used STATISTICS and I was confident with that results.

Here are the views:

CREATE VIEW [dbo].[RECIPE] AS
SELECT 0 as OPNR,...