1

I have a non primary key TicketNo. field in my SQL server database where the format is fixed but consist different formats e.g. Vou001..Vou002, TKO001...TKO002. for every record that comes in, I need to increment the last no. so the record after Vou002 is Vou003, after TKO004 is TKO005. How can I perform this task without using some sort of record counting method. Since the table will consist of different kinds of TicketNo Format I believe it's not possible to get the last table value and add on it. Please kindly advice. Thanks.

k80sg
  • 2,443
  • 11
  • 47
  • 84

2 Answers2

2

Each code here consists of 1 parts: a prefix (Vou, TKP etc) and a number.

  • The number can be generated via a sequence emulation (or directly in SQL Server 2012)
  • The prefix can be stored separately or appended to the sequence before writing to the table

Using any kind of MAX or "roll your own" won't be concurrency safe for 2 concurrent/overlapping calls even with lock hints. And these will reduce performance too.

This DBA.SE question shows how to emulate sequences in SQL Server: https://dba.stackexchange.com/questions/3307/emulate-a-tsql-sequence-via-a-stored-procedure

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

I would suggest that you have a look here, the issue was not exactly the same, but I guess that's a good start : How do I add string with auto increment value in SQL Server?

Community
  • 1
  • 1
BuZz
  • 16,318
  • 31
  • 86
  • 141
  • +1 yes- that's really the only reliable way to do it - let the database handle the autoincrementing, and just add your prefix to it. – marc_s Nov 28 '11 at 09:08
  • @marc_s: can be done by emulating a sequence per prefix. MS have an article on it (see dba.se link) – gbn Nov 28 '11 at 09:12
  • For my case, the field format can be different, it actually retrieves the formatting from another table which has a 'formatting' Field column. – k80sg Nov 29 '11 at 09:17