Hi All,
I have to insert a record in a tle where Receipt number requird auto generate.
How i can generate Random number in MS SQL server 2000 that can be updateble too.

Regards,
NAX111

Look up the RAND function. It returns a (pseudo) random floating point number with a value between 0 and 1. You can then scale this appropriately and CAST the result to whatever datatype is appropriate.

Jeff Mason
Custom Apps, Inc.
[email protected]

The problem with the RAND function is that it will return the same number throughout a given query unless you change the seed. I know it's stupid (wadaya expect from a bunch of new college grads that wrote the function?), but you need a random seed in order for RAND to give you a random number for each row (other programs do NOT work this way). For example...

 SELECT RAND()
FROM dbo.SysObjects
... returns the same number for each row. Not very useful. And, that little bit of fascination is documented in BOL...

Remarks
Repetitive invocations of RAND() in a single query will produce the same value.

...so, the only way to get random numbers is to change the seed with something that is never the same... and, a date/time is NOT it because it only changes once every 3.3 milliseconds... lot's of time to produce the same number for a number of rows. So, what to do? What IS the only seed that changes everytime it is used?

 SELECT RAND(CAST(NEWID() AS VARBINARY))
FROM dbo.SysObjects

Notice that we had to change the NEWID() to a VARBINARY or we get an error.

Ok, so we have a bunch of numbers that are >=0 and <1... what on Earth can we do with that? Well, let's just say you wanted all the whole numbers from 1000 to 9999... Notice that I'll refer to "1000" as the "RangeStart" and the "9999" as the "RangeEnd". To produce our range of random numbers, we need to do the following (works in ALL cases for whole numbers)...

DECLARE @RangeStart INT
DECLARE @RangeEnd INT
    SET @RangeStart = 1000
    SET @RangeEnd = 9999

SELECT CAST(RAND(CAST(NEWID() AS VARBINARY))*(@RangeEnd-@RangeStart+1)+@RangeStart AS INT)
FROM dbo.SysObjects

If you want random decimal numbers, just remove the outside CAST.

Here's the reason why this works... RAND returns a number >=0 and <1. For argument sake, let's say that's 0 to .999999999999 and let's use the simple example of random numbers from 1 to 10...

So, how many whole numbers are there? According to the formula I wrote, it's 10-1+1 or 10.
What's 10*0... correct... 0
What's 10*.999999999999... correct... 9.999999999999 or not quite 10
If we drop the decimal places on both numbers, we'll produce the random whole numbers for 0 to 9. Not quite what we wanted... Sooooo.... we need to add the low end of the range to both of these...
0+1 to 9+1 or 1 to 10.


Hope this helps... any questions?

--Jeff Moden

Oh yeah... almost forgot... INT does round down and UP. If you are a mathematical purest or you just want the most even distribution possible even for the first or last number, then you have to get just a little more elaborate (add FLOOR to the equation) but the same principle is working...

DECLARE @RangeStart INT
DECLARE @RangeEnd INT
    SET @RangeStart = 1000
    SET @RangeEnd = 9999

 SELECT CAST(FLOOR(RAND(CAST(NEWID() AS VARBINARY))*(@RangeEnd-@RangeStart+1)+@RangeStart) AS INT)
   FROM dbo.SysObjects

--Jeff Moden

Hi Jeff Moden,
But i want that Randome number with only 6 Bytes long and not as floating, pls. let me how this possible.

Reg,
NAX111

So, change the @RangeStart to 1 and the @RangeEnd to 999999 and you're done! And, if you read the equations, you'd notice that they are converted to INT. If you need something besides a 6 digit random number, post pack but, like I said, this is an easy to do... just change the values of the range ends.

--Jeff Moden

By the way... I think generating a random number for a receipt is a bad thing... what if the random number creates a dupe?

--Jeff Moden

For Best result try following
First, we need to create a View that returns a single random number:

Create view vw_GetRandomNumber
AS
    SELECT rand() as Num

The view is necessary because normally in a UDF we cannot use the rand() function, because that would make the function non-determistic. We can trick the UDF to accepting a random number by using a View.


CREATE FUNCTION fn_GetRandNumber(@Min int, @Max int)
RETURNS int
AS
 BEGIN
 DECLARE @Return int
    Set @Max = @Max+1
     set @Return = (@Min) + (select Num from vw_GetRandomNumber) * (@Max-@Min)
return @Return
 END

Now test with

select dbo.fn_GetRandNumber(1,1) as num

select dbo.fn_GetRandNumber(1,2) as num


select dbo.fn_GetRandNumber(1,15) as num

you will get the result

Cheers!!



Maulik Pandya

I found your posting very helpful, but upon generating the random numbers there are duplicates. Can someone tell me on how to eliminate duplication. See sample codes below, result count should be equal to number or rows in SysObjects table right? but upon running the script count changed which means that there are duplicate number generated.

hope someone can help me on this :D



SELECT count(distinct(CAST(RAND(CAST(NEWID() AS VARBINARY))*(@RangeEnd-@RangeStart+1)+@RangeStart AS INT)))
   FROM dbo.SysObjects

I warned you about this...

How do you detect dupes, anywhere... you have to dip the table and see if it exists. If it does, gen a different random number and try again.

This is such a bad idea... why does the verification number need to be random and why does it need to be six digits? Why can't you use a sequential confirmation number, instead?

--Jeff Moden