Reporting table needs to get refreshed without killing user's read connection to it, any ideas?

I'm investigating a solution that will allow a reporting table to be refreshed that can potentially have users reading from it at a time that it needs to be refreshed. Has anyone implemented a way of doing something like this without needing to kill the user's connection to that table at the time of refresh?

Sure. The simplest thing is to just make sure the reporting users using Row Versioning, either in SNAPSHOT ISOLATION, or by setting the database to READ_COMMITTED_SNAPSHOT. See eg, Snapshot Isolation in SQL Server.

Then just make your updates in a transaction. Reporting users will not be blocked, and will continue to see the "old" version of every row you change until you commit your transaction.

If the changes take a long time, you can alternatively build new tables, and once they are done start a transaction where you drop and rename the tables, or perform ALTER TABLE ... SWITCH to switch the table data out to a staging table, and switch the new data into the target table.

StackExchange Database Administrators Q#263935, answer score: 6