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 &gt;  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