locked
SQL Nested Views RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    What is the recommendation for using nested views. We have a legacy system and would like to clean up our SQL DB and would like to know that. What level of nested views is recommended and have less performance impact?

    Friday, June 26, 2020 3:08 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Views don't really have a performance impact.  They are basically "string macros" as if you typed them yourself.  They are not precompiled query plans.

    However, views which do too much or repeatedly join the same tables can cause issues.  It is better in SQL 2016+, but can still be a problem.

    Friday, June 26, 2020 8:03 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote
    I agree with Tom. The risk is that you bring in tables that aren't really necessary when using views. And perhaps get rid of duplicates (that wouldn't occurred without those unnecessary tables) using DISTINCT. The more (and deeper) you use views, the higher risk.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Saturday, June 27, 2020 12:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    hi

    Actually nested view is not good for the performances point of view  .I did not test, but it is 32 levels 

    Thanks and Regards

    Laxmidhar sahoo

    Saturday, June 27, 2020 1:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote


    Actually nested view is not good for the performances point of view  .I did not test, but it is 32 levels 


    I'm sorry, but that is a too simplified statement. If you end up with the same plan (as you would without the views), then the performance will be pretty much the same.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Saturday, June 27, 2020 1:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Avik,

    Due to some negative points about nested views, it is not strongly recommended to use it in production environment.

    One recommendation for using nested views is to list all necessary columns in SELECT statement instead of using * in case any new column is added in the origial view.

    Compared with nested views, CTE, temporary table or Table Variable is a better choice in some situations.

    You could also refer below links for some articles about nested views performance.

    Nested View Performance

    Is nested view a good database design?

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 29, 2020 6:13 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    I seem to recall Grant Fritchey saying that query optimization gives up in nested view after a certain depth (2?). I suggest searching his slidedecks

    jchang

    Tuesday, June 30, 2020 12:39 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi jchang,

    Did you mention below article by Grant Fritchey?

    The Seven Sins against TSQL Performance

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Proposed as answer by Naomi NEditor Tuesday, June 30, 2020 9:39 PM
    Tuesday, June 30, 2020 12:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I seem to recall Grant Fritchey saying that query optimization gives up in nested view after a certain depth (2?). I suggest searching his slidedecks

    No, that is not possible. For the simple reason that the optimizer does not know about these views. It only sees the expanded query.

    But as pointed out by others, nesting views can easily lead to that the same table apparing multiple times in the expanded query, when a single instance would have been enough.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 30, 2020 9:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I seem to recall that's Grant said in one of his SQL Saturday sessions, I don't recall if it was in his slide deck. Someone could send him an email, to ask if he was certain, considering Erland's statement below

    jchang

    Tuesday, June 30, 2020 9:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    BTW, I quickly reviewed that article (and the last part) and seems like the article was not printed in full or something is missing in that last part of it.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    Tuesday, June 30, 2020 10:08 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Avik,

    You could use sys.dm_sql_referenced_entities to return all user-defined entities that are referenced.

    Since the maximum nesting which SQL Server allows for views is 32 levels, it is better for you to use nested views as less as possible due to performance impact.

    You could consider to use CTE instead.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

     

    Wednesday, July 1, 2020 2:35 AM