Database scoped default online options is in preview for Azure SQL DB
This post is authored by Mirek Sztajno, Senior Program Manager, Azure SQL Database.
We are happy to announce that database scoped default options for online and resumable are now available for public preview. With this feature, you can set a default behavior for online and resumable rather than defining the option for each individual statement.
You can also avoid accidentally performing an operation that locks your table. By setting online as the default for your database, you can ensure that every supported operation will be elevated to online. You can override these set defaults by defining an option for online or resumable in the statement.
Users can set these defaults using the ELEVATE_ONLINE and ELEVATE_RESUMABLE database scoped configuration options. Both options will cause the engine to automatically elevate supported operations to online or resumable execution. You can enable the following behaviors using these options:
- You can set either option to FAIL_UNSUPPORTED if you would like to execute all operations online or resumable and fail operations that are not supported for online or resumable.
- You can set either option to WHEN_SUPPPORTED if you would like to execute supported operations online or resumable and run unsupported operations offline or nonresumable. You can set either option to OFF to maintain the current behavior of executing all operations offline and nonresumable unless explicitly specified in the statement.
- You can set either option to OFF to maintain the current behavior of executing all operations offline and nonresumable unless explicitly specified in the statement.
Note: The user can override the default setting by submitting a statement with the ONLINE option specified.
Examples:
--Set Defaults to FAIL_UNSUPPORTED ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED; ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = FAIL_UNSUPPORTED;
--Rebuild Supported Index ALTER INDEX index1 on Table1 Rebuild; --Result: The rebuild will execute as normal
--Rebuild Unsupported Index ALTER INDEX columnstoreindex1 on Table1 Rebuild; --Result: The rebuild will fail and an error message will be displayed in the log
--Set Defaults to WHEN_SUPPORTED ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = WHEN_SUPPORTED; ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;
--Rebuild Supported Index ALTER INDEX index1 on Table1 Rebuild; --Result: The rebuild will execute as normal
--Rebuild Unsupported Index ALTER INDEX columnstoreindex1 on Table1 Rebuild; --Result: The rebuild will execute nonresumably, a warning will be displayed in the log
For more information about Database Scoped Default Online and Resumable Options please review:
