Creating a clustered index on a view, minimising contention
1
vote
2
answers
345
views
I have a table of 100 billion rows with an identity bigint column that is the clustered primary key.
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 columns.
The table is highly transactional, so 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 an incremental manner (i.e. somehow pause part of the way through creation, to allow the table to catch up on its transaction backlog, then resume creation).
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.
I'm using an indexed view because I'm using the
HASHBYTES
function to generate a hash of the row, 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.)
The table is not partitioned.
Asked by J.D.
(40893 rep)
Dec 16, 2019, 08:26 PM
Last activity: May 8, 2025, 08:25 AM
Last activity: May 8, 2025, 08:25 AM