Different Methods To Limiting Result Set In SQL Server

Generally, a developer face the scenario many times ,where he required to limit the data set that is retrieving from the query. There are many methods through which he can limit the result set. Today, I will explain different methods that will help us to limit the result set. Let’s start today’s session.

Firstly, we create a table

CREATE TABLE[dbo].[Student_Information]
(
[College_Id][nvarchar](10) NOT NULL, [Student_Name][nvarchar](max) NOT NULL, [Father_Name][nchar](10) NOT NULL, [Address][nvarchar](max) NOT NULL, [Branch_Id][int] NOT NULL
)
GO
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210038', N 'Pankaj', N 'Suresh ', N 'Alwar', 1)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210039', N 'Priya', N 'Ram ', N 'Mathura', 1)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1310048', N 'Rahul', N 'Sambhu ', N 'Alwar', 3)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1310078', N 'Neeraj', N 'Nathu ', N 'Jaipur', 5)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210047', N 'Sandeep', N 'Om Parkash', N 'Alwar', 1)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210048', N 'Sanjeev', N 'Omi ', N 'Delhi', 2)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210040', N 'Sonu', N 'Suresh ', N 'Alwar', 1)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1310041', N 'Monu', N 'Sambhu ', N 'Alwar', 3)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210042', N 'Divyanshu', N 'Om Parkash', N 'Alwar', 1)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210043', N 'Saniya', N 'Ram ', N 'Mathura', 1)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1310044', N 'Bupesh', N 'Nathu ', N 'Jaipur', 5)
INSERT[dbo].[Student_Information]([College_Id], [Student_Name], [Father_Name], [Address], [Branch_Id]) VALUES(N '1210045', N 'Manjeet', N 'Omi ', N 'Delhi', 2)
SELECT * FROM dbo.Student_Information AS STU

Output

Select Top Statement

The SQL SELECT TOP statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a fixed value or percentage. When TOP statement is used with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order.

Example 1: (Top using Numeric Value)

Example 2: (Top using Percent value)

Example 3: (Top WITH TIES Option)

WITH TIES specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. Used WITH TIES when you want to return two or more rows that tie for last place in the limited results set.

Simple Without Ties

TOP WITH TIES

Limit Result Set Using ROW_NUMBER() WITH CTE

We can limit the result set by using the ROW_NUMBER() function with CTE (Common Table Expression). ROW_NUMBER() is a rank function, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example 4

WITH CTE AS
(
SELECT * , ROW_NUMBER() OVER(ORDER BY STU.Student_Name) AS 'Rank'
FROM dbo.Student_Information AS STU
)
SELECT * FROM CTE
WHERE CTE.Rank BETWEEN 4 AND 7;

Output

Limit Result Set Using TABLESAMPLE

Using TABLESAMPLE you can extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. The way this works is that data is read at a page level. Each 8K page for the table is given a random value and based on this random value and the value you specify either number of rows or percentage will determine how many rows are returned.

Syntax

SELECT * FROM TABLE_NAME TABLESAMPLE(10 PERCENT)
OR
SELECT * FROM TABLE_NAME TABLESAMPLE(100 ROWS)

Suppose Student_Information Table contain 1,20,000 records. Now we try to execute the COUNT() method with TABLESAMPLE option.

Example 5

SELECT count( * ) AS Count_, '1'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT) UNION ALL
SELECT count( * ) AS Count_, '2'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT) UNION ALL
SELECT count( * ) AS Count_, '3'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT) UNION ALL
SELECT count( * ) AS Count_, '4'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT) UNION ALL
SELECT count( * ) AS Count_, '5'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT) UNION ALL
SELECT count( * ) AS Count_, '6'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT) UNION ALL
SELECT count( * ) AS Count_, '7'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10 PERCENT)

Output

Example 6

SELECT count( * ) AS Count_, '1'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS) UNION ALL
SELECT count( * ) AS Count_, '2'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS) UNION ALL
SELECT count( * ) AS Count_, '3'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS) UNION ALL
SELECT count( * ) AS Count_, '4'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS) UNION ALL
SELECT count( * ) AS Count_, '5'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS) UNION ALL
SELECT count( * ) AS Count_, '6'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS) UNION ALL
SELECT count( * ) AS Count_, '7'
AS ATTEMP FROM dbo.Student_Information AS STU TABLESAMPLE SYSTEM(10000 ROWS)

Output

How many times we execute the above query we get the different numbers of rows every time . what is reason that for each identical query we are getting the different result ?. The reason behind this is that way in which data is read at a page level. Each 8K page for the table is given a random value and on the bases of this random value and the value that we specified in query either as number of rows or as percentage will determine how many rows are returned. Here 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.

In second example we defined the 10000 ROWS in TABLESAMPLE . Here doesn’t mean that 10000 rows are returned from pages. It means that rows number are converted into a percent prior to execution based on the number we specified and the approximate number of rows in the table.

Example 7: (TABLESAMPLE With REPEATABLE )

REPEATBLE force the TABLESAMPLE option to return the same result set. If REPEATBLE is specified with the same repeat_seed value, SQL Server returns the same subset of rows till either the changes have been made to the table or repeat_seed changed.

Example 8

Conclusion

If you want to limit the numbers of rows then first two methods (Top and ROW_NUMBER) are best. If you want to limit the amount of the data instead of rows then TABLESAMPLE is best way to limit the amount of data.

13 Comments

Join the conversation! Your thoughts help the community grow.

sreenivasa k10y ago
really good
Pankaj Kumar Choudhary10y ago
Thanks Raja T...........
Pankaj Kumar Choudhary10y ago
Thanks Ankit Bansal Sir.........
Pankaj Kumar Choudhary10y ago
Thanks Shubham Kumar Sir.......
Pankaj Kumar Choudhary10y ago
Thanks Debasis Saha Sir........
Pankaj Kumar Choudhary10y ago
Thanks Yashwant Vishwakarma Sir.......
Pankaj Kumar Choudhary10y ago
Thanks Aqib Shehzad........
Raja T10y ago
Nice, Thanks for sharing
Ankit Bansal10y ago
Nice share..thanks..
Shubham Kumar10y ago
nice info :)
Debasis Saha10y ago
Good one..
Yashwant Vishwakarma10y ago
Awesome dude, thanks for sharing !!
Muhammad Aqib Shehzad10y ago
very good info, thanks for sharing