Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 13th, 2006, 10:18 AM
Registered User
 
Join Date: Nov 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default generate Random number in MS SQL server 2000


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

 
Old November 13th, 2006, 10:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
je.mason@comcast.net
 
Old November 13th, 2006, 08:00 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 13th, 2006, 08:09 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 14th, 2006, 05:05 AM
Registered User
 
Join Date: Nov 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old November 14th, 2006, 10:36 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old November 14th, 2006, 10:37 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old December 20th, 2006, 07:18 AM
Registered User
 
Join Date: Nov 2004
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to maulik77 Send a message via Yahoo to maulik77
Default

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
 
Old March 21st, 2007, 04:49 AM
Registered User
 
Join Date: Mar 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gibski
Default

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
 
Old March 29th, 2007, 10:14 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
Import Data In MS SQL SERVER 2000 The Beginner Classic ASP Basics 1 August 14th, 2006 04:59 PM
MS SQL Server 2000 SP3 out-of-the-box ? Sebastiaan SQL Server 2000 1 February 19th, 2006 06:18 PM
generate XML from SQL server 2000 table sasidhar79 XML 2 August 3rd, 2005 11:05 AM
VBScript - Sql Server 2000 - MS Word delight_ravi VBScript 0 December 24th, 2004 03:52 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.