SET ROWCOUNT n in Font

Encoding PDF417 in Font SET ROWCOUNT n

SET ROWCOUNT n is a totally separate command from the SELECT statement and can in fact be used with other statements within T-SQL. What this command will do is limit or reset the number of records that will be processed for the session that the command is executed in.

Caution should be exercised if you have any statements that also use a TOP command, described in a moment.

The SET ROWCOUNT n function stops the processing of the SELECT command once the number of rows defined has been reached. The difference between SET ROWCOUNT and SELECT TOP n is that the latter will perform one more internal instruction to that of the former. Processing halts immediately when the number of records processed through SET ROWCOUNT is reached. However, by using the TOP command, all the rows are returned internally, the TOP n rows are selected from that group internally, and these are then passed for display. Returning a limited number of records is useful when you want to look at a handful of data to see what values could be included, or perhaps you wish to return a few rows for sampling the data.

You can set the number of rows to be affected by altering the number, n, at the end of the SET ROWCOUNT function. This setting will remain in force only within the query window in which the command is executed, or within the stored procedure in which the command is executed. To reset the session so that all rows are taken into consideration, you would set the ROWCOUNT number to 0.

1. In Query Editor, enter the following code into a new Query Editor pane; once entered, execute it: SET ROWCOUNT 3 SELECT * FROM ShareDetails.Shares SET ROWCOUNT 0 SELECT * FROM ShareDetails.Shares 2. You should see two result sets, as shown in Figure 8-26. The first will return three rows from the ShareDetails. Shares table. The second result set will return all rows from ShareDetails.Shares.

This option, found within the SELECT statement itself, will return a specific number of rows from the SELECT statement, and is very much like the SET ROWCOUNT function for that reason. In fact, the TOP n option is the preferred option to use when returning a set number of rows, as opposed to the SET ROWCOUNT function. The reason behind this is that TOP n only applies to that query command; however, by using SET ROWCOUNT n, you are altering all commands until you reset SQL Server to act on all rows through SET ROWCOUNT 0.

Caution Although it is possible to use TOP n without any ORDER BY statement, it is usual to combine TOP with ORDER BY. When no order is specified, the rows returned are arbitrary, and if you want consistent results, then ordering will provide this. If you are not concerned about which rows are returned, then you can avoid using ORDER BY.

Any WHERE statements and ORDER BY statements within the SELECT statement are dealt with first, and then, from the resultant records, the TOP n function comes into effect. This will be demonstrated with the following example.

1. In Query Editor, enter the following code into a new Query Editor pane; once entered, execute it: SELECT TOP 3 SET ROWCOUNT SELECT TOP 2 SET ROWCOUNT SELECT TOP 3 SET ROWCOUNT * FROM ShareDetails.Shares 3 * FROM ShareDetails.Shares 2 * FROM ShareDetails.Shares 0

2. The code returns three result sets, as shown in Figure 8-27. Take a moment to peruse these result sets. The first set is just the top three records that are taken from an arbitrary order SQL Server has chosen. The second will only return two records, even though the ROWCOUNT is set to 3. The third result set takes into account the ROWCOUNT setting, as this is the lesser value this time. Therefore, again, only two records are returned.