簡析一下SQL Server裡面Fast_Forword 和 SRROLL 的區別

標籤：style   blog   http   io   color   ar   os   使用   for   

這次簡單說說遊標的分類。

先看看通常遊標的文法

DECLARE cursor_name CURSOR 
　　[ LOCAL  ：局部遊標，僅在當前會話有效 
     | GLOBAL  ： 全域遊標，全域有效，可以
    ]      [ FORWARD_ONLY ：只能向前遊標，讀取遊標時只能使用 Next 謂詞
       | SCROLL  ：滾動遊標，FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE 都可以使用
    ]      [ STATIC ：定義一個遊標，以建立將由該遊標使用的資料的臨時複本。對遊標的所有請求都從 tempdb 中的這一暫存資料表中得到應答；
       | KEYSET ：對基表中的非索引值所做的更改（由遊標所有者更改或由其他使用者提交）可以在使用者滾動遊標時看到。其他使用者執行的插入是不可見的（不能通過 Transact-SQL 伺服器資料指標執行插入）。如果刪除某一行，則在嘗試提取該行時傳回值為 -2 的 @@FETCH_STATUS。
       | DYNAMIC ：定義一個遊標，以反映在滾動遊標時對結果集內的各行所做的所有資料更改。行的資料值、順序和成員資格在每次提取時都會更改。動態資料指標不支援 ABSOLUTE 提取選項
       | FAST_FORWARD ：指定啟用了效能最佳化的 FORWARD_ONLY、READ_ONLY 遊標。如果指定了 SCROLL 或 FOR_UPDATE，則不能也指定 FAST_FORWARD
    ]      [ READ_ONLY ：唯讀遊標，不能對遊標內容變更，不能使用 where current of 語句
       | SCROLL_LOCKS ：指定通過遊標進行的定點更新或刪除一定會成功。將行讀入遊標時 SQL Server 將鎖定這些行，以確保隨後可對它們進行修改。
       | OPTIMISTIC  ：指定如果行自讀入遊標以來已得到更新，則通過遊標進行的定點更新或定位刪除不成功。當將行讀入遊標時，SQL Server 不鎖定行

     ]      [ TYPE_WARNING ]      FOR select_statement      [ FOR UPDATE [ OF column_name [ ,...n ] ]  ：制定那些列可以進行更新，如果不填，則預設全部可以更新
     ]

好了，拋完書包之後，現在問題來了，本人盡量圖文並茂的描述每種類型的特點吧……_(:з」∠)_

--------------------------------------------------------------我是分割線-----------------------------------------------------------------------------------------------------------------------------------------------
測試表的結構

CREATE TABLE [dbo].[Employee]([ID] [int] NOT NULL IDENTITY(1, 1) primary key,[NAME] [nvarchar] (50)  NULL,[Name2] [varchar] (50)  NULL) ON [PRIMARY]GO

 

FORWARD_ONLY  和 SCROLL
   這兩者在用的過程中還是比較好區別。一個只能前進，一個可以前滾翻後滾翻什麼的。
先看看

FORWARD_ONLY

DECLARE CR_CURSOR CURSOR FAST_FORWARD --這個遊標，是一個唯讀遊標而已哦~FOR    SELECT  ID ,            NAME    FROM    dbo.Employee    WHERE   ID >= 24    DECLARE @ID INT ,    @Name NVARCHAR(50)    OPEN CR_CURSORFETCH NEXT FROM CR_CURSOR INTO @ID, @NameWHILE @@FETCH_STATUS = 0     BEGIN    --UPDATE dbo.Employee SET Name2 = @Name WHERE CURRENT OF CR_CURSOR  因為是唯讀遊標，所以是不允許修改遊標本身內容        UPDATE  dbo.Employee        SET     Name2 = @Name        WHERE   ID = @ID        FETCH NEXT FROM CR_CURSOR INTO @ID, @Name    ENDCLOSE CR_CURSORDEALLOCATE CR_CURSOR    

 

然後我們看看執行計畫，這個就跟普通的即時查詢時沒有任何區別的，所以我猜測，假如在遊標讀取過程中，資料發生了變化，是可以擷取出來的。下面我來驗證一下

1、在讀取之前，我先溫柔的刪除Employee 表裡面，ID = 25的記錄 

DELETE FROM dbo.Employee WHERE ID = 25

  2、然後在讀取遊標裡面開啟單步調試，讀到ID = 24的節點

     

  3、然後在讀下一個遊標之前，我添加了一條資料

SET IDENTITY_INSERT Employee ONINSERT INTO dbo.Employee        (ID,NAME )VALUES  ( 25,N‘我是插進來的小三‘)SET IDENTITY_INSERT Employee OFF

 

然後繼續F10前進 ~咦~~25出來了野~

證明了，FORWARD_ONLY 這貨是在遊標向下滾動的時候即使擷取資料的。所以能捕捉到新插入或刪除的資料。


大致是這樣紙了，下面在看看

SCROLL 

DECLARE CR_CURSOR CURSOR SCROLL  --代碼基本一致，只是換成了 SCROLLFOR    SELECT  ID ,            NAME    FROM    dbo.Employee    WHERE   ID >= 24    DECLARE @ID INT ,    @Name NVARCHAR(50)    OPEN CR_CURSORFETCH NEXT FROM CR_CURSOR INTO @ID, @NameWHILE @@FETCH_STATUS = 0     BEGIN        UPDATE dbo.Employee SET Name2 = @Name WHERE CURRENT OF CR_CURSOR  --這句現在可以執行了        FETCH NEXT FROM CR_CURSOR INTO @ID, @Name    ENDCLOSE CR_CURSORDEALLOCATE CR_CURSOR

 

 慣例先看看這個執行計畫吧~


可以看到有一個查詢過程要把資料插入到 CWT_PrimaryKey 的暫存資料表裡面。那我猜想，如果在遊標讀取途中，外部有資料的增加，是擷取不到的了，那如果更新和刪除會怎麼樣呢？實驗一下

1、在讀取之前，我還是先溫柔的刪除Employee 表裡面，ID = 25的記錄 

DELETE FROM dbo.Employee WHERE ID = 25

 

 2、然後在讀取遊標裡面開啟單步調試，讀到ID = 24的節點

     

  3、然後在讀下一個遊標之前，我添加了一條資料

SET IDENTITY_INSERT Employee ONINSERT INTO dbo.Employee        (ID,NAME )VALUES  ( 25,N‘我是插進來的小三‘)SET IDENTITY_INSERT Employee OFF

 然後F10……ID25沒有粗線_(:з」∠)_直接到26去了

確實，外部新增了資料，是擷取不到的。下面測一下修改和刪除。

 

1、然後在讀取遊標裡面開啟單步調試，讀到ID = 24的節點

     

 2、修改ID是25的資料

     

UPDATE dbo.Employee SET NAME = ‘我是修改了的ID25哦‘ WHERE ID = 25

3、然後按F10繼續走，是可以擷取的喲~

  

刪除呢？
重試一遍，在單步的過程中直接將 ID = 25的資料抹掉，然後就直接迴圈結束了~查了一下 @@Fetch_Status = -2 提取資料失敗，當然啦……資料都被刪除了。順帶一提，如果繼續往下取，還是可以取到下一條資料的喲~
……圖就不截了。。。

然後在繼續試下各種方式，再進行補充

簡析一下SQL Server裡面Fast_Forword 和 SRROLL 的區別