SQL Server优化器特性-隐式谓词

9/1/2015来源:SQL技巧人气:3442

SQL Server优化器特性-隐式谓词

我们都知道,一条SQL语句提交给优化器会产生相应的执行计划然后执行输出结果,但他的执行计划是如何产生的呢?这可能是关系型数据库最复杂的部分了.这里我为大家介绍一个有关SQL Server优化器的特性-隐式谓词,并简单介绍在此特性下如何根据场景控制优化器的行为.

在这里我通过一个简单的实例来给大家说明下.

code

CREATE TABLE T1 (A INT, B INT)CREATE TABLE T2 (A INT, B INT)set showplan_text onSELECT *FROM T1 INNER JOIN T2 ON T1.A = T2.A

可以看出我的语句执行计划中优化器为我添加了T2.A=0这个谓词.如图1-1

QQGRiGYRiGJTFEBoZhGIZhSQyRgWEYhmFYEqvqhs2X+tvZ3jZfahmrrysMK8SEFy0AAEwDIgNbjqVqJQAAMAhERtF2+eEyex2ym/wgpGolAAAwiDgio6rq1rQ/mzXmynZ9+1/tT3+eWnrNhAX56+nJsKf0D5eqaf8aM7hKNvccOtdy7x4F7WOKKgl1RqpWAgAAg+gRGTf/deOyJoE5bFvHcs9Kz5+q929ujmrm5r3lWgvyp3QNwJpXB2b5QD5MZLh209w7z1ESFdHdtaRVQmQAAMyRfpFh3axd7xqc5IO3Z5zz64PQUc1a0DCREToGBw3GrmHVVC3yfbeO6MP2y7ovsUSGp0oSnZG+vQAAQAAJRYYZ9PYPKnKRIRwXhSLDU89RPRcmMlzHeYzIED4uiVIlRAYAwOxIG8mw3ryaMwDkIiNoAkH0SEZY6QcgMoL2C5EBAHBoiETG9//2vWp1yOMSYSTDOniniGR4ghbCxyUZRcZkYYPQHURkAACASfI5GfLHEL2Dzcg5Gb16ouQ5GVFG9FV3JsSw/cpVJUQGAMDsGCsyVkMHb4kKkaiEAaNa9Hr2jsFBg7F1WJ1sRL/8cCl8lWNKkcHbJQAAcySCyGjHCW3YMP/UTEsZmknvkObKzVMfV0HBpTs+khH0Fms7sqob9mbip8jaEG7ujlBkRPxORm+VEBkAADMlwncysHSW5YufpX1mlC9+AgDMFD4rju1N/qykTEvVSgAAYBBhIsN8epJ9XMGw1pI2FQAACAWRgS3HkjYVAAAIBZGBLceSNhUAAAgFkYEtx5I2FQAACAWRUbTNehrm9AchaVMBAIBQ4ogM/1clzDS9318a+aUKYUH+enoy7Cnd8Q2Jwd/JUHPo3Ur+MZKgL38I9zFRlfhOBgDAHIkgMrRh2zqWe1Z6/lS9f3NzVDM37y3XWpA/pWsA1ry6oC17bJjIcO2muXeeoyQqIsFPvbuqhMgAAJgjY0WGa3CSD96ecc6vD0JHNWtBw0RG6Bg8YEiO/iusrr0bIC+G7dHIKvHbJQAAsyOhyDCD3v5BRS4yhOOiUGR46jmqdM+Xv3OIDNdxHiMypvxhWEQGAMDsSBvJsN68mjMA5CIjaAJB9EhGWOmOQXHks5KiREbQTiEyAAAOjeSPS4SRDOvgnSKS4QlaCB+XjBEZI2d9ThY2CN1BRAYAAJjkERmuEcU/2Iyck9GrJ5LOyRCOxK4xdcrfVR+wgxNUCZEBADA78r9dMkxkSIbGKCJDrpY8Y7BwGPYMq5ON6PLfSJtSZPB2CQDAHIn8nQz/n5ppKUMz6R3SXLl56uMqKLh090cy5I882pFV3ao3B/9B1oZwc3eEIiPidzJ6q4TIAACYKXzxs2jL8sXP0j4zyhc/U1MBgJjc7XVmIDKwvcmflZRpSZvK8sjdVwPMm9wteB4gMrDlWNKmsiRyd84AyyF3ay4dRAa2HEvaVJYBvSTASGhEQaQSGb8DTE7SprIA6BYBYoHOEJJKZCStNACEQocIEB2aVS+IDIDlQz8IkA7alwdEBsDCoQcESA2tzAUiA2Dh0PcBTAANzQoiA2DJcIMFMA20NSuIDIAlQ68HMBk0NxNEBsBiocsDmBganQYiA2Cx0N8BTAyNTgORAbBY6O8AJoZGp4HIAFgmdHahvJy/eTy/y10LmD00PRVEBsAymbSnu70whuf15uTN861k47vnozePO3u6WgeU+3D9dHTxErCBh/XmxFb67cXjyfXL1enjyfWrsc3r1em+2rvKvF6dJhcrtxfW+qTG1GFT7GwE1puTWNdJP4gMFUQGwDIJ6ulezvfDfL+d39X66HL3fKRJCl1kuIuw9f4P10/e0pVkjsHj9sK/F7oA0tPvsm2H87YsNaU60rci4+Fuc6Lvl+AIh4yCishw5txVIa9XpxIB18gmR0rzLHtERkc7Om1/bGXX3nan1puT081DU9B6c9J7VNebk3Zbb8ViSDdEhgoiA2CZRO7pjOFcH130e+veSMbd81E7Tjh5tYUQ9iv9IsM5YFjq9nKujKzqtu589Lp1K6Pd9HfyN1G3NeWROYpba3V7YRFPigoRiIzdgG3d5duLx6OLzS540zdOG4rEqLP15NrZqZBdhuvXq4vdHnXOZuey7JyR9eak73FYpPgQIkMFkQGwTKYWGfrjBn0gf+0dnMyB8Paivb9Xx+z4IqPJ5/aiKf316vTx/MJ6v6tWzxgj757d0YgAkSHZUCwydpvfPUtExu3F49Gbp5PTR5v+s9bEH8mIITLcumd7eUgiGXVd1/XD9ZO/OERGAvip98Ml6YUF2Qnv6axh5F0f3S8ytJkZ3kiGoh6cPFw/dYa6fYZSkSF9XLKVR81jjqerO7xml+fagwz/kOYMv4eIjLvn/VC33WunRFMHRUckY5unQGS8nDcZ2hI/XD9tM/cegX19IoiMdq+9ITFhJEOphqvmiIwEpBIZWPmW9MKC7AwTGfqoECQy9Nz2EkEYxlAGKtdItptZGRjJ6NbWGJaOLp7Pm9qqD+/rdkC1DnLGGKlOFNAJimQoYRvHQ6WQxyW7fHpFxj6NS1q1IqPNqnNgO7UKmZNhpVED8lBHbySjF0RGAhAZh2tJLyzITlEiQ2XMm6LNUC0SGV08tX05f/N8e/dy1dypdwMwtxePR6fP5/ZtbSKju+2wORm7rNQ3VvRQfwqRoZ/u7r6cXDyfBIuMUZEMZ7irVRV7XWjf6+3R09J7jwAiIzaIjMO1pBcWZCezyHCMED0hDdd8Q9fg5BIZfc9K7EH4ZnZh95WZx/M7fcS178vp5kFREs14f7Vuqx38dkn7wsWuPh1xFl9k6PMi1eJezk83D+1YPtXjkp5naqKJw/b05ssszZ4iMhIwG5FRVXVVxS+uyTZFzuVb0gsLspNXZFj/ux1oXf24LNguimQY45N1mqr5zq32SKXNpHmksp+C6lBFTSmN+PC9vWJi2xEjH+UERRcZ9pdI7XNi9Era38QxREA6keHWlMouayJjn/P+2kBkJGBqkTFmOE8nBaLnPDLDaURP0gsLsjOxyHi9ulBGFPtnM7Zz6yaKZISIjKZWWgzDGGK1eLtljNwO1Z477O68jYfrJ79QO+o+WmofwcQWGbbxXttEOWLKYXeKJ+GnL6KJDEvKbt0QGXlAZCTJGZEB2Znw7RJ9ANvd69tmMnr6cde/Uj8uMcbm3UsWGvoDBdsrrJ2nMObI/XLelSAPd88njoG2eUZzZX9SE1tk2L8h0T0O5mzZN8+3ls9zKZXpnhoiGQdJHJHRPnRQR0dtjZpGMoiaiZsFc3NrQVp6f+nWNa48/ZX3FOTP01pb+VEKrSciY/FEiGSoOETG65X+TYXdkwXtNY0dMSIZ1lrpUxZ64i7X3dGrncyoHgRl2faJBXWM3Oqq5oCoK7VZDpZRs/NcZsdeE6hPanqOSZjIUE6Qa8PbC+XkWj8u4gxFWIRaWpEhjWS8nF+8MCdjQiKIDE1YmMO2a9k/dlrzNPP3lK56fzU8f/pLl9S/t5699Rl/lBAZB0jkns7ox9thtdZXtsPSbuKkls+ASIb93lQVGV6FZLzC+nLeHbkdX/3SlrVIwDZPfbpoGxVQR2WrktjjPW6WyRD24dnxMS5HCGcvMhzqR5sJ26lGe/btnxO1TpW1igz30yJnrbY0ouHu+ejiRRrJ2F2xngAYIiM2MSMZ2vjnCm9Ihk9Xnp5xOqLI0Hahd3OhyHAdEElkJUhkCPNJemFBdmL0dJ33CLpDrDGoW+9rH66fLB/xFEQyOsnMKQ7Kk51mlFK+3BXyTY79D1t0viluvUXeF7Ev3SVrug+MmvQ9r9o2mzzfel7x7ZwO2Y/PRaQRGds6GE9eOmfErh52Q7h6gvy/INP3WfpdGkkkQ9eCNhAZCYg5J0MyvMlFhj/P1CJDGDIJEhmulIgMSMGUPZ350ET9ly4yQiMZEgZt+3JuHaK0Jyb7/ep5Q8RdirBuL+enT1fl/6JpGWi/L6NNADLlqXuOrRrsGXB+TRAZKjN7XBJakLAag0Mmw0RG6scliAyo6ekAMkHTU4kjMsynAOp6c2iUDNLm0wrzT09BVpHhelrRu95Tun8XPAdE/dOVWKIzhEcJkXFo0NMBZIGmpzKbj3GNt9AnNYu3pBcWZIeeDiALND2VQxEZrrBKlDxTZD6BJb2wIDv0dABZoOmpHIrIwExLemFBdujpALJA01NBZByuJb2wIDv0dABZoOmppBIZv0PxJL2wIDv0dABZoOmppBIZSSsNAL3Q0wFkgaangsgAWCb0dABZoOmpIDIAlgk9HUAWaHoqiAyAZUJPB5AFmp4KIgNgmdDTAWSBpqeCyABYJvR0W5QfaA1l2C+iOdj/tHq8lBbkv8e2fGyn/vXqNPXxoempIDIAlklQT6f+EKX9t9frujZ/UtVB81vew4fn5le5I/0q5m5QUX6LvGueIvwiQ/ib8rsc8ouMQs5ybzU6B9b/4+zeath+cb75wfrQKoeByFBBZAAsk4E93e3F45GlF24HBsGgst6cWIYuKQ/XT20FHq6fxuqMdlBRf8C989/BIsNkgrvkXkSRjOxn2V2NjsRp5KZPZziqoejUjoIx1w+NcnlAZKggMgCWSUSRsRtr754lw8/txePRm6eT00Hdt34T/3L+5vHo4iU0m05lms0FIuP2QhKZ8NwHTy8ynFEBzbRxOvNZ9lVDo+cCcFXj4fpJ3er2ojkvL+fd4kY8SvOAyFBBZAAsk7iRjLquhcPP7k5aNlb1FnF7MWYYU0IRMpHRlQhGJKMn2O4VGeZDkM4THCXbTsrdKKskVhSDGWsxIxmWpwaZz7KkGlter049IsNZDavIMM4vImMCEBkAyySTyNinGRKEUJ+VuNYE5qaKjJ45GVOKjOYWvBtEsc7e2MUqdkeyu2EmkTHuLIuq0eCfnuKuhk1kbM6NsrRkkUBkqCAyAJZJHpGxfzxh1Qeukb4zgkYSGdun9YkiGdLnFPvYgzperjcn+iMMZZg0RYZ6t63/N4PIGHmWRdUQJPBUw/G4pL698AU8IoHIUEFkACyTHCJDHztfzvvfDugpfbDI2N28lvi4xDqhdf9gyPq4pFOrrCJj9FkWVaN31qe3Gq6Jn9ploMqUeCAyVBAZAMskg8iwTN0PnL0fT2S8nJ9uHsLnZCSc+KmLDD2f/b6HiYxYEz93pD/L4mr4ZtH6q+GKZNTdeRiIjPQc+k+9Jz24ABmZXmTYhlhtk75AunmLH3XiZylzMuYcyYhwlvur0f+lkJ5qeESGeojMqaAxQGSopBIZc7GkBxcgI5OLDMs8gzr4A5S2GPiIYaDUt0tC52Q4RYbJmO9k1HU9yVn2V6OZTOMPMPRVwysy6vruudFziIz0IDIAlskkIkMZDl0bhoYi1Hz6npX0zgYI+5pW7rdLdjmXJjISnGV3Ndyvq4RUo0dk9K0fByJDBZEBsEzifozLMTVh3+87x4b+jzba69A7AcIRnNBrPrXI8E9UVPPvzCrwvT8yjciY+ixbq2GfaaEHePqrIRMZ3vM1HESGSpjIMGmG6qqqW9P+NFeqY7w1pTVPMzEiA8DD8ns6wfuHW5XQO6NTuT8On/i5+7J1jyqyR/ijww+k1bXn7RKVVGdk+U0vhB6RcfNfNy5rEjRjv6oMtAXXSvO/ws1Vj8gAcLH4nk5yGzoykmEw5ue1xn0cUwwio649j0s6ijDFqyX1ATS9IPpFhnUzl8iwSofQlX4BIZcXlx8uERlwsNDTlcW412RgRtD0VCKIDL9QsMYk/CnHi4xGXqgekQGHBj1dGbS3ziiMQ4GmpxJHZKx2Eya0P82VVtFgzr0YH8m4/HDZGJEMOEzo6QCyQNNTEYmM7//te9Vqm8iQhCUmi2QgMgDo6QCyQNNTiRbJkAiFyeZk8LgEgJ4OIAs0PZXIEz/lb5cMWBkUyeiVF4gMWDb0dABZoOmpxBEZ/g9aSD6e4flOhqYwIn4kA5EBC4aeDiALND2VCN/JmLVlOegAE0BPB5AFmp5KnC9+ztdSHVeA3NDTAWSBpqfCb5cALBN6OoAs0PRUDl1k/A4QiaQNdQD0dABZoOmpHLrIwLBYlrShDmB4T9f3A+sA4AGRoYLIwLA4lrShDmBoT2f8IrmX16tTM3HYz5IJfkzVlbjnN9Ierp/2H/Nufxf+7tn5he/Ob5hZd20o2i+r9f9I/b4OR28em1rt9j3sB8qDDq8n/cP1E7+7JgORoYLIwLA4lrShDmBYT/dyHvwr57tReT9wBomMl/OQn9sOERnd/+60xcP69fbC/pvstxePWuauYVX/UfgmZ/3nxTsH4eH66chyrHoyVyvQioyHu82J+guid8+Wk7X7b7PV7UX3UASl360fKjJer059v3dq/iy7y2aichAZKmNFhuTrF2pH7PkYRu83MKoPl6pp/+odA9oPjbefG7cu+1d6/htaurp+QD1Dj5L1uNlzyHeOQlMKcxt5QIRVStpQBzCgp2vvm61iwtLFq0O+8jvpPSJDH6G9Zg51TpFx99wZybSBvBvAsN2va3JHFxnqKNv5UfhWvqh5qvJLrcPFSy19IGWpgFJnpbbtjpsL7VaaHAxKb+y+so92pWLuyJgfVd+pkLk8wkNkqESIZAz4jqc5hpljm6ujV706WgiHHOuIrv3LtdLMxLoyqHTXtpJ6yo+Sdrh8m3vPkbY+0TmKLjLGHBB5mqQNNZSqi2ib/W+R3z2ro3ujCWwBA3VU9oRAPGPDy7ns9rQd1F0iYxsnuHt9UP7VydnzlKTe5dBJ0M3h7lm9kx4QyfCWZcW2C9ah+u756OL5/M3T1fXz0cXm6vTx/MIQDeqhC0vfSk9j17rPgNzPZUaIjJ2OmUkMowGRoRJZZJhjVbvs+jq4XGR4uvuJRYY1t1CR4RIc8nrKj5JwTO09R2aa6OfIFChRbNgBCapS0obqopIhyGm9OVGHPfUm1TE8NGPqfozv3GTLHpfoTw2cG3ZExr5im0Zk7OVRJ+enq3Vv1KQt3ZQ7yhhvBHKCIhnyxwG7EEJApOfk+rUbkNg8rG2RCe3gBKXf7fJu/e40pRUZ7rha6SAyVGKKDGuM3TU4lSMyNO9fuTKEgvBxiXXb3or56xl6lPr/5T5Hmk93jiQjuvXpVYoDIq/SKr3IEOqJoSJDwTYw2x+g7ESG9qBBKDLMSQ8ykdGNZNhuc5uwSjerRgo44hmNDrCLjHV3AoSdfc1dEz603TGr0Z0RUluOjyYE1V2z6JWNMwIRml6v3iCRERbi2l6EswpgtCAyVOKIDOsvj6wM2ZF9AFuNExnWGRVmnsLSs4gM4bOSjOdomkhGUK0mFhljxITauwX1dJ2HHa4nI0qCZvx72T6t0Ac/kciwzUsYIjLM2t5ePG4fB2jzLi9e7A9N7DH516vTx5PTJzOWExCZsB9M65RSc6VNZChHbD83UzLHQiU0/a64KSMZhuSaEYgMlYSRDNdCxgFslSCSYc1WUvr0IqP/lr2AcxQ0H1NuE1RpSPOLISZ6Mx9QsQbP6x