Counting query result without executing query, using sp_executesql

Question

Is it possibile to count the number of result that a query returns using sp_executesql without executing query? What I mean:

I have a procedure that gets a sql query in string. Example:

SELECT KolumnaA FROM Users WHERE KolumnaA > 5

I would like to assign count of how many results this query will return, and store it in a variable, but I do not want to actually execute the query.

I cannot use this solution:

EXECUTE sp_executesql @sql          
SET @allCount = @@rowcount

because it returns the query result, in addition to getting the count of returned rows.

Answer 1

Can you somehow generate another query from the above one like this

SELECT count(*) FROM Uzytkownicy WHERE KolumnaA > 5

and then execute that?

Answer 2

In general case...

SELECT COUNT(*) FROM ( <your query> )

...which in your case can be simplified into:

SELECT COUNT(*) FROM Users WHERE KolumnaA > 5

The reason it can't be done cheaper is that there are no hidden "counters" inside the data managed by the DBMS. The DBMS won't even know the total number of rows in the table, let alone the number of rows fulfilling a criteria that is not known in advance (such as KolumnaA > 5).

So, counting requires actually finding the data, so it requires the "real" query. Fortunately, all this happens on the server and only a minute amount of data is transferred to the client (the count itself), so assuming your data is properly indexed it should be pretty fast.

Be careful about consistency though: just because the counting query returned certain count, does not mean that the "real" query will return the same number of rows (in the environment where multiple clients may be modifying the data concurrently).