How To Find Random Record In SQL Server

In this blog. I will explain you how to find random record in SQL server. This is most common interview question. 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 row from a result set in SQL Server databases. NEWID is use to assign a value to a variable declared as the uniqueidentifier data type.

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.

CREATE TABLE [dbo].[Employee](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Position] [nvarchar](50) NULL,
    [Office] [nvarchar](50) NULL,
    [Salary] [money] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2

Insert Data Record.

Step 3

Open a New Query and write the following query.

First Execution

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

Output

Second Execution

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

Output

Third Execution

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

Output

Example with top 5 records.

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

Output

Conclusion

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