Sequence

參考資料:(https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15)

創建、查詢

Create

CREATE SEQUENCE SeqName 
    START WITH startNumber
    INCREMENT BY 1 ;  

Select

SELECT NEXT VALUE FOR SeqName;  

Drop

DROP SEQUENCE SeqName;

查找該 Sequence 的詳細資料

SELECT * FROM sys.sequences WHERE name = 'SeqName' ;  

循環

CREATE SEQUENCE SeqName
AS decimal(3,0)
START WITH 125 --起始大小
INCREMENT BY 25--增長間隔
MINVALUE 100--需要循環時的最小範圍
MAXVALUE 200--需要循環時的最大範圍
CYCLE--循環
;

一次取得多筆

利用系統內建的SP去達成

該SP長這樣

sp_sequence_get_range [ @sequence_name = ] N'<sequence>'   
     , [ @range_size = ] range_size  
     , [ @range_first_value = ] range_first_value OUTPUT   
    [, [ @range_last_value = ] range_last_value OUTPUT ]  
    [, [ @range_cycle_count = ] range_cycle_count OUTPUT ]  
    [, [ @sequence_increment = ] sequence_increment OUTPUT ]  
    [, [ @sequence_min_value = ] sequence_min_value OUTPUT ]  
    [, [ @sequence_max_value = ] sequence_max_value OUTPUT ]  
    [ ; ]  

使用範例

DECLARE @range_first_value_output sql_variant ;  

EXEC sys.sp_sequence_get_range 
    @sequence_name = N'SeqName', 
    @range_size = 5, 
    @range_first_value = @range_first_value_output OUTPUT ;

SELECT @range_first_value_output AS FirstNumber ;