设为首页 收藏本站
查看: 801|回复: 0

[经验分享] SQL SERVER中 ORDER BY也会偷懒

[复制链接]

尚未签到

发表于 2015-6-29 16:30:41 | 显示全部楼层 |阅读模式
  当使用TOP N(不加ORDER BY)时,SQLSERVER不会对数据做任何分析,到了N个直接截断。
  但如果你加上ORDER BY,也不一定会进行排序,因为SQLSERVER只在你WHERE子句限制的字段的值是不唯一的情况下排序。
  我举个例:比如你有一个表如下:
  名字   工资     ID
  张三    500     0
  李四    600     1
  赵五    700     2
  孙六    700     3
  田七    700     4
  王八    800     5
  如果SQL语句是:
  SELECT TOP 2 *
  FROM 表名
  WHERE 工资=700
  则出来的结果是:
  名字   工资     ID
  赵五    700     2
  孙六    700     3
  若SQL换成:
  SELECT TOP 2 *
  FROM 表名
  WHERE 工资=700
  ORDER BY 工资 DESC
  本以为这次孙六要排在赵五前面了,
  结果查询出来的结果没有变化,
  看了下执行计划:
http://www.iyunv.com/shengchanlix/archive/2011/05/24/data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAVIAAACLCAIAAAC88YbrAAAYxUlEQVR4nO2cd3gb533Hr33a/uGnTdM8fep/8jRuajdq7Nipk9iJ88RyFA9IsZ3GruUkdmrH8pKH5NqwZEm2piV5a5HiAklxgAucAsUhDpDiBEjscQAXBjEIgCTAA3BYBN/+ARLEJkhRXPf7PHjsu8N7h7sv3s/93ruDiKF5OHSMxtAszoXNIA2DRuegsJaLc+ENQ9OJ2kd/Rvhc9GemROwqDBoW2jsOHYvY07CZVGDQFvcvel9Xjfh7FR6ghkELNUl4dJGJp3CwEQ3CN8ugYRiNsbjZ0DRCDFpYBlGfqGHQMAyL/NCI9mGfmGQ7Cd5KsreL7cODSgKDRqcFt8uhY1h4742IM/RGos6c6DtKskriXVrzoBY/jEajYSEWV57/QiMIpRYFjaFJ3D60KzFLI1dJSc4kqyy+E+F8xE6mSLxNrSLx92rx0Ba/y0gtE8YX+b2EGZv8w2l0Og3DMDqHQcMwjM5Y/IzIrCJCj/tWZEyJvqSltxPzVry9RZHdaYnjXWDxvBYm68LHRnRgOidhZ07yHSXv//FZ86AwBAAAxQDtAYBygPYAQDlAewCgHKA9AFAO0B4AKEfMswYAALY8633eAQBgrQHtAYBygPYAQDlAewCgHKA9AFAO0H4DMen0KyfcuJGUGVyScadISwjUM0INIdTMCNR2wZiNPzY9ODo1ODI5MGzlDVm4Q2auaqJfaerDjX24oVeh75GP98h0OsvMeh/KxoX0BlQTbtxEyg0u6bhTrHUINcRiyGo7f2yaPzodCpmXIGSp2rzeh7JyQPsNBFtif696cl/V5Jtllr8Umf6Yp38mU/Nshvp3aSNPXlDSvpY99rloxxn+wyf6Hzra/cDhjvsPtN33QfPd++q3vV1315tV33+t4o49Zd/9Q/pZRs16H8rGZcjsfpc18V711N5y6yvFEy/kG/4nS/tspvq/00eeuqDa+Y388S/EO87wt5/k/vJYzwOHO39ysO2+D5rv2X9t29t1d+2t/v7rrDteLfveKyW/ffvseh/KygHtNxB1oulDjeQnrYFDzYEPGmbfZfverHa/WuF4ucT+QoF1N8P0TKb+6TTNznMjj36Bbz8je+iE6IFPBu8/1H/vhz13v39j23sdP9jPueMFxqfnMtf7UDYuqgn3+7XTR9sCh68H6I2z++v9e2s8r7GcL5fYXyycfD534tks/dPp2l3nRx/7UvnIWfkvT4oePMr/yWHufQd67v6gKxjyD/ZzHn/+tfU+lJUD2m8gaoXz2n/UHKA3zO5j+96sdu8pd7zEtL1QMPl87sQzmeNPXdLsPD/y6JfKcO1/9GHPD0H71FCayP+rmTraFjjUHPhwQftXK+a1/0Oe+dks/dNpml3nRx/7ShWu/b0HekF7YPWpEUx9UKGml4/tKxp6Iw9/OVP6h0viZ77mP/n5wK6zvJ1neLQzvCdO8544zXv8U95jn/Ie/ZT3m1PcHae4vz7J/fVJ7iMnuNtPcO/fWwTaJwE3ke8wxw5UqPcVD+/NV/4lS/anS5JnvxE89fnArrO8XWd4tDM8WjDk06GQeTtOcXcEQz7J3X6Cu/0EF7QHVoc64RRf67KTczZybto1N+kKWJyBCUfARPgNMy693aq1DWum+WNTLaOTFcOTWSrrZwrzJ1Ljx0L9xwOaI/2jR7qHj6Sx9oL2SVCayPKByVDIU645izNgdgRMRMAwE9DaAm9c7HjtfMer5zteOcfh6ny9Wl/nmK9txNuk8tYrPDUyD0viLhW5QXtgdagVWJUmF0IIxx/B8UdcLmFwwmrN0WrfwvEHcfw+HN+G43fg+O04/i0c/zuF4q/kcmx6GpNKMYkEm5jAqtj/ANonQWly1QisCKESgbNE4JQYva3D7hKBs3XILTH6igXOn3/M+2OG6p7327/9dFouz8HgOnL6HVn9jl6N73Ivkd5DsMTuKilVtR+e1O6rO/0G6+RLzE+ey/voqcv0x869R7vw3hPn9z369ds7vnxz+9k9v/r0pYdOvPDg0ed/eviZ+z96+r4Pf3vP+09s27fjP95++N/ffOjfXnvgX1+5f/fnmzi+1aWGb8GNrpjFAYQIhDQIcREqReg0QnsQ2onQXXNzf+v3Y243RhCYzYZZLJjRiFVeBe2ToTQ6a/iW2OVzc8g3i9y+Odo5zQds189PiL7zTJbKMis1+QUGP1fn61L72oa9jUpPrcLDklBWe6vmI07W8QHWJ/1lh7qZH3YWvtea905T9hvs9D01F/+34ps/lXzxXMGZ3+eeejLr2BNph3ecO/Dwlx/84sz+n518+/7je+/75I17P3793o9fp/151yoez6amhm/GjU4UXe23W60XtdqXcfxeHL8Lx7+L4/+M43+P43+tUGByOSaTYRYLJpFgYjGm12OsOtA+GbjRWc03o5hq3zLkFhl8RXzHE0HtT4q+80xWTr8ju9+R1efI6HN0jnrTeohL3QRTSJaLqar9kFVzoD3z+ADr42jtL++pufgS69yfSr54rvDsgvZHgto/dHb/A6feAe3jUjUwEdRerz+u1x/3etV6/XG9/ihBlFutp/T6F/X6nXr9g3r9Nr3+dr3+b8bHMa0WU6sxqxUbGsKUSkynwypqQftk4EZn1cAEQqhb7elWe7Q2v9Tk61Z7RAbvyKS/fcRNC2n/bFa1lKyUkOViV6nQ1T7ivTLoyuU5S0VkiZCq2ist6jeqTu9hnXmRefK5/GNPZR1+/OLBh796/xdn9v/s03d/euqdn5x4+/7jb/3Xsb0/PvrmvZ+88aOPX7/nyGt3H371h4f2/OdHr2w7OP8C7UNU8owKgwMhRBAcguDMztoIgkMQbI+n2eXKIQg6QfyZIB4jiG0E8S2bDZuawiwWzGTCrFZMq8XGxjCNBiurBu2ToTAQLJ4RIaS1+bU2v90dMDtmtdP+cfusiZiVT/jCB/ntw57rw55Gpeeq3N0y5KkQk0whWSJyF/FJimqvsmgKJXUqt1rhHpWRIyJSNeCU9zlFNxx8joPXMtPXMNN91d5RbWtjTV8vszaXWJqKTNcKDOy88au52rocTW3WWHXGaDVoH4LFNcj1BELos3b7Z+12rc0fnGgfcRfxncHpuK9KCRmcYArdRwp7QPskyPVEBdeAFkLuGnOXCJ2ftduL+I72Efdn7fYnQto/mxUecpPKE5zIG3AVUFb7IYv6moKDENI6TFqHye51mMkprcNkJqemvTMjM+O4XS21jwhseP+U5MYUv83Ka7T0sC2d12d6S01NhcZ6prmx0HgNtA9R3jcuG59BC4XI459TT/lHp/wmYnZ00i8x+gR6H1fr7VZ7OCOelmFPo9LDlrurpeRVhYcpJAv5rmIBebgAtE+GfHymvG8chVV7EzE7NuXX2vwG+6zU5Asf5LcMeZpUnnqFp0ZGNig9JUKykO8qEpD5g1TVXmUZq5e3I4S6jMIuo1DrMEmmhruMQvHU0BhhaDfwWvT9jfoe9viNWh2nStdapm1mahoKNeyGme7ssarM0coCA7vAUA/ahyjr1QW1D1522t2BzlFPx6hbafH3az2NSne9gqyTk9VSkiV2lYtcJUJXEd+VP+Cslrmzuc6MPkchnzx0BbRPhmx8pqxXh8Ku7cVGX8eoe2DcqzD7mpTu7Z8NBbX/p6cvlotcpUJXMd91ZdBZj3tyuM7MPmf+gCtvgLLam0fZslaEEHOogTnUIJkcbh3vZw41XB/vF06qClX1BSp2vrIuV1mbo6zJxqsycdZlvCJNUc62d15QlJyTM3O1dfnjV0H7EKU9GqnOjhZuMptmZpkCZzHfydV5rynIKwOOPJ4jl+vI4Tqy+x2ZfY6MXkd6L5HWQ5SL3Re6iHM3iPxB8iBonxSpzl7So0ELIYuN3pYhdzHf2YCT/VrvlQHHr04rg4P8f3zyQmYfkdHruNxLpPUQdXLPhS7ifBeRzXUxeFTVXmkevSptiV3un5slAx77rMPsn9Z6jcMenYwc5rlkPU5RG8FrnOmptXPKJ68zzY0FhvpcHWi/CLNLLdHaQrOBOeTxzzm8c9NkYMIR0NpmhydncfOsyOjnjft6NL6OUW/zkIet8FRJPaUid6GAzB0gD+aD9smQam3MLnVodm4OeWfnnN45GxmwOALj9tnQIP/bv8voUfs6R73Xh7z1uKda5ikTu4sE7rxBModLVe3xiZFa8XUUU+2bdL2DVsUV1dW8+VJfnYVXZuCsdLw8TVF2UVFaaWs5J2N+IyvOUdfkaGpB+xDFN8aC2pcInEyB0zAzW8R3Fg46ezTeOjmZx3MwuI7sfkdWqNT3EJe6iYvdRLGAPHeD+OYGweCRH+Z3g/ZJkGhtxTfG0EK1Fxm8zSp34aCTLScr+I6Hjgt/fLB/Xx35y9OK21+suO03Z586WpvWQ1zsJlgSdzDkzH5XFtdFZe2bUeS1/Q2jgG9V4DPqZn3ftfFu9nhnta6dpW0p1TYVaa5d0bBz1TUVU9czRljpQxU5mtpsdQ1oH6Koc0SsmUYIdas9XWMeqzPAGXG3DrvFRt+NMQ9bTtbKyCoJyRK7ykQupsBVyHflDThzuE6mkLzc60zrcWRzSXoeaJ8MiWa6qHMELYQ8Nunn671tw+4ejadP67vAmXm93PbuVf/OSxPfe439yjecwkFH/oCTwXOWi92X+5xpPY6sfldGP1W1VxiHq4WNKOxOvsllHSXGNU6jjjQJbUretKx7SsSZHGiy9LItndXm9jJTc5GxvszazNDWZo9V52hqM+EBXhgFnCGRegohpLX5x6b8dncAN/tlJt+QdVZo8PWovZ2jnrbh4L1ld63MzZKQJaLgvWV3NteV0efK7CfpuaB9MsTqqQLOEEJIa/Orp/wWx6x6alZu8skn/AqzX2z0l/LJPSW2nV+I9+fx24Y9lRKyTEQWCVwlQncO15XZ58roJzP6qKv9UJWgASF0lp93lp/XZRQyhxrO8vMKVOwWfX9wYdxXiaUpOJE+zMoYqQLtQ1xpV4nUkwihwBzyzs65fHN2MmB2BPT2wOjULG72i03+Qb2vT+vrHPO2DHvrcU+VzF0udhcJ3PkDZDaXzOhzgfbJEaknr7Sp0MJVPembm3EHLI6AYSagnp5VWvwSk7+Y5zxRP9Ex6mkd9jYoPTUyT4XEXSxw5w+SOTwys99FXe3lBlXlYD1aqPY2r8PgsgzNaEcc46Mu/cC0omdK1DE1eN3ad81yo9bMYU20ME0NBXo209yYo67JHK3MHK2+DNqHkd+mLLou6BYquwRKzqCydQBv6lewexU1XYqKTkVJu7ygVZ53XZbVJEu/Jj3Pln5ZJz1TIzlVKTlaIT5SJj5UIjrIFL14pvLMpbz1PpSNi2hs8mRxd7dQ2S2cD7mZq6jvVdR2K1g3FKUceUGrPK9FltMsS2+QXqyXflUn/axGeqpKciwYcqn4IFN0kCmi/Xnfeh/Kyrkp7VkDbLRwba92GAVWZauB22MRCWzKq+Od1bo2lq6lVNtUrLl2RX01V12TNVaZMcIqMl27pCy/gJemD7PSh0H7RYRj1nMl17/Ir73JV49Qud6HsnExTbvSqrtvPuTSxu71PpSVs3LtZXplObcOLdzJF06qmnW9har6Om1Hp1nAUNZkK6uz8KrgPfxLirKLitLzcuY5GbPAUP+1tOgrSdElVcUlFQu0B4A1ZuXaS8fxsv7a4HQAzfnm/M5Z17R/xui3qr0GpVstIlV9TvENh6CV6K+336iabi21NhWZGq7o2TmauszR6vThyovKCtAeANaYm9FeUdpXjRBiDjUUqxoGrYoGXfcV1dVqTXvbBC8br8rEKy/jFemK8kuK0guKkvNy5jlZ8TfS4jzd1a/EhV+KCy/g5aA9AKw9K9deolMwe6sQQnNozjfndwXc0/6ZCf+kxmtUejRiUsVzybqcglaCe83eVWVrLZtsLp5oKDDUM7R1WWM1l4erLqlYoD0ArD0r116slRd1s+bQXAAF3AEPEXBN+m16r3nMo1e4x4QuvNch6nAMNhN9bHsna7qlxNJYaLyWP87OVtdkjFSlDVVeVFaA9gCw9qxce5FWdu9bD/zL07ff5Ov3R36/iscDAMCSwF/O3RBgGBZ3OqpNOIk2gsVwi/Z5k5IkkFSyCrWJndhEbL493qqEpA2fjW2Q6N1EC4HY82Ci02J4+Km0iZrYRGy+Pd5KpN4j0VLab8bOty4kOmOuYBiV5Mva4GymfU3O8ePH13sXbi1JtE9Spm6mR26ZSFdwYk20HNsa1f62O9/a7C+E0PENw7K/gJguiEWO9sNbxp1OsvBmeuR6BznPivc/EUkyWZb2mxrQfpVZ9heQoHosV/u4Vf1m6rxM1L/eWc6zskOISiaVgh+aXbLNkgOHDc4W0R5t2hHpqlT7VFZZLqui3IYiNqXwzJOoG3UuQIlP1puFraP9JmW1qv2Sq6yAraT9ck+siZYnGg6A9qD9Mkix2ieaTX4u2Fx98daRpM7HNku+nGLVfneLJriGztyhaznQFbmZrsLb7nxrV5F5Yd6cu3t+xbgtw7bWcqDLnLs7fN0g0gOU0T5qInY2ic+g/ZIkOp8mTzVRJaeW9rm6eZN3FZmRruW2O9/K1S2YubtFE5Q5anpx3ciWu1s0KGxraF77jmOh1Qs7KKZ9VLWPLf6xayXqkYneBWIDCS1JHldsm9jzwuaKetnaR8m8qPRS2odazhueaEwRs/rW1h4A1p7lD/IX6nCuLmI2qfYRLUF7AFhfln9L75g0fJCfpNoHL9rjVntN0VnQHgDWi9S0392i0bVEmRkmfNileGLtw1qGX7oXdoTd/wPtAWANSFn7xVXMubtj7s8j6YE7Y+/GJ2x52zFpaD404A9rGX0fAbQHgFUEntsDAOUA7TcOHDqNEZzSMGiRz4bonOVsZlnNtyzzIdIYoZEqhz4fJ43BoK0gIw496Vcx/4GbIXzQfqOgCeuJ89Pz/1u2xyvq01sOTWQMGgZtIUYNg7ZCOzVLRbtkg40BaL9RiNNfovpQqFgtdFkGLVi4FkqYJv56FCUyBQ2DFlb3F4iJNO7C0OCLRqcvbnNx7ECLOWFvdFLSHiEUdzrREtB++SyO8BeJGgCEuiaHjs035tBDukc2iNPFqUaM9tE+xo00duFi2ih8lBDanoZBXwx7M2qfvGmiU0Dct0D7ZbKE9lHFauGNiLUWZQftUULtw2+bxEYam3PEbYDIbyTOZjaj9inW9iBR2se2BO2XxRKD/Ij+FLKdQ48YiS5Oboa+d4uJvbZfPEVy6BidEzfS2IVh1T4s7gQftEmiX572sbNRLNPYwo7gakv9RGf+kX7oJ0NbUfuEF/ILxSRsSfhonkaPqTmbo+vdamIMDH8+sjBYivOwJHbh4oo0Gm0+6PBWW73ah6+55ElhydeBrvnf6oQmEp4ddC23hf3sb0tqH3+cv4JV4AFekNgHeGvzgZshfCx1eWL1TtImWJ+TyvxWblf0D36pXO1XQOw9fABIhXWs9vF/yR/nFfHT4DhXBGuXFgBsCZZ9Sy/Fap/KK/4f6kj0mv8Zf5w/v7GWea0WGPxVnNUGIk2dm9X+Jqr94rV6sn+BH9k47j/aXdcAlwBLSqhNePvwJdBZY4FIb55o7VEKJgfXTN44lWv78L+ot/RpYjNX+yULUajLxv4XiAtEejNE39JLfhZIVO1Dm1tWtV+t13rktjyS9NHY2bjlK2UWHiwlvZ8832jZDw42EGsXafAGfXRWsTmnlDzaGOHDb/LXguR9NLY0JWq8JKGHd0mf4s0/9tvUv+Vbw0hpDE30g9LYnFNLHm2Q8EH7W0hsnUlUdkLTSdoEq0TyWsKgL/TOpL8b2QgFZ2WsfaTzDSOzis05xeTRxggfLnXWgrUqTWGVJknni/rX/Jvh5yWxrFWkQaK0j805teTRRgkftF8LkvTRqIVJSlMqRPxLkeRdar7obE7p1zBShFCcah+T8zKSR+sfPmi/FiyrjyZaMRVCV4wpWE9jaBL8K/TNwJpFihCK1T4259STRxsgfND+lpNKt0tlRJrShejiX4RY6tJxvQvOzbCWkUYMyiP+/EFkzqknj9Y/fND+FhJ3SJliabqJEelWBiJdFSACAKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAoB2gPAJQDtAcAygHaAwDlAO0BgHKA9gBAOUB7AKAcoD0AUA7QHgAox/8D/71mW2Kr/wgAAAAASUVORK5CYII=
  发现根本没进行排序操作。
  原来SQLSERVER也会偷懒,
  发现WHERE子句中约束字段的值一样时,就跳过了排序!
  为了进行验证,我又写了这样两句SQL:
  SQL1:
  SELECT TOP 4 *
  FROM 表名
  WHERE 工资>=700
  查询结果是:
  名字   工资     ID
  赵五    700     2
  孙六    700     3
  田七    700     4
  王八    800     5
  SQL2:
  SELECT TOP 4 *
  FROM 表名
  WHERE 工资>=700
  ORDER BY 工资 DESC
  查询结果是:
  名字   工资     ID
  王八    800     5
  田七    700     4
  孙六    700     3
  赵五    700     2
  
  很明显,赵五,孙六,田七都进行了倒叙。

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-81639-1-1.html 上篇帖子: update值与原值相同时,SQL Server会真的去update还是忽略呢? 下篇帖子: SQL Server性能调校系列(7)--RAID
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表