Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Difference between RAND() & NEWID()

3 posts in General Discussion Last posting was on 2012-12-26 15:58:22.0Z
Karthik M Posted on 2012-12-26 06:35:28.0Z
Sender: dc5.50da9a5c.1804289383@sybase.com
From: Karthik M
Newsgroups: sybase.public.ase.general
Subject: Difference between RAND() & NEWID()
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50da9ab0.df0.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 25 Dec 2012 22:35:28 -0800
X-Trace: forums-1-dub 1356503728 172.20.134.41 (25 Dec 2012 22:35:28 -0800)
X-Original-Trace: 25 Dec 2012 22:35:28 -0800, 172.20.134.41
Lines: 5
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31606
Article PK: 1158607

whata is the Difference between RAND() & NEWID()?

select * from sub_fund order by rand()

select * from sub_fund order by newid()


Rob V Posted on 2012-12-26 11:08:04.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Difference between RAND() & NEWID()
References: <50da9ab0.df0.1681692777@sybase.com>
In-Reply-To: <50da9ab0.df0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50dada94$1@forums-1-dub>
Date: 26 Dec 2012 03:08:04 -0800
X-Trace: forums-1-dub 1356520084 172.20.134.152 (26 Dec 2012 03:08:04 -0800)
X-Original-Trace: 26 Dec 2012 03:08:04 -0800, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31609
Article PK: 1158612


On 26-Dec-2012 07:35, Karthik M wrote:
> whata is the Difference between RAND() & NEWID()?
>
> select * from sub_fund order by rand()
>
> select * from sub_fund order by newid()
>

rand() generates a random number.
Newid generates a GUID/UUID (which is not a number but a string).

There is also rand2() BTW, which is similar to rand() but behaves
differently in queries with multiple rows in the result set.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2012-12-26 15:58:22.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Difference between RAND() & NEWID()
References: <50da9ab0.df0.1681692777@sybase.com> <50dada94$1@forums-1-dub>
In-Reply-To: <50dada94$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50db1e9e$1@forums-1-dub>
Date: 26 Dec 2012 07:58:22 -0800
X-Trace: forums-1-dub 1356537502 172.20.134.152 (26 Dec 2012 07:58:22 -0800)
X-Original-Trace: 26 Dec 2012 07:58:22 -0800, vip152.sybase.com
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31611
Article PK: 1158616


On 12/26/2012 4:08 AM, Rob V wrote:
> On 26-Dec-2012 07:35, Karthik M wrote:
>> whata is the Difference between RAND() & NEWID()?
>>
>> select * from sub_fund order by rand()
>>
>> select * from sub_fund order by newid()
>>
> rand() generates a random number.
> Newid generates a GUID/UUID (which is not a number but a string).
>
> There is also rand2() BTW, which is similar to rand() but behaves
> differently in queries with multiple rows in the result set.
>

More specifically:

rand() is evaluated once per statement, so will give the same
result for every row. rand2() is evaluated for each row and
so usually generates different values for each row. Both rand()(
and rand2() can be passed a seed value parameter to reset the sequence
of values they generate. newid() is evaluated for each row and
generates a unique value each time it is called.


-- rand2() evaluated for each row:
1> select top 3 rand(100), rand2(100) from sysusers
2> go

--------------------------- ---------------------------
0.595760 0.670970
0.595760 0.658766
0.595760 0.519489

(3 rows affected)
1> select top 3 rand(), rand2() from sysusers
2> go

--------------------------- ---------------------------
0.822619 0.309008
0.822619 0.897292
0.822619 0.803882

(3 rows affected)

Reusing a seed value repeats the sequence of generated values
(same values as above).
1> select top 3 rand(100), rand2(100) from sysusers
2> go

--------------------------- ---------------------------
0.595760 0.670970
0.595760 0.658766
0.595760 0.519489

(3 rows affected)
1> select top 3 rand(), rand2() from sysusers
2> go

--------------------------- ---------------------------
0.822619 0.309008
0.822619 0.897292
0.822619 0.803882

(3 rows affected)


--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021