Update an Indexed View "Online"

Say I have an indexed view that looks like this:

ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING
AS 
    SELECT ord.SomeColumn, COUNT_BIG(*) AS Count
    FROM dbo.Ordered ord
    WHERE ord.CreatedWhen >  CONVERT(DATETIME, '2014-11-01', 121)
    GROUP BY ord.SomeColumn
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView] ON dbo.MyIndexedView (SomeColumn)
GO

If I change the date time value in this view (say to '2014-11-01') then the clustered index needs to be regenerated.

Is there a way to have the existing view and index stay in effect until the new one is totally built and then have them swap? Similar to Online=On

Here is how I would do it. First, create a couple of spare schemas:

CREATE SCHEMA HoldingTank AUTHORIZATION dbo;
CREATE SCHEMA Swapper AUTHORIZATION dbo;

Now, when it's time to refresh the view and change the filter predicate, create it in the HoldingTank schema:

CREATE VIEW HoldingTank.MyIndexedView
WITH SCHEMABINDING
AS
  SELECT ...
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView]
ON HoldingTank.MyIndexedView(SomeColumn);

Now, start a transaction, and swap the objects.

BEGIN TRANSACTION;
  ALTER SCHEMA Swapper TRANSFER dbo.MyIndexedView;
  ALTER SCHEMA dbo TRANSFER HoldingTank.MyIndexedView;
COMMIT TRANSACTION;

Then at a later time you can simply drop the old copy of the view from Swapper:

DROP VIEW Swapper.MyIndexedView;

I go into more detail about this solution in these blog posts:

• T-SQL Tuesday #33 : Trick Shots : Schema Switch-A-Roo

• Schema Switch-A-Roo : Part 2

StackExchange Database Administrators Q#123890, answer score: 8