sql-servert-sqlindexingsql-server-2019compatibility-level

SQL Server 2019 :: does features apply to previous compatibility level?


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?


Solution

  • 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.