Niko Neugebauer looks at a feature coming in SQL Server vNext:
It is about the time to create our first Clustered Online Resumable Index:
CREATE CLUSTERED INDEX CI_SampleDataTable ON dbo.SampleDataTable (c1) WITH ( ONLINE = ON, RESUMABLE = ON ) ;But all we shall get is an error message:
Msg 155, Level 15, State 1, Line 25 'RESUMABLE' is not a recognized CREATE CLUSTERED INDEX option.I was shocked and I was disappointed, but I have understood that it was my own mind’s fault. Nobody, I repeat – NOBODY has told me that it would work for the CLUSTERED Indexes, but when I see an announcement that the Indexes are supported, I was totally believing that the traditional (not XML, no CLR, no LOB’s) Rowstore Indexes would be totally supported. Oh yes, I know that it is crazy difficult. I know that this is a pretty forward-facing feature, but come on – my mind played trick on me, telling me the story that does not exist, for now, at least.
After realising my mind’s mistake I took a deeper breath and decided to try out the Resumable Nonclustered Index Creation with the following command:
CREATE NONCLUSTERED INDEX NCI_SampleDataTable ON dbo.SampleDataTable (c1) WITH ( ONLINE = ON, RESUMABLE = ON );
Hopefully we get a bit more support as SQL Server vNext is developed and eventually released. In the meantime, Niko hits some limitations but his timings for the feature look good.