Creating views and setting them to be static or dynamic

[FatalExceptionError]

I have several questions about views as I am unclear on them at the moment.

A. When a view is created is that view just a snapshot virtual table of the data or is the information in the view updated when changes are made? Example: I create a view that holds payments from several different tables. When I make a new payment after creating the view will the view reflect that change.

B. If the view is dynamic is there a way to make it a static view that updates itself at the end of a month?

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[gmmastros]

views are dynamic. period. but there are things you can do...

When you add data (or update or delete) data from the tables that the view is pointing to, then the data from the view is changed to reflect the changes in the data.

In your case, I would recommend that you create a 'history' table. Then schedule a job so that the data is moved to a history table. Then, create another view looking at the history table. The history table should have a column for the month and year. Then, when you want the history data for a particular month, you would do...

[tt]
select *
From ViewFromHistoryTable
Where Month = 7
and year = 2006
[/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rac2]

A view is dynamic, when the data in the underlying tables changes the view will reflect that change the next time it is queried.

No there is no such thing as a static view. The options include tables and queries.

You can revise your application to write to a monthly table, then schedule a SQL Agent Job to append the table to a cumulative table, truncate the monthly table, and recreate it. Being careful to schedule this process between the last activity at the end of the month and before the first activity of the new month. And being prepared to cope with manually accomplishing this when the job fails, as it surely will.

Or you could define the view with a condition that no activity after the 1st of the month be included in the view.

SELECT colA, colB, colC, etc
FROM MyTable
WHERE DATEDIFF(month, date_of_activity, getdate() ) > 0

Which is a whole lot less trouble and may be the solution to your requirement.

The view will appear to be static during the month, then suddenly show a whole new bunch of data when the calendar turns a page.

 

[FatalExceptionError]

Figured views were dynamic just wanted to be sure as it would determine what I would end up doing.

Thanks guys

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't