{"id":4729,"date":"2017-05-31T12:28:02","date_gmt":"2017-05-31T19:28:02","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4729"},"modified":"2017-06-03T08:24:06","modified_gmt":"2017-06-03T15:24:06","slug":"sqlskills-sql101-how-can-a-select-cause-a-database-to-change","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/","title":{"rendered":"SQLskills SQL101: How can a SELECT cause a database to change?"},"content":{"rendered":"

As Kimberly blogged about recently<\/a>, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101<\/strong> blog posts, check out\u00a0SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

This is an interesting misconception\u00a0that I\u00a0was asked about last week: (paraphrasing) Surely a SELECT operation can’t cause a database to change, because it’s just reading data, not altering it in any way, right?<\/em><\/p>\n

Well, no. There are actually quite a few side effects of queries that only read data and never perform data changes (not counting a SELECT … INTO<\/em>, of course). Here are four\u00a0that spring to mind…<\/p>\n

Statistics Creation<\/strong><\/p>\n

If the\u00a0database property Auto Create Statistics<\/strong> is set to True, when a query is being compiled and the query optimizer determines that a statistic could be created that would aid the optimization process, it will create that statistic before optimization continues, thus changing the database. Your SELECT statement could cause this to happen.<\/p>\n

Statistics Update<\/strong><\/p>\n

If the database property Auto Update Statistics<\/strong> is set to True, when a query is being compiled and a necessary statistic is determined to be out-of-date, it will be automatically updated before optimization continues, thus changing the database. Your SELECT statement could cause this to happen.\u00a0Additionally, if the Auto Update Statistics Asynchronously<\/strong> property is enabled, the statistic will be automatically updated, but after the optimization process (so the compiling query doesn’t have to wait).<\/p>\n

Ghost Cleanup<\/strong><\/p>\n

Ghost cleanup is the\u00a0funky process for removing deleted records. For all indexes, and for heaps when some form of snapshot isolation is involved, deleting a record just marks it as deleted. After the deleting transaction commits, the deleted record is later removed by a background process called the ghost cleanup task. The interesting thing though is that a deleted record is not immediately entered in the task’s list of things to do. It’s usually not until the *next* use of the data file page that the Storage Engine sees that there’s a deleted record and enters it in the task’s to-do list. So, your SELECT statement could be that ‘next’ use of a data file page with a recently deleted record on that causes the record to be cleaned up by the ghost cleanup task.<\/p>\n

Query Store<\/strong><\/p>\n

From SQL Server 2016 onward, if you have the Query Store enabled, by default every query execution will cause some metrics to be captured and stored in the Query Store’s system tables (which you can query with various DMVs). You can stop it capturing information for all queries by setting the QUERY_CAPTURE_MODE<\/em> to AUTO<\/em>, which causes it to not capture “insignificant” queries, but you can’t specify what “insignificant” means. Anyway, your SELECT statement could cause this to happen.<\/p>\n

Summary<\/strong><\/p>\n

So as you can see, just because you’re not deliberately making a change in a database, that doesn’t mean that you won’t trigger something else in SQL Server to make a change. And then there’s the Auto Shrink<\/strong> option for a database, which of course should *never* be enabled! (see here<\/a> for explanation…)<\/p>\n","protected":false},"excerpt":{"rendered":"

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,61,108],"tags":[],"class_list":["post-4729","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-misconceptions","category-sql101"],"yoast_head":"\nSQLskills SQL101: How can a SELECT cause a database to change? - 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\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: How can a SELECT cause a database to change? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-05-31T19:28:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-06-03T15:24:06+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=\"3 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\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/\",\"name\":\"SQLskills SQL101: How can a SELECT cause a database to change? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-05-31T19:28:02+00:00\",\"dateModified\":\"2017-06-03T15:24:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: How can a SELECT cause a database to change?\"}]},{\"@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":"SQLskills SQL101: How can a SELECT cause a database to change? - 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\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: How can a SELECT cause a database to change? - Paul S. Randal","og_description":"As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/","og_site_name":"Paul S. Randal","article_published_time":"2017-05-31T19:28:02+00:00","article_modified_time":"2017-06-03T15:24:06+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/","name":"SQLskills SQL101: How can a SELECT cause a database to change? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-05-31T19:28:02+00:00","dateModified":"2017-06-03T15:24:06+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-how-can-a-select-cause-a-database-to-change\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: How can a SELECT cause a database to change?"}]},{"@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\/4729","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=4729"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4729\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4729"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4729"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4729"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}