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.

  • If the table is "highly transnational" and you are retrieving "last 500 million rows", you should update the index on each DML execution. That could be painful.
    – McNets
    Dec 16, 2019 at 21:40
  • Is it partitioned? Dec 16, 2019 at 21:59
  • @DavidBrowne-Microsoft Nope, one single table.
    – J.D.
    Dec 17, 2019 at 3:04
  • @McNets Could you elaborate on that further?...I don't quite understand.
    – J.D.
    Dec 17, 2019 at 3:05
  • Every new insert, update and delete must update the index because you need the "last 500 million rows". Maybe an indexed view is more useful for static data.
    – McNets
    Dec 17, 2019 at 7:46

1 Answer 1




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

  • I'm using an indexed view because I'm calling the HASHBYTES() function on the row data to generate a "unique" hash of the row and then creating an index on that hash and the primary key of the table. This allows me to quickly compare 500 million rows of data with another large dataset for any changes in data. (I realize I probably could do this in a computed column on the table itself as well, but I figured there may be less contention if the data is a materialized copy as an indexed view.)
    – J.D.
    Dec 17, 2019 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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