# Sequence

參考資料:([https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15](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 ; 
```

