Stored Procedure to Flip Staging Tables

We have several applications that are relatively data intensive. For the most part, the applications run just fine when executing simple queries against indexed tables. The problem comes when we need to aggregate data for reporting or perform queries against cross-joined or unioned tables. As a result, sometimes we create data tables that are periodically populated with the results of the slow cross-join or union and are subsequently indexed to optimize performance for future queries.


That's all well and good, but you can't really fill that table while the application is running queries against it, so what do you do? Well, you create a staging table, fill it, and the point your application at your new table, right?


Something I picked up from partitioning very large table during my data warehousing days is the idea that I can point a view at a table and treat it like a table and it will be almost as performant as the table with its indexes. Thus, if I want to have a staging table and a production table, I don't have to put any intelligence in my application; rather, I can leave the staging and swapping to the database.


For example, if I have a very large table of invoices (which I would, of course, love to have), then I could create two tables called Invoice1 and Invoice2. I could then point the view Invoice at Invoice1 and InvoiceStaging at Invoice2. Thus, any procedure or application that needs to build my staging table can do so always by referencing InvoiceStaging and my production table will always be referenced with Invoice.


The only difficulty really is switching the view to have it point to the new table and to point the staging view to the old table. I wrote a stored procedure that would look into a settings table, determine the current production and staging tables, swap them, and write the new settings back to the settings table. It was fine really and I didn't have any trouble with it, but one day I had an idea. I wrote this stored procedure to handle my switching for me and it does it all with nothing more than some of the system tables.

CREATE PROCEDURE [dbo].[FlipStagingTables]
(
  @TableName VARCHAR(255)
)
AS

  DECLARE @Active VARCHAR(255);
  DECLARE @Staging VARCHAR(255);

  SELECT 
    @Active = MAX(CASE v.name WHEN @TableName + 'Staging' THEN t.name ELSE NULL END),
    @Staging = MAX(CASE v.name WHEN @TableName THEN t.name ELSE NULL END)
  FROM sysdepends d
  INNER JOIN sysobjects v
    ON d.id = v.id
  INNER JOIN sysobjects t
    ON d.depid = t.id
  WHERE v.name IN (@TableName, @TableName + 'Staging');

  IF @Active IS NULL OR @Staging IS NULL
    SELECT @Active = @TableName + '1', @Staging = @TableName + '2';

  IF OBJECT_ID(@TableName) IS NOT NULL
    EXEC('ALTER VIEW ' + @TableName + ' AS SELECT * FROM ' + @Active);
  ELSE
    EXEC('CREATE VIEW ' + @TableName + ' AS SELECT * FROM ' + @Active);

  IF OBJECT_ID(@TableName + 'Staging') IS NOT NULL
    EXEC('ALTER VIEW ' + @TableName + 'Staging AS SELECT * FROM ' + @Staging);
  ELSE
    EXEC('CREATE VIEW ' + @TableName + 'Staging AS SELECT * FROM ' + @Staging);

  EXEC('TRUNCATE TABLE ' + @Staging);