Static SQL Server Indexes

Is it possible to create unclustered indexes in SQL Server 2005 that are not updated as data is changed (insert/delete/udpate) so I can ensure a stable set of data to report on?

Our data is changing frequently an I want to be able to snapshot it at a point with out having a column to show the late开发者_运维问答st change date/time and only selecting data based on that. Before I perform my analysis, I could update them and use them from that point forward.

I don't think you can tell an index to remain stale. A separate reporting table would be more appropriate solution.

For example, you can load a snapshot into a new reporting table like:

truncate table BigTableSnapshot

select *
from BigTable
into BigTableSnapshot

Alternatively, SQL Server Enterprise Edition allows you to take snapshots of an entire database. That can be very useful for reporting, although it does have a performance impact.

If you have the Enterprise version of SQL2005 you can snapshot the whole database giving you a read-only / static point in time copy of the database to report against.

http://msdn.microsoft.com/en-us/library/ms175158.aspx