SQLServerCentral / Discuss Content Posted by Asif Sayed / Article Discussions / Article Discussions by Author / Generating a Sequential Pattern / Latest Posts

RE: Generating a Sequential Pattern

Samuel Clough — Thu, 17 May 2007 05:41:00 GMT

We've had a similar situation and deployed a solution like Noels which I think is the best. It gives you something to lock on to prevent the concurrency problem (which will show up once you get a lot of users and data on the system). It also will perform must faster because you only have to read one row out of a table that will have only a couple of rows (if one per year) rather than scanning your invoice table for max constantly.

RE: Generating a Sequential Pattern

mitul surti — Wed, 16 May 2007 22:30:00 GMT

hmmm....well it does solve the issue MOMENTARILYhowever if its really deployed over a vast network where a transaction of insert would be like fired so very frequently...the user is definitely gettin more already used errors...i am sure...and plus there are better alternatives already available...nyways this definitely helped thoughh

RE: Generating a Sequential Pattern

SwePeso — Sat, 17 Jun 2006 11:57:00 GMT

Iron clad?

-- prepare test datadeclare @test table (t varchar(50))insert @testselect 'ov1' union allselect 'ov2' union allselect 'ov4' union allselect 'ov31' union allselect 'ssss1' union allselect 'ssss99'-- show data before inserting new valuesselect * from @test-- do the workdeclare @prefix varchar(50)select @prefix = 'ov' -- Throw anything in!insert @testselect @prefix + convert(varchar, t.n + 1)from ( select max(convert(int, substring(t, 1 + len(@prefix), 50))) n from @test where t like @prefix + '%' and not substring(t, 1 + len(@prefix), 50) like '%[^0-9]%' ) twhere not @prefix like '%[0-9]%' and not t.n like '%[^0-9]%'-- show data after inserting new valuesselect * from @test

RE: Generating a Sequential Pattern

Asif Sayed — Thu, 18 May 2006 08:39:00 GMT

I liked your revised approach of letting SQL pick proper index and help speed up performance. The goal for this approach was totally eliminate the need to “maintain any sort of counters” and I think I have mentioned that the solution I proposed is for a small to medium size operation, believe it or not it always worked for me since about 8 years

I will give it another shot using CLR integration and see how much does it makes difference.

Regards,

Asif

RE: Generating a Sequential Pattern

Phil.Nicholas — Thu, 18 May 2006 02:31:00 GMT

How about this string keys are to be avoided (not least because they become foreign keys), also the indexes will be larger and less efficient than integers. This method uses an identity key and a derived column that calculates the invoice id from the identity column. An alternative would be to calculate the invoice id using the function and storing it alongside the identity column, might be worth storing and indexing the year too as likely to group indexes by year, can use check constraints to ensure values are in sync. create table tbl(id int identity(1,1), dt datetime) go create function dbo.GetInvoiceId(@i int, @yr int) returns varchar(10) as begin declare @r varchar(10) set @r = @i - coalesce((select max(id) from tbl where year(dt) < @yr),0) return substring(cast(@yr as varchar(4)),3,2) + substring('-000000',1, 7 - len(@r)) + @r endgobegin alter table tbl add MyId as dbo.GetInvoiceId([id], year(dt)) insert tbl(dt) select top 100 '2004-01-01' from sysobjects insert tbl(dt) select top 100 '2005-01-01' from sysobjects insert tbl(dt) select top 100 '2006-01-01' from sysobjects select * from tblendgo drop table tblgodrop function dbo.GetId

RE: Generating a Sequential Pattern

Heiko Hatzfeld — Thu, 18 May 2006 00:59:00 GMT

I was thinking a bit about the performance problem I mentioned above when creating a sequence.

The group by querry could be rewritten so it can correctly use the indexec.

The following querry would do the trick:

SET @tmp_invoice_id = (SELECT top 1 (SUBSTRING(invoice_id, 4, 5) + 1) FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%')order by invoive_id desc)

This will limit the querry to only retrieve one record and only do one substring and one implicit cast on the data.

RE: Generating a Sequential Pattern

Asif Sayed — Wed, 17 May 2006 20:26:00 GMT

Thankyou all for taking time to put your comments... I am glad to see a healthy discussion here on topic which I startedRegards,Asif

RE: Generating a Sequential Pattern

noeld — Wed, 17 May 2006 15:08:00 GMT

Hehe...

I was refering to the Identity column with primary key constraint

Should you need this "sequencing" you can use the table and should you could care less about it use the Identity instead.

The sequence is very good to "reserve" client side batches of candidate keys in one go

I am certaint that scalability is excellent if you apply all the remomendations I posted (I have !)

RE: Generating a Sequential Pattern

Heiko Hatzfeld — Wed, 17 May 2006 12:34:00 GMT

It should be a separate column and concatenated into the computed column that is displayed to the user.

I think thats the most important post in this discussion. I think one of the worst things you can do to a relational DB is to store MORE THEN ONE Information in a field. And if you "force" the field to become a wrong datatype it will even get worse.

Take the Year-Number example...

SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 4, 5) + 1) FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))

To find all "Year entries" you can utilize an Index with "like '06%' after that you are using a function to find the maximum value of ALL Invoices for that year. That means in a "worst case" you have to do 99.999 times Substring ?

I think the overhead when creating the sequential Invoices can be negelected if you are using a sequence table... Also here is a small suggestion for the self updating of the sequence table. If I am faced with a the same problem, then i usually do it the "lazy way" Update the row i need to update, and if I get a rowcount of "0" then I know that i need to actually insert the row. Updating 0 Rows has no negative performace effect on SQL Server (Assuming correct indexes) and if I first check if the row exists, and then update, then i need to access the index 2 times... Once for checking and once for updating... So I can skip the 2nd Access to the index most of the times. And the Insert will automatically fierd when its needed.

But when the reporting season starts this (string) system will hurt you. It is not possible to generate a "good" index on these fields. and when you start running querries this will start to hurt you. It might only start with an concattinated Invoice Number... but in a current application i am working with this started to run rampart... They needed to change the Datatype from Varchar to Binary to store all the "extra" information (Yes I tried to convice them that this is stupid... trust me I tried.. I am also looking for a Job now where ppl actually listen)

P.s: The suggested solution can still be valid if the number of Invoices remains small. You allways have to think about which compromise you can make...

RE: Generating a Sequential Pattern

Scott Coleman — Wed, 17 May 2006 10:54:00 GMT

I'm not sure how the function will work with multiple rows, I get a duplicate key error when I try it.

Maybe if it was implemented as a CLR function and flagged as non-deterministic, but I don't think it's worth the trouble.

RE: Generating a Sequential Pattern

Dennis D. Allen — Wed, 17 May 2006 10:29:00 GMT

I love functions... below is the function declaration but first the insert statmentINSERT INTO dbo.Invoice ( Invoice_id, Customer_name, Invoice_amount )VALUES ( dbo.fn_NextKey(05), 'ACME Corp.', 12345.67 )This technique also works with SELECT inserts for multi-row inserting joy. The function is also easy to modify for different key compositions mmyy-nnnnnnnnnn or what ever...Cheers!CREATE FUNCTION dbo.fn_NextKey( @year int )RETURNS varchar(10)ASBEGIN /* ASSERTIONS * Invoice_Id has a fixed pattern of yy-nnnnnn where yy is last two digits of a year * and nnnnnn is a 6 digit 0 padded number that is incremented by 1 */ DECLARE @pattern char(3), @answer varchar(10) SET @pattern = right('00'+convert(varchar(10),@year),2) + '-' SELECT @answer = CASE WHEN max(Invoice_id) IS NULL THEN @pattern + '000001' /* first invoice of given year */ ELSE @pattern + right('000000'+convert(varchar(10),convert(int,substring(max(Invoice_id),4,6))+1),6) END FROM dbo.Invoice WHERE Invoice_id LIKE @pattern+'%' RETURN @answerEND

RE: Generating a Sequential Pattern

ScottPletcher — Wed, 17 May 2006 10:21:00 GMT

One of my concerns with a sequence table, aside from the additional I/O and other overhead, is that you cannot then easily insert multiple invoices in one statement.

If, for example, thru some EDI (yes, it is still used) or other process you have a batch of invoices to enter, you must single-step thru them with a sequence table, but not with an identity column.

As for a non-numeric, that's true, it could not be in the column itself -- then again, it shouldn't be anyway imo. It should be a separate column and concatenated into the computed column that is displayed to the user.

RE: Generating a Sequential Pattern

Scott Coleman — Wed, 17 May 2006 10:06:00 GMT

Noel, you're confusing me. "A sequence table is the right idea", but "It is not my preferred way"? So there's another way you prefer that's better than the ones posted, but you're not going to share it with us?

I agree that the sequence table is a very workable solution and would not worry about scaling if it is done right. My biggest concern would be guaranteeing that all inserts are done by a stored procedure that correctly accesses the sequence table.

You could argue that the identity field approach should scale better because you only need one action to insert the record, while the sequence table requires an extra action for the update. This is probably just a theoretical argument, because I can't imagine a real situation where a sequence table approach would break down if all of Noel's suggestions were applied.

On the other hand if you've got such a volume of invoices that you're worried about scaling, you should be able to afford enough hardware to deal with it. I'd be happy to come help set up your new 32-processor servers for the reasonable sum of twice my current salary.

RE: Generating a Sequential Pattern

noeld — Wed, 17 May 2006 09:58:00 GMT

Absolutely

RE: Generating a Sequential Pattern

Bhushan — Wed, 17 May 2006 09:51:00 GMT

Will it work in multiuser environment???

RE: Generating a Sequential Pattern

noeld — Wed, 17 May 2006 09:42:00 GMT

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,

RE: Generating a Sequential Pattern

Tatsu — Wed, 17 May 2006 09:23:00 GMT

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.

RE: Generating a Sequential Pattern

ScottPletcher — Wed, 17 May 2006 08:59:00 GMT

>> "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.

RE: Generating a Sequential Pattern

Mark Hickin — Wed, 17 May 2006 08:55:00 GMT

Or better yetupdate SequenceTableset @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.

RE: Generating a Sequential Pattern

Tatsu — Wed, 17 May 2006 08:45:00 GMT

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 SequenceTableset @NewSequenceNumber = CurrentSequenceNumber + 1, CurrentSequenceNumber = @NewSequenceNumberwhere 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.

RE: Generating a Sequential Pattern

Scott Coleman — Wed, 17 May 2006 08:45:00 GMT

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.

RE: Generating a Sequential Pattern

ScottPletcher — Wed, 17 May 2006 08:21:00 GMT

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.

RE: Generating a Sequential Pattern

Scott Coleman — Wed, 17 May 2006 08:18:00 GMT

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 breakEND

RE: Generating a Sequential Pattern

Onskee — Wed, 17 May 2006 07:04:00 GMT

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)?

RE: Generating a Sequential Pattern

Greg Burns — Wed, 17 May 2006 07:00:00 GMT

For SQLServer Magazine subscribers, here is a somewhat related artitcle

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

RE: Generating a Sequential Pattern

Aarez — Wed, 17 May 2006 06:52:00 GMT

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]

RE: Generating a Sequential Pattern

ben salemi — Wed, 17 May 2006 06:52:00 GMT

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).

RE: Generating a Sequential Pattern

SuperDBA-207096 — Wed, 17 May 2006 06:02:00 GMT

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

Generating a Sequential Pattern

Asif Sayed — Tue, 25 Apr 2006 21:27:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aSayed/generatingasequentialpattern.asp