2.4. The ROWCOUNT Function

In an earlier section, we discussed how to limit the number of rows that are returned by a SELECT statement with the use of a WHERE clause and logical operators. In this section, we introduce the ROWCOUNT function, another way of limiting the number of rows that can be the returned by a SELECT statement.

The WHERE clause assumes that you have knowledge of the actual data values present in a data set. But what if you want to see only a sample of a result set, and you have no idea which range of values are present in the table? In this case, the ROWCOUNT function can come in handy.

For example, to see the first 10 rows of the Dependent table, you can type:

SET ROWCOUNT 10
SELECT *
FROM   Dependent

This query returns the following 10 rows of output:

PNO    DNAME                RELATIONSHIP SEX  AGE
------ -------------------- ------------ ---- ------
2      Matt                 Son          M    8
2      Mary                 Daughter     F    9
2      Beena                Spouse       F    31
10     Amit                 Son          M    3
10     Shantu               Daughter     F    5
14     Raju                 Son          M    1
14     Rani                              F    3
17     Susan                Daughter     F    4
17     Sam                  Son          M    1
20     Donald II            Son          M    NULL
 
(10 row(s) affected)

After using ROWCOUNT, you should reset the ROWCOUNT property by:

SET ROWCOUNT 0

If you do not reset the ROWCOUNT property, you will keep getting whatever you set your ROWCOUNT to for the remainder of this session (that is, until you log off).

If you set ROWCOUNT and issue multiple queries in the same batch, the rows are limited for all queries within the batch.

Other important functions are discussed in Chapter 5.