Tag Archives: SQL Random

by

SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader

Just a day ago I received email from reader how to get single random number for range of rows of any table. The question was not very clear to me so I had asked him to send me question in simpler words. He sent me question back in simple words. Let us understand this problem using database AdventureWorks.

In AdventureWorks database we have table called Person.Address. How to get single random number generated for PostalCode ’98011′ and another single random number for PostalCode ’98033′. So far I have never received scenario like this. I had previously faced situation where I had to genrate new random number for each row of table.

Let us understand two scenarios here.

Different Random Number for Different Rows (Using NEWID())

USE AdventureWorks
GO
SELECT TOP 5 NEWID() AS GUID, *
FROM Person.Address
GO

Single Random Number for Set of Rows (Using RAND())

USE AdventureWorks
GO
SELECT TOP 5 RAND() AS RandomNumber, *
FROM Person.Address
WHERE PostalCode = '98011'
GO
SELECT TOP 5 RAND() AS RandomNumber, *
FROM Person.Address
WHERE PostalCode = '98033'
GO

Watch a 60 second video on this subject

I have sent email back to my reader asking his need for single random number for set of rows in real world example but I have not received his reply. I want to bring this to my readers and ask them what do you think can be usage of this concept in real life application.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads
by

SQLAuthority News – Random Article from SQLAuthority Blog

It has been wonderful writing on this blog. Many times I visit my older articles and read them. One of my favorite feature on WordPress.com (where I host my blog) is Random Article Feature. I use it quite often to land on random page on my blog. It is really good to read articles written previously because there are so many new things to learn as well keep previously learned knowledge refreshed.

I have added link to random article in the side bar of this blog. User can click on it to visit random article as well click in the link at the end of this article to view Random Article of SQLAuthority.com

View SQLAuthority.com Random Article

Reference : Pinal Dave (http://blog.SQLAuthority.com)

by

SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

Introduced in SQL Server 2005, TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

Example 1:
SELECT FirstName,LastName
FROM Person.Contact
TABLESAMPLE SYSTEM (10 PERCENT)
Example 2:
SELECT FirstName,LastName
FROM Person.Contact
TABLESAMPLE SYSTEM (1000 ROWS)

If you run above script many times you will notice that different numbers of rows are returned everytime. Without any data changing, re-running the identical query keeps giving different results. This is non -deterministic factor of TABLESAMEPLE clause. If table is static and rows are not changed what could be the reason to return different numbers of the rows to return in each execution. The factor is 10 PERCENT is not the percentages of the table rows or tables records, it is percentages of the table’s data pages. Once the sample pages of data selected, all the rows from the selected pages are returned, it will not limit the number of rows sampled from that page. Fill factor of all the pages varies depends on the data of the table. This makes script to return different row count in result set everytime it is executed. The REPEATABLE option causes a selected sample to be returned again. When REPEATABLE is specified with the same repeat_seed value, SQL Server returns the same subset of rows, as long as no changes have been made to the table. When REPEATABLE is specified with a different repeat_seed value, SQL Server will typically return a different sample of the rows in the table. .

Following example is from BOL:
The following example returns the same set of rows every time that it is executed. The seed value of 205 was chosen arbitrarily.

USE AdventureWorks
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT)
REPEATABLE (205) ;

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

by

SQL SERVER – Random Number Generator Script – SQL Query

Random Number Generator

There are many methods to generate random number in SQL Server.

Method 1 : Generate Random Numbers (Int) between Rang
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

Method 2 : Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

Method 3 : Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())

Method 4 : Random Numbers (Float, Int) Tables Based with Time
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum

Method 5 : Random number on a per row basis
---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber

Watch a 60 second video on this subject

Reference : Pinal Dave (http://blog.SQLAuthority.com), Simon Sabin (http://sqlblogcasts.com)