How to generate Unique Auto Numbers through Query

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to generate Unique Auto Numbers through Query

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2007-04-12 : 04:10:28

Hello hope you all will be fine.
I want a query that will generate the unique auto numbers which is integer. I know a query Like Select NewID() but it produces uniqueidentifier number. But I want to generate integral numbers.
Also i want the query which runs on SQL server 2000 and 2005 both.
Thanks

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 04:16:19

IDENTITY columns will do that for you.

Peter Larsson
Helsingborg, Sweden

azharrahi
Starting Member

44 Posts

Posted - 2007-04-12 : 05:05:55

I know but I want to create it through query not in a table to use it on my forms and code. I want some auto-generated unique integers through query so that it return every time a unique integer number and hence i can use it on the forms.
thanx

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 05:15:51

[code]
select convert(int, convert(varbinary, newid()))
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 05:17:37

You can never guarantee that the number will be unique.

Try this

SELECT MAX(NewNumber)
FROM (
SELECT 1 AS NewNumber
UNION ALL
SELECT MAX(Col1) FROM Table
) AS g

It will get you next integer number. BUT BEWARE! If the last record is deleted, the same ID will be returned.

Or you can use
SELECT CHECKSUM(NEWID())

which gets you a large randomized number. However, this number can not be guaranteed to be unique.

Use IDENTITY columns.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 05:26:21

What is the scope of the unique number that you required ? Within the life span of a connection ? Perpetual ?

Is an integer provide you sufficient number of unique IDs ?

"I want a query that will generate the unique auto numbers which is integer"
The only way to ensure unique is as what Peter suggested, IDENTITY column in a table.

Create a table IDENTITY column with INTEGER data type. Insert a record and read the identity number back.

azharrahi
Starting Member

44 Posts

Posted - 2007-04-12 : 05:54:57

The scope is to the database . It means whether the database enignes or services stops or connection closed, when connected or started, it must produce a unique sequential number that it never produced before.

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 06:01:17

Yes, an IDENTITY column!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources