March 17, 2018 at 1:54 pm
Hello,
I am creating a program using VB.NET. I would like the database to handle automatically creating a number for orders and customers/vendors using the following format.
Vendors - "V-001000"
Customers - "C-001000"
Purchase Orders - "PO-001000"
Sales Orders - "SO-001000"
Is it possible to have it done? I am using SQL database on Azure.
March 18, 2018 at 8:14 am
yes, with a calculated, persisted column, that uses the real identity column.
Note this is just creating a formatted version of your identity, so it's a little repetitive.
You might be able to do something similar with a single column, if it featured a SEQUENCE and the calcualted function.
Also, your specific design of 7 characters limits the number of items you can have, which would be less than the max size of an int column.
IF OBJECT_ID('[dbo].[Vendor]') IS NOT NULL
DROP TABLE [dbo].[Vendor]
GO
CREATE TABLE [dbo].[Vendor] (
[VendorID] AS ('V-'+right('0000000'+CONVERT([varchar](10),[VID]),(9))) PERSISTED,
[VID] INT IDENTITY(1000,1) NOT NULL,
[VendorName] VARCHAR(50) NOT NULL,
[OtherColumns] VARCHAR(30) NULL,
CONSTRAINT [PK__Vendor__VendorID] PRIMARY KEY NONCLUSTERED ([VID] asc) )
CREATE CLUSTERED INDEX IX_Vendor_VendorID ON [Vendor]([VendorID])
insert into Vendor(VendorName,OtherColumns)
SELECT 'Google','SomeStuff' UNION ALL SELECT 'Amazon','otherStuff'
SELECT * FROM Vendor
/*
V-000001000 1000 Google SomeStuff
V-000001001 1001 Amazon otherStuff
*/
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy