Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Using SET ROWCOUNT to Limit the Number of Rows Returned

This article describes a straightforward method of limiting the number of rows returned in a SQL Server results set by making use of the SET ROWCOUNT statement.

This technique has a number of potential uses, such as displaying the 10 most recent news article headlines on a website.

Although this functionality can also obviously be provided by using SELECT TOP..., setting the rowcount is a useful technique to use when SELECT TOP cannot be used, such as in this example of generating randomly ordered SQL Server result sets, which uses SELECT TOP to select a number of adverts, then limits the rowcount to return a specified number of randomly sorted adverts.

The use of the SET ROWCOUNT statement is shown in the following example for the pubs database. The SQL statements return the first 2 authors who live in the state of California:

DECLARE @NumberOfAuthors int
SET @NumberOfAuthors = 2

SET ROWCOUNT @NumberOfAuthors

SELECT * FROM AUTHORS
WHERE state = 'CA'

The number of rows to return can also be specified as a stored procedure argument:

CREATE PROCEDURE sp_GetAuthors
(
    @NumberOfAuthors int
)

AS

SET ROWCOUNT @NumberOfAuthors

SELECT * FROM AUTHORS
WHERE state = 'CA'

Note that setting ROWCOUNT to 0 will cause all of the rows to be returned.

Useful Links

  • The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012