Generate unique number

Posted on 2000-02-14
Last Modified: 2012-05-04
I have a Clerk table that contains a LogonCode field of type int - it is a unique key.  
I am writing a program that uses this table and when a new Clerk is created I would like to get the next valid Unique Logon code from the table.  Is there any SQL code that can do this???

Question by:I_jolly
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3

Expert Comment

ID: 2518975
You have some options:

1) You could setup the datatype of that column to be Identity and the system would handle it for you.

2) You could have another table with 2 columns that keeps track of the key (say Clerk) and the next number to use. You update that table and use the next number.

3) you can select max(LogonCode) from the table and use that+1.


Author Comment

ID: 2522134
I want to use a form of loop within SQL whereby I check if the number exists in the table and if it doesn't then select that number.

Author Comment

ID: 2522136
Sorry,  Forgot to thank David for his last answer.
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Expert Comment

ID: 2522167
No, you don't want to use a loop. Not in SQL anyway.

Can you let me know my answers were not acceptable. They would give you the rseults you requested and even the comment you made would be satisfied by one or all of those answers.


Author Comment

ID: 2522271
1, Identity - This assumes that I will be inserting the record into the database - After a new Clerk is created by the user it is not Necessarily persisted to the database.

2, I don't want to add a new table to the existing database - Although this would provide a solution I am sure that there is another method of doing this without adding additional tables \ DRI to the database.

3, max(LogonCode) from the table and use that+1 - The LogonCode would only increment.  I am expecting that users will constantly be added and removed - Using this method could lead to problems - (There is a business Rule within the Program that LogonCodes must be in the range 0 - 999).

Why should I not use a loop.  I had in mind something like this

      If Not EXISTS (Select PKClerkID from CLERK Where LogonCode = @COUNTER)
            SELECT @COUNTER = @COUNTER +1
Print @Counter


Expert Comment

ID: 2522293
Well in a real world environment, with hundreds of users and tens of thousands of transactions a day, your idea is no good for the following reasons...

1) You might need to process through a large amount of records every time you need to find a number... bad.

2) Two people, running the same query at almost the same time, could get the same number back... bad.

3) Where's the loop in your code?

You need to design your applications intelligently. This isn't a personal attack against you. I've just inherited code in the past that was written like you suggest and it's a nightmare to maintain and enhance. Write it correctly the first time. You aren't taking advantage of the power and capabilities in SQL.


Author Comment

ID: 2522468

I did miss the top out.  There will be no concurrency within the program I am coding and the Business Rule limits the size to 999.  I do agree with you that it may cause problems later on if the program is enhanced - So will look into your answers again

Accepted Solution

ginde earned 30 total points
ID: 2523413
You do not need loop for what you are tring to do. You can also do it as

If exists ( select id from yourtable where id = 1 )
select @newid = min(id) + 1
from yourtable a
where not exists ( select id from yourtable b where =
 @newid = 1

This gives you the minimum id available.


Author Comment

ID: 2523814
It's beautiful.....

I do agree that all of Davids previous answers are also correct but this was exactly what I was looking for.

Thanks everyone

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question