{"id":16,"date":"2007-07-29T20:20:52","date_gmt":"2007-07-29T18:20:52","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=16"},"modified":"2010-09-24T18:09:52","modified_gmt":"2010-09-24T16:09:52","slug":"order-of-execution","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/07\/29\/order-of-execution\/","title":{"rendered":"Order of execution"},"content":{"rendered":"

Or “Which where runs when?<\/em>”<\/p>\n

There seems to be a lot of misunderstanding about what order conditions within a where clause are executed. In two days I heard from three different places comments about what order conditions must be put in the where clause.<\/p>\n

The simple truth is that is doesn’t matter. Let’s look at a very simple example. (Sample code at the end as always)<\/p>\n

SELECT * from tblTestWhereOrder WHERE A=1 and B=2 and C is not null<\/pre>\n
When run, the execution plan shows a table scan with a single filter, for A=1, B=2 and C not null. If the order of filters is changed in the query and the query rerun, the plan shows the same thing. A single filter with all three conditions evaluated.<\/p>\n
Now, let’s try adding an index on A. Now the query is run as an index seek on A (essentially doing the filter on A first) and then doing the other two filters later. This is regardless of the order the filters are placed in the where clause.<\/p>\n
Now add another index on B. The query is still run as an index seek, using the index on A.<\/p>\n
Essentially, the order that filters are applied during the execution of the query has everything to do with the indexes available and on the distribution of data in the columns been searched, and nothing on the order in which they are specified.<\/p>\n
Sample code:<\/p>\n
create table tblTestWhereOrder (\r\nA int identity,\r\nB int,\r\nC char(1)\r\n)\r\n\r\nwith Populate (a,b,c) as (\r\nselect 1 as A, floor(rand()*25) as B,\r\nCASE floor(rand()*25) WHEN 0 then null else char(65+floor(rand()*25)) end as C\r\nunion all\r\nselect a+1, floor(rand(A*10000)*25) as B,\r\nCASE floor(rand(a*853200)*25)\r\nWHEN 0 then null\r\nELSE char(65+floor(rand(a*452059)*25))\r\nEND from Populate\r\nwhere A<1000\r\n)\r\ninsert into tblTestWhereOrder (b,c)\r\nselect b,c from populate\r\noption (maxrecursion 1000)\r\n\r\ncreate index idx_A on tblTestWhereOrder (A)\r\n\r\ncreate index idx_B on tblTestWhereOrder (B)<\/pre>\n","protected":false},"excerpt":{"rendered":"
Or “Which where runs when?” There seems to be a lot of misunderstanding about what order conditions within a where clause are executed. In two days I heard from three different places comments about what order conditions must be put… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-g","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}