In this tutorial i am going to explain how to generate unique auto incremented alphanumeric code or number and store in sql server database within table while inserting any record.
Description: This is common requirement to generate unique auto incremented alphanumeric code.While working on my live project i got same requirement to generate 8 character Order ID having 3 characters "ORD" as a prefix and 5 digits number and it should be auto incremented whenever a new record in inserted in the table.
As we know primary key column of a table is always unique so we can use that column value to auto generate OrderID. I made the "OrderID" column a computed column and specified the formula to generate the auto incremented alphanumeric number. Computed columns are derived columns based on other existing columns in the same table or we can say a computed column is computed from an expression or formula that can use another column or columns in the same table.
Implementation: So, Let's Take one example to generate unique auto incremented alphanumeric code.
Explanation: When the very first Order record is saved in the table, the value of primary key column "Id" will be 1 so the computed column "OrderID" will be auto generated as ORD00001 and auto stored in table, similarly when 225th Order record is saved, "OrderID" will be generated as EMP00225, for 1790th record it will be ORD01790 and for 50000th record it will generate ORD50000.
Description: This is common requirement to generate unique auto incremented alphanumeric code.While working on my live project i got same requirement to generate 8 character Order ID having 3 characters "ORD" as a prefix and 5 digits number and it should be auto incremented whenever a new record in inserted in the table.
As we know primary key column of a table is always unique so we can use that column value to auto generate OrderID. I made the "OrderID" column a computed column and specified the formula to generate the auto incremented alphanumeric number. Computed columns are derived columns based on other existing columns in the same table or we can say a computed column is computed from an expression or formula that can use another column or columns in the same table.
Implementation: So, Let's Take one example to generate unique auto incremented alphanumeric code.
CREATE TABLE OrderMaster ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, OrderID AS 'ORD' + RIGHT('0000'+ CONVERT(VARCHAR(5),Id),5) PERSISTED, ProductName VARCHAR(50), Prize INT, ) --Insert Your Records in Table INSERT INTO OrderMaster (ProductName,Prize)VALUES ('Moto E3 Power',7999), ('Lenovo Vibe K5 Note',12999), ('LeEco Le 2',10499), ('Asus Zenfone Max',9499), ('Mi Max',13999) --Check Inserted Record SELECT * FROM OrderMasterResult will be:
 |
Explanation: When the very first Order record is saved in the table, the value of primary key column "Id" will be 1 so the computed column "OrderID" will be auto generated as ORD00001 and auto stored in table, similarly when 225th Order record is saved, "OrderID" will be generated as EMP00225, for 1790th record it will be ORD01790 and for 50000th record it will generate ORD50000.
I am a passionate programmer and a Software Engineer who love developing applications in Microsoft Technologies, blogging and other related technologies like Asp.Net, VB.Net, SEO, Sql Server,My SQL,Asp.Net-MVC, LINQ, C#.Net, Stored Procedures, Ajax, JavaScript, JQuery, xml etc.
If you have any kind of question about any post, Feel free to ask.You can simply drop a comment below post. Your feedback and suggestions will be highly appreciated. ConversionConversion EmoticonEmoticon