After an application submits an SQL statement, Microsoft® SQL Server™ 2000 returns any resulting data as one or more result sets. A result set is a set of rows and columns that match the criteria of the query. SELECT statements, catalog functions, and some procedures produce a result set made available to an application in tabular form. If the executed SQL statement is a stored procedure, a batch containing multiple commands, or a SELECT statement containing keywords, such as COMPUTE or COMPUTE BY, there will be multiple result sets to process.
The ADOX Catalog object can also retrieve data. For example, The Catalog object allows you to manipulate and retrieve data about tables, views, and stored procedures in a SQL Server 2000 database. These result sets can contain zero or more rows. Other SQL statements, such as GRANT or REVOKE, do not return result sets.
Each INSERT, UPDATE, and DELETE statement returns a result set containing only the number of rows affected by the modification. These counts can be canceled by including a SET NOCOUNT ON statement in the batch or stored procedure.
Transact-SQL includes the SET NOCOUNT statement. When the NOCOUNT option is set to ON, SQL Server does not return the counts of the rows affected by a statement.
Several other Transact-SQL statements return their data in messages rather than result sets, such as:
- SET SHOWPLAN
- SET STATISTICS
ADO applications use the Recordset object to manipulate result sets, and the Fields collection and Field object to access data in a row. In addition, you can use the Properties collection and Property object to provide information about the characteristics of a result set.