-1

I do not want to pay any more money - so, this is not a duplicate.

A request has come down to restart using numbers after 30 days.

I am using AZURE SQL Server.

I have an integer field that is populated when a new record is created. This number, from the previous usage was an autoincrement integer, but, from the manager, this is getting confusing to the users to a see a number that keeps getting larger as they use the system.

So, the manager wants to see the number restart at one on the first of the month, and then increment from there, until the 1st of the month, then the number should start over at 1.

This resetting number is used as part of a concatenated string to generate a unique value, so i am not too worried about the repeating effect of using the same number.

My question is this - how in the world would this resetting be done? Azure does not really have a event scheduler 'thing' like a locally installed SQL Server does, and I only need it to run one time, where if two records are entered at 12:02AM only the first record would reset the number, the second would build from there.

My first guess was to use an insert trigger on the table, but this would require a large effort. my second thought was to have a next number table, but since it is a reactive type scenario, the number cannot be reset until AFTER midnight and on the first request.

any ideas would be greatly appreciated.

pithhelmet
  • 2,222
  • 6
  • 35
  • 60
  • Ugh...why are people (in this case the manager) so worried about identity values? If you don't want an ever increasing number then identity is the wrong thing to use. You could use ROW_NUMBER() or a sequence. Keep in mind that since you concatenating this value with a string that ordering is going to be a bit tricky. The order of strings would be something like 1, 11, 111, 12, 2 – Sean Lange Jun 01 '18 at 14:20
  • Bad request, bad solution. If Version >= 2012 I would use a SEQUENCE wrapped with a stored procedure to get the next value and do some fuzzy date lookup to see if the sequence should reset. If performance would be an issue with the date logic then simply have a job run at 12:00AM on the first day of the month to reset the sequence. – Ross Bush Jun 01 '18 at 14:35
  • You really just need a job scheduler, which Azure has (multiple). So this is a duplicate of https://stackoverflow.com/questions/27309474/best-way-to-schedule-sql-job-in-microsoft-azure – David Browne - Microsoft Jun 01 '18 at 14:57
  • 1
    Possible duplicate of [Best way to Schedule Sql job in Microsoft Azure?](https://stackoverflow.com/questions/27309474/best-way-to-schedule-sql-job-in-microsoft-azure) – Richard Jun 01 '18 at 15:13
  • Or maybe someone could just run down to the application users and explain how these new-fangled "number" things work. – Eric Brandt Jun 01 '18 at 16:14

1 Answers1

0

Just done a lift and shift to Azure (mainly App Services & Azure SQL) where there were more than 30 SQL Agent and SSIS Jobs

Solution was to use Azure Functions with Schedule triggers and a set of helpers (there is a small set of common operations that covers most of what was done like sending an email, generating a CSV, saving content to blob storage).

Richard
  • 106,783
  • 21
  • 203
  • 265
  • Thank you Richard - i went ahead and talked to the budget people, and they approved the monthly dollar addition to the azure account. – pithhelmet Jun 05 '18 at 12:28