[SQL] Sequence

MSDN Sequence 說明

建立順序物件，並指定其屬性。 順序是使用者定義之結構描述繫結的物件，該物件會根據建立順序所使用的規格產生數值序列。 數值序列會在定義的間隔依照遞增或遞減順序來產生，而且在用完時可設定為重新啟動 (循環)。 順序不會與特定資料表產生關聯，與識別欄位不同。 應用程式會參考順序物件，以擷取它的下一個值。 順序與資料表之間的關聯性是由應用程式所控制。 使用者應用程式可以參考順序物件，並協調跨越多個資料列和資料表的值。

• 建立 Sequence 語法

• 預設值 Sequence

IF EXISTS(SELECT 1 FROM sys.sequences WHERE name = 'DefaultSeq')
    DROP SEQUENCE dbo.DefaultSeq

CREATE SEQUENCE dbo.DefaultSeq

• Sequence 物件設定值查詢語法

SELECT 
    S.Name AS N'順序物件',
    S.start_value AS N'起始值',
    S.increment AS N'遞增值',
    S.current_value AS N'目前值',
    T.name AS N'資料類型',
    S.minimum_value AS N'最小值',
    S.maximum_value AS N'最大值',
    S.is_cycling AS N'循環',
    S.is_cached AS N'快取',
    S.cache_size AS N'快取大小',
    S.create_date AS N'建立日期',
    S.modify_date AS N'修改日期',
    S.cache_size AS N'快取值'
FROM sys.sequences AS S 
    JOIN sys.types AS T ON S.system_type_id = T.system_type_id

• 簡易 Demo

-- 建立 MySeq Sequence物件
IF EXISTS(SELECT 1 FROM sys.sequences WHERE name = 'MySeq')
    DROP SEQUENCE dbo.MySeq

CREATE SEQUENCE dbo.MySeq
    AS bigint -- bigint 為預設值，指定資料型態要有 AS
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 10
    NO CYCLE
    NO CACHE
GO

-- 利用 NEXT VALUE FOR 來取值
SELECT NEXT VALUE FOR dbo.MySeq

-- 故意爆表
-- 之前已經抽掉序號 1，再抽 10 次的話，會超過 MySeq 設定上限，會出現 Error
SELECT NEXT VALUE FOR dbo.MySeq
GO 10

Sequence 爆表會產生下列錯誤訊息

序列物件 'MySeq' 已達其最小值或最大值。請重新啟動此序列物件以便產生新值。

• 單一 Table 內有多欄位使用 Sequence 為預設值

注意事項：

1. 當 NEXT VALUE FOR 函數用於查詢或預設條件約束時，如果使用相同的順序物件不只一次，或在提供值的陳述式中以及正在執行的預設條件約束中使用相同的順序物件，則會對結果集中資料列內參考相同順序的所有資料行傳回相同值。
2. Table 和 Sequence 必須在同一個 DB 內

-- 重新啟動 Sequence
ALTER SEQUENCE MySeq
    RESTART WITH 1
GO

IF OBJECT_ID('tbSeq') IS NOT NULL
    DROP TABLE tbSeq

CREATE TABLE tbSeq
(
    Col1 int default(NEXT VALUE FOR dbo.MySeq),
    Col2 int default(NEXT VALUE FOR dbo.MySeq)
)

INSERT INTO tbSeq VALUEs(default,default)
GO 10

SELECT * FROM tbSeq

• Sequence 循環

循環是從最小值或最大值重新啟動，而不是從開始值重新啟動

IF EXISTS(SELECT 1 FROM sys.sequences WHERE name = 'MySeqCycle')
    DROP SEQUENCE dbo.MySeqCycle

-- MySeqCycle 從 3 開始、每隔 2 個號碼，最大值為 10、最小值為 1，
CREATE SEQUENCE dbo.MySeqCycle
    AS smallint
    START WITH 3
    INCREMENT BY 2
    MINVALUE 1
    MAXVALUE 10
    CYCLE
    NO CACHE
GO

SELECT NEXT VALUE FOR dbo.MySeqCycle
GO 10

• Sequence 和交易

序號是在目前交異範圍之外產生的，無論使用序號的交易被認可或回復交易，都會耗用序號

IF EXISTS(SELECT 1 FROM sys.sequences WHERE name = 'MySeqTran')
    DROP SEQUENCE dbo.MySeqTran

CREATE SEQUENCE dbo.MySeqTran
    AS bigint
    START WITH 1
    INCREMENT BY 1
GO

BEGIN TRANSACTION

SELECT NEXT VALUE FOR dbo.MySeqTran
GO 10

ROLLBACK TRANSACTION

從下圖來看，可以發現 Sequence 並沒有被 Rollback

• sp_sequence_get_range 使用

CREATE SEQUENCE dbo.MySeqRange
    AS bigint
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 10
    CYCLE
GO

DECLARE  
    @FirstSeqNum sql_variant, 
    @LastSeqNum sql_variant, 
    @CycleCount int, 
    @SeqIncr sql_variant, 
    @SeqMinVal sql_variant, 
    @SeqMaxVal sql_variant ;

EXEC sys.sp_sequence_get_range
    @sequence_name = N'dbo.MySeqRange', 
    @range_size = 20,  -- 一次取 20 個號碼
    @range_first_value = @FirstSeqNum OUTPUT, 
    @range_last_value = @LastSeqNum OUTPUT, 
    @range_cycle_count = @CycleCount OUTPUT, 
    @sequence_increment = @SeqIncr OUTPUT, 
    @sequence_min_value = @SeqMinVal OUTPUT,
    @sequence_max_value = @SeqMaxVal OUTPUT ;

SELECT
    @FirstSeqNum AS FirstVal, 
    @LastSeqNum AS LastVal, 
    @CycleCount AS CycleCount, 
    @SeqIncr AS SeqIncrement, 
    @SeqMinVal AS MinSeq, 
    @SeqMaxVal AS MaxSeq ;

從 @range_cycle_count 可以觀察到上述語法循環兩次

• T-SQL 應用

搭配 OVER() 使用，但不支援 PARTITION BY 語法

IF EXISTS(SELECT 1 FROM sys.sequences WHERE name = 'MySeqTSQL')
    DROP SEQUENCE dbo.MySeqTSQL

CREATE SEQUENCE dbo.MySeqTSQL
    AS bigint
    START WITH 1
    INCREMENT BY 1
GO

SELECT 
    SalesOrderID ,
    OrderDate ,
    ShipDate ,
    CustomerID ,
    NEXT VALUE FOR MySeqTSQL OVER (ORDER BY CustomerID)
FROM [Sales].[SalesOrderHeader]
ORDER BY SalesOrderID

無法在 WHERE 中使用

SELECT 
    NEXT VALUE FOR MySeqTSQL
FROM [Sales].[SalesOrderHeader]
WHERE NEXT VALUE FOR MySeqTSQL > 1000

WHERE 中使用 Sequence 來判斷會出現下面錯誤訊息

TOP、OVER、OUTPUT、ON、WHERE、GROUP BY、HAVING 或 ORDER BY 子句中不允許 NEXT VALUE FOR 函式。

• 更改 Sequence 資料型態

資料類型無法透過 ALTER SEQUENCE 陳述式來變更。若要變更資料類型，請卸除並重新建立順序物件。

參考資料
• 論壇討論
• MSDN：序號、CREATE SEQUENCE
  
• SQL Server 2012 T-SQL 新語法應用實務 - 使用 Cache 的效能 Demo 為影片重點
  
• 認識「SEQUENCE(順序物件)」，以 SQL Server 2012 為例
  
  • 在同一個資料表中，使用多個「SEQUENCE(順序物件)」 
  • 認識順序物件與CACHE
  • 認識順序物件與交易 
  • 順序物件與 sp_sequence_get_range