Resumable Online Index Creation In Azure SQL Database

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.

Related Posts

Adaptive Joins and Index Width

Erik Darling wants to pump your indexes up: Now, there’s an Extended Event that… Used to work. These days it just stares blankly at me. But since I’ve worked with this before, I know the problem. It’s that Key Lookup — I’ll explain more in a minute. Adaptive joins won’t do all the work for you, […]

Read More

Making Non-SARGable Queries SARGable with an Index

Denis Gobo violates Betteridge’s Law of Headlines: This question came up the other day from a co-worker, he said he couldn’t change a query but was there a way of making the same query produce a better plan by doing something else perhaps (magic?) He said his query had a WHERE clause that looked like […]

Read More

Categories

July 2018
MTWTFSS
« Jun Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031