Returning overall count inside a TOP query optimally

SELECT TOP 100 *, (SELECT COUNT(*) FROM Machines WHERE Speed <=1500) AS RowCount
FROM Machines 
WHERE Speed <= 1500;

Let's say this would return 100 rows and if TOP were removed it would be 200. Does this mean it's running the embedded SELECT 100 times? Or is it only doing it once? If the former how do I get it to only run once in the same query?

The approach you have in your question may already be optimal.

You need to check the execution plan to see how it is optimised but potentially the underlying query will be executed only twice.

• Once to get the count
• Once to get the row details (this execution can stop after 100 rows)

The two separate executions can be optimised separately - e.g. use a narrower index to get the count than for the main query (that needs to return all columns due to *).

Using a windowed function may look more optimal.

SELECT TOP 100 *
    , COUNT(*) OVER () AS [RowCount]
FROM Machines 
WHERE Speed <= 1500;

but in fact will likely end up materializing the entire result set matching

SELECT *
FROM Machines 
WHERE Speed <= 1500;

to a spool (i.e. all columns and all matching rows without any TOP 100 applied).

Potentially this could add overhead that is worse than just executing two queries against the base data. It would be possible to come up with examples where either approach is best.

If the WHERE clause is highly selective and there are no indexes that can be used for it then just running it once and spooling the result could well be favoured. If the table is very wide and there will be many more than 100 rows matching the WHERE clause then the two query approach could be best.

As an example, noting that different queries will be optimised differently see:

Note with regard to the window function version: the execution order is WHERE then OVER then TOP, so both the count and the top are working on a filtered set, but the count runs before the top and can see the whole filtered set.