CODEWITHSUNDEEP
c#sqlsql-server-2008asp.net-mvc-4
BenM
BenM

Reputation: 4278

Best practice to stop duplication of a ++ incremented number being populated in a database

I'll try to word this as best I can.

I'm coding a website using C# MVC4 with a MS SQL database. Whenever a user submits an application via the website I do a look-up on the table, find the highest numbered session code (7 digit, number) add 1 to this number and populate it in the database along with the user details.

We are on the verge of starting a huge add campaign and there will be a massive influx of users. I am worried that if two users submit their applications at the same time that the same session code will be generated for both. What are some practices to stop this? I'm thinking make the field unique in the DB and have a loop surrounding my session code generation and population to try again should it fail.

Is there a better practice than this (other than creating a composite primary key)?

Upvotes: 0

Views: 160

Answers (3)

juergen d
juergen d

Reputation: 204794

You can make the number identity starting from any number you like. Then the DB will add 1 to the highest number by itself.

See the MSSQL doc

Upvotes: 5

chris warner
chris warner

Reputation: 178

in SQL Server Management Studio set the column properties to

Identity Specification Yes (IS Identity) Yes Identity Increment 1 Identity Seed [Your last Number]

This will set that column to auto increment, you then no longer need to set this column when submitting data to the DB

Upvotes: 0

Vulcano
Vulcano

Reputation: 435

You might set the number to the identifier and let it autoincrement Index autoincrement for Microsoft SQL Server 2008 R2

Otherwise if it is possible to change the datatype - create Guids (uniqueidentifier in the database). Then your quite safe against collisions

Upvotes: 1

Related Questions