sp_refreshview

Question

Post reply

sp_refreshview

touchmeknot

SSCommitted

Points: 1769
More actions
September 28, 2010 at 11:50 am

#238915

Why do we use the SP - sp_refreshview ? The view is updated as the table is updated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

I'm staring at a case for the need right now: The dependency broke across a linked server.

- Craig Farrell

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

touchmeknot SSCommitted Points: 1769 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

touchmeknot SSCommitted Points: 1769 More actions · Answer 4

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

touchmeknot SSCommitted Points: 1769 More actions · Answer 6

touchmeknot

SSCommitted

Points: 1769

September 28, 2010 at 12:45 pm

#1228610

Thank you 🙂