Why VIEW is corrupted occasionally?

Question

Hi,

One of our application made use of VIEW of tables.  From my understanding, VIEW is used for security reason.

However, for unknown reason, some of those VIEW are corrupted occassionally and has to recreated. 

Just wonder whether the correct way to recreate VIEW is to DROP and then CREATE ?

Besides, end users also ask why they are corrupted ?

Your advice is sought.

Thanks

Answer 1

What version of SQL are you running... You may be hitting this.. http://support.microsoft.com/kb/905765

Answer 2

Sorry for missing out the version of SQL Server used - It is SQL Server 2005.

In weekly database maintenance plan, we do "Check Database Integrity" / "Reorganize Index" and "Update Statistics".  Will it be the cause ?  Is there any way to handle it ?

Thanks 

Answer 3

Define "corrupted".

In general, if you change the structure of any of the underlying tables, then you want to re-create the views that use those tables (or possibly just do sp_refreshview).

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 4

My guess at this issue is the same as Tibor's.  Do these views by chance use the "Select *" syntax?  For instance, say that you have a view definition such as this:

create view bleah
as
select *
from some_Target_Table

When this view is created it is created with all of the columns that are defined for "some_Target_Table".  This meaning of the view remains the same until (1) the view is re-created, (2) altered or (3) sp_refreshView is run. 

Now suppose that three new columns are added to some_Target_Table and the view is again used as the basis of query.  The meaning of the view is still the same as it was before the new columns were added; to get the view to once again contain all of the columns of the table you either need to (1) recreate the view, (2) alter the view or (3) run sp_refreshView.

The moral of the story is to avoid using "select *" to define the meaning of views.  Here are some previous posts that discuss this issue:

Select * Problems:

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/8b2412d4-6c4c-49c5-b3fb-9657fe24961e/
   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/aee49f56-3b0b-45fd-96cf-99042db721a7/
      Umachandar Jayachandran

   http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/18830904-c70d-430c-83b8-b2bf59c17523/
      Alejandro Mesa
      Dale Joyce
      Jonathan Kehayias
      Kent Waldrop

   http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ba5eb059-dab3-4f3e-98bf-34e9c8138e95
      Kent Waldrop

Answer 5

We find that the VIEW is corrupted when there is end user complain.

The consultant who sets up the application advises us to recreate the VIEW by using CREATE VIEW XXX AS SELECT * FROM YYYY

As I am new to SQL Server, instead of using SELECT *, is there any other better way to create VIEW ?

Thanks

Answer 6

Define "corrupted".

In general, if you change the structure of any of the underlying tables, then you want to re-create the views that use those tables (or possibly just do sp_refreshview).

---

Tibor Karaszi, SQL Server MVP | web | blog

Please re-read Tibor's post.  I suggest that you try the sp_refreshView system procedure.

Answer 7

The consultant who sets up the application advises us to recreate the VIEW by using CREATE VIEW XXX AS SELECT * FROM YYYY

As I am new to SQL Server, instead of using SELECT *, is there any other better way to create VIEW ?

Time for a new consultant:-) 

The lesson learned is to specify an explicit column list and execute sp_refreshview if any changes are made to the data types of the exposed columns.  These practices will prevent the problem from reoccuring.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 8

Time for a new consultant:-) 

It looks that way to me, too!