Way to know the amount of data generated by a given query?

Question

Typically, it is possible to know how many lines a query returns by using COUNT(*).

In the same manner, is there any way know how many megabytes for example the output of a given query is ?

Something like

SELECT MEMORYUSE(*) FROM bla bla bla

EDIT : I like the *exec sp_spaceused ... * approach, as it can be scripted!

Answer 1

For completeness, there are a couple of options to give you more information about the executing / executed query that you can view / set using SSMS as well. As shown below, the rowcount for the query is shown in the bottom right of SSMS by default. Also, I've highlighted the advanced query options, which you can set globally as shown here. Of course, you can also turn any of these options on for the particular statement or batch by including them in the query, i.e. 'set showplan_test on', etc..

Also you can turn on 'show client statistics' in SSMS as shown below (with sample output).

Answer 2

If you're using SQL Server, turn on Client Statistics and you 'll find "Bytes sent from client" and "Bytes received from server"

Here is related question

SQL Finding the size of query result

Answer 3

I think this will be useful:

SQL Server Query Size of Results Set

I don't think there is anyway without creating a temp table for the results and checking the size.