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
 Site Related Forums
 Article Discussion
 Article: Custom Auto-Generated Sequences with SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

xnguyen
Starting Member

USA
2 Posts

Posted - 02/07/2012 :  12:49:34  Show Profile  Reply with Quote
The article was great, and method 3 has the advantage of mapping the system generated int to our sequence. However I would like help in how to tackle the issue if a key row is deleted because I want the sequence to be continuous. For example, the sequence is: TNT-YY-XXX where TNT is text, YY is 2 digit years, and XXX is the sequential number which resets to 001 every year. XXX has to be continuous, so if XXX is mapped to internal generated number, if a row is deleted, then there would be a gap, say current XXX is 012 and next one is 013, and if 013 is deleted, then the next would be 014. How can I get this to be 013, and reset the value automatically to 001 first day of next year?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/07/2012 :  13:30:23  Show Profile  Reply with Quote
I hear of people wanting contiguous sequences from time to time ... the issue, for me, is what happens if there is a ROLLBACK - how do you want that scenario handled?

Here's the chronological steps:

UserA starts a "transaction" and get ID=123
UserB starts a "transaction" and get ID=124

UserB finishes the transaction (i.e. BEFORE UserA). ID=124 is now written to the database

At this point UserA aborts their transaction, and it is rolled back.

So no there is no ID=123, and there is a gap. How do you want to handle this situation?
Go to Top of Page

xnguyen
Starting Member

USA
2 Posts

Posted - 02/07/2012 :  16:12:04  Show Profile  Reply with Quote
How complicate is it to roll back if I want that to happen? I don't even know where to start, and in my case, if I want to reset the counter each year, how to I proceed? Appreciate any help!

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/07/2012 :  17:13:55  Show Profile  Reply with Quote
quote:
Originally posted by xnguyen

How complicate is it to roll back if I want that to happen?


Normally a rollback is because you get half way through a procedure and then discover that you cannot complete it - e.g. there is some associated data missing, or some validation fails.

Other reasons might be because of a DEADLOCK and your transaction is aborted; or someone reboots the server.

If that the sort of thing you meant? or some other "business process rollback" - which might be refered to as hosuekeeping perhaps?

for the first:

BEGIN TRANSACTION

... do stuff ...
IF ... some errors condition ... GOTO PROC_ABORT

... do more stuff ...
IF ... a different error condition ... GOTO PROC_ABORT

-- Transaction completed, no errors
COMMIT
RETURN 0

PROC_ABORT:
ROLLBACK
RETURN -1
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 02/08/2012 :  07:49:02  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

I hear of people wanting contiguous sequences from time to time ... the issue, for me, is what happens if there is a ROLLBACK - how do you want that scenario handled?

...[snip]...

At this point UserA aborts their transaction, and it is rolled back.

So no there is no ID=123, and there is a gap. How do you want to handle this situation?



That's why I educate users to understand that contiguous sequences are the wrong thing to expect.


--Jeff Moden
Go to Top of Page

bagaisamang
Starting Member

2 Posts

Posted - 09/21/2013 :  11:27:40  Show Profile  Reply with Quote
hello.m new 2 programming n i would like to use the second option for assigning primary keys in my tables but i don know how to implement it in sql server version 5.5.16
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/21/2013 :  11:32:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There is no SQL Server version 5.5.16.
Are you referring to MySQL (owned by Oracle)?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

hehling01
Starting Member

China
1 Posts

Posted - 09/25/2013 :  02:36:31  Show Profile  Reply with Quote
unspammed
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.2 seconds. Powered By: Snitz Forums 2000