I have a SQL Server 2019 instance on my hands but all database in it are set to Compatibility Level 110 (SQL Server 2012).
I would like to rebuild a few indexes with the options:
ALTER INDEX ALL ON [MyIndexName]
REBUILD WITH(FILLFACTOR = 80,
ONLINE = ON,
SORT_IN_TEMPDB = ON
RESUMABLE = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
My question is, as RESUMABLE = ON
and OPTIMIZE_FOR_SEQUENTIAL_KEY = ON
are features introduced from SQL Server 2019 will they apply to my databases as well even if they are still on Compatibility Level 110?
The reply is: we cannot tell.
I also asked to Randolph West on a post and the reply is: "Some features apply to the instance level, while some apply only to databases in the correct compatibility level. Optimize for sequential key is a 150-level feature."
So we just need to try out on a test environment as suggested in the comments above.