Click here to Skip to main content
15,935,728 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table in which we need to create a unique ID 15 digit. But It should be auto incremented. Suppose My unique Id is something like
It should get incremented based on date and also last three digits like
If there is one more entry on same date it should be like

Now I want to know ideas to develop such requirement. Should I get the existing number from DB and simply increment by 1 if it is on same date? Or is there anything other that?

Can anyone give me some ideas for these kind of custom auto increment ID?

In my opinion the proble is that you have put the three separate things into a single column. Break the data into three columns:
- prefix (containing M) datatype varchar?
- itemdate containing the date dtatype date
- counter datatype int, always incremented by 1 within a date
then define a unique constraint on these three columns to ensure to never have a duplicate.

When showing the custom id just concatenate and format the values of these three columns.

However, if you don't really need a custom ID like this I would suggest using uniqueidentifier[^]. It's guaranteed to be unique and you don't have to worry so much about generating the value.
Share this answer
sudevsu 19-Feb-15 10:45am    
Thank you so much I already have unique ID. I am specific to 15 digit becoz we need to print that on Stickers for Bar code purpose. But I like the idea of using normalization splitting the column in three different columns and concatenate them when displayed or used
Wendelius 19-Feb-15 11:52am    
Glad it was helpful :)
CHill60 19-Feb-15 17:35pm    
Wendelius 20-Feb-15 2:06am    
Thank you :)
Santosh K. Tripathi 20-Feb-15 1:23am    
5* from me also :)
I was going to post this as a comment to solution 1 but it's easier to get the formatting with a "solution" than a comment ...

You said you already had a unique ID and I thought a bit more about the "unique counter within a date". This CP article gave me the idea - How to use ROW_NUMBER() to enumerate and partition records in SQL Server[^]

Using that principle I wanted to create a unique-within-a-date identifier...

I created a sample table with this
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [recDate] [date] NOT NULL,
    [someData] [nchar](10) NULL,
    [NewId] [int] NULL
I won't repeat any sample data I inserted (unless you ask me to) but the thing I was trying to get to was not having to generate another unique ID.

This sql query will "format" your unique ID based on the date and identity column
WITH T1(ID, recDate, someData, NewId)
    SELECT [ID],recDate,someData,
     ROW_NUMBER() over(PARTITION BY recDate
                         ORDER BY ID ASC) Number
    FROM CP1
select ID, recDate, someData, 'M' +
        REPLACE(CONVERT(CHAR(10), recDate, 103), '/', '')
        + Replace(Str(NewId, 7), ' ' , '0')
        from T1 where ID=@input
where ID=@input is however you want to identify the record you are after.

You could put this into a insert trigger on the table to generate the ID and persist it on the table, or use it when extracting for display.

One important caveat - if the ID is generated at display time only, it would change if records had been deleted. If persisted (via a trigger) then it should work regardless.

Please note this is meant to be complementary to Solution 1 - not an alternative
Share this answer
Wendelius 20-Feb-15 2:06am    
Good addition, +5.
CHill60 20-Feb-15 4:34am    
Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900