How do I periodically rebuild a reporting table that is very frequently accessed?

Question

It takes about 5-10 minutes to refresh a prepared reporting table. We want to refresh this table constantly (maybe once every 15 minutes or continuously).

We query this reporting table very frequently (many times per minute) and I can't keep it down for any length of time. It is okay if the data is 15 minutes old.

I can't drop the table and recreate it. I can't delete the table's contents and recreate it.

Is there a technique I should be using, like swapping between two tables (read from one while we build the other) or do I put this 5-10 minute process in a large transaction?

Answer 1

Use synonyms?. On creation this points to tableA.

CREATE SYNONYM ReportingTable FOR dbo.tableA;

15 minutes later you create tableB and redefine the synonym

DROP SYNONYM ReportingTable;
CREATE SYNONYM ReportingTable FOR dbo.tableB;

The synonym is merely a pointer to the actual table: this way the handling of the actual table renames etc is simplified and abstracted away and all code/clients would use ReportingTable

Edit, 24 Nov 2011

Synonyms are available in all edition: partition switching is Enterprise/Developer only.

Edit, Feb 2012

You can switch whole tables in standard edition (maybe Express, untested)

ALTER TABLE .. SWITCH ..

This would be more elegant than synonyms if the target table is empty.

Edit, Feb 2012 (2)

Also, you can rotate via schemas as per Caching joined tables in SQL Server