{"id":419,"date":"2008-02-11T21:02:29","date_gmt":"2008-02-11T21:02:29","guid":{"rendered":"\/blogs\/conor\/post\/Wrapping-my-head-around-RAND()-in-SQL-Server.aspx"},"modified":"2008-02-11T21:02:29","modified_gmt":"2008-02-11T21:02:29","slug":"wrapping-my-head-around-rand-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/","title":{"rendered":"Wrapping my head around RAND() in SQL Server"},"content":{"rendered":"<p>So there&#8217;s all sorts of legacy behaviors in SQL Server that will keep me employable for years and years because it didn&#8217;t make sense when Microsoft started shipping SQL Server and there&#8217;s too many people to change it now ;).<\/p>\n<p>RAND() is one of those things that behaves differently in the various commercial database vendors, and it takes some time to grok what&#8217;s happening.<\/p>\n<p>Let&#8217;s say that I want to get a random set of rows from a table &#8211; that seems like a nice thing to do, right?<\/p>\n<p>So I go write:<\/p>\n<pre style=\"font-size: 11px; font-family: Courier New;\"><span style=\"color: Black; background-color: transparent; font-family: Courier New; font-size: 11px;\"><span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">SELECT<\/span> * <span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">FROM<\/span> MyTable <span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\">WHERE<\/span> <span style=\"color: Fuchsia; background-color: transparent; font-family: Courier New; font-size: 11px;\">RAND<\/span>() &gt; 0.5<\/span><\/pre>\n<p><\/p>\n<p>This seems like a great first attempt to get about half the rows back, assuming a good pseudo-random number implementation.&nbsp; However, you&#8217;ll notice that if you run this, the behavior isn&#8217;t quite what might be expected (at least the way I&#8217;ve described it in this example).<\/p>\n<p>So when one executes this query, you&#8217;ll notice that sometimes you get all rows from your table and other times you&#8217;ll get none.&nbsp; But wait &#8211; functions are run per-row, right?&nbsp; Well, no, at least not in all cases.&nbsp; The complete details are beyond a single blog posting, but for largely historical reasons the RAND() function is actually executed per query, not per row.&nbsp; That means that, logically, you get the query from the plan cache, get it ready to run, and then run RAND() once, caching the value.&nbsp; Then, each and every row uses that cached value when evaluating any scalar logic.&nbsp; <\/p>\n<p>So, for this example, either that initial call to RAND() returned a value greater than .5 or it didn&#8217;t.&nbsp; As such, you get all or no rows only.<\/p>\n<p>SQL Server added TABLESAMPLE as a mechanism to address part of this need &#8211; retreiving a sample from a table.&nbsp; However, it isn&#8217;t arbitrarily composable, and the semantics used in the default system implementation in SQL 2005 are actually intended to be used for statistics generation &#8211; a slightly different goal than quality sampling.<\/p>\n<p>But wait &#8211; a lot of functions DO execute per row.&nbsp; How can I tell which is which?&nbsp; Well, I am not sure that there is a publicly exposed way, other than trial and error, to determine this.&nbsp; I need to go do some more homework, as my memory is, well, random.<\/p>\n<p>Even worse, some function computations are moved around in query trees, and so the actual number of executions may not be what you think as &#8220;per row&#8221;.&nbsp; For example, if you execute a filter before\/after the scalar logic, then the results may be different.&nbsp; Put another way, if you use an index seek vs. a table scan, how many times do you expect any old function to be executed?<\/p>\n<p>It&#8217;s enough to make your head spin!&nbsp; <\/p>\n<p>The basic rules are that, as long as the optimizer knows about the semantic issues, it will try very hard to not move around functions who have different semantics when executed different numbers of times.<\/p>\n<p>So, be careful with your functions, built-in, user-defined, or whatever exotic form gets dreamed up next.&nbsp; There&#8217;s a lot going on under the covers, and knowing a few details can help you more deeply understand what a query is doing.<\/p>\n<p>Thanks,<\/p>\n<p>Conor Cunningham<\/p>\n<pre style=\"font-size: 11px; font-family: Courier New;\"><span style=\"color: Black; background-color: transparent; font-family: Courier New; font-size: 11px;\"><span style=\"color: Blue; background-color: transparent; font-family: Courier New; font-size: 11px;\"><\/span><br><\/span><\/pre>\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>So there&#8217;s all sorts of legacy behaviors in SQL Server that will keep me employable for years and years because it didn&#8217;t make sense when Microsoft started shipping SQL Server and there&#8217;s too many people to change it now ;). RAND() is one of those things that behaves differently in the various commercial database vendors, [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-419","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Wrapping my head around RAND() in SQL Server - Conor Cunningham<\/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\/conor\/wrapping-my-head-around-rand-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Wrapping my head around RAND() in SQL Server - Conor Cunningham\" \/>\n<meta property=\"og:description\" content=\"So there&#8217;s all sorts of legacy behaviors in SQL Server that will keep me employable for years and years because it didn&#8217;t make sense when Microsoft started shipping SQL Server and there&#8217;s too many people to change it now ;). RAND() is one of those things that behaves differently in the various commercial database vendors, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Conor Cunningham\" \/>\n<meta property=\"article:published_time\" content=\"2008-02-11T21:02:29+00:00\" \/>\n<meta name=\"author\" content=\"Conor Cunningham\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Conor Cunningham\" \/>\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\/conor\/wrapping-my-head-around-rand-in-sql-server\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/\",\"name\":\"Wrapping my head around RAND() in SQL Server - Conor Cunningham\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\"},\"datePublished\":\"2008-02-11T21:02:29+00:00\",\"dateModified\":\"2008-02-11T21:02:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Wrapping my head around RAND() in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/\",\"name\":\"Conor Cunningham\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3\",\"name\":\"Conor Cunningham\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g\",\"caption\":\"Conor Cunningham\"},\"url\":\"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Wrapping my head around RAND() in SQL Server - Conor Cunningham","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\/conor\/wrapping-my-head-around-rand-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Wrapping my head around RAND() in SQL Server - Conor Cunningham","og_description":"So there&#8217;s all sorts of legacy behaviors in SQL Server that will keep me employable for years and years because it didn&#8217;t make sense when Microsoft started shipping SQL Server and there&#8217;s too many people to change it now ;). RAND() is one of those things that behaves differently in the various commercial database vendors, [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/","og_site_name":"Conor Cunningham","article_published_time":"2008-02-11T21:02:29+00:00","author":"Conor Cunningham","twitter_misc":{"Written by":"Conor Cunningham","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/","name":"Wrapping my head around RAND() in SQL Server - Conor Cunningham","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website"},"datePublished":"2008-02-11T21:02:29+00:00","dateModified":"2008-02-11T21:02:29+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/wrapping-my-head-around-rand-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/conor\/"},{"@type":"ListItem","position":2,"name":"Wrapping my head around RAND() in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/conor\/","name":"Conor Cunningham","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/conor\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/f9106e03423de6b5157295891b8c3ae3","name":"Conor Cunningham","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/conor\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d9c37eff231ec89c1b244347d966860875eea8b55b366911d2694e8cd9913e57?s=96&d=mm&r=g","caption":"Conor Cunningham"},"url":"https:\/\/www.sqlskills.com\/blogs\/conor\/author\/conor\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/comments?post=419"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/posts\/419\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/media?parent=419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/categories?post=419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/conor\/wp-json\/wp\/v2\/tags?post=419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}