sql腳本批處理效率

1. [杞杞絔sql鏁版嵁搴撳備綍鍔犲揩鏌ヨ㈤熷害

[杞杞斤細http://www.3lian.com/e/2013/12-30/120014.html]

1銆佸崌綰х‖浠

2銆佹牴鎹鏌ヨ㈡潯浠,寤虹珛緔㈠紩,浼樺寲緔㈠紩銆佷紭鍖栬塊棶鏂瑰紡錛岄檺鍒剁粨鏋滈泦鐨勬暟鎹閲忋

3銆佹墿澶ф湇鍔″櫒鐨勫唴瀛

4銆佸炲姞鏈嶅姟鍣–PU涓鏁

5銆佸逛簬澶х殑鏁版嵁搴撲笉瑕佽劇疆鏁版嵁搴撹嚜鍔ㄥ為暱錛屽畠浼氶檷浣庢湇鍔″櫒鐨勬ц兘

6銆佸湪鏌ヨSelect璇鍙ヤ腑鐢╓here瀛楀彞闄愬埗榪斿洖鐨勮屾暟,閬垮厤琛ㄦ壂鎻,濡傛灉榪斿洖涓嶅繀瑕佺殑鏁版嵁錛屾氮璐逛簡鏈嶅姟鍣ㄧ殑I/O璧勬簮錛屽姞閲嶄簡緗戠粶鐨勮礋鎷呴檷浣庢ц兘銆傚傛槬鎼忔灉琛ㄥ緢澶э紝鍦ㄨ〃鎵鎻忕殑鏈熼棿灝嗚〃閿佷綇錛岀佹㈠叾浠栫殑鑱旀帴璁塊棶琛,鍚庢灉涓ラ噸銆

7銆佹煡璇㈡椂涓嶈佽繑鍥炰笉闇瑕佺殑琛屻佸垪

8銆佺敤select top 100 / 10 Percent 鏉ラ檺鍒剁敤鎴瘋繑鍥炵殑琛屾暟鎴栬匰ET ROWCOUNT鏉ラ檺鍒舵搷鍒欏惖浣滅殑琛

9銆佸湪IN鍚庨潰鍊肩殑鍒楄〃涓錛屽皢鍑虹幇鏈棰戠箒鐨勫兼斁鍦ㄦ渶鍓嶉潰錛屽嚭鐜板緱鏈灝戠殑鏀懼湪鏈鍚庨潰錛屽噺灝戝垽鏂鐨勬℃暟

10銆佷竴鑸鍦℅ROUP BY 涓狧AVING瀛楀彞涔嬪墠灝辮兘鍓旈櫎澶氫綑鐨勮岋紝鎵浠ュ敖閲忎笉瑕佺敤瀹冧滑鏉ュ仛鍓旈櫎琛岀殑宸ヤ綔銆備粬浠鐨勬墽琛岄『搴忓簲璇ュ備笅鏈浼橈細

select鐨刉here瀛楀彞閫夋嫨鎵鏈夊悎閫傜殑琛岋紝Group By鐢ㄦ潵鍒嗙粍涓緇熻¤岋紝Having瀛楀彞鐢ㄦ潵鍓旈櫎澶氫綑鐨勫垎緇勩傝繖鏍稧roup By 涓狧aving鐨勫紑閿灝忥紝鏌ヨ㈠揩.瀵逛簬澶х殑鏁版嵁琛岃繘琛屽垎緇勫拰Having鍗佸垎娑堣楄祫婧愩傚傛灉Group BY鐨勭洰鐨勪笉鍖呮嫭璁＄畻錛屽彧鏄鍒嗙粍錛岄偅涔堢敤Distinct鏇村揩

11銆佷竴嬈℃洿鏂板氭潯璁板綍姣斿垎澶氭℃洿鏂版瘡嬈′竴鏉″揩,灝辨槸璇存壒澶勭悊濂

MS SQL Server鏌ヨ浼樺寲鏂規硶

鏌ヨ㈤熷害鎱㈢殑鍘熷洜寰堝氾紝甯歌佸備笅鍑犵

1銆佹病鏈夌儲寮曟垨鑰呮病鏈夌敤鍒扮儲寮(榪欐槸鏌ヨ㈡參鏈甯歌佺殑闂棰橈紝鏄紼嬪簭璁捐＄殑緙洪櫡)

2銆両/O鍚炲悙閲忓皬錛屽艦鎴愪簡鐡墮堟晥搴斻

3銆佹病鏈夊壋寤鴻＄畻鍒楀艱嚧鏌ヨ涓嶄紭鍖栥

4銆佸唴瀛樹笉瓚

5銆佺綉緇滈熷害鎱

6銆佹煡璇㈠嚭鐨勬暟鎹閲忚繃澶(鍙浠ラ噰鐢ㄥ氭℃煡璇錛屽叾浠栫殑鏂規硶闄嶄綆鏁版嵁閲)

7銆侀攣鎴栬呮婚攣(榪欎篃鏄鏌ヨ㈡參鏈甯歌佺殑闂棰橈紝鏄紼嬪簭璁捐＄殑緙洪櫡)

8銆乻p_lock,sp_who,媧誨姩鐨勭敤鎴鋒煡鐪,鍘熷洜鏄璇誨啓絝炰簤璧勬簮銆

9銆佽繑鍥炰簡涓嶅繀瑕佺殑琛屽拰鍒

10銆佹煡璇㈣鍙ヤ笉濂斤紝娌℃湁浼樺寲

鍙浠ラ氳繃濡備笅鏂規硶鏉ヤ紭鍖栨煡璇

1銆佹妸鏁版嵁銆佹棩蹇椼佺儲寮曟斁鍒頒笉鍚岀殑I/O璁懼囦笂錛屽炲姞璇誨彇閫熷害錛屼互鍓嶅彲浠ュ皢Tempdb搴旀斁鍦≧AID0涓婏紝SQL2000涓嶅湪鏀鎸併傛暟鎹閲(灝哄)瓚婂ぇ錛屾彁楂業/O瓚婇噸瑕.

2銆佺旱鍚戙佹í鍚戝垎鍓茶〃錛屽噺灝戣〃鐨勫昂瀵(sp_spaceuse)

3銆佸崌綰х‖浠

4銆佹牴鎹鏌ヨ㈡潯浠,寤虹珛緔㈠紩,浼樺寲緔㈠紩銆佷紭鍖栬塊棶鏂瑰紡錛岄檺鍒剁粨鏋滈泦鐨勬暟鎹閲忋傛敞鎰忓～鍏呭洜瀛愯侀傚綋(鏈濂芥槸浣跨敤榛樿ゅ0)銆傜儲寮曞簲璇ュ敖閲忓皬錛屼嬌鐢ㄥ瓧鑺傛暟灝忕殑鍒楀緩緔㈠紩濂(鍙傜収緔㈠紩鐨勫壋寤),涓嶈佸規湁闄愮殑鍑犱釜鍊肩殑瀛楁靛緩鍗曚竴緔㈠紩濡傛у埆瀛楁

5銆佹彁楂樼綉閫;

6銆佹墿澶ф湇鍔″櫒鐨勫唴瀛,Windows 2000鍜孲QL server 2000鑳芥敮鎸4-8G鐨勫唴瀛樸傞厤緗鉶氭嫙鍐呭瓨錛氳櫄鎷熷唴瀛樺ぇ灝忓簲鍩轟簬璁＄畻鏈轟笂騫跺彂榪愯岀殑鏈嶅姟榪涜岄厤緗銆傝繍琛 Microsoft SQL Server? 2000 鏃訛紝鍙鑰冭檻灝嗚櫄鎷熷唴瀛樺ぇ灝忚劇疆涓鴻＄畻鏈轟腑瀹夎呯殑鐗╃悊鍐呭瓨鐨 1.5 鍊嶃傚傛灉鍙﹀栧畨瑁呬簡鍏ㄦ枃媯緔㈠姛鑳斤紝騫舵墦綆楄繍琛 Microsoft 鎼滅儲鏈嶅姟浠ヤ究鎵ц屽叏鏂囩儲寮曞拰鏌ヨ錛屽彲鑰冭檻錛氬皢鉶氭嫙鍐呭瓨澶у皬閰嶇疆涓鴻嚦灝戞槸璁＄畻鏈轟腑瀹夎呯殑鐗╃悊鍐呭瓨鐨 3 鍊嶃傚皢 SQL Server max server memory 鏈嶅姟鍣ㄩ厤緗閫夐」閰嶇疆涓虹墿鐞嗗唴瀛樼殑 1.5 鍊(鉶氭嫙鍐呭瓨澶у皬璁劇疆鐨勪竴鍗)銆

7銆佸炲姞鏈嶅姟鍣–PU涓鏁;浣嗘槸蹇呴』鏄庣櫧騫惰屽勭悊涓茶屽勭悊鏇撮渶瑕佽祫婧愪緥濡傚唴瀛樸備嬌鐢ㄥ苟琛岃繕鏄涓茶岀▼鏄疢sSQL鑷鍔ㄨ瘎浼伴夋嫨鐨勩傚崟涓浠誨姟鍒嗚В鎴愬氫釜浠誨姟錛屽氨鍙浠ュ湪澶勭悊鍣ㄤ笂榪愯屻備緥濡傝芥悂鏌ヨ㈢殑鎺掑簭銆佽繛鎺ャ佹壂鎻忓拰GROUP BY瀛楀彞鍚屾椂鎵ц岋紝SQL SERVER鏍規嵁緋葷粺鐨勮礋杞芥儏鍐靛喅瀹氭渶浼樼殑騫惰岀瓑綰э紝澶嶆潅鐨勯渶瑕佹秷鑰楀ぇ閲忕殑CPU鐨勬煡璇㈡渶閫傚悎騫惰屽勭悊銆備絾鏄鏇存柊鎿嶄綔 UPDATE,INSERT錛孌ELETE榪樹笉鑳藉苟琛屽勭悊銆

8銆佸傛灉鏄浣跨敤like榪涜屾煡璇㈢殑璇濓紝綆鍗曠殑浣跨敤index鏄涓嶈岀殑錛屼絾鏄鍏ㄦ枃緔㈠紩錛岃楃┖闂淬傛墥鐩紲 like 鈥榓%鈥 浣跨敤緔㈠紩 like 鈥%a鈥 涓嶄嬌鐢ㄧ儲寮曠敤 like 鈥%a%鈥 鏌ヨ㈡椂錛屾煡璇㈣楁椂鍜屽瓧孌靛兼婚暱搴︽垚姝ｆ瘮,鎵浠ヤ笉鑳界敤CHAR綾誨瀷錛岃屾槸VARCHAR銆傚逛簬瀛楁電殑鍊煎緢闀跨殑寤哄叏鏂囩儲寮曘

9銆丏B Server 鍜孉PPLication Server 鍒嗙;OLTP鍜孫LAP鍒嗙

10銆佸垎甯冨紡鍒嗗尯瑙嗗浘鍙鐢ㄤ簬瀹炵幇鏁版嵁搴撴湇鍔″櫒鑱斿悎浣撱傝仈鍚堜綋鏄涓緇勫垎寮綆＄悊鐨勬湇鍔″櫒錛屼絾瀹冧滑鐩鎬簰鍗忎綔鍒嗘媴緋葷粺鐨勫勭悊璐熻嵎銆傝繖縐嶉氳繃鍒嗗尯鏁版嵁褰㈡垚鏁版嵁搴撴湇鍔″櫒鑱斿悎浣撶殑鏈哄埗鑳藉熸墿澶т竴緇勬湇鍔″櫒錛屼互鏀鎸佸ぇ鍨嬬殑澶氬眰 Web 絝欑偣鐨勫勭悊闇瑕併傛湁鍏蟲洿澶氫俊鎮錛屽弬瑙佽捐¤仈鍚堟暟鎹搴撴湇鍔″櫒銆(鍙傜収SQL甯鍔╂枃浠垛樺垎鍖鴻嗗浘鈥)

a銆佸湪瀹炵幇鍒嗗尯瑙嗗浘涔嬪墠錛屽繀欏誨厛姘村鉤鍒嗗尯琛

b銆佸湪鍒涘緩鎴愬憳琛ㄥ悗錛屽湪姣忎釜鎴愬憳鏈嶅姟鍣ㄤ笂瀹氫箟涓涓鍒嗗竷寮忓垎鍖鴻嗗浘錛屽苟涓旀瘡涓瑙嗗浘鍏鋒湁鐩稿悓鐨勫悕縐般傝繖鏍鳳紝寮曠敤鍒嗗竷寮忓垎鍖鴻嗗浘鍚嶇殑鏌ヨ㈠彲浠ュ湪浠諱綍涓涓鎴愬憳鏈嶅姟鍣ㄤ笂榪愯屻傜郴緇熸搷浣滃傚悓姣忎釜鎴愬憳鏈嶅姟鍣ㄤ笂閮芥湁涓涓鍘熷嬭〃鐨勫嶆湰涓鏍鳳紝浣嗗叾瀹炴瘡涓鏈嶅姟鍣ㄤ笂鍙鏈変竴涓鎴愬憳琛ㄥ拰涓涓鍒嗗竷寮忓垎鍖鴻嗗浘銆傛暟鎹鐨勪綅緗瀵瑰簲鐢ㄧ▼搴忔槸閫忔槑鐨勩

11銆侀噸寤虹儲寮 DBCC REINDEX ,DBCC INDEXDEFRAG,鏀剁緝鏁版嵁鍜屾棩蹇 DBCC SHRINKDB,DBCC SHRINKFILE. 璁劇疆鑷鍔ㄦ敹緙╂棩蹇.瀵逛簬澶х殑鏁版嵁搴撲笉瑕佽劇疆鏁版嵁搴撹嚜鍔ㄥ為暱錛屽畠浼氶檷浣庢湇鍔″櫒鐨勬ц兘銆 鍦═-sql鐨勫啓娉曚笂鏈夊緢澶х殑璁茬┒錛屼笅闈㈠垪鍑哄父瑙佺殑瑕佺偣錛氶栧厛錛孌BMS澶勭悊鏌ヨ㈣″垝鐨勮繃紼嬫槸榪欐牱鐨勶細

1銆 鏌ヨ㈣鍙ョ殑璇嶆硶銆佽娉曟鏌

2銆 灝嗚鍙ユ彁浜ょ粰DBMS鐨勬煡璇浼樺寲鍣

3銆 浼樺寲鍣ㄥ仛浠ｆ暟浼樺寲鍜屽瓨鍙栬礬寰勭殑浼樺寲

4銆 鐢遍勭紪璇戞ā鍧楃敓鎴愭煡璇㈣勫垝

5銆 鐒跺悗鍦ㄥ悎閫傜殑鏃墮棿鎻愪氦緇欑郴緇熷勭悊鎵ц

6銆 鏈鍚庡皢鎵ц岀粨鏋滆繑鍥炵粰鐢ㄦ埛鍏舵★紝鐪嬩竴涓婼QL SERVER鐨勬暟鎹瀛樻斁鐨勭粨鏋勶細涓涓欏甸潰鐨勫ぇ灝忎負8K(8060)瀛楄妭錛8涓欏甸潰涓轟竴涓鐩樺尯錛屾寜鐓B鏍戝瓨鏀俱

12銆丆ommit鍜宺ollback鐨勫尯鍒 Rollback:鍥炴粴鎵鏈夌殑浜嬬墿銆 Commit:鎻愪氦褰撳墠鐨勪簨鐗. 娌℃湁蹇呰佸湪鍔ㄦ丼QL閲屽啓浜嬬墿錛屽傛灉瑕佸啓璇峰啓鍦ㄥ栭潰濡傦細 begin tran exec(@s) commit trans 鎴栬呭皢鍔ㄦ丼QL 鍐欐垚鍑芥暟鎴栬呭瓨鍌ㄨ繃紼嬨

13銆佸湪鏌ヨSelect璇鍙ヤ腑鐢╓here瀛楀彞闄愬埗榪斿洖鐨勮屾暟,閬垮厤琛ㄦ壂鎻,濡傛灉榪斿洖涓嶅繀瑕佺殑鏁版嵁錛屾氮璐逛簡鏈嶅姟鍣ㄧ殑I/O璧勬簮錛屽姞閲嶄簡緗戠粶鐨勮礋鎷呴檷浣庢ц兘銆傚傛灉琛ㄥ緢澶э紝鍦ㄨ〃鎵鎻忕殑鏈熼棿灝嗚〃閿佷綇錛岀佹㈠叾浠栫殑鑱旀帴璁塊棶琛,鍚庢灉涓ラ噸銆

14銆丼QL鐨勬敞閲婄敵鏄庡規墽琛屾病鏈変換浣曞獎鍝

15銆佸敖鍙鑳戒笉浣跨敤鍏夋爣錛屽畠鍗犵敤澶ч噺鐨勮祫婧愩傚傛灉闇瑕乺ow-by-row鍦版墽琛岋紝灝介噺閲囩敤闈炲厜鏍囨妧鏈,濡傦細鍦ㄥ㈡埛絝寰鐜錛岀敤涓存椂琛錛孴able鍙橀噺錛岀敤瀛愭煡璇錛岀敤Case璇鍙ョ瓑絳夈傛父鏍囧彲浠ユ寜鐓у畠鎵鏀鎸佺殑鎻愬彇閫夐」榪涜屽垎綾伙細 鍙榪 蹇呴』鎸夌収浠庣涓琛屽埌鏈鍚庝竴琛岀殑欏哄簭鎻愬彇琛屻侳ETCH NEXT 鏄鍞涓鍏佽哥殑鎻愬彇鎿嶄綔,涔熸槸榛樿ゆ柟寮忋傚彲婊氬姩鎬 鍙浠ュ湪娓告爣涓浠諱綍鍦版柟闅忔満鎻愬彇浠繪剰琛屻傛父鏍囩殑鎶鏈鍦⊿QL2000涓嬪彉寰楀姛鑳藉緢寮哄ぇ錛屼粬鐨勭洰鐨勬槸鏀鎸佸驚鐜銆

鏈夊洓涓騫跺彂閫夐」

READ_ONLY錛氫笉鍏佽擱氳繃娓告爣瀹氫綅鏇存柊(Update)錛屼笖鍦ㄧ粍鎴愮粨鏋滈泦鐨勮屼腑娌℃湁閿併

OPTIMISTIC WITH valueS:涔愯傚苟鍙戞帶鍒舵槸浜嬪姟鎺у埗鐞嗚虹殑涓涓鏍囧噯閮ㄥ垎銆備箰瑙傚苟鍙戞帶鍒剁敤浜庤繖鏍風殑鎯呭艦錛屽嵆鍦ㄦ墦寮娓告爣鍙婃洿鏂拌岀殑闂撮殧涓錛屽彧鏈夊緢灝忕殑鏈轟細璁╃浜屼釜鐢ㄦ埛鏇存柊鏌愪竴琛屻傚綋鏌愪釜娓告爣浠ユら夐」鎵撳紑鏃訛紝娌℃湁閿佹帶鍒跺叾涓鐨勮岋紝榪欏皢鏈夊姪浜庢渶澶у寲鍏跺勭悊鑳藉姏銆傚傛灉鐢ㄦ埛璇曞浘淇鏀規煇涓琛岋紝鍒欐よ岀殑褰撳墠鍊間細涓庢渶鍚庝竴嬈℃彁鍙栨よ屾椂鑾峰彇鐨勫艱繘琛屾瘮杈冦傚傛灉浠諱綍鍊煎彂鐢熸敼鍙橈紝鍒欐湇鍔″櫒灝變細鐭ラ亾鍏朵粬浜哄凡鏇存柊浜嗘よ岋紝騫朵細榪斿洖涓涓閿欒銆傚傛灉鍊兼槸涓鏍風殑錛屾湇鍔″櫒灝辨墽琛屼慨鏀廣 閫夋嫨榪欎釜騫跺彂閫夐」 OPTIMISTIC WITH ROW VERSIONING:姝や箰瑙傚苟鍙戞帶鍒墮夐」鍩轟簬琛岀増鏈鎺у埗銆備嬌鐢ㄨ岀増鏈鎺у埗錛屽叾涓鐨勮〃蹇呴』鍏鋒湁鏌愮嶇増鏈鏍囪瘑絎︼紝鏈嶅姟鍣ㄥ彲鐢ㄥ畠鏉ョ『瀹氳ヨ屽湪璇誨叆娓告爣鍚庢槸鍚︽湁鎵鏇存敼銆

鍦 SQL Server 涓錛岃繖涓鎬ц兘鐢 timestamp 鏁版嵁綾誨瀷鎻愪緵錛屽畠鏄涓涓浜岃繘鍒舵暟瀛楋紝琛ㄧず鏁版嵁搴撲腑鏇存敼鐨勭浉瀵歸『搴忋傛瘡涓鏁版嵁搴撻兘鏈変竴涓鍏ㄥ矓褰撳墠鏃墮棿鎴沖礆細@@DBTS銆傛瘡嬈′互浠諱綍鏂瑰紡鏇存敼甯︽湁 timestamp 鍒楃殑琛屾椂錛孲QL Server 鍏堝湪鏃墮棿鎴沖垪涓瀛樺偍褰撳墠鐨 @@DBTS 鍊礆紝鐒跺悗澧炲姞 @@DBTS 鐨勫箋傚傛灉鏌 涓琛ㄥ叿鏈 timestamp 鍒楋紝鍒欐椂闂存埑浼氳璁板埌琛岀駭銆傛湇鍔″櫒灝卞彲浠ユ瘮杈冩煇琛岀殑褰撳墠鏃墮棿鎴沖煎拰涓婃℃彁鍙栨椂鎵瀛樺偍鐨勬椂闂存埑鍊礆紝浠庤岀『瀹氳ヨ屾槸鍚﹀凡鏇存柊銆傛湇鍔″櫒涓嶅繀姣旇緝鎵鏈夊垪鐨勫礆紝鍙闇姣旇緝 timestamp 鍒楀嵆鍙銆傚傛灉搴旂敤紼嬪簭瀵規病鏈 timestamp 鍒楃殑琛ㄨ佹眰鍩轟簬琛岀増鏈鎺у埗鐨勪箰瑙傚苟鍙戱紝鍒欐父鏍囬粯璁や負鍩轟簬鏁板肩殑涔愯傚苟鍙戞帶鍒躲

SCROLL LOCKS 榪欎釜閫夐」瀹炵幇鎮茶傚苟鍙戞帶鍒躲傚湪鎮茶傚苟鍙戞帶鍒朵腑錛屽湪鎶婃暟鎹搴撶殑琛岃誨叆娓告爣緇撴灉闆嗘椂錛屽簲鐢ㄧ▼搴忓皢璇曞浘閿佸畾鏁版嵁搴撹屻傚湪浣跨敤鏈嶅姟鍣ㄦ父鏍囨椂錛屽皢琛岃誨叆娓告爣鏃朵細鍦ㄥ叾涓婃斁緗涓涓鏇存柊閿併傚傛灉鍦ㄤ簨鍔″唴鎵撳紑娓告爣錛屽垯璇ヤ簨鍔℃洿鏂伴攣灝嗕竴鐩翠繚鎸佸埌浜嬪姟琚鎻愪氦鎴栧洖婊;褰撴彁鍙栦笅涓琛屾椂錛屽皢闄ゅ幓娓告爣閿併傚傛灉鍦ㄤ簨鍔″栨墦寮娓告爣錛屽垯鎻愬彇涓嬩竴琛屾椂錛岄攣灝辮涓㈠純銆傚洜姝わ紝姣忓綋鐢ㄦ埛闇瑕佸畬鍏ㄧ殑鎮茶傚苟鍙戞帶鍒舵椂錛屾父鏍囬兘搴斿湪浜嬪姟鍐呮墦寮銆傛洿鏂伴攣灝嗛樆姝浠諱綍鍏跺畠浠誨姟鑾峰彇鏇存柊閿佹垨鎺掑畠閿侊紝浠庤岄樆姝㈠叾瀹冧換鍔℃洿鏂拌ヨ屻

鐒惰岋紝鏇存柊閿佸苟涓嶉樆姝㈠叡浜閿侊紝鎵浠ュ畠涓嶄細闃繪㈠叾瀹冧換鍔¤誨彇琛岋紝闄ら潪絎浜屼釜浠誨姟涔熷湪瑕佹眰甯︽洿鏂伴攣鐨勮誨彇銆傛粴鍔ㄩ攣鏍規嵁鍦ㄦ父鏍囧畾涔夌殑 SELECT 璇鍙ヤ腑鎸囧畾鐨勯攣鎻愮ず錛岃繖浜涙父鏍囧苟鍙戦夐」鍙浠ョ敓鎴愭粴鍔ㄩ攣銆傛粴鍔ㄩ攣鍦ㄦ彁鍙栨椂鍦ㄦ瘡琛屼笂鑾峰彇錛屽苟淇濇寔鍒頒笅嬈℃彁鍙栨垨鑰呮父鏍囧叧闂錛屼互鍏堝彂鐢熻呬負鍑嗐備笅嬈℃彁鍙栨椂錛屾湇鍔″櫒涓烘柊鎻愬彇涓鐨勮岃幏鍙栨粴鍔ㄩ攣錛屽苟閲婃斁涓婃℃彁鍙栦腑琛岀殑婊氬姩閿併傛粴鍔ㄩ攣鐙絝嬩簬浜嬪姟閿侊紝騫跺彲浠ヤ繚鎸佸埌涓涓鎻愪氦鎴栧洖婊氭搷浣滀箣鍚庛傚傛灉鎻愪氦鏃跺叧闂娓告爣鐨勯夐」涓哄叧錛屽垯 COMMIT 璇鍙ュ苟涓嶅叧闂浠諱綍鎵撳紑鐨勬父鏍囷紝鑰屼笖婊氬姩閿佽淇濈暀鍒版彁浜や箣鍚庯紝浠ョ淮鎶ゅ規墍鎻愬彇鏁版嵁鐨勯殧紱匯傛墍鑾峰彇婊氬姩閿佺殑綾誨瀷鍙栧喅浜庢父鏍囧苟鍙戦夐」鍜屾父鏍 SELECT 璇鍙ヤ腑鐨勯攣鎻愮ず銆

閿佹彁紺 鍙璇 涔愯傛暟鍊 涔愯傝岀増鏈鎺у埗 閿佸畾鏃犳彁紺 鏈閿佸畾 鏈閿佸畾 鏈閿佸畾 鏇存柊 NOLOCK 鏈閿佸畾 鏈閿佸畾 鏈閿佸畾 鏈閿佸畾 HOLDLOCK 鍏變韓 鍏變韓 鍏變韓 鏇存柊 UPDLOCK 閿欒 鏇存柊 鏇存柊 鏇存柊 TABLOCKX 閿欒 鏈閿佸畾 鏈閿佸畾 鏇存柊鍏跺畠 鏈閿佸畾 鏈閿佸畾 鏈閿佸畾 鏇存柊 *鎸囧畾 NOLOCK 鎻愮ず灝嗕嬌鎸囧畾浜嗚ユ彁紺虹殑琛ㄥ湪娓告爣鍐呮槸鍙璇葷殑銆

16銆佺敤Profiler鏉ヨ窡韙鏌ヨ錛屽緱鍒版煡璇㈡墍闇鐨勬椂闂達紝鎵懼嚭SQL鐨勯棶棰樻墍鍦;鐢ㄧ儲寮曚紭鍖栧櫒浼樺寲緔㈠紩

17銆佹敞鎰廢Nion鍜孶Nion all 鐨勫尯鍒銆俇NION all濂

18銆佹敞鎰忎嬌鐢―ISTINCT錛屽湪娌℃湁蹇呰佹椂涓嶈佺敤錛屽畠鍚孶NION涓鏍蜂細浣挎煡璇㈠彉鎱銆傞噸澶嶇殑璁板綍鍦ㄦ煡璇㈤噷鏄娌℃湁闂棰樼殑

19銆佹煡璇㈡椂涓嶈佽繑鍥炰笉闇瑕佺殑琛屻佸垪

20銆佺敤sp_configure 鈥榪uery governor cost limit鈥樻垨鑰匰ET QUERY_GOVERNOR_COST_LIMIT鏉ラ檺鍒舵煡璇㈡秷鑰楃殑璧勬簮銆傚綋璇勪及鏌ヨ㈡秷鑰楃殑璧勬簮瓚呭嚭闄愬埗鏃訛紝鏈嶅姟鍣ㄨ嚜鍔ㄥ彇娑堟煡璇,鍦ㄦ煡璇涔嬪墠灝辨壖鏉鎺夈 SET LOCKTIME璁劇疆閿佺殑鏃墮棿

10涓囨暟鎹閲忔煡璇㈠眳鐒惰50縐

鐢╯elect top 100 / 10 Percent 鏉ラ檺鍒剁敤鎴瘋繑鍥炵殑琛屾暟鎴栬匰ET ROWCOUNT鏉ラ檺鍒舵搷浣滅殑琛

22銆佸湪SQL2000浠ュ墠錛屼竴鑸涓嶈佺敤濡備笅鐨勫瓧鍙: "IS NULL", " <> ", "!=", "!> ", "! <", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE 鈥%500鈥"錛屽洜涓轟粬浠涓嶈蛋緔㈠紩鍏ㄦ槸琛ㄦ壂鎻忋備篃涓嶈佸湪WHere瀛楀彞涓鐨勫垪鍚嶅姞鍑芥暟錛屽侰onvert錛宻ubstring絳,濡傛灉蹇呴』鐢ㄥ嚱鏁扮殑鏃跺欙紝鍒涘緩璁＄畻鍒楀啀鍒涘緩緔㈠紩鏉ユ浛浠.榪樺彲浠ュ彉閫氬啓娉曪細WHERE SUBSTRING(firstname,1,1) = 鈥榤鈥樻敼涓篧HERE firstname like 鈥榤%鈥(緔㈠紩鎵鎻)錛屼竴瀹氳佸皢鍑芥暟鍜屽垪鍚嶅垎寮銆傚苟涓旂儲寮曚笉鑳藉緩寰楀お澶氬拰澶澶с侼OT IN浼氬氭℃壂鎻忚〃錛屼嬌鐢‥XISTS銆丯OT EXISTS 錛孖N , LEFT OUTER JOIN 鏉ユ浛浠ｏ紝鐗瑰埆鏄宸﹁繛鎺,鑰孍xists姣擨N鏇村揩錛屾渶鎱㈢殑鏄疦OT鎿嶄綔.濡傛灉鍒楃殑鍊煎惈鏈夌┖錛屼互鍓嶅畠鐨勭儲寮曚笉璧蜂綔鐢錛岀幇鍦2000鐨勪紭鍖栧櫒鑳藉熷勭悊浜嗐傜浉鍚岀殑鏄疘S NULL錛“NOT", "NOT EXISTS", "NOT IN"鑳戒紭鍖栧ス錛岃” <> ”絳夎繕鏄涓嶈兘浼樺寲錛岀敤涓嶅埌緔㈠紩銆

23銆佷嬌鐢≦uery Analyzer錛屾煡鐪婼QL璇鍙ョ殑鏌ヨ㈣″垝鍜岃瘎浼板垎鏋愭槸鍚︽槸浼樺寲鐨凷QL銆備竴鑸鐨20%鐨勪唬鐮佸崰鎹浜80%鐨勮祫婧愶紝鎴戜滑浼樺寲鐨勯噸鐐規槸榪欎簺鎱㈢殑鍦版柟銆

24銆佸傛灉浣跨敤浜咺N鎴栬匫R絳夋椂鍙戠幇鏌ヨ㈡病鏈夎蛋緔㈠紩錛屼嬌鐢ㄦ樉紺虹敵鏄庢寚瀹氱儲寮曪細 SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN (‘鐢’錛‘濂’)

25銆佸皢闇瑕佹煡璇㈢殑緇撴灉棰勫厛璁＄畻濂芥斁鍦ㄨ〃涓錛屾煡璇㈢殑鏃跺欏啀SELECT銆傝繖鍦⊿QL7.0浠ュ墠鏄鏈閲嶈佺殑鎵嬫點備緥濡傚尰闄㈢殑浣忛櫌璐硅＄畻銆

26銆丮IN() 鍜 MAX()鑳戒嬌鐢ㄥ埌鍚堥傜殑緔㈠紩

27銆佹暟鎹搴撴湁涓涓鍘熷垯鏄浠ｇ爜紱繪暟鎹瓚婅繎瓚婂ソ錛屾墍浠ヤ紭鍏堥夋嫨Default,渚濇′負Rules,Triggers, Constraint(綰︽潫濡傚栧仴涓誨仴CheckUNIQUE……,鏁版嵁綾誨瀷鐨勬渶澶ч暱搴︾瓑絳夐兘鏄綰︽潫),Procere.榪欐牱涓嶄粎緇存姢宸ヤ綔灝忥紝緙栧啓紼嬪簭璐ㄩ噺楂橈紝騫朵笖鎵ц岀殑閫熷害蹇銆

28銆佸傛灉瑕佹彃鍏ュぇ鐨勪簩榪涘埗鍊煎埌Image鍒楋紝浣跨敤瀛樺偍榪囩▼錛屽崈涓囦笉瑕佺敤鍐呭祵INsert鏉ユ彃鍏(涓嶇煡JAVA鏄鍚)銆傚洜涓鴻繖鏍峰簲鐢ㄧ▼搴忛栧厛灝嗕簩榪涘埗鍊艱漿鎹㈡垚瀛楃︿覆(灝哄告槸瀹冪殑涓ゅ)錛屾湇鍔″櫒鍙楀埌瀛楃﹀悗鍙堝皢浠栬漿鎹㈡垚浜岃繘鍒跺.瀛樺偍榪囩▼灝辨病鏈夎繖浜涘姩浣: 鏂規硶錛欳reate procere p_insert as insert into table(Fimage) values (@image), 鍦ㄥ墠鍙拌皟鐢ㄨ繖涓瀛樺偍榪囩▼浼犲叆浜岃繘鍒跺弬鏁幫紝榪欐牱澶勭悊閫熷害鏄庢樉鏀瑰杽銆

29銆丅etween鍦ㄦ煇浜涙椂鍊欐瘮IN閫熷害鏇村揩,Between鑳藉熸洿蹇鍦版牴鎹緔㈠紩鎵懼埌鑼冨洿銆傜敤鏌ヨ浼樺寲鍣ㄥ彲瑙佸埌宸鍒銆 select * from chineseresume where title in (鈥樼敺鈥,鈥樺コ鈥) Select * from chineseresume where between 鈥樼敺鈥 and 鈥樺コ鈥 鏄涓鏍風殑銆傜敱浜巌n浼氬湪姣旇緝澶氭★紝鎵浠ユ湁鏃朵細鎱浜涖

30銆佸湪蹇呰佹槸瀵瑰叏灞鎴栬呭矓閮ㄤ復鏃惰〃鍒涘緩緔㈠紩錛屾湁鏃惰兘澶熸彁楂橀熷害錛屼絾涓嶆槸涓瀹氫細榪欐牱錛屽洜涓虹儲寮曚篃鑰楄垂澶ч噺鐨勮祫婧愩備粬鐨勫壋寤哄悓鏄瀹為檯琛ㄤ竴鏍楓

31銆佷笉瑕佸緩娌℃湁浣滅敤鐨勪簨鐗╀緥濡備駭鐢熸姤琛ㄦ椂錛屾氮璐硅祫婧愩傚彧鏈夊湪蹇呰佷嬌鐢ㄤ簨鐗╂椂浣跨敤瀹冦

32銆佺敤OR鐨勫瓧鍙ュ彲浠ュ垎瑙ｆ垚澶氫釜鏌ヨ錛屽苟涓旈氳繃UNION 榪炴帴澶氫釜鏌ヨ銆備粬浠鐨勯熷害鍙鍚屾槸鍚︿嬌鐢ㄧ儲寮曟湁鍏,濡傛灉鏌ヨ㈤渶瑕佺敤鍒拌仈鍚堢儲寮曪紝鐢║NION all鎵ц岀殑鏁堢巼鏇撮珮.澶氫釜OR鐨勫瓧鍙ユ病鏈夌敤鍒扮儲寮曪紝鏀瑰啓鎴怳NION鐨勫艦寮忓啀璇曞浘涓庣儲寮曞尮閰嶃備竴涓鍏抽敭鐨勯棶棰樻槸鍚︾敤鍒扮儲寮曘

33銆佸敖閲忓皯鐢ㄨ嗗浘錛屽畠鐨勬晥鐜囦綆銆傚硅嗗浘鎿嶄綔姣旂洿鎺ュ硅〃鎿嶄綔鎱,鍙浠ョ敤stored procere鏉ヤ唬鏇垮ス銆傜壒鍒鐨勬槸涓嶈佺敤瑙嗗浘宓屽,宓屽楄嗗浘澧炲姞浜嗗繪壘鍘熷嬭祫鏂欑殑闅懼害銆傛垜浠鐪嬭嗗浘鐨勬湰璐錛氬畠鏄瀛樻斁鍦ㄦ湇鍔″櫒涓婄殑琚浼樺寲濂戒簡鐨勫凡緇忎駭鐢熶簡鏌ヨ㈣勫垝鐨凷QL銆傚瑰崟涓琛ㄦ緔㈡暟鎹鏃訛紝涓嶈佷嬌鐢ㄦ寚鍚戝氫釜琛ㄧ殑瑙嗗浘錛岀洿鎺ヤ粠琛ㄦ緔㈡垨鑰呬粎浠呭寘鍚榪欎釜琛ㄧ殑瑙嗗浘涓婅伙紝鍚﹀垯澧炲姞浜嗕笉蹇呰佺殑寮閿,鏌ヨ㈠彈鍒板共鎵.涓轟簡鍔犲揩瑙嗗浘鐨勬煡璇錛孧sSQL澧炲姞浜嗚嗗浘緔㈠紩鐨勫姛鑳姐

34銆佹病鏈夊繀瑕佹椂涓嶈佺敤DISTINCT鍜孫RDER BY錛岃繖浜涘姩浣滃彲浠ユ敼鍦ㄥ㈡埛絝鎵ц屻傚畠浠澧炲姞浜嗛濆栫殑寮閿銆傝繖鍚孶NION 鍜孶NION ALL涓鏍風殑閬撶悊銆 SELECT top 20 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in(鈥楯CNAD00329667鈥,鈥楯CNAD132168鈥,鈥楯CNAD00337748鈥,鈥楯CNAD00338345鈥,鈥楯CNAD00333138鈥,鈥楯CNAD00303570鈥, 鈥楯CNAD00303569鈥,鈥楯CNAD00303568鈥,鈥楯CNAD00306698鈥,鈥楯CNAD00231935鈥,鈥楯CNAD00231933鈥,鈥楯CNAD00254567鈥, 鈥楯CNAD00254585鈥,鈥楯CNAD00254608鈥,鈥楯CNAD00254607鈥,鈥楯CNAD00258524鈥,鈥楯CNAD00332133鈥,鈥楯CNAD00268618鈥, 鈥楯CNAD00279196鈥,鈥楯CNAD00268613鈥) order by postdate desc

35銆佸湪IN鍚庨潰鍊肩殑鍒楄〃涓錛屽皢鍑虹幇鏈棰戠箒鐨勫兼斁鍦ㄦ渶鍓嶉潰錛屽嚭鐜板緱鏈灝戠殑鏀懼湪鏈鍚庨潰錛屽噺灝戝垽鏂鐨勬℃暟

36銆佸綋鐢⊿ELECT INTO鏃訛紝瀹冧細閿佷綇緋葷粺琛(sysobjects錛宻ysindexes絳夌瓑)錛岄樆濉炲叾浠栫殑榪炴帴鐨勫瓨鍙栥傚壋寤轟復鏃惰〃鏃剁敤鏄劇ず鐢蟲槑璇鍙ワ紝鑰屼笉鏄 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 鈥榲YZ鈥 --commit 鍦ㄥ彟涓涓榪炴帴涓璖ELECT * from sysobjects鍙浠ョ湅鍒 SELECT INTO 浼氶攣浣忕郴緇熻〃錛孋reate table 涔熶細閿佺郴緇熻〃(涓嶇℃槸涓存椂琛ㄨ繕鏄緋葷粺琛)銆傛墍浠ュ崈涓囦笉瑕佸湪浜嬬墿鍐呬嬌鐢ㄥ畠!!!榪欐牱鐨勮瘽濡傛灉鏄緇忓父瑕佺敤鐨勪復鏃惰〃璇蜂嬌鐢ㄥ疄琛錛屾垨鑰呬復鏃惰〃鍙橀噺銆

37銆佷竴鑸鍦℅ROUP BY 涓狧AVING瀛楀彞涔嬪墠灝辮兘鍓旈櫎澶氫綑鐨勮岋紝鎵浠ュ敖閲忎笉瑕佺敤瀹冧滑鏉ュ仛鍓旈櫎琛岀殑宸ヤ綔銆備粬浠鐨勬墽琛岄『搴忓簲璇ュ備笅鏈浼橈細select 鐨刉here瀛楀彞閫夋嫨鎵鏈夊悎閫傜殑琛岋紝Group By鐢ㄦ潵鍒嗙粍涓緇熻¤岋紝Having瀛楀彞鐢ㄦ潵鍓旈櫎澶氫綑鐨勫垎緇勩傝繖鏍稧roup By 涓狧aving鐨勫紑閿灝忥紝鏌ヨ㈠揩.瀵逛簬澶х殑鏁版嵁琛岃繘琛屽垎緇勫拰Having鍗佸垎娑堣楄祫婧愩傚傛灉Group BY鐨勭洰鐨勪笉鍖呮嫭璁＄畻錛屽彧鏄鍒嗙粍錛岄偅涔堢敤Distinct鏇村揩

38銆佷竴嬈℃洿鏂板氭潯璁板綍姣斿垎澶氭℃洿鏂版瘡嬈′竴鏉″揩,灝辨槸璇存壒澶勭悊濂

39銆佸皯鐢ㄤ復鏃惰〃錛屽敖閲忕敤緇撴灉闆嗗拰Table綾繪х殑鍙橀噺鏉ヤ唬鏇垮畠,Table 綾誨瀷鐨勫彉閲忔瘮涓存椂琛ㄥソ

40銆佸湪SQL2000涓嬶紝璁＄畻瀛楁墊槸鍙浠ョ儲寮曠殑錛岄渶瑕佹弧瓚崇殑鏉′歡濡備笅錛

a銆佽＄畻瀛楁電殑琛ㄨ揪鏄紜瀹氱殑

b銆佷笉鑳界敤鍦═EXT,Ntext錛孖mage鏁版嵁綾誨瀷

c銆佸繀欏婚厤鍒跺備笅閫夐」 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

41銆佸敖閲忓皢鏁版嵁鐨勫勭悊宸ヤ綔鏀懼湪鏈嶅姟鍣ㄤ笂錛屽噺灝戠綉緇滅殑寮閿錛屽備嬌鐢ㄥ瓨鍌ㄨ繃紼嬨傚瓨鍌ㄨ繃紼嬫槸緙栬瘧濂姐佷紭鍖栬繃銆佸苟涓旇緇勭粐鍒頒竴涓鎵ц岃勫垝閲屻佷笖瀛樺偍鍦ㄦ暟鎹搴撲腑鐨 SQL璇鍙ワ紝鏄鎺у埗嫻佽璦鐨勯泦鍚堬紝閫熷害褰撶劧蹇銆傚弽澶嶆墽琛岀殑鍔ㄦ丼QL,鍙浠ヤ嬌鐢ㄤ復鏃跺瓨鍌ㄨ繃紼嬶紝璇ヨ繃紼(涓存椂琛)琚鏀懼湪Tempdb涓銆備互鍓嶇敱浜嶴QL SERVER瀵瑰嶆潅鐨勬暟瀛﹁＄畻涓嶆敮鎸侊紝鎵浠ヤ笉寰椾笉灝嗚繖涓宸ヤ綔鏀懼湪鍏朵粬鐨勫眰涓婅屽炲姞緗戠粶鐨勫紑閿銆係QL2000鏀鎸乁DFs,鐜板湪鏀鎸佸嶆潅鐨勬暟瀛﹁＄畻錛屽嚱鏁扮殑榪斿洖鍊間笉瑕佸お澶э紝榪欐牱鐨勫紑閿寰堝ぇ銆傜敤鎴瘋嚜瀹氫箟鍑芥暟璞″厜鏍囦竴鏍鋒墽琛岀殑娑堣楀ぇ閲忕殑璧勬簮錛屽傛灉榪斿洖澶х殑緇撴灉閲囩敤瀛樺偍榪囩▼

42銆佷笉瑕佸湪涓鍙ヨ瘽閲屽啀涓夌殑浣跨敤鐩稿悓鐨勫嚱鏁幫紝嫻璐硅祫婧,灝嗙粨鏋滄斁鍦ㄥ彉閲忛噷鍐嶈皟鐢ㄦ洿蹇

43銆丼ELECT COUNT(*)鐨勬晥鐜囨暀浣庯紝灝介噺鍙橀氫粬鐨勫啓娉曪紝鑰孍XISTS蹇.鍚屾椂璇鋒敞鎰忓尯鍒錛 select count(Field of null) from Table 鍜 select count(Field of NOT null) from Table 鐨勮繑鍥炲兼槸涓嶅悓鐨勩

44銆佸綋鏈嶅姟鍣ㄧ殑鍐呭瓨澶熷氭椂錛岄厤鍒剁嚎紼嬫暟閲 = 鏈澶ц繛鎺ユ暟+5錛岃繖鏍瘋兘鍙戞尌鏈澶х殑鏁堢巼;鍚﹀垯浣跨敤 閰嶅埗綰跨▼鏁伴噺 <鏈澶ц繛鎺ユ暟鍚鐢⊿QL SERVER鐨勭嚎紼嬫睜鏉ヨВ鍐,濡傛灉榪樻槸鏁伴噺 = 鏈澶ц繛鎺ユ暟+5錛屼弗閲嶇殑鎹熷蟲湇鍔″櫒鐨勬ц兘銆

45銆佹寜鐓т竴瀹氱殑嬈″簭鏉ヨ塊棶浣犵殑琛ㄣ傚傛灉浣犲厛閿佷綇琛ˋ錛屽啀閿佷綇琛˙錛岄偅涔堝湪鎵鏈夌殑瀛樺偍榪囩▼涓閮借佹寜鐓ц繖涓欏哄簭鏉ラ攣瀹氬畠浠銆傚傛灉浣(涓嶇粡鎰忕殑)鏌愪釜瀛樺偍榪囩▼涓鍏堥攣瀹氳〃B錛屽啀閿佸畾琛ˋ錛岃繖鍙鑳藉氨浼氬艱嚧涓涓姝婚攣銆傚傛灉閿佸畾欏哄簭娌℃湁琚棰勫厛璇︾粏鐨勮捐″ソ錛屾婚攣寰堥毦琚鍙戠幇

46銆侀氳繃SQL Server Performance Monitor鐩戣嗙浉搴旂‖浠剁殑璐熻澆 Memory: Page Faults / sec璁℃暟鍣ㄥ傛灉璇ュ煎伓灝旇蛋楂橈紝琛ㄦ槑褰撴椂鏈夌嚎紼嬬珵浜夊唴瀛樸傚傛灉鎸佺畫寰堥珮錛屽垯鍐呭瓨鍙鑳芥槸鐡墮堛

Process:

1銆% DPC Time 鎸囧湪鑼冧緥闂撮殧鏈熼棿澶勭悊鍣ㄧ敤鍦ㄧ紦寤剁▼搴忚皟鐢(DPC)鎺ユ敹鍜屾彁渚涙湇鍔＄殑鐧懼垎姣斻(DPC 姝ｅ湪榪愯岀殑涓烘瘮鏍囧噯闂撮殧浼樺厛鏉冧綆鐨勯棿闅)銆 鐢變簬 DPC 鏄浠ョ壒鏉冩ā寮忔墽琛岀殑錛孌PC 鏃墮棿鐨勭櫨鍒嗘瘮涓虹壒鏉冩椂闂 鐧懼垎姣旂殑涓閮ㄥ垎銆傝繖浜涙椂闂村崟鐙璁＄畻騫朵笖涓嶅睘浜庨棿闅旇＄畻鎬繪暟鐨勪竴閮 鍒嗐傝繖涓鎬繪暟鏄劇ず浜嗕綔涓哄疄渚嬫椂闂寸櫨鍒嗘瘮鐨勫鉤鍧囧繖鏃躲

2銆%Processor Time璁℃暟鍣ㄥ傛灉璇ュ弬鏁板兼寔緇瓚呰繃95%錛岃〃鏄庣摱棰堟槸CPU銆傚彲浠ヨ冭檻澧炲姞涓涓澶勭悊鍣ㄦ垨鎹涓涓鏇村揩鐨勫勭悊鍣ㄣ

3銆% Privileged Time 鎸囬潪闂茬疆澶勭悊鍣ㄦ椂闂寸敤浜庣壒鏉冩ā寮忕殑鐧懼垎姣斻(鐗規潈妯″紡鏄涓烘搷浣滅郴緇熺粍浠跺拰鎿嶇旱紜浠墮┍鍔ㄧ▼搴忚岃捐＄殑涓縐嶅勭悊妯″紡銆傚畠鍏佽哥洿鎺ヨ塊棶紜浠跺拰鎵鏈夊唴瀛樸傚彟涓縐嶆ā寮忎負鐢ㄦ埛妯″紡錛屽畠鏄涓縐嶄負搴旂敤紼嬪簭銆佺幆澧冨垎緋葷粺鍜屾暣鏁板垎緋葷粺璁

2. sql中的批處理

SQL批處理：指包含一條或多條T - SQL語句的語句組，這組語句從應用程序一次性地發送到SQL server伺服器執行。編批處理程序時，最好能夠以分號結束相關語句。雖然這不資料庫強制求，但筆還強烈建議如此處理。方面這有利於提批處理程序讀性。批處理程序往往完成些比較復雜成套功能，而每條語句則完成項獨立功能。此有時個比較復雜些批處理程序其往往有百行容。此時提其讀性，最好能夠利分號進行語句語句間分隔。二未版本性。其實SQL Server資料庫設計時候，開始這方面就關不嚴。現部分標准程序編器都實現類似強制控制。根據憲梓微軟官方提供資料看，以SQL Server資料庫版本，這個規則能成個強執行規則，即必須每條語句面利分號進行分隔。此能夠跟續SQL Server資料庫版本進行，最好從現開始就采分號分隔批處理程序每條語句。