MS SQL Server 21 - Retrieve 50% of records from a SQL Server table

To retrieve 50% of records from a SQL Server table, you can use the TABLESAMPLE clause or a combination of ROW_NUMBER() and TOP. Below are two methods to achieve this:

Using ROW_NUMBER() and TOP

This method involves ordering the records and then selecting the top 50% based on the total row count. This method provides more control and ensures you get exactly 50% of the records.

Here's the complete SQL script to create the database, create the Users table, insert the sample data, and select all records:

create database MyLearningDB

use MyLearningDB

create table Users
(
	id int primary key identity(1,1),
	fullname varchar(100),
	emailid  varchar(200),
	status   varchar(10)
)

insert into Users values
('user1','user1@email.com','active'),
('user2','user2@email.com','active'),
('user3','user3@email.com','active'),
('user4','user4@email.com','in-active'),
('user5','user5@email.com','active'),
('user6','user6@email.com','in-active'),
('user7','user7@email.com','in-active'),
('user8','user8@email.com','active')

select * from Users

Running this script will create a database named MyLearningDB, create a table named Users within that database, insert 8 sample records, and then display all the records in the Users table.

Here is the logic to retrieve the 50% of records.

WITH RankedRecords AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
           COUNT(*) OVER () AS TotalRows
    FROM Users
)
SELECT *
FROM RankedRecords
WHERE RowNum <= TotalRows / 2;

Output:

Explanation:

1. ROW_NUMBER(): Assigns a unique sequential integer to rows within the result set.
2. COUNT(*) OVER (): Calculates the total number of rows in the table.
3. RankedRecords CTE: Creates a Common Table Expression (CTE) that includes the row number and total row count.
4. Final SELECT: Filters the rows to include only the first 50% based on the row number.

Notes:

• Ensure that the ordering within ROW_NUMBER() is appropriate for your use case. The ORDER BY (SELECT NULL) syntax generates a random ordering, but you may want to order by a specific column instead.
• These queries should be tested and optimized according to your specific database schema and requirements.