September 28, 2010 at 11:50 am
Why do we use the SP - sp_refreshview ? The view is updated as the table is updated.
September 28, 2010 at 11:54 am
I'm staring at a case for the need right now: The dependency broke across a linked server.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 28, 2010 at 12:02 pm
Thanks for the reply.
I guess, I did not follow your answer.
I just created view on one server from a table on other server and it gets updated.
September 28, 2010 at 12:16 pm
Schema changes
The data that a view shows changes as the table's data changes, because the view is just a stored select statement. If the table's schema changes in such a way that it will affect the view and the view is not schema bound, this will need to be run or the view will not reflect the metadata changes.
Quick demo:
create table test (
id int,
somecol varchar(10)
)
go
insert into test values (1,'abc')
go
create view testview AS
select * from test
go
alter table test add col2 datetime
go
select * from testview -- still shows only two columns
go
exec sp_refreshview 'testview'
go
select * from testview -- now shows all three columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2010 at 12:23 pm
Thanks Gail.
May I ask another question, based on your answer.
When we use 'SELECT *' in our query, then this is helpful. Say that I specify columnNames in my query then will it make a difference ? (I mean that the schema changes, will not make a difference to my view, since I am using column Names)
September 28, 2010 at 12:35 pm
Yes, if you drop columns. Refreshview will throw an error if the view is no longer valid. Better to find out then than at a later date when someone queries the view and it fails.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2010 at 12:45 pm
Thank you 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply