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

User · Answer

If you are really concerned that your row count will change between the select count and the select statement  why not select your rows into a temp table first   That way  you know you will be in sync

User · Answer

Here are some ideas    Go with Approach  1 and resize the array to hold additional results or use a type that automatically resizes as neccessary  you don t mention what language you are using so I can t be more specific   You could execute both statements in Approach  1 within a transaction to guarantee the counts are the same both times if your database supports this  I m not sure what you are doing with the data but if it is possible to process the results without storing all of them first this might be the best method

User · Answer

Approach 2 will always return a count that matches your result set   I suggest you link the sub-query to your outer query though  to guarantee that the condition on your count matches the condition on the dataset   SELECT    mt my row    SELECT COUNT mt2 my row  FROM my table mt2 WHERE mt2 foo   mt foo  as cnt FROM my table mt WHERE mt foo    bar

User · Answer

If you re concerned the number of rows that meet the condition may change in the few milliseconds since execution of the query and retrieval of results  you could should execute the queries inside a transaction   BEGIN TRAN bogus  SELECT COUNT  my table my col   AS row count FROM my table WHERE my table foo    bar   SELECT my table my col FROM my table WHERE my table foo    bar  ROLLBACK TRAN bogus   This would return the correct values  always   Furthermore  if you re using SQL Server  you can use   ROWCOUNT to get the number of rows affected by last statement  and redirect the output of real query to a temp table or table variable  so you can return everything altogether  and no need of a transaction   DECLARE  dummy INT  SELECT my table my col INTO  temp table FROM my table WHERE my table foo    bar   SET  dummy   ROWCOUNT SELECT  dummy    FROM  temp table

User · Answer

If you re concerned the number of rows that meet the condition may change in the few milliseconds since execution of the query and retrieval of results  you could should execute the queries inside a transaction   BEGIN TRAN bogus  SELECT COUNT  my table my col   AS row count FROM my table WHERE my table foo    bar   SELECT my table my col FROM my table WHERE my table foo    bar  ROLLBACK TRAN bogus   This would return the correct values  always   Furthermore  if you re using SQL Server  you can use   ROWCOUNT to get the number of rows affected by last statement  and redirect the output of real query to a temp table or table variable  so you can return everything altogether  and no need of a transaction   DECLARE  dummy INT  SELECT my table my col INTO  temp table FROM my table WHERE my table foo    bar   SET  dummy   ROWCOUNT SELECT  dummy    FROM  temp table

User · Answer

You might want to think about a better pattern for dealing with data of this type   No self-prespecting SQL driver will tell you how many rows your query will return before returning the rows  because the answer might change  unless you use a Transaction  which creates problems of its own    The number of rows won t change - google for ACID and SQL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you are really concerned that your row count will change between the select count and the select statement  why not select your rows into a temp table first   That way  you know you will be in sync

User · Answer

You might want to think about a better pattern for dealing with data of this type   No self-prespecting SQL driver will tell you how many rows your query will return before returning the rows  because the answer might change  unless you use a Transaction  which creates problems of its own    The number of rows won t change - google for ACID and SQL

User · Answer

Why don t you put your results into a vector   That way you don t have to know the size before hand

User · Answer

Just to add this because this is the top result in google for this question  In sqlite I used this to get the rowcount   WITH temptable AS    SELECT one two    FROM       SELECT one  two       FROM table3       WHERE dimension 0       UNION ALL SELECT one  two       FROM table2       WHERE dimension 0       UNION ALL SELECT one  two       FROM table1       WHERE dimension 0     ORDER BY date DESC  SELECT   FROM temptable LEFT JOIN    SELECT count    7 AS cnt                          0 AS bonus    FROM temptable  counter WHERE 0   counter bonus

User · Answer

Here are some ideas    Go with Approach  1 and resize the array to hold additional results or use a type that automatically resizes as neccessary  you don t mention what language you are using so I can t be more specific   You could execute both statements in Approach  1 within a transaction to guarantee the counts are the same both times if your database supports this  I m not sure what you are doing with the data but if it is possible to process the results without storing all of them first this might be the best method

User · Answer

Approach 2 will always return a count that matches your result set   I suggest you link the sub-query to your outer query though  to guarantee that the condition on your count matches the condition on the dataset   SELECT    mt my row    SELECT COUNT mt2 my row  FROM my table mt2 WHERE mt2 foo   mt foo  as cnt FROM my table mt WHERE mt foo    bar

User · Answer

IF    ROWCOUNT  gt  0  BEGIN SELECT my table my col   FROM my table  WHERE my table foo    bar  END

User · Answer

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

User · Answer

Why don t you put your results into a vector   That way you don t have to know the size before hand

User · Answer

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

User · Answer

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

User · Answer

Just to add this because this is the top result in google for this question  In sqlite I used this to get the rowcount   WITH temptable AS    SELECT one two    FROM       SELECT one  two       FROM table3       WHERE dimension 0       UNION ALL SELECT one  two       FROM table2       WHERE dimension 0       UNION ALL SELECT one  two       FROM table1       WHERE dimension 0     ORDER BY date DESC  SELECT   FROM temptable LEFT JOIN    SELECT count    7 AS cnt                          0 AS bonus    FROM temptable  counter WHERE 0   counter bonus

User · Answer

Approach 2 will always return a count that matches your result set   I suggest you link the sub-query to your outer query though  to guarantee that the condition on your count matches the condition on the dataset   SELECT    mt my row    SELECT COUNT mt2 my row  FROM my table mt2 WHERE mt2 foo   mt foo  as cnt FROM my table mt WHERE mt foo    bar

User · Answer

If you are really concerned that your row count will change between the select count and the select statement  why not select your rows into a temp table first   That way  you know you will be in sync

User · Answer

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

User · Answer

Why don t you put your results into a vector   That way you don t have to know the size before hand

User · Answer

If you re concerned the number of rows that meet the condition may change in the few milliseconds since execution of the query and retrieval of results  you could should execute the queries inside a transaction   BEGIN TRAN bogus  SELECT COUNT  my table my col   AS row count FROM my table WHERE my table foo    bar   SELECT my table my col FROM my table WHERE my table foo    bar  ROLLBACK TRAN bogus   This would return the correct values  always   Furthermore  if you re using SQL Server  you can use   ROWCOUNT to get the number of rows affected by last statement  and redirect the output of real query to a temp table or table variable  so you can return everything altogether  and no need of a transaction   DECLARE  dummy INT  SELECT my table my col INTO  temp table FROM my table WHERE my table foo    bar   SET  dummy   ROWCOUNT SELECT  dummy    FROM  temp table

User · Answer

Approach 2 will always return a count that matches your result set   I suggest you link the sub-query to your outer query though  to guarantee that the condition on your count matches the condition on the dataset   SELECT    mt my row    SELECT COUNT mt2 my row  FROM my table mt2 WHERE mt2 foo   mt foo  as cnt FROM my table mt WHERE mt foo    bar

User · Answer

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

User · Answer

Here are some ideas    Go with Approach  1 and resize the array to hold additional results or use a type that automatically resizes as neccessary  you don t mention what language you are using so I can t be more specific   You could execute both statements in Approach  1 within a transaction to guarantee the counts are the same both times if your database supports this  I m not sure what you are doing with the data but if it is possible to process the results without storing all of them first this might be the best method

User · Answer

If you are really concerned that your row count will change between the select count and the select statement  why not select your rows into a temp table first   That way  you know you will be in sync

User · Answer

You might want to think about a better pattern for dealing with data of this type   No self-prespecting SQL driver will tell you how many rows your query will return before returning the rows  because the answer might change  unless you use a Transaction  which creates problems of its own    The number of rows won t change - google for ACID and SQL

User · Answer

IF    ROWCOUNT  gt  0  BEGIN SELECT my table my col   FROM my table  WHERE my table foo    bar  END

User · Answer

Here are some ideas    Go with Approach  1 and resize the array to hold additional results or use a type that automatically resizes as neccessary  you don t mention what language you are using so I can t be more specific   You could execute both statements in Approach  1 within a transaction to guarantee the counts are the same both times if your database supports this  I m not sure what you are doing with the data but if it is possible to process the results without storing all of them first this might be the best method

User · Answer

If you re concerned the number of rows that meet the condition may change in the few milliseconds since execution of the query and retrieval of results  you could should execute the queries inside a transaction   BEGIN TRAN bogus  SELECT COUNT  my table my col   AS row count FROM my table WHERE my table foo    bar   SELECT my table my col FROM my table WHERE my table foo    bar  ROLLBACK TRAN bogus   This would return the correct values  always   Furthermore  if you re using SQL Server  you can use   ROWCOUNT to get the number of rows affected by last statement  and redirect the output of real query to a temp table or table variable  so you can return everything altogether  and no need of a transaction   DECLARE  dummy INT  SELECT my table my col INTO  temp table FROM my table WHERE my table foo    bar   SET  dummy   ROWCOUNT SELECT  dummy    FROM  temp table

User · Answer

Why don t you put your results into a vector   That way you don t have to know the size before hand

User · Answer

You might want to think about a better pattern for dealing with data of this type   No self-prespecting SQL driver will tell you how many rows your query will return before returning the rows  because the answer might change  unless you use a Transaction  which creates problems of its own    The number of rows won t change - google for ACID and SQL

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

Examples related to sql

Examples related to odbc

Examples related to sqlncli