Need a row count after SELECT statement: what's the optimal SQL approach?

Question

I'm trying to select a column from a single table (no joins) and I need the count of the number of rows, ideally before I begin retrieving the rows. I have come to two approaches that provide the information I need.

Approach 1:

SELECT COUNT( my_table.my_col ) AS row_count   FROM my_table  WHERE my_table.foo = 'bar' 

Then

SELECT my_table.my_col   FROM my_table  WHERE my_table.foo = 'bar' 

Or Approach 2

SELECT my_table.my_col, ( SELECT COUNT ( my_table.my_col )                             FROM my_table                            WHERE my_table.foo = 'bar' ) AS row_count   FROM my_table  WHERE my_table.foo = 'bar' 

I am doing this because my SQL driver (SQL Native Client 9.0) does not allow me to use SQLRowCount on a SELECT statement but I need to know the number of rows in my result in order to allocate an array before assigning information to it. The use of a dynamically allocated container is, unfortunately, not an option in this area of my program.

I am concerned that the following scenario might occur:

• SELECT for count occurs
• Another instruction occurs, adding or removing a row
• SELECT for data occurs and suddenly the array is the wrong size.
  -In the worse case, this will attempt to write data beyond the arrays limits and crash my program.

Does Approach 2 prohibit this issue?

Also, Will one of the two approaches be faster? If so, which?

Finally, is there a better approach that I should consider (perhaps a way to instruct the driver to return the number of rows in a SELECT result using SQLRowCount?)

For those that asked, I am using Native C++ with the aforementioned SQL driver (provided by Microsoft.)

Answer 1

If you're using SQL Server, after your query you can select the @@RowCount function (or if your result set might have more than 2 billion rows use the RowCount_Big() function). This will return the number of rows selected by the previous statement or number of rows affected by an insert/update/delete statement.

SELECT my_table.my_col   FROM my_table  WHERE my_table.foo = 'bar'  SELECT @@Rowcount 

Or if you want to row count included in the result sent similar to Approach #2, you can use the the OVER clause.

SELECT my_table.my_col,     count(*) OVER(PARTITION BY my_table.foo) AS 'Count'   FROM my_table  WHERE my_table.foo = 'bar' 

Using the OVER clause will have much better performance than using a subquery to get the row count. Using the @@RowCount will have the best performance because the there won't be any query cost for the select @@RowCount statement

Update in response to comment: The example I gave would give the # of rows in partition - defined in this case by "PARTITION BY my_table.foo". The value of the column in each row is the # of rows with the same value of my_table.foo. Since your example query had the clause "WHERE my_table.foo = 'bar'", all rows in the resultset will have the same value of my_table.foo and therefore the value in the column will be the same for all rows and equal (in this case) this the # of rows in the query.

Here is a better/simpler example of how to include a column in each row that is the total # of rows in the resultset. Simply remove the optional Partition By clause.

SELECT my_table.my_col, count(*) OVER() AS 'Count'   FROM my_table  WHERE my_table.foo = 'bar' 

Answer 2

There are only two ways to be 100% certain that the COUNT(*) and the actual query will give consistent results:

• Combined the COUNT(*) with the query, as in your Approach 2. I recommend the form you show in your example, not the correlated subquery form shown in the comment from kogus.
• Use two queries, as in your Approach 1, after starting a transaction in SNAPSHOT or SERIALIZABLE isolation level.

Using one of those isolation levels is important because any other isolation level allows new rows created by other clients to become visible in your current transaction. Read the MSDN documentation on SET TRANSACTION ISOLATION for more details.