如何在SQL Server中生成包含今天日期，月份，年份和某些数字的生成自动增量唯一ID [英] How can Make an Generating auto increment unique ID which contain today date, month, year and some number in SQL Server

问题描述

我想创建一个自动生成唯一ID，其中包含今天的日期，月份，年份和一些数字。



Ex。在2016年 - 将只使用16，
将使用1月01日的
，
今天26美元将使用
，

和slno从0001到9999开始



所以我的身份证应该是1601250001.



日期每天都在变化，数量应该增加..





我试过..

I want to create a auto generate unique Id which contains today's date, month, year and some number.

Ex. in year 2016-only 16 will be used,
for the month of January 01 will be used,
for today's day 26 would be used,
and the slno is starts 0001 to 9999

So my id should be like 1601250001.

The date changes everyday and number should be increases ..


I Have tried ..

Create procedure InsertRegistration
(
    @name nvarchar(50),
    @Address nvarchar(100)
)
as
begin
    DECLARE @BdID VARCHAR(25)

    SET @BdID = (SELECT convert(varchar, getdate(), 112))+
                 CAST(((SELECT COUNT(*) 
                     FROM BasicInformation  
                 WHERE @BdID like (SELECT convert(varchar, getdate(),112))+'%') 
                       +1 ) AS VARCHAR(5))

     Insert into BasicInformation(BdID, name, address) 
     values (@BdID, @name, @address)
end

It's working but the increment is not happening for each registration. I am a beginner. Please help me

推荐答案

中等水平的设计可能就像



*有一个表COUNTERS（ID，currentValue，maskingRules）

*有值（1,1，'yymmdd9999'）

*有一个SQL函数关注

*阅读掩蔽规则

*解析它并相应填写

*更新CurrentValue

*返回业务层的累计价值



这将有助于以下方式

*您有一个可以轻松测试的独立功能

*您可以定义不同的屏蔽规则

*您可以立即更改规则（在制作时）



这种方法确实有副作用，重要的一个是COUNTERS表，如果定义了太多的计数器，就会因为锁而自行成为负载的瓶颈

A moderate level design could be like

* Have a table COUNTERS(ID, currentValue, maskingRules)
* With values (1, 1, 'yymmdd9999')
* Have a SQL Function which does following
* Read masking rule
* Parse it and fill it accordingly
* Update CurrentValue
* Return the accumulated value to the business layer

This will help in following ways
* You have an independent function that can be easily tested
* You can define different masking rules
* You can change the rules instantly (on production)

This approach do have side effects, significant one is the COUNTERS table could it self be a bottleneck on load because of locks if too many counters have been defined

这篇关于如何在SQL Server中生成包含今天日期，月份，年份和某些数字的生成自动增量唯一ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！