最近有一个需求，就是对不同类型的用户生成不同区间段的8位邀请码，而且邀请码是纯数字格式，并保存到对应的用户表中，其实使用代码实现的逻辑是：
- 根据用户类型找到当前最大的邀请码；
- 将当前最大的邀请码＋1得到新生成的邀请码；
- 保存到数据库中；
但是这样不仅逻辑麻烦，性能差，而且需要考虑到并发量大时多线程的处理，数据库中的
SequenceNumber就更适合我们这种情景
SequenceNumber在sql server中是2012版本才引入的，官方文档的说法是有以下几种用途：
- The application requires a number before the insert into the table is made.
- The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
- The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
- The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
- An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
- You need to change the specification of the sequence, such as the increment value.
在我们的项目中用法如下
代码块
CREATE sequence dbo.SequenceInvitationCodeForAgent
as int
Start with 30000001
Increment by 1
MaxValue 39999999
MinValue 30000001
CREATE sequence dbo.SequenceInvitationCodeForFundPartner
as int
Start with 60000001
Increment by 1
MinValue 60000001
CREATE sequence dbo.SequenceInvitationCodeForRY
as int
Start with 20000001
Increment by 1
MaxValue 29999999
MinValue 20000001
每次得到新的邀请码只需要运行
SELECT NEXT VALUE FOR MySequenceName就可以得到了，就是这么简单，而且不必考虑多线程的问题