{"id":3404,"date":"2018-10-31T19:44:39","date_gmt":"2018-11-01T02:44:39","guid":{"rendered":"https:\/\/database.guide\/?p=3404"},"modified":"2019-11-19T22:57:08","modified_gmt":"2019-11-20T05:57:08","slug":"rand-examples-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/rand-examples-in-sql-server\/","title":{"rendered":"RAND() Examples in SQL Server"},"content":{"rendered":"

In\u00a0SQL Server<\/a>, the T-SQL<\/a> RAND()<\/code> function allows you to generate a random number. Specifically, it returns a pseudo-random float value from 0 through 1, exclusive.<\/p>\n

The function accepts an optional argument which provides the seed value. For any given seed value, the results will always be the same.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

RAND ( [ seed ] ) \r\n<\/pre>\n
Where seed<\/var> is an integer expression (tinyint, smallint, or int) that gives the seed value. If this argument isn’t provided, then SQL Server assigns a seed at random. <\/p>\n
Example 1 – No Arguments<\/h2>\n
Here’s a basic example to demonstrate what happens when we don’t provide an argument.<\/p>\n
SELECT RAND() Result;\r\n<\/pre>\n
Result:<\/p>\n
+--------------------+\r\n| Result             |\r\n|--------------------|\r\n| 0.0354675287734768 |\r\n+--------------------+\r\n<\/pre>\n
The result is not a constant – it will be different each time you run it.<\/p>\n
Here’s an example of what happens when we run multiple RAND()<\/code> functions together.<\/p>\n
SELECT \r\n  RAND() 'Result 1',\r\n  RAND() 'Result 2',\r\n  RAND() 'Result 3';\r\n<\/pre>\n
Result:<\/p>\n
+-------------------+------------------+--------------------+\r\n| Result 1          | Result 2         | Result 3           |\r\n|-------------------+------------------+--------------------|\r\n| 0.999568268427369 | 0.40098746841349 | 0.0606836711764244 |\r\n+-------------------+------------------+--------------------+\r\n<\/pre>\n
Example 2 – Using a Seed Value<\/h2>\n
As mentioned, you can pass in an argument to determine the seed value. This allows you to influence the output of the function.<\/p>\n
SELECT RAND(8) Result;\r\n<\/pre>\n
Result:<\/p>\n
+-------------------+\r\n| Result            |\r\n|-------------------|\r\n| 0.713722424011731 |\r\n+-------------------+\r\n<\/pre>\n
In this case, the function will return the same value each time if an equal argument value is provided. This is demonstrated in the following example:<\/p>\n
SELECT \r\n  RAND(8) 'Result 1',\r\n  RAND(8) 'Result 2',\r\n  RAND(8) 'Result 3';\r\n<\/pre>\n
Result:<\/p>\n
+-------------------+-------------------+-------------------+\r\n| Result 1          | Result 2          | Result 3          |\r\n|-------------------+-------------------+-------------------|\r\n| 0.713722424011731 | 0.713722424011731 | 0.713722424011731 |\r\n+-------------------+-------------------+-------------------+\r\n<\/pre>\n
We ran the function three times. And because we used the same seed each time, the results were all the same.<\/p>\n
Example 3 – Using RAND() as Part of an Expression<\/h2>\n
You can use the RAND()<\/code> function as part of an expression.<\/p>\n
SELECT RAND()*10 Result;\r\n<\/pre>\n
Result:<\/p>\n
+------------------+\r\n| Result           |\r\n|------------------|\r\n| 3.32720913214171 |\r\n+------------------+\r\n<\/pre>\n
Example 4 – Rounding the Result<\/h2>\n
We can also remove the fractional part by nesting the function inside a function such as FLOOR()<\/code><\/a> or CEILING()<\/code><\/a>.<\/p>\n
SELECT CEILING(RAND()*10) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 3        |\r\n+----------+\r\n<\/pre>\n
Example 5 – Returning a Random Integer Between Two Numbers<\/h2>\n
You can also specify that the random number must be between two numbers.<\/p>\n
Here’s an example of generating a random number between 5 and 10 (inclusive).<\/p>\n
SELECT FLOOR(RAND()*(10-5+1)+5) Result;\r\n<\/pre>\n
Result:<\/p>\n
+----------+\r\n| Result   |\r\n|----------|\r\n| 9        |\r\n+----------+\r\n<\/pre>\n
Let’s run the same code multiple times to see the varying results.<\/p>\n
SELECT \r\n  FLOOR(RAND()*(10-5+1)+5) 'Result 1',\r\n  FLOOR(RAND()*(10-5+1)+5) 'Result 2',\r\n  FLOOR(RAND()*(10-5+1)+5) 'Result 3';\r\n<\/pre>\n
Result:<\/p>\n
+------------+------------+------------+\r\n| Result 1   | Result 2   | Result 3   |\r\n|------------+------------+------------|\r\n| 10         | 7          | 6          |\r\n+------------+------------+------------+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
In\u00a0SQL Server, the T-SQL RAND() function allows you to generate a random number. Specifically, it returns a pseudo-random float value from 0 through 1, exclusive. The function accepts an optional argument which provides the seed value. For any given seed value, the results will always be the same.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,70,77,61],"class_list":["post-3404","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mathematical-functions","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/3404","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=3404"}],"version-history":[{"count":1,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/3404\/revisions"}],"predecessor-version":[{"id":3406,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/3404\/revisions\/3406"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=3404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=3404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=3404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}