none
How to create IDs RRS feed

  • Question

  • For my database applications, I like to use character based IDs.  I typically will have a letter that I use as a designator, followed by a set of numbers consisting of a specific number of digits.  Additionally, I like the number part of the ID to be sequential.

    e.g. B100012491 followed by B100012492 …..

    I have tried many different methods to be able to create new ID numbers for new records.  Some of them are quite complex, some not so much.  What I am shopping for is an easy, non complex way to get this done.


    gwboolean

    Sunday, February 24, 2019 4:45 PM

Answers

  • Hello,

    I would suggest (if using SQL-Server) to following the instructions on the following page which requires a trigger. What is not covered is incrementing the prefix character e.g. when should B increment to C etc. What I've done is to keep the prefix letter for an entire year then for the following year increment it to the next letter e.g. in this case C for 2010, D for 2021 etc.

    https://www.sqlteam.com/articles/custom-auto-generated-sequences-with-sql-server


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by gwboolean Sunday, February 24, 2019 10:01 PM
    Sunday, February 24, 2019 5:32 PM
    Moderator
  • Here is another idea

    Create a sequence where MAXVALUE can be higher

    CREATE SEQUENCE dbo.GetPartNumber
        AS INT
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        MAXVALUE 999
        CYCLE
        CACHE 10;

    To get the value

    SELECT 'B' + CONVERT(VARCHAR(4), GETDATE(), 12)
           + RIGHT('0000'
                   + CAST(NEXT VALUE FOR dbo.GetInvoiceNumber AS VARCHAR(3)), 4);
    For an example see my code sample, look at Operations.vb and search for GenerateInvoice.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by gwboolean Sunday, February 24, 2019 10:04 PM
    Sunday, February 24, 2019 5:52 PM
    Moderator

All replies

  • Hi

    Confused here. You say you are using a system where you take a letter followed by a series of number characters. So what is your question? Do you want a way to choose a random character and a random series of number characters that would be the 'starting ID?


    Regards Les, Livingston, Scotland


    • Edited by leshay Sunday, February 24, 2019 5:05 PM
    Sunday, February 24, 2019 5:05 PM
  • Hello,

    I would suggest (if using SQL-Server) to following the instructions on the following page which requires a trigger. What is not covered is incrementing the prefix character e.g. when should B increment to C etc. What I've done is to keep the prefix letter for an entire year then for the following year increment it to the next letter e.g. in this case C for 2010, D for 2021 etc.

    https://www.sqlteam.com/articles/custom-auto-generated-sequences-with-sql-server


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by gwboolean Sunday, February 24, 2019 10:01 PM
    Sunday, February 24, 2019 5:32 PM
    Moderator
  • Thanks Karen, that is exactly the kind of thing I am looking for.  In fact, I had not gotten to the point, yet, where I had thought about incrementing the letter(s) component of the ID.

    I used to use smart IDs, but there is just no reason for doing that (if there ever really was).  Now I like my IDs to be incremental numbers, but in a character format so that I can have letters as well. And since you added incremental letters, I like that too.


    gwboolean

    • Marked as answer by gwboolean Sunday, February 24, 2019 5:45 PM
    • Unmarked as answer by gwboolean Sunday, February 24, 2019 10:01 PM
    Sunday, February 24, 2019 5:45 PM
  • Here is another idea

    Create a sequence where MAXVALUE can be higher

    CREATE SEQUENCE dbo.GetPartNumber
        AS INT
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        MAXVALUE 999
        CYCLE
        CACHE 10;

    To get the value

    SELECT 'B' + CONVERT(VARCHAR(4), GETDATE(), 12)
           + RIGHT('0000'
                   + CAST(NEXT VALUE FOR dbo.GetInvoiceNumber AS VARCHAR(3)), 4);
    For an example see my code sample, look at Operations.vb and search for GenerateInvoice.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by gwboolean Sunday, February 24, 2019 10:04 PM
    Sunday, February 24, 2019 5:52 PM
    Moderator
  • Thanks Karen, that is exactly the kind of thing I am looking for.  In fact, I had not gotten to the point, yet, where I had thought about incrementing the letter(s) component of the ID.

    I used to use smart IDs, but there is just no reason for doing that (if there ever really was).  Now I like my IDs to be incremental numbers, but in a character format so that I can have letters as well. And since you added incremental letters, I like that too.


    gwboolean

    Great although you should mark helpful replies and or replies that answered your question as a so which assist others looking for the same thing.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, February 24, 2019 5:54 PM
    Moderator
  • Sorry, I don't always pay attention very well when I click things.  Perhaps that might be one of the explanations why things go wrong for me with this stuff.  Anyway, thanks and corrected.

    gwboolean

    Sunday, February 24, 2019 10:03 PM