Auto Number

Hi,
I want to create a random unique auto generated number. dont want
to use GUIDs. Any other way to create it in isert statement itself,
apart from storing a number in a table and increment while inserting.
Help me!

On 3 Apr 2006 13:48:16 -0700, Ramaarneh wrote:

>Hi,
>    I want to create a random unique auto generated number. dont want
>to use GUIDs. Any other way to create it in isert statement itself,
>apart from storing a number in a table and increment while inserting.
>Help me!

Hi Ramaarneh,

Strange requirements. Why not a GUID? Why not store it in a table?

But if you must, then you might consider using the RAND() function. Put
it in a loop and iterate until an unused value is found. (Make sure that
the range of numbers that the RAND() can generate is at least 1000 times
bigger than the expected number of rows, to make sure that the loop will
iterate just once in most cases - otherwise, it'll be a performance
hog).

--
Hugo Kornelis, SQL Server MVP

Ramaarneh  wrote:
> Hi,
>     I want to create a random unique auto generated number. dont want
> to use GUIDs. Any other way to create it in isert statement itself,
> apart from storing a number in a table and increment while inserting.
> Help me!

Let's assume you can create a table of numbers from 0 upto the largest
number you need.

CREATE TABLE numbers (num INTEGER NOT NULL PRIMARY KEY);

INSERT INTO numbers VALUES (0)
WHILE (SELECT MAX(num) FROM numbers)<32768
INSERT INTO numbers
SELECT num+(SELECT MAX(num)+1 FROM numbers)
FROM numbers ;

Now you can easily populate another table with random selections from
your Numbers table:

CREATE TABLE your_tbl (num INTEGER NOT NULL PRIMARY KEY);

INSERT INTO your_tbl (num)
SELECT TOP 1 N.num
FROM numbers AS N
LEFT JOIN your_tbl AS T
ON N.num = T.num
WHERE T.num IS NULL
ORDER BY NEWID() ;

The beauty of this is that you can periodically delete the used ones
from Numbers and you can repopulate it as often as you like. True, it
probably won't scale well to millions of rows but it has worked pretty
well for me with smaller data sets.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Here is an implementation of the additive congruential method of
generating values in pseudo-random order and is due to Roy Hann of
Rational Commerce Limited, a CA-Ingres consulting firm.  It is based on
a shift-register and an XOR-gate, and it has its origins in
cryptography.  While there are other ways to do this, this code is nice
because:

1) The algorithm can be written in C or another low level language for
speed.  But math is fairly simple even in base ten.

2) The algorithm tends to generate successive values that are (usually)
"far apart", which is handy for improving the performance of tree
indexes.  You will tend to put data on separate physical data pages in
storage.

3) The algorithm does not cycle until it has generated every possible
value, so we don't have to worry about duplicates.  Just count how many
calls have been made to the generator.

4) The algorithm produces uniformly distributed values, which is a nice
mathematical property to have.  It also does not include zero.

Generalizing the algorithm to arbitrary binary word sizes, and
therefore longer number sequences, is not as easy as you might think.
Finding the "tap" positions where bits are extracted for feedback
varies according to the word-size in an extremely non-obvious way.
Choosing incorrect tap positions results in an incomplete and usually
very short cycle, which is unusable.  If you want the details and tap
positions for words of one to 100 bits, see E. J. Watson, "Primitive
Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962,
p.368-369.

We need to tap bits 0 and 3 to construct the 31-bit random-order
generated value Generator (which is the one most people would want to
use in practice):

UPDATE Generator31
SET keyval =
keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)* 8;

Or if you prefer, the algorithm in C:

int Generator31 ()
{static int n = 1;
n = n >> 1 | ((n^n >> 3) & 1) << 30;
return n;
}

Ramaarneh (ramkumar.saranathan@gmail.com)  writes:
>     I want to create a random unique auto generated number. dont want
> to use GUIDs. Any other way to create it in isert statement itself,
> apart from storing a number in a table and increment while inserting.

In addition to the other suggestions is to use checksum(newid()). However,
beware that this will occassionally give you duplicates, so you must be
able to handle this some way.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx