Using SET ROWCOUNT, TOP, and PERCENT to limit a result set Using the SET ROWCOUNT, TOP, and PERCENT statements to limit a result set can be useful. You can use this statement to limit the rows returned from a simple select query. This option is session-specific and does not affect any other existing connection.Assume you have a table of 23 rows, but you only want a sampling of the data. Use the following to accomplish this task. SET�ROWCOUNT�5 SELECT�*�FROM�pubs..authors This would return the first five rows in the table pubs..authors. Alternatively, you can write the preceding statement to return the same result set with the TOP keyword. SELECT�TOP�5�*�FROM�pubs..authors If you are in need of a percentage of the data in a table, use the PERCENT keyword in your statement. SELECT�TOP�5�*�PERCENT�FROM�pubs..authors This would only return 2 rows. i.e., 2 rows / 23 rows = 1.15 rows rounded to the next highest number. One thing to remember is that SET ROWCOUNT takes precedence over TOP. If you have previously defined your SET ROWCOUNT option to 5 and then try to select TOP 10 from your table, you will only get 5 rows returned. To return your ROWCOUNT setting to normal, issue the following: SET�ROWCOUNT�0 You can also use the SET ROWCOUNT to act upon a certain number of row(s) in a data modification statement. SET�ROWCOUNT�1 DELETE�FROM�pubs..authors� Although this statement looks dangerous, it will be limited to the first row it encounters and will delete only that row. This method is also helpful if you have a duplicate row in a table that you need to purge. Set your ROWCOUNT option to the appropriate value and delete the record based on the criteria in the WHERE clause. This will delete the first occurrence of the row while leaving the remaining row intact. |