How to create a clustered index on a schema bound view with the least amount of contention caused?

Question

I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 million rows or so. I want to create a clustered index on the view that incorporates the primary key and a few other fields from the table.

The table is highly transactional, and can't really be locked for more than a minute at a time. Will the creation of the index on the view cause contention directly on the underlying table during the creation? If so, is it possible for me to create the index on my schema bound view in either an incremental manner (i.e. somehow pause part of the way through creation, to allow the table to catch up to it's transaction backlog, then resume creation). Also I have to be careful with overall server contention as well, I've seen heavy operations against one table like this slow up my whole server before.

Essentially I'm looking for the most efficient way to create the index on my view to minimize contention.

Answer 1

EXAMPLE,

CREATE VIEW FilteredIndexedView WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2, DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < '1/1/2019';

CREATE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexView (DateColumn1);

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view

Whenever data will be inserted WHERE DateColumn1 < '1/1/2019' then index IX_FilteredIndexView_DateColumn1 will be updated in same query plan. That means Insert query cost will increase. If data inserted is outside the bound of give view i.e. WHERE DateColumn1 >='1/1/2019' then in this case index IX_FilteredIndexView_DateColumn1 won't be maintain. That means there won't be extra cost of maintaining index.

As this table is highly transnational and if most of the INSERT or DML operation lies within WHERE DateColumn1 < '1/1/2019' then INSERT COST will INCREASE significantly.

In this example it appear that Index View is NO NO.

If Insert operation finish in seconds and whatever the cost incur is acceptable then Index View is no problem.

In First place, why you will create view with Single table ?

Alternatively you can try Filtered Index

Create NonClustered index ix_Boring_Date on Boring(DateColumn1) include (TextColumn1,TextColumn2) where DateColumn1 < '1/1/2019'

Keep Rebuilding index from time to time.

Suggested Reading by @PaulWhite