Posted by Hugo Kornelis on 06/18/93 11:31

On 7 Nov 2005 16:53:51 -0800, Sue wrote:

>Question:
>My understanding of views is that by simply stating 'select * from
>myviewname where 0=1' that the view is recompiled. If so, what
>advantages are there in using sp_refreshviews?

Hi Sue,

Views are never compiled, much less recompiled.

Views are more like macros then anything else: when you include them in
a query, their name is replaced by their definition before the query
optizer starts to think about the bests way to execute the query.

If you execute
select * from myviewname where 0=1
Then the optimizer will "see" this:
select * from (insert view-definition here) AS myviewname where 0=1"


The goal of sp_refreshview is not to recompile, but to update the
meta-data of a view. This is only necessary for views that were created
with SELECT * (which should be avoided anyway!), and only if the
underlying tables have been changed since the view was created or
refreshed.

Here's an example of where you would use sp_refreshview:

CREATE TABLE Test
(A char(1) NOT NULL,
C char(1) NOT NULL
)
go
CREATE VIEW GoodView
AS
SELECT A, C FROM Test
go
CREATE VIEW BadView
AS
SELECT * FROM Test
go
INSERT INTO Test (A, C)
SELECT 'A', 'C'
go
PRINT 'Before changing the table'
SELECT * FROM GoodView
SELECT * FROM BadView
go
DROP TABLE Test
go
CREATE TABLE Test
(A char(1) NOT NULL,
B char(1) NOT NULL,
C char(1) NOT NULL
)
go
INSERT INTO Test (A, B, C)
SELECT 'A', 'B', 'C'
go
PRINT 'After changing the table'
SELECT * FROM GoodView
SELECT * FROM BadView
go
exec sp_refreshview GoodView
exec sp_refreshview BadView
go
PRINT 'After refreshing the views'
SELECT * FROM GoodView
SELECT * FROM BadView
go
DROP VIEW BadView
DROP VIEW GoodView
go
DROP TABLE Test
go


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]