7

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

8

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.