简书链接:sqlserver序列实现
文章字数：78,阅读全文大约需要1分钟

1
2
3
4
5
6
7
8
9
10
11
-- 创建序列
CREATE SEQUENCE dbo.MySequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NO MAXVALUE
    NO CYCLE;
 
-- 查询序列的当前值

用法

1
2
SELECT NEXT VALUE FOR dbo.MySequence AS CurrentSequenceValue;

1
2
3
4
5
6
7
8
9
10
CREATE SEQUENCE [dbo].[GENEREATE_CARTON_NO]
AS bigint
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999
NO CYCLE
CACHE 25
GO

限定固定长度 不足补0 如限定长度为5 生产了15
那么就是00015
套路代码

1
2
3
4
5
DECLARE @GenereateLen INT = 4;
DECLARE @Number INT 
set @Number=NEXT VALUE FOR GENEREATE_SEQUENCE
DECLARE @ENDStr VARCHAR(100) = REPLICATE('0', @GenereateLen - LEN(CONVERT(VARCHAR(10), @Number))) + CONVERT(VARCHAR(10), @Number);