0

I have a SQL Server 2008 R2 database containing a very huge table that we use for reporting. Every night around 40,000 records are inserted into the table. I read in many articles that Indexed views are suitable for OLAP or Warehouse databases, not for transaction tables.

My goal is not to query the whole table, but to query a subset, say last 3 months data. Don't want to use triggers to create a subset. Would an indexed view be suitable for my scenario ? If not, any better ideas ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tanveer-Ibn- Haresh
  • 196
  • 2
  • 10

2 Answers2

2

You might need to check some repercussions about using an indexed view. Here are some details of some items to consider before. http://msdotnetbuddy.blogspot.com/2010/12/indexed-view-in-mssql-server.html

You could also partition your big table, into let's say having only quarterly data. You would only query on a subset. If that is not an option, you could also create a temporary cache table, that only contains data specific for this report.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mez
  • 4,666
  • 4
  • 29
  • 57
0

You could use an indexed view, you will need to use the "with schemabinding" keywords, you can put this into any search engine to find the implications of using this.

Scott
  • 19
  • 2
  • 6