Re: View to drop or not?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/04/04 

Date: Thu, 4 Mar 2004 15:11:51 -0600

This is not true. For example:

create table testView
(
 value int
)
go

create view testViewView
as
select * from testView
go

select * from testViewView
go

Returns:

value
----------

Then add a column and select:

alter table testView add value2 int
go
select * from testViewView
go

Returns:

value
----------

Like Rohtash says sp_refreshview will do it however:

sp_refreshview 'testViewView'
go
select * from testViewView
go

Returns:

value value2
----------- -----------

Generally, it is not considered best practice to use the * construct in
coded objects like procedures, functions, triggers, etc. It is best
practice to code out all of the columns desired. It avoids future problems
because you can add columns and all subordinate code continues to exist. If
you reduce the number of columns, potential errors will be noticed much
quicker, since the view would not work, identifying places where the users
of the view might not work.

Louis 

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Armando" <blba2k@nospamyahoo.com> wrote in message
news:e8kVmIgAEHA.2600@TK2MSFTNGP09.phx.gbl...
> Views are dynamic and the data gets pulled together as needed.
>
> If you're using a SELECT * on the table, the column will show up.  If
you're
> SELECTing individual columns,  then you'll need to ALTER the view in
> order to make the column appear.    I recommend ALTERing over
> DROP and CREATE so you don't have to re-apply any permissions.
>
> "Shaleen Chugh" <shaleen.chugh@the-web-works-co.uk> wrote in message
> news:eAr1SDgAEHA.2292@TK2MSFTNGP12.phx.gbl...
> > I have a view in SQL Server 2000 which uses 10 table joins.I have
modified
> > one table by adding a column to it.Does the view get refreshed on its
own
> or
> > do I have to drop and recreate it?
> > Its a simple view with no options used.
> > Pls let me know.
> >
> > Thanks and Regards,
> >
> > Shaleen
> >
> >
>
>