XML Feeds
sp_refreshview or how to make sure that the view will have the underlying table changes
Got a question about this on our Microsoft SQL Server Programming Forum so you know it is time for a quick blog post.
Did you know that when you create a view and then later change the table the view is not updated?
let me show you what I mean.
Run the following block of code
- CREATE TABLE TestTable (id INT,SomeCol VARCHAR(666))
- GO
- INSERT TestTable VALUES(1,'ABC')
- GO
- SELECT * FROM TestTable
- GO
- CREATE VIEW TestView
- AS
- SELECT * FROM TestTable
- GO
- SELECT * FROM TestView
- GO
Now we will change that table by adding another column
- ALTER TABLE TestTable
- ADD Col2 DATETIME DEFAULT CURRENT_TIMESTAMP
- GO
- INSERT TestTable(id,SomeCol) VALUES(2,'XYZ')
- GO
Now run the selects again
- SELECT * FROM TestTable
- GO
- SELECT * FROM TestView
- GO
See what happened? The TestView does not include the Col2 column. So what can you do? There are at least two things that you can do. You can recreate the view with a create or alter statement or you can use sp_refreshview, run the code below to see how that works
- SP_REFRESHVIEW TestView
- GO
- --All good now
- SELECT * FROM TestView
- GO
- --Clean up this mess--
- DROP VIEW TestView
- GO
- DROP TABLE TestTable
- GO
And yes I know 'real' SQL programmers never use SELECT * and 'real' SQL programmers name their defaults ;-)
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.