StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="http://www.google.com/search?q=mssql+2005+generate+random" rel="nofollow noreferrer">easy google</a>: <a href="http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx" rel="nofollow noreferrer">1</a> <a href="http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/" rel="nofollow noreferrer">2</a> ...</p> <hr> <blockquote> <p>Returning Random Numbers in a SELECT statement</p> </blockquote> <p>As it is implemented, <strong>the RAND() function in SQL Server doesn't let you return a different random number per row</strong> in your SELECT statement. For example, if you execute this:</p> <pre><code>SELECT Rand() as RandomNumber, * FROM Northwind..Customers </code></pre> <p>You will see the same number over and over. However, sometimes, you may want to return a randomly generated number per line in your SELECT. Here's one way to do that, in SQL Server 2000, using a UDF.</p> <p>First, we need to create a View that returns a single random number:</p> <pre><code>CREATE VIEW vRandNumber AS SELECT RAND() as RandNumber </code></pre> <p>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.</p> <p>Once that is set up, we can then create our function:</p> <pre><code>CREATE FUNCTION RandNumber() RETURNS float AS BEGIN RETURN (SELECT RandNumber FROM vRandNumber) END </code></pre> <p>Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row:</p> <pre><code>SELECT dbo.RandNumber(), * FROM Northwind..Customers </code></pre> <p>You can get even fancier in your RandNumber function by accepting a seed if you like, or allowing for range parameters like this:</p> <pre><code>CREATE FUNCTION RandNumber2(@Min int, @Max int) RETURNS float AS BEGIN RETURN @Min + (select RandNumber from RetRandNumber) * (@Max-@Min) END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload