To SELECT only top N rows from a table we can use TOP clause in SELECT statement. Using TOP clause we can also specify percentage option.

For example, both of these statements are valid:

USE SqlAndMe

GO

SELECT TOP 5 EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

SELECT TOP 5 PERCENT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

(5 row(s) affected)

EmployeeId    FirstName     LastName

1             Ken           Sánchez

(1 row(s) affected)

Another way to limit rows in result set is to use SET ROWCOUNT N statement. SET ROWCOUNT N stops processing the query after specified number of rows are returned.

It can be used as below:

USE SqlAndMe

GO

SET ROWCOUNT 5

GO

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

SET ROWCOUNT 1

GO

SELECT EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO

EmployeeId    FirstName     LastName

1             Ken           Sánchez

2             Terri         Duffy

3             Gail          Erickson

4             Ken           Sánchez

5             Terri         Duffy

(5 row(s) affected)

EmployeeId    FirstName     LastName

1             Ken           Sánchez

(1 row(s) affected)

Using SET ROWCOUNT N affects current session, to turn off this setting we need to set it to 0.

SET ROWCOUNT 0

GO

There are some differences between using SET ROWCOUNT N and TOP N [PERCENT]. You can read about these differences here: SET ROWCOUNT VS TOP in SQL Server

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com

Follow me on Twitter -> @SqlAndMe

Filed under: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data