I heard from a Developer that when we create a regular View, SQL server will cache it's execution plan. And that using a regular View is actually a performance enhancement for this reason.
Does anyone agree with that or has observed anything of that nature?
My understanding is that regular views are neither positive nor negative in terms of performance. SQL Server will evaluate every query that uses a view on it's own merit. In addition, I've tried clearing the cache and creating a view and I don't see anything in the cache, until I create the query. Then again I haven't used SQL Server 2019 too much so maybe things are different there.
Your understanding (neither positive nor negative) is correct. A view is just storing a query definition as a named object, so that the name can substitute as shorthand for that definition. The query plan is determined by the optimizer, based on the entire query.
Indexed views may affect query plans against tables that are in the indexed view, it depends on the NOEXPAND query hint. That could improve performance, but you'd need to evaluate that specifically. If you're not using indexed views then there's no difference.
There may also be different plans based on simple or forced parameterization, but generally that won't impact a query plan other than reducing the number of different plans that are cached.
2 Likes
Exactly.
Think through this. You can write completely different WHERE conditions on the view, and each of those might generate a different plan. For example, if you write "SELECT * FROM dbo.view_name WHERE start_date >= '20200101'" you will like get a different plan than if you write "SELECT * FROM dbo.view_name WHERE cust_code IN ('A', 'B')".
So the view by itself does not have a plan.
1 Like
Well this actually originated from here: How to Delete Just Some Rows from a Really Big Table: Fast Ordered Deletes - Brent Ozar Unlimited® . Brent Ozar wrote 'The view indeed does something magical.'
And basically our team worships Brent Ozar.
The idea of deleting in chunks to reduce locking/blocking is great. But I tried this with and without the View. And I noticed identical durations and identical Execution Plans! So I brought that back to the team and they were like sorry - we don't accept your 'view' over Ozar's.
Ah yes, the "Brent says do it this way and I'm not going to try alternatives or variations". 
Like, for instance, the CTE option listed in the comments.
As long as you have the correct indexes, adding a view is really not a big deal. It still doesn't cache a query plan for the view. Did anyone on your team notice the word "cache" doesn't appear in that link?
2 Likes
[quote="denis_the_thief, post:4, magical + worships + human person
[/quote]
Have lead to disastrous decisions in humam history 
1 Like
Yeah, that's what I compared it with. No difference.
True. Our discussion let to that.
oh by the way , are you also by any chance have views on linked servers?