API 伺服器資料指標用的系統預存程序sp_cursoropen、sp_cursorfetch...

　　不知你曾注意到用Profiler追蹤一些事件時，會看到明明很簡單的一行Select語法，後面卻跟了一大串的
  declare @p1 int…
  Fetch API…
  exec sp_cursorfetch…
     之類的，一直重複，如下圖(一)紅框處，前一行是簡單的Select

　　圖(一)

　　以前我看到時，認為這是系統背後運作的東西，就忽略它，直到最近同事問我，他系統有段地方會執行簡單的Select語法，資料也沒很多，頂多千筆資料吧，常常執行到逾時，我看他的Select語法真的很簡單，直接在SSMS執行還滿快的
　　那我就用Profiler幫他追蹤到底在SQL端執行了什麼，一錄又錄到圖(一)的一大串東西，跑起來還真的很慢很慢，這時我就不能忽視它了，仔細一看，奇怪，為什會用到像是指標行為呢？用指標一筆一筆跑當然快不起來囉，那我就問我同事程式那段在作什，他說有用到分頁

　　分頁的做法我同事是用ADO實作的，我研究了一下ADO，可參考ADO Open Method的說明，關鍵程式是下面這行囉，擷取網頁內容如下
　　objRecordset.Open source,actconn,cursortyp,locktyp,opt

　　一般來說，若只是單純用Recordset取得資料，cursortyp與locktyp用預設值就好，預設值為adOpenForwardOnly與adLockReadOnly，留空即可，範例如下：
 　　rs.Open "Select * from Table", conn,,,1

　　執行的同時，我用Profiler追蹤一下，畫面如下圖(二)

圖(二)

　　嗯，很正常，就只執行Select語法而已，但若是你cursortyp沒用預設值，改用adOpenStatic值為3會怎樣，範例如下
　　rs.Open "Select * from Table", conn,3,,1

　　同樣的追蹤一下 ，結果如下圖(三)

圖(三)

　　你會發現，在正常的Select後面，系統啟動了指標，使用了特殊系統預存程序（sp_cursoropen、sp_cursorfetch及sp_cursorclose），我研究了一下線上叢書關於資料指標的說明，原來這種特殊的系統預存程序是API 伺服器資料指標用的
　　SQL 2005支援兩種資料指標方法，都是屬於伺服器端資料指標，一種是我們常見的Transact-SQL，另一種是資料庫應用程式發展介面 (API) 資料指標函數，此種API資料指標支援ADO、OLE DB及ODBC

　　如果應用程式沒有要求 Transact-SQL 或 API 資料指標，SQL Server 預設會傳回完整的結果集 (亦即預設結果集) 給應用程式喔，就像圖(二)的例子，預設結果集處理也指出符合下列條件時，就會以預設結果集處理，內容截取如下：

• 應用程式未使用 DECLARE CURSOR 陳述式要求 Transact-SQL 伺服器資料指標。應用程式反而會直接執行 Transact-SQL 陳述式，例如 SELECT。
• 應用程式使用 ADO、OLE DB 和 ODBC 時，它讓所有的 API 資料指標屬性均保持為預設值，所以沒有任何 API 資料指標被要求。此種預設屬性集將要求順向 (ForwardOnly)、資料列集為 1 的唯讀資料指標。
• 由於當所有資料指標屬性設為其預設值，且所處理的資料指標與 SQL Server 或資料庫 API 實際上無關時會使用此處理類型，因此稱之為預設結果集。

　　像圖(二)的例子裡，使用的資料指標屬性都是預設值，也就是第二點說的那樣，所以沒有要求任何API 資料指標，直接將結果傳輸至用戶端，這也是最有效率的方式了

　　

　　而圖(三)的例子裡，並沒有使用預設的資料指標屬性，用的是adOpenStatic，就算只有open就close了，也會啟動了API伺服器資料指標喔，所以在Profiler裡就會看到那些特殊的系統預存程序，指定資料指標有提到怎麼實作的

　　從圖(二)與圖(三)的例子可以看出來，資料指標類型的選擇很重要，用錯了，可能為誤使用了資料指標，造成前端系統變慢(你不要看duration說哪有，明明圖(三)時間較短，因為圖(二)第一次執行啊，時間會較長，多執行幾次兩者duration幾乎一樣了，問題後者還多用了指標，肯定較慢)，那怎麼選呢？關於選擇資料指標類型裡有提到簡單的選擇規則，我就不提了
　　
　　想了解sp_cursoropen語法內容的可參考sp_cursoropen (Transact-SQL)，sp_cursorfetch的可參考sp_cursorfetch (Transact-SQL)，我對sp_cursorfetch裡第二個fetchtype參數很有疑問，因為我錄製到的值都是16，這值並不在fetchtype允許的值裡，很怪，這我不知為什，有人知道嗎？
　　回到分頁，這篇SQL分頁裡提到的幾個連結有很不錯的參考價值，自己去看吧