DB concurrency how to....

• vinpes

  SSC Veteran

  Points: 276

  More actions

  June 2, 2014 at 12:49 pm

  #1718327

  In Italy the tax office requires a complete seq. number for invoices 🙁

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  June 2, 2014 at 12:54 pm

  #1718332

  vinpes (6/2/2014)

  ---

  In Italy the tax office requires a complete seq. number for invoices 🙁

  Can you just use a normal process (identity/sequence) for use in the system and then create another column for TaxOfficeInvoiceNumber. Then once a day (or whatever interval) update your base table using ROW_NUMBER or similar? Would something like that be sufficient?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 2, 2014 at 12:55 pm

  #1718333

  vinpes (6/2/2014)

  ---

  In Italy the tax office requires a complete seq. number for invoices 🙁

  Yes, I have read about that sort of thing in the EU in general. But from what I read that doesn't preclude having VOIDED invoice numbers, unless you can find some references to the contrary.

• vinpes

  SSC Veteran

  Points: 276

  More actions

  June 2, 2014 at 1:04 pm

  #1718336

  Hi Sean,

  Can u provide to me an example, pls?

• ScottPletcher

  SSC Guru

  Points: 101130

  More actions

  June 3, 2014 at 1:27 pm

  #1718668

  Yeah, no gaps will be a royal pain to implement.

  If you can allow "voided" gaps, then I think a standard identity would handle this, don't see the need for using a sequence. The identity is simply reseeded every Jan 01 at midnight with YYYY000001 (which could still be just an int and last through year 2146).

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

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 3, 2014 at 5:13 pm

  #1718716

  Here is one way, which may or may not work for you.

  Have a table of Invoice Numbers.

  create table InvoiceNumbers(InvoiceNumber int);

  create unique clustered index ci_InvoiceNumbers on InvoiceNumbers (InvoiceNumber DESC) WITH(FILLFACTOR=100);

  insert invoicenumbers(invoicenumber)

  select top(1000000) 2014000000 + row_number() over(order by (select null))

  from sys.all_columns a, sys.all_columns b;

  When you want one, DELETE it and OUTPUT the one you deleted.

  Make sure you COMMIT this straight away, so as not to block anyone else.

  declare @nextnum table(InvoiceNumber int);

  begin tran;

  begin try

  with data as (select top(1) invoicenumber from invoicenumbers order by invoicenumber asc)

  delete data

  output deleted.invoicenumber into @nextnum(invoicenumber);

  end try

  begin catch

  -- failed to get an invoice number, do some error handling

  end catch

  commit;

  IF you need to rollback the transaction and re-use the invoice number, just INSERT it back into the pool...

  Make sure this happens in an isolated transaction, AFTER you ROLLBACK your Invoice Creation transaction.

  begin tran;

  insert invoicenumbers(invoicenumber)

  select invoicenumber

  from @nextnum;

  commit;

  On my quick test, (just using three query sessions with GO 10000) on a desktop PC , this can push Invoice Numbers as fast as the machine can request them (GO 10000 is not that quick a method, so only ran at about 300 invoice numbers per second on each thread)

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]