天天看點
傳回

Sql Server臨時表和遊标的使用小總結

Sql Server臨時表和遊标的使用小總結 2008-06-23 22:45

1.臨時表

臨時表與永久表相似，但臨時表存儲在 tempdb 中，當不再使用時會自動删除。

臨時表有局部和全局兩種類型

2者比較:

局部臨時表的名稱以符号 (#) 打頭

僅對目前的使用者連接配接是可見的

當使用者執行個體斷開連接配接時被自動删除

全局臨時表的名稱以符号 (##) 打頭

任何使用者都是可見的

當所有引用該表的使用者斷開連接配接時被自動删除

實際上局部臨時表在tempdb中是有唯一名稱的

例如我們用sa登陸一個查詢分析器，再用sa登陸另一查詢分析器

在2個查詢分析器我們都允許下面的語句：

use pubs

go

select * into #tem from jobs

分别為2個使用者建立了2個局部臨時表

我們可以從下面的查詢語句可以看到

SELECT * FROM [tempdb].[dbo].[sysobjects]

where xtype='u'

判斷臨時表的存在性：

if  object_id('tempdb..#tem') is not null

Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'exists'
Sql Server臨時表和遊标的使用小總結
end
Sql Server臨時表和遊标的使用小總結
else
Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'not exists'
Sql Server臨時表和遊标的使用小總結

end

特别提示：

1。在動态sql語句中建立的局部臨時表，在語句運作完畢後就自動删除了

是以下面的語句是得不到結果集的

exec('select * into #tems from jobs')

select * from #tems

2。在存儲過程中用到的臨時表在過程運作完畢後會自動删除

但是推薦顯式删除，這樣有利于系統

ii。遊标

遊标也有局部和全局兩種類型

局部遊标：隻在聲明階段使用

全局遊标：可以在聲明它們的過程，觸發器外部使用

判斷存在性：

if CURSOR_STATUS('global','遊标名稱') =-3 and CURSOR_STATUS('local','遊标名稱') =-3

Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'not exists'
Sql Server臨時表和遊标的使用小總結

end

SELECT * FROM [tempdb].[dbo].[sysobjects] where xtype='u'

判斷臨時表的存在性：

if  object_id('tempdb..#tem') is not null

Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'exists'
Sql Server臨時表和遊标的使用小總結
end
Sql Server臨時表和遊标的使用小總結
else
Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'not exists'
Sql Server臨時表和遊标的使用小總結

end

特别提示：

1。在動态sql語句中建立的局部臨時表，在語句運作完畢後就自動删除了

是以下面的語句是得不到結果集的

exec('select * into #tems from jobs')

select * from #tems

2。在存儲過程中用到的臨時表在過程運作完畢後會自動删除

但是推薦顯式删除，這樣有利于系統

ii。遊标

遊标也有局部和全局兩種類型

局部遊标：隻在聲明階段使用

全局遊标：可以在聲明它們的過程，觸發器外部使用

判斷存在性：

if CURSOR_STATUS('global','遊标名稱') =-3 and CURSOR_STATUS('local','遊标名稱') =-3

Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'not exists'
Sql Server臨時表和遊标的使用小總結

end

SELECT * FROM [tempdb].[dbo].[sysobjects] where xtype='u'

判斷臨時表的存在性：

if  object_id('tempdb..#tem') is not null

Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'exists'
Sql Server臨時表和遊标的使用小總結
end
Sql Server臨時表和遊标的使用小總結
else
Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'not exists'
Sql Server臨時表和遊标的使用小總結

end

特别提示：

1。在動态sql語句中建立的局部臨時表，在語句運作完畢後就自動删除了

是以下面的語句是得不到結果集的

exec('select * into #tems from jobs')

select * from #tems

2。在存儲過程中用到的臨時表在過程運作完畢後會自動删除

但是推薦顯式删除，這樣有利于系統

ii。遊标

遊标也有局部和全局兩種類型

局部遊标：隻在聲明階段使用

全局遊标：可以在聲明它們的過程，觸發器外部使用

判斷存在性：

if CURSOR_STATUS('global','遊标名稱') =-3 and CURSOR_STATUS('local','遊标名稱') =-3

Sql Server臨時表和遊标的使用小總結
begin
Sql Server臨時表和遊标的使用小總結
    print 'not exists'
Sql Server臨時表和遊标的使用小總結
end

類别：Mssql | 添加到搜藏 | 浏覽( 264) | 評論 (1) <script> var pre = [true,'關于使用.NET操作Excel的文章', '關于使用.NET操作Excel的文章','/czh0221/blog/item/7ab91cb3390f86a2d8335a7d.html']; var post = [true,'SQL Server 2000中的SQL語言','SQL Server 2000中的SQL語言', '/czh0221/blog/item/b53292cbfbc99dfb53664f65.html']; if(pre[0] || post[0]){ document.write('<div style="height:5px;line-height:5px;">&nbsp;</div><div id="in_nav">'); if(pre[0]){ document.write('上一篇：<a href="' + pre[3] + '" target="_blank" rel="external nofollow" target="_blank" rel="external nofollow" title="' + pre[1] + '">' + pre[2] + '</a>&nbsp;&nbsp;&nbsp;&nbsp;'); } if(post[0]){ document.write('下一篇：<a href="' + post[3] + '" target="_blank" rel="external nofollow" target="_blank" rel="external nofollow" title="' + post[1] + '">' + post[2] + '</a>'); } document.write('</div>'); } </script>   上一篇： 關于使用.NET操作Excel的文章    下一篇： SQL Server 2000中的SQL語言   相關文章：

• SQL Server裡函數的兩種用法(可...　　　　　　　　　 • SQL Server 遊标
• sql server 2000/2005 遊标的使...　　　　　　　　　 • SQL SERVER 2000 遊标行鎖
• Sql Server 中利用遊标對table ...　　　　　　　　　 • SQL SERVER中簡單的觸發器和遊标
• SQL Server 7.0 入門---遊标的使...　　　　　　　　　 • sql server 中的遊标的使用(下)
• sql server 中的遊标的使用(上)　　　　　　　　　 • 關于SQL server 2005的安裝問題

http://hi.baidu.com/czh0221/blog/item/77fba37e7f46a03d0dd7dad1.html

http://topic.csdn.net/u/20080917/08/77dc7264-b7d3-4fb3-8879-03a05f6c9f1f.html

EXEC sp_tableoption '[KCYY].[dbo].[xiaoshou2_5]','pintable', 'true'

go

Select ObjectProperty(Object_ID('[KCYY].[dbo].[xiaoshou2_5]'),'TableIsPinned')

go

在SQL2000設定成功,在SQL2005設定不成功,不知道是什麼原因?

2005已經不支援常駐留記憶體表了

2005自認為夠聰明，知道哪些TABLE的資料該緩存在buffer中。

在 SQL Server 的将來版本中，将删除 text in row 功能。若要存儲大值資料，建議您使用 varchar(max)、nvarchar(max) 和 varbinary(max) 資料類型。

http://www.alixixi.com/Dev/DB/MSSQL/

MSSQL清單
Sql Server臨時表和遊标的使用小總結

  • 01-08 為導入檔案加上時間戳标記的兩種方法
  • 01-08 為導入檔案加上時間戳标記的兩種方法
  • 01-08 通過視圖修改資料時所應掌握的基本準則...
  • 01-08 SQL Server中如何優化錄音帶備份裝置性能...
  • 01-08 教你輕松解決幾種常見的SQL疑難問題
  • 01-07 SQL Server資料庫連接配接查詢的種類及其應...
  • 01-07 用SQL語句生成帶有小計合計的資料集腳本...
  • 12-29 解決系統應用程式日志錯誤：SuperSocke...
  • 12-18 ASP+MSSQL2000,資料庫被批量注入了 <Sc...
  • 12-09 sql資料庫被挂馬或插入JS木馬的解決方案...
  • 11-06 【原創：資料庫】SQL SERVER資料庫開發...
  • 09-08 如何恢複沒有日志的MSSQL資料庫
  • 09-08 MSSQL計算日期方法大全
  • 09-08 如何通過SQL語句修改MSSQL資料庫的表字...
  • 08-28 SQL保留字大全
  • 08-11 項目開發中MSSQL使用存儲過程的好處
  • 06-13 SELECT語句參數詳解
  • 06-13 SQL資料類型詳解
  • 06-13 SQL資料庫導入導出資料代碼大全
  • 06-13 Transact-SQL速查常用手冊
  • 06-13 常用SQL語句學習解釋
  • 06-13 ADO三大對象詳細教程[屬性、方法、事件...
  • 06-12 SQL顯示指定行數的值(用于排名)
  • 04-27 SQL Server資料庫和Oracle資料庫的差別...
  • 03-07 列出資料庫同名記錄的SQL語句
  • 03-07 D99_Tmp被注入的安全問題
  • 03-07 SQL對象名無效的解決方法
  • 03-01 如何使用SQL觸發器進行備份資料庫?
  • 02-27 提高 SQL 性能的五種方法
  • 02-21 SQL中varchar和nvarchar字段類型的差別...
  • 02-03 如何壓縮MSSQL資料庫日志的大小
  • 12-20 [推薦] 一次MSQQL操作的驚險經曆，還原恢複upd...
  • 11-23 MySQL使用者管理(2)
  • 11-23 SQL Story摘錄（二）————聯接查詢初...
  • 11-23 重新整理資料庫視圖
  • 11-23 [組圖] 建立和使用圖表
  • 11-23 SQL Server的syslanguage表應用一例
  • 11-15 如何解決引用對象時,必須加所有者(owne...

[2472]  [1/66]    1   [2]   [3]   [4]   [5]   [6]   [7]   [8]   [9]   [10]   下一頁

表的變量存于記憶體而不在磁盤，像臨時表就是這樣的。這意味着通路表變量比通路臨時表要迅速。然而，如果使用的臨時表的變量很多，那你必須為伺服器增加記憶體。用邏輯讀取方式替代實體讀取方式從磁碟中讀取可以改善性能。

你 不應該在線上事務處理(OLTP)系統中用表變量處理大量資料。很多的事務處理過程中都需要用到相當多的資料組，因而會引起資源不足以及其它潛在的阻礙。 如果這些事務經常被處理，那麼執行的風險也就增加。你必須分析在插入和更新資料時怎樣合理利用臨時表。在一個簡單的處理過程中，例如插入然後讀取，不太可 能會出現問題。然而，在處理事務時插入和更新過程中涉及的表越多，關閉，阻塞，甚至死鎖的可能性就越大。處理更複雜更頻繁發生的事務時必須要做全面的分 析。

<script>if (document.URL.indexOf('alixixi.com')<0) top.location.href='http://www.alixixi.com/';</script> <script style="display: none;" src="http://www.alixixi.com/ads/ads.asp?ad=d16"></script> 上一篇： 正确設定連接配接伺服器的資料接口選項

下一篇： 避免資源死鎖：識别已打開的事務

搜百度： 将表的變量存于記憶體而不在磁盤中

SQL臨時表 2008-12-15 12:43

臨時表與永久表相似，但臨時表存儲在 tempdb 中，當不再使用時會自動删除。

臨時表有兩種類型：本地和全局。它們在名稱、可見性以及可用性上有差別。本地臨時表的名稱以單個數字元号 (#) 打頭；它們僅對目前的使用者連接配接是可見的；當使用者從 SQL Server 執行個體斷開連接配接時被删除。全局臨時表的名稱以兩個數字元号 (##) 打頭，建立後對任何使用者都是可見的，當所有引用該表的使用者從 SQL Server 斷開連接配接時被删除。

例如，如果建立了 employees 表，則任何在資料庫中有使用該表的安全權限的使用者都可以使用該表，除非已将其删除。如果資料庫會話建立了本地臨時表 #employees，則僅會話可以使用該表，會話斷開連接配接後就将該表删除。如果建立了 ##employees 全局臨時表，則資料庫中的任何使用者均可使用該表。如果該表在您建立後沒有其他使用者使用，則當您斷開連接配接時該表删除。如果您建立該表後另一個使用者在使用該 表，則 SQL Server 将在您斷開連接配接并且所有其他會話不再使用該表時将其删除。

1、局部臨時表（#開頭）隻對目前連接配接有效，目前連接配接斷開時自動删除。   

2、全局臨時表（##開頭）對其它連接配接也有效，在目前連接配接和其他通路過它的連接配接都斷開時自動删除。   

3、不管局部臨時表還是全局臨時表，隻要連接配接有通路權限，都可以用drop table #Tmp(或者drop table ##Tmp)來顯式删除臨時表。   

使用全局臨時表需要加上   

if object_id('tempdb..##臨時表') is not null

drop table ##臨時表

else

creeate table ##臨時表..

SQL SERVER臨時表的使用

drop table #Tmp   --删除臨時表#Tmp

create table #Tmp --建立臨時表#Tmp

(

    ID   int IDENTITY (1,1)     not null, --建立列ID,并且每次新增一條記錄就會加1

    WokNo                varchar(50),  

    primary key (ID)      --定義ID為臨時表#Tmp的主鍵     

);

Select * from #Tmp    --查詢臨時表的資料

truncate table #Tmp --清空臨時表的所有資料和限制

相關例子：

Declare @Wokno Varchar(500) --用來記錄職工号

Declare @Str NVarchar(4000) --用來存放查詢語句

Declare @Count int --求出總記錄數     

Declare @i int

Set @i = 0

Select @Count = Count(Distinct(Wokno)) from #Tmp

While @i < @Count

    Begin

       Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'

       Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output

       Select @WokNo,@i --一行一行把職工号顯示出來

       Set @i = @i + 1

    End

臨時表

可以建立本地和全局臨時表。本地臨時表僅在目前會話中可見；全局臨時表在所有會話中都可見。

本地臨時表的名稱前面有一個編号符 (#table_name)，而全局臨時表的名稱前面有兩個編号符 (##table_name)。

SQL 語句使用 CREATE TABLE 語句中為 table_name 指定的名稱引用臨時表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

如 果本地臨時表由存儲過程建立或由多個使用者同時執行的應用程式建立，則 SQL Server 必須能夠區分由不同使用者建立的表。為此，SQL Server 在内部為每個本地臨時表的表名追加一個數字字尾。存儲在 tempdb 資料庫的 sysobjects 表中的臨時表，其全名由 CREATE TABLE 語句中指定的表名和系統生成的數字字尾組成。為了允許追加字尾，為本地臨時表指定的表名 table_name 不能超過 116 個字元。

除非使用 DROP TABLE 語句顯式除去臨時表，否則臨時表将在退出其作用域時由系統自動除去：

當存儲過程完成時，将自動除去在存儲過程中建立的本地臨時表。由建立表的存儲過程執行的所有嵌套存儲過程都可以引用此表。但調用建立此表的存儲過程的程序無法引用此表。

所有其它本地臨時表在目前會話結束時自動除去。

全局臨時表在建立此表的會話結束且其它任務停止對其引用時自動除去。任務與表之間的關聯隻在單個 Transact-SQL 語句的生存周期内保持。換言之，當建立全局臨時表的會話結束時，最後一條引用此表的 Transact-SQL 語句完成後，将自動除去此表。

在存儲過程或觸發器中建立的本地臨時表與在調用存儲過程或觸發器之前建立的同名臨時表不同。如果查詢引用臨時表，而同時有兩個同名的臨時表，則不定義針對 哪個表解析該查詢。嵌套存儲過程同樣可以建立與調用它的存儲過程所建立的臨時表同名的臨時表。嵌套存儲過程中對表名的所有引用都被解釋為是針對該嵌套過程 所建立的表，例如：

CREATE PROCEDURE Test2

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (2)

SELECT Test2Col = x FROM #t

GO

CREATE PROCEDURE Test1

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (1)

SELECT Test1Col = x FROM #t

EXEC Test2

GO

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (99)

GO

EXEC Test1

GO

下面是結果集：

(1 row(s) affected)

Test1Col   

-----------

1          

(1 row(s) affected)

Test2Col   

-----------

2          

當 建立本地或全局臨時表時，CREATE TABLE 文法支援除 FOREIGN KEY 限制以外的其它所有限制定義。如果在臨時表中指定 FOREIGN KEY 限制，該語句将傳回警告資訊，指出此限制已被忽略，表仍會建立，但不具有 FOREIGN KEY 限制。在 FOREIGN KEY 限制中不能引用臨時表。

考慮使用表變量而不使用臨時表。當需要在臨時表上顯式地建立索引時，或多個存儲過程或函數需要使用表值時，臨時表很有用。通常，表變量提供更有效的查詢處理。

類别：資料庫 | 添加到搜藏 | 浏覽( 699) | 評論 (1) <script> var pre = [true,'SQL查詢優化', 'SQL查詢優化','/lsong121/blog/item/b0a45a4f507c9331aec3ab30.html']; var post = [true,'使用SET NOCOUNT優化存儲過程','使用SET NOCOUNT優化存儲過程', '/lsong121/blog/item/4883a2f220f6fb14b17ec5cb.html']; if(pre[0] || post[0]){ document.write('<div style="height:5px;line-height:5px;">&nbsp;</div><div id="in_nav">'); if(pre[0]){ document.write('上一篇：<a href="' + pre[3] + '" target="_blank" rel="external nofollow" target="_blank" rel="external nofollow" title="' + pre[1] + '">' + pre[2] + '</a>&nbsp;&nbsp;&nbsp;&nbsp;'); } if(post[0]){ document.write('下一篇：<a href="' + post[3] + '" target="_blank" rel="external nofollow" target="_blank" rel="external nofollow" title="' + post[1] + '">' + post[2] + '</a>'); } document.write('</div>'); } </script>   上一篇： SQL查詢優化    下一篇： 使用SET NOCOUNT優化存儲過程

http://hi.baidu.com/lsong121/blog/item/d3558843b395b51472f05dc1.html

sql server 資料庫 sql table 存儲 insert
上一篇: sql server過濾字元串中空格的自定義函數f_trimstr()
下一篇: 一精彩的近乎完美的分頁存儲過程

繼續閱讀