Generating a Sequential Pattern
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aSayed/generatingasequentialpattern.asp
-
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
-
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]
-
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).
-
For SQLServer Magazine subscribers, here is a somewhat related artitcle
http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html
-
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)?
-
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
-
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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.
-
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.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Or better yet
update SequenceTable
set @NewSequenceNumber = CurrentSequenceNumber = CurrentSequenceNumber + 1
where SequenceYear = year(getdate())
Having said that, central locations for keys are a concurrency nightmare for anything that needs to scale. I would generally steer clear.
-
>> "SET IDENTITY_INSERT ON" would be needed for prior year, with logic similar to above, but hopefully that would be relatively rare. <<
In fact, in most systems, especially dealing with invoices, it should never be allowed -- once the current year is past, no new invoices with the prior yr on them should be created.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Mark,
Where are you updating the stored value? I have used this a few times and have not seen any impact on scalability but it may not have been under a big enough load. The statement as I posted it gets in and out of the table very quickly so I am not sure what kind of concurrency problems you might be referring to. Please give us a little more detail before I use this again!
Scott,
I like your idea as well. It wouldn't be hard to create a job that ran at the beginning of every year to reset the seed and increment of the identity column. The only downside (very minor imo) would be that you can't put any non-numeric characters in the field itself.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
First.
The Idea of a Sequence table is the right idea in this case.
Second.
"Central location for keys is a concurrency nightmare" if you are not using the appropriate locking mechanisms.
ex:
- do not compute the sequence year on the query
- Should you need more than one at a time add a @count param and get only the latest
- if you have multiple sequences make sure you have a primary key on the sequencing key
- should the table grow make sure that the update is using (with rowlock)
- Last but not least if the number of proccess competing for keys is very ( and I mean very
) high use sp_tableoption and "pin" the sequence table.
As you can see I have experience with this structure and have been successfuly able to implemet such construct.
It is not my preffered way but it is very portable and very flexible
Cheers,
* Noel -
Will it work in multiuser environment???
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply