Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generating a Sequential Pattern


Generating a Sequential Pattern

Author
Message
Asif Sayed
Asif Sayed
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 3
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aSayed/generatingasequentialpattern.asp
SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1479 Visits: 711

Asif,

Nice article! I've dealt with this problem before (I implemented a client-side solution instead of an SQL based solution). I like the approach and probably will use it in the future.

Mark


ben salemi
ben salemi
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11

If you did want to worry about concurrency, there is always the technique of serializing the requests by obtaining a row lock on a common resource.

Imagine a new table (SERIALIZER) that has one row, start a transaction and update the row as the first action in the stored procedure. Now, other calls to the stored proc will wait on the first call to finish the transaction.

Just makes sure to commit the transaction at the end of the stored procedure (or rollback in case of an error).


Sabir Khan
Sabir Khan
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 18

Asif,

If it is your first article then I must say -decently written article.

I have also dealt with such situations many times but what I noticed in your solution was that you did not handle the case if maximum possible Invoice Id (i.e. yy-99999) was already generated. However, you put enough attention not to let generate a duplicate.

Anyways, it was a good article and keep sharing.

[Aarez]


Greg Burns
Greg Burns
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 57

For SQLServer Magazine subscribers, here is a somewhat related artitcle

http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html


sqlservercentral@onskee.com
sqlservercentral@onskee.com
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 292

Is there a benefit to dealing with storing and looking up the string 'yy-xxxxx', rather than 2 numerical fields (a year field and some incrementing number)?





Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 1418

I think this code solves the problem of concurrent inserts. One SELECT is used to generate the new invoice id, but if another user manages to sneak it in first the code loops and tries the next number.

SET @tmp_date = RIGHT(CAST(YEAR(GETDATE()) + CASE WHEN @year_flag = 'P' THEN -1 ELSE 0 END AS CHAR(4)),2) + '-'

while 1=1 begin
SET @new_invoice_id = @tmp_date
+ RIGHT(STR(ISNULL(
CAST(RIGHT(
(SELECT MAX(invoice_id) FROM dbo.Invoice WHERE invoice_id LIKE (@tmp_date + '%'))
,5) AS int) + 100001
,100001),6,0),5)

/* For testing purposes, uncomment the following code to randomly insert conflicting records
if rand() > 0.6
insert into Invoice (invoice_id, customer_name, invoice_amount) values(@new_invoice_id, @customer_name, @invoice_amount)
*/

insert into Invoice (invoice_id, customer_name, invoice_amount)
select @new_invoice_id, @customer_name, @invoice_amount
where not exists(select null from Invoice with(holdlock) where invoice_id = @new_invoice_id)

if
@@ROWCOUNT > 0 break
END





ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3954 Visits: 6692

Yes. Or possibly even a single identity column that was re-seeded each year to yy00000[0]; the "-" could be added prior to display just for the "visual effect" for the user. "SET IDENTITY_INSERT ON" would be needed for prior year, with logic similar to above, but hopefully that would be relatively rare.



SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Scott Coleman
Scott Coleman
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 1418

Yes, this is a much easier problem if you just use an integer identity field. You could create a view using "STUFF(CAST(invoice_id as char(8)),3,0,'-') as invoice_id" for anything that insists on seeing it that way.

I have tried for years to convince boneheads that if they want to see numbers with leading zeros, embedded dashes, etc, in their stupid spreadsheets then they should figure out how to format a column in Excel rather than try to dictate that I need to change all the numeric fields in the database to characters. If you have better luck than I do, let me know how you do it. Funny how after they run the data through Excel and lose all the leading zeroes they still want to blame the database.

The last time someone whined about storing the leading zeroes I offered to print a binary dump of the data pages to prove that every 32-bit integer field did indeed have all of the leading zero bits intact. I haven't had any complaints since then, but it's only a matter of time.





Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307

Why not just store the sequence number in a table and perform an update statement like this (assumes a table called SequenceTable exists and has two columns, CurrentSequenceNumber [int or bigint] and SequenceYear [int]):

update SequenceTable
set @NewSequenceNumber = CurrentSequenceNumber + 1, CurrentSequenceNumber = @NewSequenceNumber
where SequenceYear = year(getdate())

This gives you the next number in the sequence and updates the sequence table in a single transaction. When the new year rolls around just add a new row to the sequence table for the new year and start the sequence at zero again.

The update statement could also be executed from a user-defined function that concatenates the year and sequence number into a string.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search