SQL Server 2012: Using Top with Percent in 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.
Follow the reactions below and share your own thoughts.