Edit Question - KnowledgeHub

Title Body

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.)

Tags (comma-separated) Cancel