Often people struggle to get a RANDOM record from a table in SQL Server. There is a function in SQL Server RAND() which generates Random Numbers. However this function does not work as expected while selecting a RANDOM row from a table in SQL Server.

To address this issue, there is a workaround to select a RANDOM row from a table in SQL Server. Let us see how we can achieve this using the following demonstration.

Create a Temp Table using the following query:

CREATE TABLE #TempTable (
  IntValue INT NOT NULL,
  StrValue NVARCHAR(20) NOT NULL)
GO

Now insert some sample data into the Temp Table using the following query:

INSERT INTO #TempTable (IntValue,StrValue)
SELECT IntValue, StrValue
FROM (SELECT 1 AS IntValue, 'String Value 1' AS StrValue
      UNION ALL
      SELECT 2 AS IntValue, 'String Value 2' AS StrValue
      UNION ALL
      SELECT 3 AS IntValue, 'String Value 3' AS StrValue
      UNION ALL
      SELECT 4 AS IntValue, 'String Value 4' AS StrValue
      UNION ALL
      SELECT 5 AS IntValue, 'String Value 5' AS StrValue
      UNION ALL
      SELECT 6 AS IntValue, 'String Value 6' AS StrValue
      UNION ALL
      SELECT 7 AS IntValue, 'String Value 7' AS StrValue
      UNION ALL
      SELECT 8 AS IntValue, 'String Value 8' AS StrValue
      UNION ALL
      SELECT 9 AS IntValue, 'String Value 9' AS StrValue
      UNION ALL
      SELECT 10 AS IntValue, 'String Value 10' AS StrValue) StaticData
GO

Now run the following queries to see how 2 RANDOM rows are selected from Temp Table every time you run these queries.

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

Here is the output of the above queries. Run these queries a couple times to see the difference in the number of selected rows every time the above queries are run.

Find this post useful ? Please do let me know by leaving a comment below :-)