Position could be an option. However, it's not enough. I've renamed a column and drop another, in a higher position of the renamed and SQL Compare detected the drop and...

Ok, I will leave the original view then. I have to admit that even when I don't like it, performance is better 🙂

Thanks for all the advices and time, guys!

You can try this link. Both views (vRecipe and Recipe) have 626387 rows: https://www.dropbox.com/s/pgdjg9pel9z2fbc/TEST_DB.zip?dl=0

I will answer Frederico and Lynn. First, I've checked that output is the same, at least in my test database (not the one that I've uploaded, which has only a...

So, from your point of view, in this case it's better to use UNION ALL even when some levels are not used than to use a recursive CTE?. I know...

Yes, but the amount of logical reads is too high and if you compare with the UNION ALL version, you can see the difference.

No, I think that's because I had some problems executing the script for exporting data (it was too huge). In my databases both views produce the same result.

Hi Jonathan, still a lot of logical reads.

I've tried this:

SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT *
FROM dbo.vRecipe

SELECT *
FROM dbo.vRecipe3 -- with indexed view

SELECT * 
FROM dbo.Recipe...

I tried that but, because of RECIPE.ANTL * DOP.ANT I was getting an arithmetic overflow.

Hi,

I've created a small database with data (enough data for test). The link for downloading is this one: https://www.dropbox.com/s/lf20z838x1j9g4o/TEST_DB.zip?dl=0

It's a zip file, about 3,8 MB. Database is SQL 2017.

I had already tried your suggestion, no big differences in fact. For DBRVARE I think it's using a covered index, not sure if I need a new one.

• This reply was modified 4 years, 10 months ago by  Mauricio_ .

These are the statistics when query just one value.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms,...

Hi Lynn, I appreciate your comment, however I don't agree with it :). I really believe that the one with many UNION ALL is more complicated but probably I'm wrong....

Sure. Here are the plans for a given record:

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

And here for all records:

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

Problem is in the amount of logical reads for DBDOPSK and DBRVARE.

SQL Server Execution Times:

CPU time...

That's an interesting alternative. Right now, as I said before, CTE view performs better if you filter for one of the first 2 columns. However, if you just SELECT all...