Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: How do I refresh a view?

    You know how I mentioned before that I was working with a database with a (bunch of) view(s) that are defined like this...
    Code:
    CREATE VIEW MyView AS
    SELECT * FROM ExampleTable
    Well, after altering the underlying table I noticed that the view is not picking up the new columns which leads me to believe I need to refresh the view.

    Is this possible, if so, how?
    SS 2K
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - you don't need to "refesh" the view.

    Are you 100% sure? Double check all your names etc.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I've since stumbled across this (it's amazing how you find the answer so soon after asking for help!).
    Code:
    EXEC sp_refreshview MyView
    Which appears to be doing the trick.
    I ran it on the following test script
    Code:
    USE Northwind
    SET NOCOUNT ON
    GO
    
    IF EXISTS(SELECT NULL FROM sysobjects WHERE name = 'gvTemp') BEGIN
    	DROP TABLE gvTemp
    END
    IF EXISTS(SELECT NULL FROM sysobjects WHERE name = 'gvView') BEGIN
    	DROP VIEW gvView
    END
    
    CREATE TABLE gvTemp (test int)
    GO
    CREATE VIEW gvView AS
    SELECT * FROM gvTemp
    GO
    
    INSERT INTO gvTemp(test) VALUES(1)
    INSERT INTO gvTemp(test) VALUES(2)
    INSERT INTO gvTemp(test) VALUES(3)
    INSERT INTO gvTemp(test) VALUES(4)
    INSERT INTO gvTemp(test) VALUES(5)
    
    GO
    ALTER TABLE gvTemp
    ADD test2 varchar(10)
    GO
    
    UPDATE gvTemp
    SET test2 = 'Test ' + Convert(varchar, test)
    
    SELECT * FROM gvView
    EXEC sp_refreshview 'gvView'
    SELECT * FROM gvView
    
    SET NOCOUNT OFF
    DROP VIEW gvView
    DROP TABLE gvTemp
    Try it for yourself
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Apols - I thought the add column would mark the table for recompilation. It does not.

    You need to recompile the view.
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

    Don't understand - recompile does not work. Refresh does though.

    Code:
    
    USE northwind
    go
     
    CREATE TABLE dbo.delete_me
    (
    col_a VARCHAR(1)
    )
    INSERT dbo.delete_me
    SELECT 'a'
    go
     
    CREATEVIEW dbo.delete_me_too
    AS
    SELECT *
    FROM dbo.delete_me
    go
     
    SELECT *
    FROM dbo.delete_me_too
    ALTER TABLE dbo.delete_me
    ADD col_b VARCHAR(1)
    go
    EXEC sp_recompile @objname ='delete_me_too'
    --EXEC sp_refreshview 'delete_me_too'
    SELECT *
    FROM dbo.delete_me_too
     
    DROPVIEW dbo.delete_me_too
    DROPTABLE dbo.delete_me
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    best not to use select * in the view definition at all. use a column list.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh heh - that has been covered. Inherited design and all that.

    Jesse - do you know why recompilation is insufficient to get the full definition reflected in the view?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Recompiling wont work because when you're recompiling the same problem. I believe that views store the column headers (so the use of SELECT * actually means the column headings that exist at this point in time) so recompiling just repeats the process...

    meta data - is that the right terminology to be using here?
    George
    Home | Blog

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by msdn2
    Updates the metadata for the specified non-schemabound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
    I think that about covers it
    Thanks peoples
    George
    Home | Blog

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    yes, it's related to the WITH SCHEMABINDING clause as I recall - if you specify it when creating a view you can't modify the underlying table like this. probably a good idea to use WITH SCHEMABINDING if you are forced to use select * in a view.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Still don't get why recomile isn't enough. Surely the structure of the underlying table is relevent to getting the optimum execution plan for flips sake. I appreciate that it isn't.... but it should be.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I see this being the one single "advantage" of using SELECT * in views... You can prat about with the underlyign table to your hearts content and thee view remains unaffected until you refresh it.

    But then again - that's what test environments are for.
    George
    Home | Blog

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    not if you remove a column from the table. i imagine then the select * view would be completely broken.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    But then a benefit is just refreshing the view rather than having to alter it...
    *shrug*
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    George, don't you drink that kool-aid. We will have to remove you from the forums for lack of adherence to best practices, and a general leave of good sense.

Posting Permissions

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