Help! I added a new column to a table and my view is not showing this new column (with explanation and resolution)
- by Scott Whigham on January 29, 2009 11:37 AMIt happens to me once a year at a minimum: I forget that, if I add a new column to a table, I must refresh my views otherwise I will not see that column in my views. Maybe you haven't seen this - if not, you might want to follow along by running the scripts embedded below so that you can reproduce this behavior and learn from it! FYI: this article only applies to non-schema-bound views (which is probably wayyyy in the majority for most sites).
Step 1: Create the base table and a supporting "Get"-type of view:
USE tempdb
GO
CREATE TABLE Employee (
EmployeeId INT NOT NULL PRIMARY KEY
, FirstName VARCHAR(128) NOT NULL
, LastName VARCHAR(128) NOT NULL
, City VARCHAR(128) NOT NULL
)
GO
INSERT Employee VALUES(1, 'Scott', 'Whigham', 'Dallas')
INSERT Employee VALUES(2, 'Chad', 'Weaver', 'Dallas')
INSERT Employee VALUES(3, 'Johnny', 'Evans', 'Manchester')
INSERT Employee VALUES(4, 'Robson', 'de Souza', 'Manchester')
GO
CREATE VIEW GetEmployees AS SELECT * FROM Employee
GO
SELECT * FROM GetEmployees
After running the above script, you get a result set showing the four columns: EmployeeId, FirstName, LastName and City. Now let's confuse SQL Server by adding a new column to the table:
ALTER TABLE Employee ADD Country VARCHAR(128)
GO
UPDATE Employee SET Country='USA' WHERE EmployeeId IN (1,2)
UPDATE Employee SET Country='UK' WHERE EmployeeId IN (3,4)
GO
There are now five columns in the original table and our view is a SELECT * query - which means that SQL Server will return all columns in the table...
Right?
Errrrr... not exactly.
Try this:
SELECT * FROM GetEmployees
Notice that you still only get the original four columns that were present when the view was created - you don't even see the new Country column!
Now this is unexpected by a lot of people - you see "SELECT *" and you think, "SQL Server will just return all columns in the table at runtime" but you'd be wrong.
How did this happen?
SQL Server stores what it calls "Persistent metadata" when you create (or alter) your view. You can view this metadata by using either the schema views or the catalog views - take your pick:
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('GetEmployees')
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='GetEmployees'
These views represent the information SQL Server has about the view - we are viewing the columns that SQL Server thinks the view exposes to the caller. When you create or alter a view, this persistent metadata is calculated and it can become out of date very easily (by adding a new column to the base table, for example).
Solution: Update the metadata!
There are a few ways to update SQL Server's metadata:
- Drop and re-create the view
- Very bad - run the risk of losing all permissions!
- Alter the view
- Simply running ALTER VIEW GetEmployees AS SELECT * FROM Employee would actually work!
- Refreshing the view using the stored procedure sp_refreshview
- EXEC sp_refreshview GetEmployees
- My favorite technique
While all three will work, sp_refreshview is the recommended technique.
So there - now you know! And I hope that you don't forget it... Because I forget it at least once a year... And the worst part is that there's a weird thing in SQL Server regarding persistent metadata and altering tables that will sometimes (but not always) change switch around the data in the columns so that the LastName data is in the Country column...
A few common terms to help people find this article
I've seen people ask this question lots of different ways:
- I added a new column to my table and it doesn't show up in my view
- My view does not return all columns in my table
- My SELECT * view isn't returning all of the columns in my table
- Table changed but view shows old table
- My view is switching column data around (this helps those who run into the situation where SQL Server will return the wrong data in the wrong column!)
Learn More on LearnTransactSQL.com
3 Part Video Series
- SQL Server Programming Tutorial: Why You Need to Refresh Your Views After Adding Columns to a Table, Part 1
- SQL Server Programming Tutorial: Why You Need to Refresh Your Views After Adding Columns to a Table, Part 2
- SQL Server Programming Tutorial: Why You Need to Refresh Your Views After Adding Columns to a Table, Part 3
Great article buddy!
Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon.
Odd , your post shows up with a brownish hue to it, what color is the main color on your site?
Thanks for the advice. Will put it to work. Tom
I can't find how to subscribe to the comments via feedburner. I want to keep up to date on this, how do I do that?
I just stumbled on your blog when i was lookng on google. I have to say that the information here was the most complet that I found anywhere. I am definitly bookmarkin this to come bck and read later
It's not in my nature to frequently reply to posts but I've made an exception in this case.
I like the template of your blog. It is very easy on the eye and appealing.
Hey mate, thanks for posting but this page isn't vewable in Safari it is doubled up.
@chels I know what you mean, its hard to find good help these days. People now days just don't have the work ethic they used to have. I mean consider whoever wrote this post, they must have been working hard to write that good and it took a good bit of their time I am sure. I work with people who couldn't write like this if they tried, and getting them to try is hard enough as it is.
Great web page, I just came across it and I’m already a fan. I just shed 30 pounds in 30 days, and I am excited to share my weight loss success with as many people as possible. If I can lose the weight then any one can. No matter what you do, never give up and you WILL attain all your weight loss goals!
Great web page, I just came across it and I’m already a fan. I just shed 30 pounds in 30 days, and I am excited to share my weight loss success with as many people as possible. If I can lose the weight then any one can. No matter what you do, never give up and you WILL attain all your weight loss goals!
Great web page, I just came across it and I’m already a fan. I just shed 30 pounds in 30 days, and I am excited to share my weight loss success with as many people as possible. If I can lose the weight then any one can. No matter what you do, never give up and you WILL attain all your weight loss goals!
Thanks for some quality points there. I am kind of new to the internet , so I printed this off to put in my file, any better way to go about keeping track of it then printing?
Hey mate, thanks for writing but this page doesn't format correctly in Internet Explorer it is showing only half the page.
This is very interesting - thanks for a good read!!!
I am sure that i will come back to your blog soon. Keep us posting interesting articles
Great post!
I think that to receive the mortgage loans from creditors you ought to present a great reason. However, one time I've got a consolidation loan, just because I was willing to buy a bike.
Thanks for sharing this helpful info!
It is so easy to criticize others. SO EASY to post that criticism as a comment. Perhaps he does come across as a bit of a know-it-all sometimes, but I doubt he would actually think that way. Personally, I have learned a lot from just a handful of his videos (I’m a system manager and have worked in IT for 22 years!) and I can easily see how others could get a lot more out of his material than I have.
Excellent job.
I was just doing some web browsing on my Google Phone during my spare time at work , and I came across something I thought was intriguing. It linked over to your site so I came over. I can't really figure out the relevance between your site and the one I came from, but your site good none the less .
Thanks for some quality points there. I am kind of new to web surfing, so I printed this off to put in my file, any better way to go about keeping track of it then printing?
done a lot of anti-counterfeiting
measures,. For example, Rolex has 5 numbers, they are: (1) case models, (2) Watch production sequence number
movement and on the number, (4) the movement and on the production sequence number, (5) the band number. In addition to the
band number in the band discount out surface, the rest are hidden, not to remove the watch strap or open the rear door in order to
see it.
my younger bro is having issues with increase of weight and i want to help out. Your blog is quite very insightful and will recommend this to my coworkers.
Interesting post and I really like your take on the issue. I now have a clear idea on what this matter is all about. Thank you so much.
Should you be fascinated over a link exchange? Please reply with us a message.
This is such a great resource that you are providing and you give it away for free. I enjoy seeing websites that understand the value of providing a prime resource for free. I truly loved reading your post. Thanks!
Good read … headline catchy … good points, some of which I have learned along the way as well (humility, grace, layoff the controversial stuff). Will share with my colleagues at work as we begin blogging from a corporate perspective. Thanks!
I saw your site on Google, I will come back to visit and refer my friends.
I saw your site on Bing, I will come back to visit and refer my friends.
now this? Is actually this method in creative commons?
Intriguing , I am curious what the statistics are on your first point there...
Good read … headline catchy … good points, some of which I have learned along the way as well (humility, grace, layoff the controversial stuff). Will share with my colleagues at work as we begin blogging from a corporate perspective. Thanks!
material? Is this method deep under creative commons?
Hola from Spain! Like your site.
I just sent this post to a bunch of my friends as I agree with most of what you’re saying here and the way you’ve presented it is awesome.
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material.
it is always so hard when it comes to roofing
Which Golf Clubs Are Better - Steel or Graphite ?
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material.
Thanks For This Post, was added to my bookmarks.
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Thank You For This Post, was added to my bookmarks.
Thank You For This Post, was added to my bookmarks.
Hy...state I didn't had a great read from a extended time.Really happy i noticed it on google.I was talking with my friends three days ago around this matter and I have to state you clear out a lot of things.Thanks and good luck
Ever cogitated relatively what if you certainly could achieving your goals invincibly? That very same image has occurred to a lot of people. Some have done it. Some got intimidated by the princely unknowns and never got started. Thanks a lot! Tomas
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material.
Very informative post. Thanks for taking the time to share your view with us.
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
Which golf clubs will be the best for beginner ?
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Awesome Post. I add this Blog to my bookmarks.
Which golf clubs will be the best for beginner ?
Thank You For This Post, was added to my bookmarks.
Should I buy steel golf clubs or graphite ?
Great Post. I add this Post to my bookmarks.
Which Golf Clubs Are Better - Steel or Graphite ?
You certainly have some agreeable opinions and views. Your blog provides a fresh look at the subject.
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Very Interesting Post! Thank You For Thi Post!
Hello. Great job. I did not expect this on a Wednesday. This is a great story. Thanks!
Hello webmaster I like your post about drugs and tratments.
Should I buy steel golf clubs or graphite ?
I just sent this post to a bunch of my friends as I agree with most of what you’re saying here and the way you’ve presented it is awesome.
Very informative post. Thanks for taking the time to share your view with us.
Which golf clubs will be the best for beginner ?
Great Post. I add this Post to my bookmarks.
Very Interesting Information! Thank You For Thi Post!
Thanks For This Blog, was added to my bookmarks.
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material.
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Thank You For This Post, was added to my bookmarks.
Stop Alibris Seller - Condition: Fair - Book selection as BIG as Texas. From the very beginning Sam Stop was an enigma to the people living in Pony, Montana. Stop didn't look like a banker, dress like a banker, or act like a banker. So while there were ....
Very informative post. Thanks for taking the time to share your view with us.
Awesome Blog. I add this Blog to my bookmarks.
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Thank You For This Blog, was added to my bookmarks.
I’ve been visiting your blog for a while now and I always find a gem in your new posts. Thanks for sharing.
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
Thanks For This Post, was added to my bookmarks.
Very informative post. Thanks for taking the time to share your view with us.
Very informative post. Thanks for taking the time to share your view with us.
Should I buy steel golf clubs or graphite ?
Which golf clubs will be the best for beginner ?
I just sent this post to a bunch of my friends as I agree with most of what you’re saying here and the way you’ve presented it is awesome.
I just book marked your blog on Digg and StumbleUpon.I enjoy reading your commentaries.
Should I buy steel golf clubs or graphite ?
Very Interesting Blog! Thank You For Thi Post!
I’ve been visiting your blog for a while now and I always find a gem in your new posts. Thanks for sharing.
Which golf clubs will be the best for beginner ?
I just sent this post to a bunch of my friends as I agree with most of what you’re saying here and the way you’ve presented it is awesome.
Great text and nice site.
Which Golf Clubs Are Better - Steel or Graphite ?
Very informative post. Thanks for taking the time to share your view with us.
Awesome Blog. I add this Blog to my bookmarks.
Thanks For This Post, was added to my bookmarks.
I’ve been visiting your blog for a while now and I always find a gem in your new posts. Thanks for sharing.
I’ve been visiting your blog for a while now and I always find a gem in your new posts. Thanks for sharing.
Great article! Any predictions that you might be able to divulge to explain your second part a small amount further? peace
Native American Jokes LOL
Greetings! I hooked to your content material still , I couldn't request a trackback. Can you proclaim to me personally what's transpiring?
most of
Good Morning, I do not normally post feedback on web sites, as I prefer to read only. However I find the web site that you have created earlier has very insightful information, and I discover it very informational. I was searching on Yahoo 4 goal setting and anger management information & discovered your wonderful article. Could you post something the same unique on set goals accomplish them? Thank you. Carlton Sarchet
Good post, thanks
Great post!