Difference between New_Id() & Rand() SQLSERVER2005

Question

How newid() Function Work in Back Ground ?

What is the difference between rand() and newid() ?

Which is more efficient?

Thanks in advance.

Answer 1

"Which is more efficient?" For what?

What is the context? What are you trying to do?

RAND() returns a pseudo-random float value from 0 through 1, exclusive.

NEWID() creates a unique value of type uniqueidentifier.

Answer 2

NEWID() generates a GUID which is stored in a uniqueidentifier column type. RAND() is simply a pseudo-random number (int, smallint, or tinyint). It is really less about efficiency and more about what your needs are. RAND() is not guaranteed to be unique, NEWID() is.

Answer 3

newid() doesn't actually generate random numbers, it generates GUIDs, which have a random part to them, and serve their purpose well for random ordering.

In terms of which is more efficient, when ordering is a factor, its actually a bigger question of which is more random. By default, without some trickery with regards to the query, rand() will generate one random number at the start of execution of a query, and use that for each row. It will not generate a different random number for each row. In a simple query, doing a simple "ORDER BY rand()" will not randomly order the results at all.

So in this case, newid() is much better, even if not more efficient.

Answer 4

The problem with rand is that it returns the same value for all the rows for a call. newid will be different for every row. This article explains things clearly

http://articles.techrepublic.com.com/5100-10878_11-6089823.html#

Answer 5

According to SQL Server estimation plan sorting with NewId() is more time consuming than Rand(). I think rand() number generation is based on time factor and If you want to sort records of query with Rand() your records will get same rand number and result will be the same as previous. for example see the following

Select *,rand() as rnd from dbo.Products Order by rnd

All records will have same rnd value because random generation factor does not change during record selection.