ansaurus

Question

Answer 1

A:

Randolph Potter 2009-11-18 10:54:02

-1 Turning off statistics causes the query optimizer to use a suboptimal plan, but updates or inserts will still make it into the index. A disabled index cannot be used for anything, including reporting

Andomar 2009-11-18 11:00:31

Fair enough. I'll defer to your answer.

Randolph Potter 2009-11-18 11:15:21

Answer 2

+2 A:

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.

Andomar 2009-11-18 10:57:35

Its not quite the solution that I was looking for but I think that its the best that you can get in SQL Server. Thanks.

Penfold 2009-12-04 10:25:12

Answer 3

+1 A:

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

SuperCoolMoss 2009-11-18 12:08:30

Sorry Andomar, I didn't read you answer properly and see that you've already pointed this out.

SuperCoolMoss 2009-11-18 12:13:10

@SuperCoolMoss: It's okay to post multiple answers! The voters can decide which one explains it better

Andomar 2009-11-18 14:22:05

ansaurus

tags:

views:

answers:

Static SQL Server Indexes

related questions