Introduction

In this blog, I will explain how to find random records in SQL Server. This is one of the most common interview questions. The CHECKSUM function returns the checksum value computed over a table row, or over an expression list. Use CHECKSUM to build hash indexes. A hash index will result if the CHECKSUM function has column arguments, and an index is built over the computed CHECKSUM value. This can be used for equality searches over the columns.

SQL NEWID function is used for selecting random rows from a result set in SQL Server databases. NEWID is used to assign a value to a variable declared as the uniqueidentifier data type.

Syntax 1

Select column_name from table_name order by CHECKSUM (NEWID)

Syntax 2

Select top N column_name from table_name order by CHECKSUM (NEWID)

N represents the value you want select

Step1

Create a table in SQL Server 2014.
  1. CREATE TABLE [dbo].[Employee](
  2. [ID] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [nvarchar](50) NULL,
  4. [Position] [nvarchar](50) NULL,
  5. [Office] [nvarchar](50) NULL,
  6. [Salary] [money] NULL,
  7. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  8. (
  9. [ID] ASC
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  11. ) ON [PRIMARY]
  13. GO

Step 2

Insert Data Record.

Step 3

Open a New Query and write the following query.

First Execution

  1. select Name,Position from Employee
  2. order by CHECKSUM(NEWID())

Output

Find Random Record In SQL Server

Second Execution

  1. select Name,Position from Employee
  2. order by CHECKSUM(NEWID())

Output

Find Random Record In SQL Server

Third Execution

  1. select Name,Position from Employee
  2. order by CHECKSUM(NEWID())

Output

Find Random Record In SQL Server

Example with top 5 records.

  1. select top 5 Name,Position from Employee
  2. order by CHECKSUM(NEWID())

Output

Find Random Record In SQL Server

Conclusion

I have explained checksum and newid in this blog. Using both, we have found random values from the database table.