Auto increment based on date

I have a Primary Key column that I would like to have formatted as follows: 'yyyymmdd[auto increment]' . I would also like the auto increment to restart at the beginning of a new month. Any idea's on how to achieve this?

Thanks

You will have to manually control all the inserts via stored procs. Have a separate column with identity property, do the insert, get the Id via SCOPE_IDENTITY() then update your column with the concatenated value. Only drawback is you cannot have it as PK since the value has to be calculated. You can however have a unique constraint on the column. Alternatively if you have a separate table that has just one column with identity values, you can do an insert there first, get the seed value and then use that to insert into your regular table with the concatenation.

|||

An interesting concept for sure but doesn't this smack of a normalization violation which may be the least of your problems with this idea. I'd keep the date in a separate column and use a normal auto increment. From that you should be able to accomplish just about everything you're trying to do with this unusual idea including presenting the user with a computed column that looks like 'yyyymmdd[order number].'

If I just had to succeed with this idea I'd consider writing a function that returns your special key but you'd probably have to input your last increment used from an embedded SELECT statement.

Something like INSERT tblName (pKey,...) VALUES( dbo.fnGetPrimary((SELECT MAX(pKey) from tblName WHERE (pKey > (DATEPART(.. hand build the earliest possible key for the current month which could be a separate function.)) ), .....)

If dbo.fnGetPrimary is fed a null then it would know it is the first entry of the current month. If dbo.fnGetPrimary is fed a value such as 2007110510 then you would just increment it by 1 and return it.

I'd be a little concerned with performance and concurrency with this idea - two records trying to insert and both trying to use the same primary key. You might be able to avoid that with a transaction.

Any way it was fun playing around with it. Good Luck.