Resumable Online Index Create in SQL Server 2019 and Azure SQL Databases

         I always get excited when I speak about the Resumable Online Index Rebuild feature of SQL Server 2017 in my talks. SQL Server 2017 introduced many new features and I think Resumable Online Index Rebuild was one of the hidden gem of SQL Server 2017. With this feature you can pause, resume or abort an ongoing index rebuild. This is such a great feature for DBAs if you have the Enterprise Edition of SQL Server.

        Resumable Online Index Rebuild works great for exist and online indexes. You were out of luck if you like to create a new index in a large table. Because SQL Server 2017 does not support to create an index with Resumable online index option. SQL Server 2019 fixes that and let us use the same functionality when we create a new index.

CREATE NONCLUSTERED INDEX IX_MyAwesomeResumableIndex 
ON dbo.SavranWeb(CustomerId) 
WITH (ONLINE=ON, RESUMABLE = ON)

       The rest of the functionality is same with SQL Server 2017. In this point, you can pause / resume or abort this ongoing index build. For more information for the following commands, please check my earlier post about SQL Server 2017 Resumable Index Rebuild

ALTER INDEX [IX_MyAwesomeResumableIndex] 
ON dbo.SavranWeb PAUSE

ALTER INDEX [IX_MyAwesomeResumableIndex] 
ON dbo.SavranWeb RESUME

ALTER INDEX [IX_MyAwesomeResumableIndex] 
ON dbo.SavranWeb ABORT