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')
GONow, we have COMPANY table with ten rows.
-- Select 100 PERCENT ROWS
SELECT TOP (100) PERCENT * FROM COMPANYTOP 100 Percent gives us all the rows from company table.
-- Select 50 PERCENT ROWS
SELECT TOP (50) PERCENT * FROM COMPANYWe 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 COMPANYThere 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 COMPANYThere 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 COMPANYThis gives the error “Percent values must be between 0 and 100,” so negative values are not allowed with TOP with Percentage.
