February 25, 2015 at 7:46 am
Hi and welcome to the forums. I see what you are trying to do here but I would suggest that you should avoid nesting views like the plague. The challenge you are running into here is the same challenge the sql engine runs into when generating execution plans. In other words, when you have a view calling a view you are highly likely to get poor execution plans.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2015 at 11:16 am
Sean Lange (2/25/2015)
Hi and welcome to the forums. I see what you are trying to do here but I would suggest that you should avoid nesting views like the plague. The challenge you are running into here is the same challenge the sql engine runs into when generating execution plans. In other words, when you have a view calling a view you are highly likely to get poor execution plans.
This is news to me! (Don't get me wrong, I learn new stuff every day, so I'm not disagreeing with you, just a follow up question).
So why would SQL Server have difficulty creating a plan using nested views as opposed to say derived tables in subqueries? Obviously I could think of special cases, like "VIEW_METADATA", "SCHEMABINDING" or other options, but I'm interested in how views that are essentially just saved queries could hide information from the query optimizer.
February 25, 2015 at 12:46 pm
patrickmcginnis59 10839 (2/25/2015)
Sean Lange (2/25/2015)
Hi and welcome to the forums. I see what you are trying to do here but I would suggest that you should avoid nesting views like the plague. The challenge you are running into here is the same challenge the sql engine runs into when generating execution plans. In other words, when you have a view calling a view you are highly likely to get poor execution plans.This is news to me! (Don't get me wrong, I learn new stuff every day, so I'm not disagreeing with you, just a follow up question).
So why would SQL Server have difficulty creating a plan using nested views as opposed to say derived tables in subqueries? Obviously I could think of special cases, like "VIEW_METADATA", "SCHEMABINDING" or other options, but I'm interested in how views that are essentially just saved queries could hide information from the query optimizer.
Here is one article from Grant about this. http://www.scarydba.com/tag/nested-view/%5B/url%5D
This one has links to several other articles discussing what happens performance wise with nested views. http://stackoverflow.com/questions/5913995/sql-server-2008-nesting-views
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 25, 2015 at 6:31 pm
It is an existing system with +- 1000 tables and +-250 complex views. To change the views to not link in other views is not worth the effort. The reason why I need the "REAL" table / column names of the selection in the views is so that I can apply "other business logic" that is linked to the "actual database field" in our database objects. I know it is not the best practice to link views inside views, but in our situation that is something we can't change.
I know that the execution path of these views is not perfect, but we cannot change that at the moment.
We have field level security that is applied on table column level, and if it is used in a view it must be applied there as well. If it is a concatenation of fields we can return NULL for that field or the AliasName so that we can apply security manually for that Alias name in the VIEW. And maybe an extra column that indicate that this alias is NotLinkedToTableColumn so that I know how to treat the security
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply