{"id":882,"date":"2009-04-29T14:26:00","date_gmt":"2009-04-29T14:26:00","guid":{"rendered":"\/blogs\/paul\/post\/Why-isnt-there-automatic-3cinsert-feature3e-in-SQL-Server.aspx"},"modified":"2009-04-29T14:26:00","modified_gmt":"2009-04-29T14:26:00","slug":"why-isnt-there-automatic-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/","title":{"rendered":"Why isn&#8217;t there automatic <insert feature> in SQL Server?"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">I was in a discussion earlier today, and it&#39;s one I&#39;ve had lots of times in the past &#8211; both inside and outside Microsoft and the SQL team: why doesn&#39;t SQL Server do automatic &lt;defrag, index creation, refactoring, de\/normalization, backups, CHECKDBs, etc&gt;?<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Some of these were considered while I was still on the team, and I was *very* cautious about them. Here&#39;s why, taking automatic table de\/normalization as an example&nbsp;(this morning&#39;s discussion). Assuming the logic to do the de\/normalization based on usage patterns can be worked out, here&#39;s why I don&#39;t think it will ever happen (just off the top of my head):<\/font>\n<\/p>\n<ul>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">If it&#39;s wrong even once, no-one will ever use it again.<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">When should SQL Server change the table definitions? What time of day is best?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">Where should the new table be placed? In which filegroup?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">What if there&#39;s no space to do it &#8211; should the database autogrow because of an automatic process? And then should it shrink afterwards?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">How far should the change plan parallelize?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">Who manages the transaction log size if the table is really large? Automatically take log backups? What if there&#39;s no space for the log backups? What about space on the log shipping secondaries? And the time required to roll-forward the log on the log-shipping secondaries?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">What about the impact of extra transaction log on database mirroring? Both in terms of the huge SEND queue preventing log truncation, and the huge REDO queue preventing the mirror coming online quickly?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">What if a cluster failover occurs during the operation and the rollback prevents the database coming online within the company&#39;s RTO?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">What if there are an replication topologies setup? How do they get quiesced for the schema change?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">How does the app get changed to handle the different schema, or do views get created automatically? Indexed views or not?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">What if Change Data Capture is running? Should it automatically create a new capture instance? What if both are already in use?<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">How do SPs get updated to the new schema? Build in the datadude engine as part of SQL Server?<\/font>\n\t<\/div>\n<\/li>\n<\/ul>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">My point: it&#39;s a lot harder than you think to just put some automatic behavior into SQL&nbsp;Server.&nbsp;<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Now, saying that, I hope that some of the others do happen at some point, but with lots of config parameters so I can control them.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I was in a discussion earlier today, and it&#39;s one I&#39;ve had lots of times in the past &#8211; both inside and outside Microsoft and the SQL team: why doesn&#39;t SQL Server do automatic &lt;defrag, index creation, refactoring, de\/normalization, backups, CHECKDBs, etc&gt;? Some of these were considered while I was still on the team, and [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44],"tags":[],"class_list":["post-882","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Why isn&#039;t there automatic  in SQL Server? - Paul S. Randal<\/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.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Why isn&#039;t there automatic  in SQL Server? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I was in a discussion earlier today, and it&#039;s one I&#039;ve had lots of times in the past &#8211; both inside and outside Microsoft and the SQL team: why doesn&#039;t SQL Server do automatic &lt;defrag, index creation, refactoring, de\/normalization, backups, CHECKDBs, etc&gt;? Some of these were considered while I was still on the team, and [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-04-29T14:26:00+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/\",\"name\":\"Why isn't there automatic in SQL Server? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-04-29T14:26:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Why isn&#8217;t there automatic in SQL Server?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Why isn't there automatic  in SQL Server? - Paul S. Randal","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.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Why isn't there automatic  in SQL Server? - Paul S. Randal","og_description":"I was in a discussion earlier today, and it&#39;s one I&#39;ve had lots of times in the past &#8211; both inside and outside Microsoft and the SQL team: why doesn&#39;t SQL Server do automatic &lt;defrag, index creation, refactoring, de\/normalization, backups, CHECKDBs, etc&gt;? Some of these were considered while I was still on the team, and [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/","og_site_name":"Paul S. Randal","article_published_time":"2009-04-29T14:26:00+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/","name":"Why isn't there automatic in SQL Server? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-04-29T14:26:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/why-isnt-there-automatic-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Why isn&#8217;t there automatic in SQL Server?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/882","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=882"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/882\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}