SQL Server 2012: Using Top with Percent in SQL Server

Posted June 14, 2013 by Vishwanath Dalvi in Database, SQL Server

The TOP clause in SQL Server is used to limit the number of rows in a result set. You can specify the number of rows or percentage of rows to be returned with the TOP clause.

Specifying the percentage of rows with the TOP clause is a newly introduced feature in SQL Server 2012.

You can download the SQL code for TOP with Percentage and try it out in your SQL Server machine.

We will demonstrate TOP with Percentage.

Create a table COMPANY, and populate it with ten rows.

IF OBJECT_ID('COMPANY') IS NOT NULL
DROP TABLE COMPANY
GO
 
CREATE TABLE COMPANY
(
ID          INT PRIMARY KEY,
NAME        VARCHAR(25),
LOCATION    VARCHAR(25)
)
GO
 
INSERT INTO COMPANY
VALUES (1,'HCL','London'),
       (2,'HP','Bangalore'),
       (3,'Microsoft','Bangalore'),
       (4,'Infosys','Pune'),
       (5,'Google','London'),
       (6,'GE', 'London'),
       (7,'AltiSource','New York'),
       (8,'Facebook','Palo alto'),
       (9,'IBM','New York'),
       (10,'TCS','Mumbai')
GO


Now, we have COMPANY table with ten rows.

-- Select 100 PERCENT ROWS
SELECT TOP (100) PERCENT * FROM COMPANY


TOP 100 Percent gives us all the rows from company table.

-- Select 50 PERCENT ROWS
SELECT TOP (50) PERCENT * FROM COMPANY


We have ten rows in company table, and 50 percent of ten rows is five. Therefore, we have five rows in the result.

-- Select 25 PERCENT ROWS
SELECT TOP (25) PERCENT * FROM COMPANY


There are ten rows in company table, and 25 percent of ten rows is a fractional value of 2.5. It is rounded to three. Therefore, the above query returns three rows.

-- Variable with TOP PERCENTAGE
Declare @Percentage AS int = 30
SELECT TOP (@Percentage) PERCENT * FROM COMPANY


There are ten rows in company table, and 30 percent of ten rows is three. Therefore, we have three rows in the result.

-- Select -100 PERCENT ROWS, using a Negative value which results in an Error.
SELECT TOP (-100) PERCENT * FROM COMPANY


This gives the error “Percent values must be between 0 and 100,” so negative values are not allowed with TOP with Percentage.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.