我們在資料庫中使用Table的時候,經常會遇到兩種使用Table的方法,分別就是使用臨時TableTable變數。在實際使用的時候,我們如何靈活的在儲存過程中運用它們,雖然它們實現的功能基本上是一樣的,可如何在一個儲存過程中有時候去使用臨時Table而不使用Table變數,有時候去使用Table變數而不使用臨時Table?


  臨時Table


  臨時Table與永久Table相似,只是它的建立是在Tempdb中,它只有在一個資料庫連接結束後或者由SQL命令DROP掉,才會消失,否則就會一直存在。臨時Table在建立的時候都會產生SQL Server的系統日誌,雖它們在Tempdb中體現,是分配在記憶體中的,它們也支援物理的磁片,但用戶在指定的磁片裏看不到檔。


  臨時Table分為本機和全域兩種,本機臨時Table的名稱都是以“#”為首碼,只有在本機當前的用戶連接中才是可見的,當用戶從Instance中斷連接時被刪除。全域臨時Table的名稱都是以“##”為首碼,建立後對任何用戶都是可見的,當所有引用該Table的用戶中斷連接時被刪除。


  下面我們來看一個建立臨時Table的例子:  







      CREATE TABLE dbo.#News
  (
  News_id int NOT NULL
  NewsTitle varchar(100)
  NewsContent varchar(2000)
  NewsDateTime datetime
  )



  臨時Table可以建立索引,也可以定義統計資料,所以可以用資料定義語言(DDL)的聲明來阻止臨時Table添加的限制,約束,並參照完整性,如主鍵和外鍵約束。比如來說,我們現在來為#NewsTable字段NewsDateTime來添加一個預設的GetData()當前日期值,並且為News_id添加一個主鍵,我們就可以使用下面的語句: 







      ALTER TABLE dbo.#News
  ADD
  CONSTRAINT [DF_NewsDateTime] DEFAULT (GETDATE()) FOR [NewsDateTime]
  PRIMARY KEY CLUSTERED
  (
  [News_id]
  ) ON [PRIMARY]
  GO



  臨時Table在建立之後可以修改許多已定義的選項,包括:


  1)添加、修改、刪除列。例如,列的名稱、長度、資料類型、精度、小數位數以及為空性均可進行修改,只是有一些限制而已。


  2)可添加或刪除主鍵和外鍵約束。


  3)可添加或刪除 UNIQUE CHECK 約束及 DEFAULT 定義(物件)


  4)可使用 IDENTITY ROWGUIDCOL 屬性添加或刪除識別字列。雖然 ROWGUIDCOL 屬性也可添加至現有列或從現有列刪除,但是任何時候在Table中只能有一列可具有該屬性。    


5)TableTable中所選定的列已註冊為全文索引。


  Table變數


  Table變數建立的語法類似於臨時Table,區別就在於建立的時候,必須要為之命名。Table變數是變數的一種,Table變數也分為本機及全域的兩種,本機Table變數的名稱都是以“@”為首碼,只有在本機當前的用戶連接中才可以訪問。全域的Table變數的名稱都是以“@@”為首碼,一般都是系統的總體變數,像我們常用到的,如@@ErrorTable錯誤的號,@@RowCountTable影響的行數。


  如我們看看建立Table變數的語句:







    DECLARE @News Table
  (
  News_id int NOT NULL
  NewsTitle varchar(100)
  NewsContent varchar(2000)
  NewsDateTime datetime
  )



  比較臨時TableTable變數都可以通過SQL的選擇、插入、更新及刪除語句,它們的的不同主要體現在以下這些:


  1)Table變數是儲存在記憶體中的,當用戶在使用Table變數的時候,SQL Server是不產生日誌的,而在臨時Table中是產生日誌的;


  2)Table變數中,是不允許有非叢集索引的;


  3)Table變數是不允許有DEFAULT預設值,也不允許有約束;


  4)臨時Table上的統計資訊是健全而可靠的,但是Table變數上的統計資訊是不可靠的;


  5)臨時Table中是有Lock的機制,而Table變數中就沒有Lock的機制。


  我們現在來看一個完整的例子,來看它們的用法的異同:


  利用臨時Table  







      CREATE TABLE dbo.#News
  (
  News_id int NOT NULL
  NewsTitle varchar(100)
  NewsContent varchar(2000)
  NewsDateTime datetime
  )
  INSERT INTO dbo.#News (News_id NewsTitle NewsContent NewsDateTime)
  VALUES (1'BlueGreen' 'Austen' 200801 GETDATE())
  SELECT News_id NewsTitle NewsContent NewsDateTime FROM dbo.#News
  DROP TABLE dbo.[#News]



  利用Table變數            







   DECLARE @News table
  (
  News_id int NOT NULL
  NewsTitle varchar(100)
  NewsContent varchar(2000)
  NewsDateTime datetime
  )
  INSERT INTO @News (News_id NewsTitle NewsContent NewsDateTime)
  VALUES (1'BlueGreen' 'Austen' 200801 GETDATE())
  SELECT News_id NewsTitle NewsContent NewsDateTime FROM @News



  我們可以看到上面兩種情況實現的是一樣的效果,第一種利用臨時Table的時候,臨時Table一般被建立後,如果在執行的時候,沒有通過DROP Table的操作,第二次就不能再被建立,而定義Table變數也不需要進行DROP Table的操作,一次執行完成後就會消失。


  其實在選擇臨時Table還是Table變數的時候,我們大多數情況下在使用的時候都是可以的,但一般我們需要遵循下面這個情況,選擇對應的方式:


  1)使用Table變數主要需要考慮的就是應用程式對記憶體的壓力,如果程式的執行Instance很多,就要特別注意記憶體變數對記憶體的消耗。我們對於較小的資料或者是通過計算出來的推薦使用Table變數。如果資料的結果比較大,在程式中用於臨時計算,在選取的時候沒有什麼分組的聚合,就可以考慮使用Table變數。


  2)一般對於大的資料結果,或者因為統計出來的資料為了便於更好的優化,我們就推薦使用臨時Table,同時還可以建立索引,由於臨時Table是存放在Tempdb中,一般預設分配的空間很少,需要對tempdb進行調優,增大其儲存的空間。

nelman 發表在 痞客邦 PIXNET 留言(0) 人氣()