How to Generate sequential Unique Numeric Trans No

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Generate sequential Unique Numeric Trans No

Author

Topic

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 04:17:37

Case
1)There are 50+ workstation across the 4 different branches.
2)Tno should be unique across the table.
3)Tno should be numeric, sequential generated,and must be unique on concurrent request of it.
4)NEWID() cannot be used.

Current applying As follows

Create PROCEDURE [dbo].[GetTransno](@TNO_2 NUMERIC OUTPUT)
as
SELECT @tno_2 = isnull(max(tno),0)+1 FROM uttno
update uttno set tno=@tno_2
select @tno_2

The above script sometimes gets lock and takes time on concurrent access.So anyone pls suggest best alternative practice without using table.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 05:04:46

erm. Why not just use an IDENTITY column on the table and join to it rather than what you are doing?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 05:18:39

Charlie,

Thanks for quick response

i used that procedure so that tno dont get repeat in multiple tables..., IDENTITY will create unique tno for the table only...I need it to be unique across the multiple tables...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 05:54:25

if the [transactionNumber] represents the same entity across multiple tables then you should probably model it as a foreign key mapped to a transaction table.

If you were to do this then you could simply have a transaction table with an autonumber primary key, then have foreign keys for all the other tables to that transaction table. then you will be consistent.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 06:16:55

i dont want to use any table or trxn table with autonumber as PK..., is there any way of generating TNO with combination of datetime or else ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 06:30:05

nope -- you need some sort of broker because you've got concurrency. You can use GUID's but you've already discounted that. If each machine had something unique about it then you could probably cobble something together by concatenating unique things about the machine but..... seems really horrible.

I don't understand why you refuse to use a table because you *are* using a table right now, you are just doing it manually. Even if you didn't want the referential integrity of the FK's you could at least let the table handle the concurrency/locking for you with autonumber or a SEQUENTIALGUID or something.

Maybe someone else can help you but I doubt that I can.
Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 06:32:28

[code]CREATE PROCEDURE dbo.GetTransno
(
@TNO_2 NUMERIC OUTPUT
)
AS

SET NOCOUNT ON

DECLARE @Return TABLE
(
Tno NUMERIC
)

MERGE dbo.UtTno AS tgt
USING (
VALUES (1)
) AS src(Delta) ON src.Tno = tgt.Tno
WHEN MATCHED
THEN UPDATE
SET tgt.Tno += src.Delta
WHEN NOT MATCHED BY TARGET
THEN INSERT (
Tno
)
VALUES (
src.Delta
)
OUTPUT inserted.Tno
INTO @Return;

SELECT @TNO_2 = Tno
FROM @Return[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 06:33:39

If you are using SQL Server 2012, you can use a SEQUENCE object instead. Much easier.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 06:40:03

quote:

Originally posted by SwePeso

CREATE PROCEDURE dbo.GetTransno
(
	@TNO_2 NUMERIC OUTPUT
)
AS

SET NOCOUNT ON

DECLARE	@Return TABLE
	(
		Tno NUMERIC
	)

MERGE	dbo.UtTno AS tgt
USING	(
		VALUES	(1)
	) AS src(Delta) ON src.Tno = tgt.Tno
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Tno += src.Delta
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					Tno
				)
			VALUES	(
					src.Delta
				)
OUTPUT	inserted.Tno
INTO	@Return;

SELECT	@TNO_2 = Tno
FROM	@Return

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,

can you talk me through this? I understand what it does but I'd like to know if there are any advantages over just INSERTING into a table with a simple IDENTITY column with a SCOPE_IDENTITY() call afterwards.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 07:26:30

The statement above is wrong. I can see that now. OP's idea is to update the Tno table which has only one row.

CREATE PROCEDURE dbo.GetTransno
(
	@TNO_2 NUMERIC OUTPUT
)
AS

SET NOCOUNT ON

DECLARE	@Return TABLE
	(
		Tno NUMERIC
	)

IF EXISTS(SELECT * FROM dbo.UtTno)
	UPDATE	dbo.UtTno
	SET	Tno += 1
	OUTPUT	inserted.Tno
	INTO	@Return
ELSE
	INSERT	dbo.UtTno
		(
			Tno
		)
	OUTPUT	inserted.Tno
	INTO	@Return
	VALUES	(1)

SELECT	@TNO_2 = Tno
FROM	@Return

N 56°04'39.26"
E 12°55'05.63"

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 10:19:26

Hi Peso...
I dont want to use table...

While using table as you guys suggested..., there is problem.., i want to give example why i refuse to use table

Branch 1 running End of Day operation(EOD)...and one procedure accessing recursively {exec GetTransno @TNO_2 OUTPUT} within begin transaction...and assume this procedure takes 1 min. to complete(depends on trxn)...Here UTTno table is Lock for a min...

Now Branch 2 wants tno {exec GetTransno @TNO_2 OUTPUT}, during branch 1 performing EOD, Branch 2 need to wait until branch 1 completes its EOD operation.

So i need script that can generate unique sequential number like @@IDENTITY but its should be unique across multiple tables.

Any suggestion....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 10:27:06

OK. That's probably *not* what you want to do.

IF you need to get a transaction number then you should do that first and as quickly as possible. Get the transaction number and commit as soon as possible. then store that transaction number in a variable and use it. if you end up rolling back live the fact that you'll get gaps -- at least you want be locking other processes.

OR use a GUID. That's what they are designed for.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 10:39:12

Why do you care if the values are sequential???
Just grab the new value from the stored procedure first, and then do whatever you want in a transaction.
Don't include the stored procedure above to be included in the transaction.

N 56°04'39.26"
E 12°55'05.63"

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 11:17:29

might b mine bad luck...but still i will find out the wayout...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 11:28:30

Good luck. Without a broker or some deterministic way to make the numbers, or without a globally unique source, I think you will be looking for some time.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-02-23 : 11:31:00

Another possibility would be to use tables at the branches with a BIGINT transaction id, make them IDENTITY with very large IDENTITY seed values that are different for each branch, and add a check constraint on the primary key to prevent overlaps.

Example:
Table for Branch 1

create table MyTrans
(
MyTransID	bigint	identity(100000000000000,1) primary key clustered,
.. remaining columns...
)

alter table MyTrans
add constraint CK_MyTrans__MyTransID_Range
check (MyTransID between 100000000000000 and 199999999999999)

Table for Branch 2

create table MyTrans
(MyTransID	bigint	identity(200000000000000,1) primary key clustered,
.. remaining columns...
)
 
alter table MyTrans
add constraint CK_MyTrans__MyTransID_Range
check (MyTransID between 200000000000000 and 299999999999999)

CODO ERGO SUM

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 23:03:57

Thnxs Jones for response, but no use.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-02-24 : 09:29:12

quote:
Originally posted by Csoft

Thnxs Jones for response, but no use.

So you want help, but can't even be bothered to even explain why it is no use?

CODO ERGO SUM

Csoft
Starting Member

8 Posts

Posted - 2012-02-24 : 20:26:36

Michael...
i have already stated why your suggestion is no use in above comments...

mine current implementation is as you suggested..but its giving like 1% problems...so i just want to fixed it also....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources