德瑞克：SQL Server 學習筆記: 初探Cursors(資料指標) 與資料列集(Rowsets)

SQL Server 會使用兩個方法將結果集傳回給取用者：

1. 「資料列集(Rowsets)」具有以下功能：

將負擔最小化。
在提取資料時提供最大效能。
只支援預設的順向、唯讀資料指標功能。
一次將一個資料列傳回給取用者。
一次只支援連接上有一個作用中陳述式。
在執行陳述式之後，要等到取用者已經擷取所有結果或是陳述式已被取消之後，才可以在連接上執行其他陳述式。
支援所有 Transact-SQL 陳述式。

2. Server Cursors(伺服器資料指標)具有以下功能：

支援所有的資料指標功能。
可以將資料列區塊傳回給取用者。
支援單一連接上有多個作用中陳述式。
平衡資料指標功能與效能。
資料指標功能的支援會減少相對於預設結果集的效能。如果取用者可以使用資料指標功能來擷取較小的資料列集，就可以抵銷這個作用。
請勿支援會傳回單一結果集以上的任何 Transact-SQL 陳述式。

認識 Cursors(資料指標)

SQL Server 陳述式雖然會產生完整的結果集，但有時候這些結果或許可以改以一次處理一個資料列。

您可以在結果集上開啟一個資料指標，一次處理一個資料列結果集。

您可以指派一個資料指標給 cursor 資料類型的變數或參數。

關聯式資料庫中的作業會針對完整的資料列集運作。
例如，由 SELECT 陳述式所傳回的資料列集包括所有滿足陳述式 WHERE 子句之條件的資料列。

由陳述式傳回的完整資料列稱為結果集。
應用程式 (尤其是互動式線上應用程式) 不一定能夠以一個單位有效地運用整個結果集。

這些應用程式需要一個機制，一次運用一個資料列或小型資料列區塊。
資料指標就是一種結果集的擴充，提供此種機制。

資料指標擴充結果處理的方式是：

1. 允許定位於結果集中的特定資料列。
2. 從結果集的目前位置，擷取一個資料列或資料列區塊。

3. 支援結果集目前位置上資料列的資料修改。
4. 支援以不同可見性層級來檢視其他使用者對結果集所呈現的資料庫資料所作的變更。

5. 讓指令碼、預存程序和觸發程序中的 Transact-SQL 陳述式能夠存取結果集中的資料。

--
資料指標的 tempdb 使用量

索引鍵集驅動及靜態資料指標會使用 tempdb 內建的工作資料表。
索引鍵集驅動資料指標會使用工作資料表，來儲存識別資料指標中資料列的索引鍵集。

靜態資料指標使用工作資料表來儲存資料指標的完整結果集。
資料指標的磁碟空間使用量可能不同，視所選擇的查詢計畫而定。

如果查詢計畫與 SQL Server 的舊版相同，則磁碟空間使用量大致相同。

DECLARE CURSOR (Transact-SQL)

定義 Transact-SQL 伺服器資料指標的屬性，例如其捲動行為以及用來建立資料指標運作所在之結果集的查詢。
DECLARE CURSOR 可接受 ISO 標準語法，以及使用一組 Transact-SQL 延伸模組的語法。

--
引數：
FORWARD_ONLY

指定資料指標只能從第一個資料列捲到最後一個資料列。
FETCH NEXT 是唯一支援的提取選項。

如果指定不含 STATIC、KEYSET 或 DYNAMIC 等關鍵字的 FORWARD_ONLY 時，資料指標便會作為 DYNAMIC 資料指標操作。
當 FORWARD_ONLY 和 SCROLL 兩者都沒有指定時，除非指定了 STATIC、KEYSET 或 DYNAMIC 等關鍵字，否則，預設值是 FORWARD_ONLY。

STATIC、KEYSET 和 DYNAMIC 資料指標預設為 SCROLL。
與 ODBC 和 ADO 等資料庫 API 不同的是，STATIC、KEYSET 和 DYNAMIC 等 Transact-SQL 資料指標支援 FORWARD_ONLY。

--
@@FETCH_STATUS 資料指標函數

傳回針對連接目前開啟的任何資料指標而發出的最後一個資料指標 FETCH 陳述式的狀態。

0：FETCH 陳述式成功。
-1：FETCH 陳述式失敗，或資料列已超出結果集。
-2：遺漏提取的資料列。

EX00. 建立資料表：myOrders

-- 因為要觀察 tempdb 資料庫，重新啟動 SQL Server

-- EX00. 建立資料表：myOrders

-- 00_建立資料表：myOrders，
-- 耗時約：27 秒。HD：80 GB 。
SET NOCOUNT ON
USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myOrders]') AND type in (N'U'))
DROP TABLE [dbo].[myOrders]
GO
CREATE TABLE myOrders
(EmployeeID INT , OrderDate DATETIME)
GO
DECLARE @CNT INT= 1
WHILE @CNT < 100001
BEGIN
 INSERT myOrders SELECT CAST(RAND() * 10 AS INT) , GETDATE()+@CNT
 SET @CNT +=1
END
GO

--  01_100,000 筆資料列
SELECT COUNT(*) FROM dbo.myOrders
GO

--  02_檢視資料表：dbo.myOrders
SELECT * FROM dbo.myOrders
GO

-- 01_檢視資料表：dbo.myOrders

-- 03_查詢目前資料庫內，每一個資料表的使用空間資訊
SELECT a3.name AS N'結構描述',  a2.name AS N'資料表',  a1.rows AS N'資料列筆數',  (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)',  a1.data * 8.0/1024 AS '資料(MB)',  
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)',  
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)' 
FROM  (SELECT   ps.object_id, SUM ( CASE  WHEN (ps.index_id < 2) THEN row_count  ELSE 0 END  ) AS [rows],  
SUM (ps.reserved_page_count) AS reserved, SUM (  CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)  
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)  END ) AS data,  
SUM (ps.used_page_count) AS used  
FROM sys.dm_db_partition_stats ps  
GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN
(SELECT it.parent_id,  SUM(ps.reserved_page_count) AS reserved,  SUM(ps.used_page_count) AS used  
FROM sys.dm_db_partition_stats ps  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)  
WHERE it.internal_type IN (202,204)  
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)  
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )   
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)  
WHERE a2.type <> N'S' and a2.type <> N'IT' 
ORDER BY 4 DESC;
GO

/*
資料表：myOrders
資料列筆數：100000
配置的空間總量(MB)：2.195312
資料(MB)：2.132812
*/

-- 02_查詢目前資料庫內，每一個資料表的使用空間資訊

-- 04_檢視資料庫 tempdb 各個檔案的空間資料
USE tempdb
GO
SELECT name N'邏輯名稱' , size/128.0 N'使用的磁碟空間(MB)' ,
 CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 N'資料實際上使用的空間(MB)'
 ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 N'剩餘的可用空間(MB)',
 physical_name N'實體檔案'
FROM sys.database_files;
GO

/*
tempdev 
使用的磁碟空間(MB)：8.000000
資料實際上使用的空間(MB)：2.500000
*/

-- 03_檢視資料庫 tempdb 各個檔案的空間資料

EX1. 使用 CURSOR 來計算每一位員工，最近一次接單的日期

-- EX1. 使用 CURSOR 來計算每一位員工，最近一次接單的日期

-- 宣告：區域變數
USE Northwind_Dev
GO
DECLARE @empid AS INT, @orderdate AS DATETIME, @prevempid AS INT, @prevorderdate AS DATETIME;

-- 宣告：資料表變數：@T
DECLARE @T AS TABLE (empid INT,maxorderdate DATETIME);

-- 宣告 CURSOR：存放 EmployeeID 與 OrderDate
-- FORWARD_ONLY：指定資料指標只能從第一個資料列捲到最後一個資料列。 
DECLARE OrdersCursor CURSOR FAST_FORWARD FOR
  SELECT EmployeeID, OrderDate
  FROM dbo.myOrders
  ORDER BY EmployeeID, OrderDate; -- 排序：EmployeeID、OrderDate

-- 開啟此 CURSOR
OPEN OrdersCursor;

-- 在伺服器資料指標中，擷取第一筆資料列
FETCH NEXT FROM OrdersCursor INTO @empid, @orderdate;

-- 前一次擷取資料是成功時
WHILE @@FETCH_STATUS = 0
BEGIN
  -- 如果前一筆資料列的 EmployeeID 不同於目前的資料列，取出前一筆的 OrderDate 存放到 資料表變數：@T
 IF @prevempid <> @empid
 BEGIN
  INSERT INTO @T(empid, maxorderdate) VALUES(@prevempid, @prevorderdate);
 END

 SELECT @prevempid = @empid, @prevorderdate = @orderdate;

 FETCH NEXT FROM OrdersCursor INTO @empid, @orderdate;
END

-- 如果已經擷取全部的資料列後(此情形下 EmployeeID 會是 NULL)，最後一筆資料列會是最後一位員工的最近一次的 OrderDate
IF @empid IS NOT NULL
BEGIN
 INSERT INTO @T(empid, maxorderdate) VALUES(@prevempid, @prevorderdate);
END

-- 查詢資料表變數：@T
SELECT empid, maxorderdate
FROM @T;

-- 若去觀察資料庫 tempdb 各個檔案的空間資料
/*
tempdev 
使用的磁碟空間(MB)：8.000000
資料實際上使用的空間(MB)：5.000000
*/

-- 釋出目前結果集
CLOSE OrdersCursor;

-- 移除資料指標參考。
DEALLOCATE OrdersCursor;
GO

-- 檢視資料庫 tempdb 各個檔案的空間資料
/*
tempdev 
使用的磁碟空間(MB)：8.000000
資料實際上使用的空間(MB)：2.562500
*/

-- 04_執行結果，查詢資料表變數：@T

-- 05_CURSOR執行後，檢視資料庫 tempdb 各個檔案的空間資料

-- 06_釋出與移除資料指標參考後，檢視資料庫 tempdb 各個檔案的空間資料

EX2. 改用資料列集(Rowsets)方式來計算

-- EX2. 改用資料列集(Rowsets)方式來計算

-- 計算每一位員工，最近一次接單的日期
USE Northwind_Dev
GO
SELECT EmployeeID, MAX(OrderDate) AS MaxOrderDate
FROM dbo.myOrders
GROUP BY EmployeeID
ORDER BY EmployeeID;
GO

EX3. 測試使用 CURSOR 與資料列集(Rowsets) 方式的執行時間

-- EX3. 測試使用 CURSOR 與 資料列集(Rowsets) 方式的執行時間

-- 01_測試：使用 CURSOR 的執行時間：1.8270000(秒)

USE Northwind_Dev
GO
DECLARE @StartTime DATETIME2 = SYSDATETIME()

DECLARE @empid AS INT, @orderdate AS DATETIME, @prevempid AS INT, @prevorderdate AS DATETIME;
DECLARE @T AS TABLE (empid INT,maxorderdate DATETIME);

DECLARE OrdersCursor CURSOR FAST_FORWARD FOR
  SELECT EmployeeID, OrderDate
  FROM dbo.myOrders
  ORDER BY EmployeeID, OrderDate

OPEN OrdersCursor;

FETCH NEXT FROM OrdersCursor INTO @empid, @orderdate;

WHILE @@FETCH_STATUS = 0
BEGIN
 IF @prevempid <> @empid
 BEGIN
  INSERT INTO @T(empid, maxorderdate) VALUES(@prevempid, @prevorderdate);
 END

 SELECT @prevempid = @empid, @prevorderdate = @orderdate;

 FETCH NEXT FROM OrdersCursor INTO @empid, @orderdate;
END

IF @empid IS NOT NULL
BEGIN
 INSERT INTO @T(empid, maxorderdate) VALUES(@prevempid, @prevorderdate);
END

SELECT empid, maxorderdate
FROM @T;

CLOSE OrdersCursor;
DEALLOCATE OrdersCursor;

SELECT DATEDIFF(ms, @StartTime, SYSDATETIME())/1000.00 N'使用 CURSOR 的執行時間(秒)'
GO

-- 02_測試：資料列集(Rowsets)方式，所需的執行時間：0.0470000(秒)
USE Northwind_Dev
GO
DECLARE @StartTime DATETIME2 = SYSDATETIME()

SELECT EmployeeID, MAX(OrderDate) AS MaxOrderDate
FROM dbo.myOrders
GROUP BY EmployeeID
ORDER BY EmployeeID;

SELECT DATEDIFF(ms, @StartTime, SYSDATETIME())/1000.00 N'資料列集(Rowsets)方式的執行時間(秒)'
GO

-- 07_使用 CURSOR 的執行時間