{"id":1868,"date":"2011-07-18T14:11:13","date_gmt":"2011-07-18T06:11:13","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=1868"},"modified":"2011-08-18T16:07:11","modified_gmt":"2011-08-18T08:07:11","slug":"%e6%9a%ab%e5%ad%98%e8%a1%a8temporary-tables%e7%9a%84%e4%bd%bf%e7%94%a8%e7%b0%a1%e4%bb%8b","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2011\/07\/18\/%e6%9a%ab%e5%ad%98%e8%a1%a8temporary-tables%e7%9a%84%e4%bd%bf%e7%94%a8%e7%b0%a1%e4%bb%8b\/","title":{"rendered":"\u66ab\u5b58\u8868(Temporary Tables)\u7684\u4f7f\u7528\u7c21\u4ecb"},"content":{"rendered":"<p><a href=\"http:\/\/cbw0731.pixnet.net\/blog\/post\/24993864\" target=\"_blank\">http:\/\/cbw0731.pixnet.net\/blog\/post\/24993864<\/a><\/p>\n<p><strong>\u66ab\u5b58\u8868(Temporary Tables)<\/strong><\/p>\n<div>\n<pre>CREATE TABLE #Yaks (\r\nYakID int,\r\nYakName char(30) )<\/pre>\n<\/div>\n<div>\n<ul>\n<li>table name \u524d\u52a0\u5165&#8221;#&#8221;, \u8868\u793a\u9019\u662f\u4e00\u500b\u66ab\u5b58\u8868(temporary table)<\/li>\n<li>\u7576session \u95dc\u9589\u6642, \u9019\u500btable \u5c07\u6703\u81ea\u52d5drop<\/li>\n<li>\u597d\u7684\u5beb\u4f5c\u7fd2\u6163, \u61c9\u5728\u66ab\u5b58\u8868\u4f7f\u7528\u5b8c\u7562\u5f8c, \u4e0b\u6307\u4ee4\u53bb drop, \u800c\u4e0d\u662f\u8b93\u7cfb\u7d71\u81ea\u52d5\u56de\u6536<\/li>\n<li>\u66ab\u5b58\u8868\u662f\u5b58\u5728\u4e3b\u6a5f\u8a18\u61b6\u9ad4\u4e2d, \u56e0\u6b64\u5b58\u53d6\u901f\u5ea6\u8f03\u5feb<\/li>\n<li>\u66ab\u5b58 table \u7684\u9650\u5236:<\/li>\n<li>\u66ab\u5b58\u8868\u5b58\u5728\u65bc&#8221;tempdb&#8221;\u9019\u500bdatabase \u88e1<\/li>\n<li>\u5982\u679c\u6709\u5169\u500b\u4f7f\u7528\u8005\u5efa\u7acb\u540c\u4e00\u500b\u540d\u5b57\u7684\u66ab\u5b58\u8868, \u5247\u4ed6\u5011\u6703\u5404\u81ea\u64c1\u6709\u7368\u7acb\u7684\u4e00\u4efd, \u4e92\u76f8\u4e0d\u6703\u5e72\u64fe.<\/li>\n<li>\u82e5stored procedure A \u5efa\u7acb\u4e86\u4e00\u500b\u66ab\u5b58\u8868, \u4e26\u547c\u53eb stored procedure B, \u5247\u5728 B \u4e2d\u53ef\u4ee5\u5b58\u53d6\u9019\u500b\u66ab\u5b58\u8868<\/li>\n<li>\u5982\u679c\u5728SQL Server Management Studio or Query Analyzer \u4e2d\u5efa\u7acb\u7684\u66ab\u5b58\u8868, \u6703\u7b49\u5230\u6211\u5011\u624b\u52d5drop \u53bb\u95dc\u9589session \u624d\u6703\u6d88\u5931<\/li>\n<\/ul>\n<\/div>\n<p><strong>\u8868\u683c\u8b8a\u6578(Table Variables)<\/strong><\/p>\n<p>\u7576\u6211\u5011\u4f7f\u7528 SQL Server 2000 \u6216\u4ee5\u5f8c\u7684\u7248\u672c, \u5247\u53ef\u4ee5\u8003\u616e\u4f7f\u7528 &#8220;Table Variables&#8221; (\u8868\u683c\u8b8a\u6578); \u4f7f\u7528\u65b9\u5f0f\u5982\u4e0b\u4f8b:<\/p>\n<p>DECLARE @TibetanYaks TABLE (<br \/>\nYakID int,<br \/>\nYakName char(30) )<\/p>\n<p>INSERT INTO @TibetanYaks (YakID, YakName)<br \/>\nSELECT YakID, YakName<br \/>\nFROM dbo.Yaks<br \/>\nWHERE YakType = &#8216;Tibetan&#8217;<\/p>\n<p>&#8212; Do some stuff with the table<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>\u5b83\u548c\u66ab\u5b58\u8868\u985e\u4f3c, \u4f46\u5b83\u66f4\u52a0\u5f48\u6027, \u4e14\u4e0d\u6703\u5b58\u5728\u65bctempdb \u4e2d(\u5b8c\u5168\u5b58\u5728\u65bc\u8a18\u61b6\u9ad4).<\/li>\n<li>\u4f7f\u7528\u5b8c\u7562\u5f8c, \u4e0d\u9808\u624b\u52d5\u53bb drop\u5b83<\/li>\n<\/ul>\n<p><strong><\/strong><\/p>\n<p><strong>\u5169\u8005\u7684\u4f7f\u7528\u6642\u6a5f<\/strong><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>\u7576\u66ab\u5b58\u7684\u8cc7\u6599\u7b46\u6578\u5c0f\u65bc100\u7b46\u6642, \u4f7f\u7528\u8868\u683c\u8b8a\u6578, \u5426\u5247, \u53ef\u4f7f\u7528\u66ab\u5b58\u8868, \u56e0\u70ba\u91dd\u5c0d\u8868\u683c\u8b8a\u6578, SQL Server \u4e0d\u6703\u53bb\u89e3\u6790\/\u6700\u4f73\u5316\u5b83\u7684\u6548\u80fd.<\/li>\n<li>\u7576\u6211\u5011\u9808\u8981\u5c0d\u8868\u683c\u5efa\u7acb\u7d22\u5f15(Index)\u6642, \u5247\u5fc5\u9808\u4f7f\u7528\u66ab\u5b58\u8868.<\/li>\n<li>\u5728\u4f7f\u7528\u66ab\u5b58\u8868\u6642, \u6700\u597d\u80fd\u5728\u5efa\u7acb\u5f8c\u4e00\u4f75\u5efa\u7acb\u7d22\u5f15, \u9019\u80fd\u589e\u52a0\u6548\u80fd (SQL Server 2005\u5f8c, \u9019\u65b9\u9762\u5df2\u6539\u5584, \u6240\u4ee5\u53ef\u4ee5\u4e0d\u5efa\u7d22\u5f15; \u4f46\u5efa\u7acb\u5b83\u4ecd\u662f\u4e00\u500b\u597d\u7fd2\u6163)<\/li>\n<\/ul>\n<p><strong><\/strong><\/p>\n<p><strong>\u5168\u57df\u66ab\u5b58\u8868(Global Temporary Tables)<\/strong><\/p>\n<p>\u5728\u8868\u683c\u540d\u5b57\u524d\u9762, \u52a0\u5165\u5169\u500b&#8221;#&#8221;, \u6bd4\u5982&#8221;##YakHerders&#8221;, \u5247\u8868\u793a\u9019\u662f\u500b\u5168\u57df\u66ab\u5b58\u8868, \u4e5f\u5c31\u662f\u8aaa, \u9019\u500b\u8868\u683c\u548c\u4e00\u822c\u8868\u683c\u4e00\u6a23, \u53ef\u4ee5\u88ab\u6240\u6709\u9023\u7dda(connections\/sessions)\u4f7f\u7528; \u5728SQL Server \u4e2d, \u9019\u6a23\u7684\u61c9\u7528\u4e26\u4e0d\u591a\u898b.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>http:\/\/cbw0731.pixnet.net\/blog\/post\/24993864 \u66ab\u5b58\u8868(Tempor &hellip; <a href=\"https:\/\/jason695.why3s.tw\/wordpress\/2011\/07\/18\/%e6%9a%ab%e5%ad%98%e8%a1%a8temporary-tables%e7%9a%84%e4%bd%bf%e7%94%a8%e7%b0%a1%e4%bb%8b\/\" class=\"more-link\">\u95b1\u8b80\u5168\u6587 <span class=\"screen-reader-text\">\u66ab\u5b58\u8868(Temporary Tables)\u7684\u4f7f\u7528\u7c21\u4ecb<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[],"class_list":["post-1868","post","type-post","status-publish","format-standard","hentry","category-sql-server","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/1868","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=1868"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/1868\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=1868"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=1868"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=1868"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}