0

Manually generate a number counter in SQL Server.

I have a table with quotes. The columns are ID which is primary key and identity, Title Nvarchar, Des Nvarchar, UpdateNumber.

I want to add a number like an index which should increase by 1 whenever new record is inserted.

I cannot use identity in SQL for the same as it leaves gaps in between.

I have used the following code in stored procedure whenever new record is inserted.

INSERT INTO [mps] ([Title], [Des], UpdateNumber) 
VALUES(@title, @des, (select (max(id) + 1) from mps))

My concern is that there shouldn't be any duplicate, what if two people insert the records at the very same time. How will SQL handles this? Or is there any other better method of doing this?

I'm using a similar numbering in another project where I want an incremental number whenever new record is inserted. There cannot be gaps in these as I'm printing the same as document number for collating and gaps might create confusion after printing. So should the below method work in that solution

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Danish_k12
  • 329
  • 2
  • 3
  • 20
  • I read it here: http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values – Danish_k12 Jun 02 '16 at 09:33
  • 3
    An identity column would only leave gaps if records are deleted or insert fails. but an Identity column is not about generating sequential numbers, It's about generating *unique* numbers. Why would you care about gaps anyway? – Zohar Peled Jun 02 '16 at 09:36
  • 1
    Read this comment, explains the cases where you'll get gaps, but even still i'd use an identity: http://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values#comment20456462_14642046 – Tanner Jun 02 '16 at 09:37
  • So I should use the identity column instead of creating this field? – Danish_k12 Jun 02 '16 at 09:38
  • 4
    Yes, you should use an identity column. – Zohar Peled Jun 02 '16 at 09:39
  • 1
    Why are gaps a problem? Use an Identity column – Nick.Mc Jun 02 '16 at 10:13

1 Answers1

0

You can use Count function

INSERT INTO [mps] ([Title],[Des],UpdateNumber) VALUES(@title,@des,(select (count(*)+1) from mps))

or you can use Max

INSERT INTO [mps] ([Title],[Des],UpdateNumber) VALUES(@title,@des,(select (max(isnull(UpdateNumber, 0))+1) from mps))

with these there will be no duplicates

MSL
  • 990
  • 1
  • 12
  • 28