SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Generate sequential Unique Numeric Trans No
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/23/2012 :  04:17:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/23/2012 :  05:04:46  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/23/2012 :  05:18:39  Show Profile  Reply with Quote
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...

Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/23/2012 :  05:54:25  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/23/2012 :  06:16:55  Show Profile  Reply with Quote
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 ?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/23/2012 :  06:30:05  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/23/2012 :  06:32:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/23/2012 :  06:33:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/23/2012 :  06:40:03  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/23/2012 :  07:26:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/23/2012 :  10:19:26  Show Profile  Reply with Quote
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....
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/23/2012 :  10:27:06  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 02/23/2012 :  10:39:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/23/2012 :  11:17:29  Show Profile  Reply with Quote
might b mine bad luck...but still i will find out the wayout...
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/23/2012 :  11:28:30  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/23/2012 :  11:31:00  Show Profile  Reply with Quote
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
Go to Top of Page

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/23/2012 :  23:03:57  Show Profile  Reply with Quote
Thnxs Jones for response, but no use.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 02/24/2012 :  09:29:12  Show Profile  Reply with Quote
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
Go to Top of Page

Csoft
Starting Member

Nepal
8 Posts

Posted - 02/24/2012 :  20:26:36  Show Profile  Reply with Quote
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....
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000