SQL Server Query - How to append row showing total record count?

Question

What is the best approach to append a row to a SQL Server query showing the total count of rows resulting from the query? UNION is one way, but seems very inefficient:

SELECT col1, col2 FROM tbl1
UNION ALL
SELECT STR(COUNT(col1)), NULL FROM tbl1

ROLLUP isn't an option because it requires GROUP BY, which we're not using for the queries in question.

Answer 1

You can use GROUPING SETS for this

SELECT
  CASE WHEN GROUPING(col1) = 0 THEN col1 ELSE CAST(COUNT(*) AS varchar(30)) END AS col1,
  col2
FROM tbl1
GROUP BY GROUPING SETS (
    (col1, col2),
    ()
);

The GROUPING function will tell you whether the row is the Total row or not.

This does have the effect of grouping the columns which could be a different result and possibly less efficient. But if you include a unique/primary key as the first column in the grouping list then this shouldn't make a difference, and should be almost as performant as the original query.

---

You can also use a window function, which will return the total on each row as another column

SELECT
  col1,
  col2,
  COUNT(*) OVER ()
FROM tbl1;