0

In SQL Server I have a table with the following data: first name, last name, birthplace, etc, etc.

The table has an identity ID column (the primary key of the table). In the interface of my application, the user can modify this data until they hit a button named "Close Record", when this happens I have to generate a Closed_Record_ID with syntax year + consecutive_number. For all the records in my table, the order they entered the database (given by the identity ID), may not be the same in wich they were closed, so I have to generate a new consecutive number.

How should I use the Tablockx hint, or what should I do to avoid duplicate consecutive numbers in the Closed_Record_ID column?

thx0125
  • 129
  • 1
  • 9
  • What version of SQL Server is this ? – M.Ali Nov 21 '14 at 19:41
  • Microsoft SQL Server 2014 – thx0125 Nov 21 '14 at 20:18
  • Why not use a [SEQUENCE](http://msdn.microsoft.com/en-us/library/ff878091.aspx)? You might have to do some data type conversion a couple of times to get the `year + consecutive_number` format you're looking for. Should work, though. – Dave Mason Nov 21 '14 at 22:09
  • On a side note, I would put `year` and `consecutive_number` in separate columns. It's generally not a good idea to put multiple pieces of data in the same column. – Dave Mason Nov 21 '14 at 22:12
  • Thank you for the tip; I'm gonna read about SEQUENCE to see if it fits my needs. And yes, year and consecutive_number are two separate columns – thx0125 Nov 22 '14 at 07:52

0 Answers0