How to get the next ID from DB Table without an IDENTITY or GUID Column

Question

0.00/5 (No votes)

See more:

I have a table with INT PrimaryKey Column. I do not want to use GUID or IDENTITY as Primary Key.

I wanted to know what is the best possible way to get the next ID without using an IDENTITY or GUID as Primary Key column in the table. I do not mind using GUID or IDENTITY in the table unless its the Primary Key column.

I have to find the next available ID (i.e get the MAX ID and increment it by 1),and use that:

SQL

Select @id=ISNULL(max(AlbumId)+1,1) from Albums

However, I want to prevent other applications from inserting into the table when I'm doing this so that we don't have any problems.

NOTE:I have gone through this[^] answer and could not understand it properly. Would anyone be kind enough to explain me what is it they are doing?? And can i use the same technique??

Thanks.

Posted 5-Dec-12 5:51am

Member 7781963

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DaveP62 · Answer 1 · 2012-12-05T09:41:00

It seems like you're going through a lot of trouble to find "the best possible way" without using IDENTITY when the best possible way is to use an Identity field and have the Insert statement return the SCOPE_IDENTITY() variable from SQL Server after your insert. This will insure that all clients get the correct ID related to their specific insert.

If you need to do it the hard way for some reason, one approach is to make another table to hold ID's. The table can have 2 fields: New_Id and Random

The New_Id field can be a unique auto-incremented integer
The Random field can be a varchar(32)

Your application can generate a random string of 32 characters and insert it into the table. Then select the New_Id for that string of characters. This way the applications will always get the correct Id for what they're doing without interfering with each other.

I would never suggest doing this over using IDENTITY but I think it does answer your question.

Good Luck!