Generate Sequence Number Safely
-
Hi,
I want to generate a sequence number which is not affected by concurrent insert. Previously, I use a simple T-SQL like this:
SELECT @LastNumber=MAX(SeqField) FROM MyTable
SET @NextNumber = @LastNumber + 1
..until someone told me that it's not safe when concurrent insert happens, unfortunately that guy didn't tell me the right way to do it.
Can somebody enlight me?
Thanks in advance
Hendry
-
if you can live with gaps in these numbers you could use an identity field (check BOL)
You can retrieve the inserted value afterward with SCOPE_IDENTITY()
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If this is for some kind of index, I would also use the identity property on a numeric column. This is a built-in function of SQL Server that I'm feeling more confident with than having my own code. The gaps that will appear in the sequence are irrelevant.
If you're looking for a perfect sequence, look at
http://www.sqlservercentral.com/columnists/glarsen/sequentialnumbering.asp or
http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1
IMHO, a perfect sequence does not need to be stored, it gets only important when presenting the data.
Frank
Edited by - Frank Kalis on 12/29/2003 02:44:17 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If you are replicating the table with updates occurring on the subscriber and publisher take care to plan carefully what you do in this regard since coming up with sequential numbers is much harder then and may not be possible depending on your connectivity.
-
Another approach is to use another table to hold your values. Then use a stored procedure to update the other table and get the last value. Always get your values through this stored procedure.
The procedure should put an update lock on the row of the table (you can manage many keys in the same table), and thus, you should get a unique value every time.
-
I can't use identity field since there's a business rule to reset the number to 0 for every new month.
It's a requirement to store the sequential number as it's part of the invoice number.
jxflagg, could you show me how to use temp table and update lock?
Thanks
Hendry
-
"as part of the invoicenumber" ?
Is your invoicenumber still numeric ?
Is this to-be-generated-sequence-number used as a suffix to a fixed prefix (e.g. yearmonth&sequencenumber) ?
maybe this can help out :
You would have to do some testing, but I believe even then you can use an idendity
Test it.
set nocount on
create table t_invoice_ALZDBA(invoice_nr bigint not null identity(1,1), annotation varchar(25))
go
insert into t_invoice_ALZDBA (annotation) values('a')
insert into t_invoice_ALZDBA (annotation) values('b')
insert into t_invoice_ALZDBA (annotation) values('c')
insert into t_invoice_ALZDBA (annotation) values('d')
insert into t_invoice_ALZDBA (annotation) values('e')
insert into t_invoice_ALZDBA (annotation) values('f')
go
DBCC CHECKIDENT
( 't_invoice_ALZDBA'
, RESEED
, 200311000000 )
go
insert into t_invoice_ALZDBA (annotation) values('a')
insert into t_invoice_ALZDBA (annotation) values('b')
insert into t_invoice_ALZDBA (annotation) values('c')
insert into t_invoice_ALZDBA (annotation) values('d')
insert into t_invoice_ALZDBA (annotation) values('e')
insert into t_invoice_ALZDBA (annotation) values('f')
go
DBCC CHECKIDENT
( 't_invoice_ALZDBA'
, RESEED
, 200312000000 )
go
insert into t_invoice_ALZDBA (annotation) values('a')
insert into t_invoice_ALZDBA (annotation) values('b')
insert into t_invoice_ALZDBA (annotation) values('c')
insert into t_invoice_ALZDBA (annotation) values('d')
insert into t_invoice_ALZDBA (annotation) values('e')
insert into t_invoice_ALZDBA (annotation) values('f')
go
select * from t_invoice_ALZDBA
go
-- drop table t_invoice_ALZDBA
Edited by - alzdba on 12/30/2003 12:25:32 AM
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
quote:
jxflagg, could you show me how to use temp table and update lock?Not a temp table, a real table. Like this:
CREATE TABLE [Exp_UniqueKeys] (
[Table_Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_key] [int] NOT NULL CONSTRAINT [DF__Exp_Uniqu__last___515009E6] DEFAULT (1),
CONSTRAINT [PK_UniqueKeys] PRIMARY KEY NONCLUSTERED
(
[Table_Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
With this stored procedure:
CREATE PROCEDURE NextKey(
@p_tablenamevarchar(128) = null,
@p_howmanyint=1,
@p_nextkeyintoutput
)
AS
begin
if @p_tablename is null
begin
raiserror (' @p_tablename is required', 16,1)
return -1
end
UPDATE exp_UniqueKeys
SET last_key = last_key + @p_howmany
WHERE table_name = @p_tablename
if @@rowcount > 0
Begin
SELECT @p_nextkey = last_key
FROM exp_UniqueKeys
WHERE table_name = @p_tablename
end
else
begin
INSERT INTO exp_UniqueKeys (
table_name,
last_key
)
values (
@p_tablename,
@p_howmany
)
if @@error != 0
begin
raiserror ('the insert statement failed', 16, 1)
return -1
end
SELECT @p_nextkey = @p_howmany
end
return 0
End
Call the above stored procedure from all of your other procedures as follows:
EXEC NextKey @p_tablename = 'Exp_Expense', @p_nextkey = @ID output
The calling procedure should be in a transaction bracket.
-
I know I've posted this before...
CREATE PROC p_NextID @SeqType char(2), @NextID int OUTPUT AS
SET NOCOUNT ON
UPDATE Sequences
SET @NextID = LastID = LastID + 1
WHERE SeqType = @SeqTypeAs this is one statement, there's no need to use any explicit transactions.
--Jonathan
--Jonathan -
But you still need a transaction because of the usage of the generated number, let's say an invoice.
begin tran
get new invoice number
insert data into invoice table
insert data into invoice details table
insert data into invoice details table
...
commit tran
If you won't do this you will get a hole within your numbering if for example your communication link is broken between inserting the invoice and the invoice details rows.
Bye
Gabor
Bye
Gabor -
We use a table named LNUs (LastNumbersUsed) with an integer value for LNU. There is a trigger on the table to update the LNU = LNU + 1 from deleted and the current value of LNU is taken and then updated by a delete statement.
-
As this is one statement, there's no need to use any explicit transactions.
That's true. But the calling procedure generally has lots of statements, one of them being the statement which gets the key value.
-
As this is one statement, there's no need to use any explicit transactions.
That's true. But the calling procedure generally has lots of statements, one of them being the statement which gets the key value.
-
quote:
We use a table named LNUs (LastNumbersUsed) with an integer value for LNU. There is a trigger on the table to update the LNU = LNU + 1 from deleted and the current value of LNU is taken and then updated by a delete statement.recycled, I'm not sure I follow you. You update the table on delete, not insert?
-
I would think the LNU concept would create a hotspot on the LNU table.
How about inserting via a SP, and store the Next key in a global or a temp table, you could even pass the value back. Just have a SP or ?? to load the value at startup.
KlK, MCSE
KlK
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply