December 10, 2008 at 6:08 am
I have Order table as follows:
OrderID - PK - identity
OrderType - numeric or char (?) 1,2,3 or 'A','B','C'
OrderSeqNo - to be automatically inserted
OrderDate - datetime
I would like to have OrderSeqNo automatically generated by using trigger, or function, or something else, on the following format:
A08-001
A08-002
B08-001
C08-001
C09-001
C09-002
etc...
where 'A','B','C' are OrderType, 08 and 09 are year of OrderDate, and 001,... sequential numbers for each type and year.
I can not use identity column (OrderID), since my client don't want to have "holes" in sequential numbers.
I think that system should first find MAX of seqno for newly inserted OrderType and Year(from OrderDate), and add one, before it assembly complete new seqno.
Creating it this way, probably also means, that deleting Orders should not be allowed, as well as changing of OrderType, or OrderDate )how to accomplish this) ?
Please advice.
December 10, 2008 at 6:50 am
What do you (or they) intend to use this field for?
Doing this kind of generation and ensuring there will be no holes can cause a lot of problems. First, it means that all insert transactions must be entirely sequential - you cannot have two insert transactions running at the same time because if the first one rolls back, you cannot go through with the second one. To accomplish this, you will need to lock the table for inserts at the beginning of an insert transaction (using one of a number of methods) and anyone else that wants to insert has to wait for the first transaction to complete before starting. This can cause blocking and performance issues pretty easily.
Next, if you use a MAX of some sort, your inserts will have to query the table being inserted into. So, if the table gets big you will end up with a pretty nasty query happening every time you do an insert. Not to mention dealing with multi-record inserts can be a bit more complex.
You already noticed that deletes will be a problem.
On the other hand, you can calculate this field with information you have on-hand. I assume there is an Order Type field and a Date field. If this number is to be used for identification purposes on documents to something just for the users to query, you may want to consider just calculating it when you need it or building it in the application UI where it needs to be displayed.
December 10, 2008 at 7:46 am
Thanks Michael.
I agree with you, but still my client wants to have numbering system as described. This is of course, only for end users, and this "key" has nothing with DB itself. Moreover, inserting new orders will not happen too often (maybe 200-300 per year), and it will be done only by 1 person, so I don't except concurrency issues, etc...
Your idea for calculating this field, based on existing columns is fine, and I already have first three characters: type 'A','B' or 'C', as well as last two numbers of year from OrderDate, but how to calculate sequential number 001,002,etc... ?
Regards, Vladimir.
December 10, 2008 at 10:46 am
CREATE TABLE dbo.TempOrders (
OrderType varchar(1) NOT NULL,
OrderYear int,
OrderSequence int,
OrderNumber AS ( OrderType + RIGHT(CAST(OrderYear as varchar(4)),2) + '-' + RIGHT('000' + CAST(OrderSequence as varchar),3) ),
PRIMARY KEY ( OrderType, OrderYear, OrderSequence ))
INSERT INTO dbo.TempOrders VALUES( 'A', 2008, 1 )
INSERT INTO dbo.TempOrders VALUES( 'A', 2008, 2 )
INSERT INTO dbo.TempOrders VALUES( 'B', 2008, 1 )
INSERT INTO dbo.TempOrders VALUES( 'C', 2008, 1 )
INSERT INTO dbo.TempOrders VALUES( 'C', 2009, 1 )
INSERT INTO dbo.TempOrders VALUES( 'C', 2009, 2 )
SELECT * FROM dbo.TempOrders
GO
--procedure to get next order sequence
CREATE PROCEDURE dbo.GetNextOrderNumber
@OrderType varchar(1),
@OrderYear int,
@OrderSequence int OUTPUT
AS
SET NOCOUNT ON
BEGIN TRY
SELECT @OrderSequence = COALESCE(MAX(OrderSequence),0) + 1
FROM dbo.TempOrders
WHERE OrderType = @OrderType
AND OrderYear = @OrderYear
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT,
@ErrorNumber INT;
SET @ErrorMessage = ERROR_MESSAGE();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
SET @ErrorNumber = ERROR_NUMBER();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @ErrorNumber;
END CATCH
GO
--get the next sequence for an order type by year
DECLARE @OrderSequence int
EXEC dbo.GetNextOrderNumber 'C', 2008, @OrderSequence OUTPUT
SELECT @OrderSequence as SequenceForC08
EXEC dbo.GetNextOrderNumber 'C', 2009, @OrderSequence OUTPUT
SELECT @OrderSequence as SequenceForC09
Not allowing holes will cause alot of problems and there are other posts explaining how you can fill the gaps. The numbering scheme alaso limits you to less than 100 years and less than 999 orders before you will run into issues.
You might want to add a surrogate key, and set your three natural keys to be a unique constraint.
If you are not going to allow deletes you could raise an error in an on delete trigger
or create a secondary table that just holds the OrderType, OrderYear, OrderSequence and Foreign key it to your orders table (do not cascade delete) and it will error if you try to delete the main record since it is referenced.
December 11, 2008 at 2:05 am
Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67923
N 56°04'39.16"
E 12°55'05.25"
December 11, 2008 at 5:02 am
Be very careful... There is nothing there that will keep 2 or more simultaneous calls to the same code from returning the same number.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2008 at 5:15 am
I also don't see anything that would prevent "overruns"... what happens if you use 1001 of any given sequence? Either BOOM or duplication.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2008 at 6:30 am
Thanks guys for answers.
I am fully aware of possible problems:
1. Overruns will never happen, meaning that client will never have more then 200 orders per year. If it will happen in the future, I can change sequence number to have four numbers
2. Simultaneous inserting: Since Order inserts will be mainly done by only one user, it is impossible to have same number, but still I don't understand how two users can create their inserts exactly on the same time. Is it that server will always take one by one ? What if I am using trigger on insert (see below) ?
CREATE TRIGGER [cbs].[CreateOrderSeqNo]
ON [cbs].[Orders]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrderSeqNo AS smallint
SELECT @OrderSeqNo = COALESCE(MAX(OrderSeqNo),0) + 1
FROM Orders
WHERE OrderType = (SELECT OrderType FROM Inserted)
AND OrderYear = (SELECT OrderYear FROM Inserted)
UPDATE Orders
SET Orders.OrderSeqNo = @OrderSeqNo
FROM Orders
INNER JOIN Inserted ON Orders.OrderID = Inserted.OrderID
END
December 11, 2008 at 6:41 am
You would have to ensure you create the orders one at a time and wrap it in a transaction so that the sequence returned is unique.
You could also implement a table with the order number information and update it / return the new sequence. However, this way may burn sequence numbers.
It depends on how the code is written to work.
There are many possible solutions.
December 11, 2008 at 11:59 pm
vbugarsk (12/11/2008)
Thanks guys for answers.I am fully aware of possible problems:
1. Overruns will never happen, meaning that client will never have more then 200 orders per year. If it will happen in the future, I can change sequence number to have four numbers
2. Simultaneous inserting: Since Order inserts will be mainly done by only one user, it is impossible to have same number, but still I don't understand how two users can create their inserts exactly on the same time. Is it that server will always take one by one ? What if I am using trigger on insert (see below) ?
Heh... overruns will never happen... famous last words of the last guy I knew that said that. At least put in a rollover detector to reject the order with the proper error message when it does eventually happen.
Same goes for the assumption that "inserts will be mainly done by only one user". Users can insert rows into a table simultaneously if only rowlocks are invoked especially on multi-processor boxes and triggers won't prevent it. I suppose you could throw a TABLOCKX on the table, but that's a patch, not a solution for the problem.
I've been thinking about this problem and I believe the only way to guarantee no dupes is to build a sequence table and the correct code to update it without becoming a source of major deadlocks.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2008 at 12:17 am
There is a workaround, or a quirk, and that is to have an auxiliary sequence table, and not use source table for sequencing.
See http://weblogs.sqlteam.com/peterl/archive/2008/11/28/An-alternative-to-IDENTITY-column.aspx
N 56°04'39.16"
E 12°55'05.25"
December 12, 2008 at 3:23 am
I think it would be better to work like this:
The table TemoOrders would be like this
CREATE TABLE dbo.TempOrders (
OrderType varchar(1) NOT NULL,
OrderYear int,
OrderSequence int,
OrderNumber AS ( OrderType + RIGHT(CAST(OrderYear as varchar(4)),2) + '-' + RIGHT('000' + CAST(OrderSequence as varchar),3) ),
PRIMARY KEY ( OrderType, OrderYear))
Then u have to insert the order sequence 1 for each ordertypw and years u want.
The when inserting new records into the table u can do it like this:
BEGIN TRANSACTION
DECLARE @ordernumber TABLE (OrderNumber VARCHAR(15)),
UPDATE TempOrders WITH (ROWLOCK)
SET OrderSequence = OrderSequence + 1
OUTPUT inserted.OrderSequence INTO @ordernumber
INSERT INTO [yourtable]
VALUES(ordernumber,Value1,.....)
SELECT (select top 1 OrderNumber from @ordernumber), @parValue1, ....)
COMMIT TRANSACTION
This way u minimize database size, u can use rowlock in temp orders since the transaction will lock the temporder row for a short amount of time and since its a transaction the update wont occur if the rest of the statement fails. I don't recommend triggers, u should avoid it and control the stuff in the applications. Create stored procedures for inserts and control the use of it in the applications. In the sps do all the calculations before the transaction and use variables to reduce the lock time. Since u need to know the order number in the statement calling the sp return int in a output parameter of the spm if u can, return the entire row to the application whith a select * from yourtable. This way u can also control the value of the defaults, identity columns, ...
Hope it helps
December 12, 2008 at 8:29 am
One other alternative would be to store a datetime field that represents the datetime when the order is entered into the system. Determining the order sequence number can then be done dynamically at any time using something along the lines of:
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE)
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2008 at 9:50 am
The code
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE)
will error if someone deletes a row in the table. And since it's about document numbering, it can't repeat a number even if it doesn't give an error in the db system.
December 12, 2008 at 10:57 am
Skull Killer (12/12/2008)
The code
ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE)will error if someone deletes a row in the table. And since it's about document numbering, it can't repeat a number even if it doesn't give an error in the db system.
Why would it ever error? This is generated at run-time, based on what is in the table at the time.
The ugliness in this case in my mind is that the reference number would CHANGE if anyone went in and deleted a row, inserted rows out of sequence or updated the order dates. Kind of defeats the purpose of a reference number if it no longer refers to what it USED to refer to....
I must admit I've never fully understood why these requirements are so damn popular. It's an arbitrary rule, most often enforced at great expense to the overall system, and in most cases - entirely unused and serving no point whatsoever. This is usually the point at which I tend to push back, hard, until the customer actually has a valid reason for this.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply