Generating Unique Numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Generating Unique Numbers

Author

Topic

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-18 : 08:37:45

Hi there.

I am looking for a clean, simple way to generate a unique number that does not currently exist in my table. This number will be used to group data together in logical groups.

For example:

ID Grouper Data
--- ------- ----
1 1 sdsdfgfsdg
2 1 dfdfdfdf
3 1 8dfsdf
4 2 dfdfdfdf
5 2 kjlksjgsg
6 3 kldjflkajf
7 4 dfasdfadsfas
8 5 adsfasfasf
9 5 adsfasdfasdf

I am looking to generate the next number in grouper - trying not to do multiple selects. I could do a SELECT MAX(Grouper) but not sure if that is the best way. Looking for new ideas and best practices.

Thanks in advance!

Dennis

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 09:14:31

NEWID() function?

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 09:36:57

if you want it sequential use NEWSEQUENTIALID()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 10:10:09

Isn't a GUID a bit of an overkill chaps?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-18 : 10:14:29

I agree with Kristen.

You can store "grouper" in a seperate table with an identity column, then create a function (or SP) to insert the next value and return it.

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 10:20:34


SELECT @NextValue = COALESCE(MAX(Grouper), 0)+1

??

(Dunno if that will be safe if there is simultaneous update contention.)

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-18 : 11:12:47

Thanks for the ideas everyone! I think that SELECT MAX(Grouper) is probably the simplest way. Question - if I do not have any NULLs in that column, the COALESCE is not needed, right?
Dennis

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 11:49:34

COALESCE was for the situation where there were no rows (yet) in the table

If there is risk of NULLs in that column add

WHERE Grouper IS NOT NULL

otherwise SQL will generate a warning ("Aggregate contains NULL values" or similar) and that may cause your application to barf!

Beware any issues associated with two processes trying to get the next-available-number at the same time (which Russell's proposal avoids). I'm not sure if its a problem, or not; it may just depend on the isolation level and whether your query to set the NextID is within the transaction scope, so to speak.

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-18 : 12:58:11

Hi Kristen. Yeah, you are right. If I have alot of application activity, in the few milliseconds it takes to get the NextId using SELECT MAX and processing my inserts, there could be a problem with two people getting the same number! Dropping in a value in to another table and selecting it would work, just seems like overkill.
What if we change the requirement a bit? What if I do not care of the Grouper is sequential, but MUST be unique and no other application user can grab it at the same time? DOes that help? Grouper really is only there to group like data, so i really do not care what the value is as long as it only occurs ONCE for the data I group with it.
thanks again, please let me know.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-08-18 : 15:46:44

Perhaps looking at this the wrong way. What identifies 2 or more rows getting the same "grouper" value?

dennisgaudenzi
Starting Member

26 Posts

Posted - 2011-08-18 : 16:44:39

Hi Bruce. That is just how the data is. The Grouper number will be set outside of the insert of the data, in other words, the Grouper number will be passed in with the "data". This is here just to logically group those items together for another part of my application. Please let me know if you have any other questions - thanks! Dennis

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-08-18 : 17:08:44

Sounds like your best bet (certainly the simplest) then is a GUID which you can either let SQL generate or have your app generate it and pass it in with the data to be grouped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources