{"id":24273,"date":"2018-05-08T09:00:15","date_gmt":"2018-05-08T16:00:15","guid":{"rendered":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?p=24273"},"modified":"2024-01-22T22:51:16","modified_gmt":"2024-01-23T06:51:16","slug":"database-scoped-default-online-options-is-in-preview-for-azure-sql-db","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/","title":{"rendered":"Database scoped default online options is in preview for Azure SQL DB"},"content":{"rendered":"<p><em>This post is authored by Mirek Sztajno, Senior Program Manager, Azure SQL Database.<\/em><\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>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:<\/p>\n<ul>\n<li>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.<\/li>\n<li>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.<\/li>\n<li>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.<\/li>\n<\/ul>\n<p>Note: The user can override the default setting by submitting a statement with the ONLINE option specified.<\/p>\n<h3>Examples:<\/h3>\n<pre>--Set Defaults to FAIL_UNSUPPORTED\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = FAIL_UNSUPPORTED;\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = FAIL_UNSUPPORTED;<\/pre>\n<pre>--Rebuild Supported Index\r\n\r\nALTER INDEX index1 on Table1\r\n\r\nRebuild;\r\n\r\n--Result: The rebuild will execute as normal<\/pre>\n<pre>--Rebuild Unsupported Index\r\n\r\nALTER INDEX columnstoreindex1 on Table1\r\n\r\nRebuild;\r\n\r\n--Result: The rebuild will fail and an error message will be displayed in the log<\/pre>\n<pre>--Set Defaults to WHEN_SUPPORTED\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = WHEN_SUPPORTED;\r\n\r\nALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_RESUMABLE = WHEN_SUPPORTED;<\/pre>\n<pre>--Rebuild Supported Index\r\n\r\nALTER INDEX index1 on Table1\r\n\r\nRebuild;\r\n\r\n--Result: The rebuild will execute as normal<\/pre>\n<pre>--Rebuild Unsupported Index\r\n\r\nALTER INDEX columnstoreindex1 on Table1\r\n\r\nRebuild;\r\n\r\n--Result: The rebuild will execute nonresumably, a warning will be displayed in the log<\/pre>\n<p>&nbsp;<\/p>\n<p>For more information about Database Scoped Default Online and Resumable Options please review:<\/p>\n<ul>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-scoped-configuration-transact-sql?view=sql-server-2017\" target=\"_blank\" rel=\"noopener\">Alter Database Scoped Configuratioin (Transact-SQL)<\/a><\/li>\n<li><a href=\"https:\/\/msdn.microsoft.com\/library\/ms190981(SQL.130).aspx\">Guidelines for Online Index Operations<\/a><\/li>\n<li><a href=\"https:\/\/msdn.microsoft.com\/library\/ms188388(SQL.130).aspx\">ALTER INDEX (Transact-SQL)<\/a><\/li>\n<li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-index-resumable-operations\">sys.index_resumable_operations<br \/>\n<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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.<\/p>\n","protected":false},"author":1457,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","footnotes":""},"post_tag":[],"product":[5226,2391],"content-type":[2448],"topic":[],"coauthors":[2487],"class_list":["post-24273","post","type-post","status-publish","format-standard","hentry","product-azure","product-azure-sql-database","content-type-updates"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Database scoped default online options is in preview for Azure SQL DB - Microsoft SQL Server Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database scoped default online options is in preview for Azure SQL DB - Microsoft SQL Server Blog\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft SQL Server Blog\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/sqlserver\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-08T16:00:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-23T06:51:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png\" \/>\n\t<meta property=\"og:image:width\" content=\"512\" \/>\n\t<meta property=\"og:image:height\" content=\"512\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"SQL Server Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:site\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"SQL Server Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/\",\"@type\":\"Person\",\"@name\":\"SQL Server Team\"}],\"headline\":\"Database scoped default online options is in preview for Azure SQL DB\",\"datePublished\":\"2018-05-08T16:00:15+00:00\",\"dateModified\":\"2024-01-23T06:51:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\"},\"wordCount\":313,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\",\"name\":\"Database scoped default online options is in preview for Azure SQL DB - Microsoft SQL Server Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\"},\"datePublished\":\"2018-05-08T16:00:15+00:00\",\"dateModified\":\"2024-01-23T06:51:16+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Database scoped default online options is in preview for Azure SQL DB\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"name\":\"Microsoft SQL Server Blog\",\"description\":\"Official News from Microsoft\u2019s Information Platform\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\",\"name\":\"Microsoft SQL Server Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"width\":259,\"height\":194,\"caption\":\"Microsoft SQL Server Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/www.facebook.com\/sqlserver\",\"https:\/\/x.com\/SQLServer\",\"https:\/\/www.youtube.com\/user\/MSCloudOS\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Database scoped default online options is in preview for Azure SQL DB - Microsoft SQL Server Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/","og_locale":"en_US","og_type":"article","og_title":"Database scoped default online options is in preview for Azure SQL DB - Microsoft SQL Server Blog","og_description":"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.","og_url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/","og_site_name":"Microsoft SQL Server Blog","article_publisher":"http:\/\/www.facebook.com\/sqlserver","article_published_time":"2018-05-08T16:00:15+00:00","article_modified_time":"2024-01-23T06:51:16+00:00","og_image":[{"width":512,"height":512,"url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png","type":"image\/png"}],"author":"SQL Server Team","twitter_card":"summary_large_image","twitter_creator":"@SQLServer","twitter_site":"@SQLServer","twitter_misc":{"Written by":"SQL Server Team","Est. reading time":"2 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/","@type":"Person","@name":"SQL Server Team"}],"headline":"Database scoped default online options is in preview for Azure SQL DB","datePublished":"2018-05-08T16:00:15+00:00","dateModified":"2024-01-23T06:51:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/"},"wordCount":313,"commentCount":0,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/","name":"Database scoped default online options is in preview for Azure SQL DB - Microsoft SQL Server Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website"},"datePublished":"2018-05-08T16:00:15+00:00","dateModified":"2024-01-23T06:51:16+00:00","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/05\/08\/database-scoped-default-online-options-is-in-preview-for-azure-sql-db\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/"},{"@type":"ListItem","position":2,"name":"Database scoped default online options is in preview for Azure SQL DB"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","name":"Microsoft SQL Server Blog","description":"Official News from Microsoft\u2019s Information Platform","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization","name":"Microsoft SQL Server Blog","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","width":259,"height":194,"caption":"Microsoft SQL Server Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/www.facebook.com\/sqlserver","https:\/\/x.com\/SQLServer","https:\/\/www.youtube.com\/user\/MSCloudOS"]}]}},"msxcm_display_generated_audio":false,"msxcm_animated_featured_image":null,"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/24273","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/users\/1457"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/comments?post=24273"}],"version-history":[{"count":0,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/24273\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/media?parent=24273"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/post_tag?post=24273"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/product?post=24273"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/content-type?post=24273"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/topic?post=24273"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/coauthors?post=24273"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}