Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: Insight on Views?

    SQL 2005

    Just curious if anyone would have any additional in-depth knowledge as to why this is happening:

    View 1 (v_one): SELECT a, b, c, d FROM tbl

    View 2 (v_two): SELECT * FROM v_one

    If I were to make a column change to 'tbl' and add that column to v_one, then call v_two, the change is not reflected in the output.

    From MSDN: "Each time a view is accessed, its execution plan is recompiled."

    The above said, it would seem intuitive that as long as v_one reflects any changes made to the table, calling v_two should also show those changes. However, they do not.

    This is not pressing by any means, but I thought it was interesting. Ideas?
    www.socialec.com - Social Engineering/NLP

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    From MSDN: "Each time a view is accessed, its execution plan is recompiled."

    Well that isn't true for starters. You got a link?

    Anyhoo - this is defined in the SQL Standard (only learnt this myself recently). Changes to underlying objects are not cascaded through to views. You need to recreate the view.

  3. #3
    Join Date
    Oct 2007
    Posts
    22
    Link: http://msdn2.microsoft.com/en-us/lib...3(SQL.80).aspx (The "Remarks" section)

    Yeah, I found that by either recreating, or even issuing an ALTER statement on the view, it will update the outer view's results.

    I was just curious as to why this was happening.
    www.socialec.com - Social Engineering/NLP

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This happens because the compiled plan for the view's query uses only ID's to resolve where to get data from. If you add a column, that ID is not in the compiled plan. Clearing the whole procedure cache, running sp_recompile on the base table(s), or re-creating the view will clear this. It is far better, however, to not use "select *" in 95% of your code for this, and similar reasons.

  5. #5
    Join Date
    Oct 2007
    Posts
    22
    I totally agree. SELECT * = ack.

    Unforunately, this is a small portion of functionality from one of our lovely vendor applications.
    www.socialec.com - Social Engineering/NLP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think that sp_recompile will help a view, it only does executable code like stored procedures and triggers.

    -PatP

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    From BOL:

    When a view is created, information about the view is stored in these catalog views: sys.views, sys.columns, and sys.sql_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.
    This means that when you create view v_two the columns from view v_one is stored in sys.columns. Altering columns in v_one will not affect data in sys.columns for view v_two. You must wither execute a ALTER VIEW on v_two or execute sp_refreshview 'v_two' to refresh the columns.

  9. #9
    Join Date
    Oct 2007
    Posts
    22
    Doing even a more simple test, I removed the second view from the equation.

    The results were, as expected, the same. Adding a column to the table, then running the view with a SELECT * provided no additional information about the new table column. I then actually deleted a column that WAS defined in the view's definition and I got an error when trying to run the view.

    "View or function 'v_one' has more column names specified than columns defined."

    Now I'm curious is Microsoft has a reason for not implementing a fix for this. It wouldn't be hard for the execution of a view to update it's definition prior to actually running the query. However, I'm assuming since this is still 'wrong' in SQL 2005 that there is reasoning behind keeping it the way it is.

    I don't think that sp_recompile will help a view, it only does executable code like stored procedures and triggers.

    -PatP
    You're correct on that.

    Hopefully this thread is a good reminder to all SQL coders new and old to avoid SELECT * at all costs!
    www.socialec.com - Social Engineering/NLP

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Joe_F
    Now I'm curious is Microsoft has a reason for not implementing a fix for this. It wouldn't be hard for the execution of a view to update it's definition prior to actually running the query. However, I'm assuming since this is still 'wrong' in SQL 2005 that there is reasoning behind keeping it the way it is.
    I think you pretty well hit the nail on the head - there will be no fix because it is not recognised as a bug. I need to dig out the thread that said this behaviour is in the ANSI standard....

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Found it. Gotta admit - not an unrefutable source. Pat & Rudy are pretty up on the standard - perhaps they would deign to comment.
    http://www.dbforums.com/showthread.php?t=1621069

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should ask stolze to comment, he knows the standard a lot better than i do

    by the way, i am continually (or is it continuously?) amazed at your magnificent ability to recall and then find old posts on just about any topic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Careful, Pootle. The next post from Rudy may be a request to retype all the "lost posts" from memory. ;-)

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i wouldn't do that

    not least because he can probably do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •