select count(*);

Quick trivia: what is the result of running SELECT COUNT(*);?

That’s right, no FROM clause, just COUNT(*). The answer may be a little bit surprising, is 1. When you query SELECT 1; the result is, as expected, 1. And SELECT 2; will return 2. So SELECT COUNT(2); returns, as expected, 1, after all it counts how many rows are in the result set. But SELECT COUNT(*); has a certain smell of voo-doo to it. Ok, is the * project operator, but project from… what exactly? It feels eerie, like a count is materialized out of the blue.

How about SELECT COUNT(*) [MyTable]. Well, that’s actually just a shortcut for SELECT COUNT(*) AS [MyTable], so it still returns 1 but in a column named MyTable. Now you understand why my heart missed a bit when I checked how I initialized a replication subscription and I forgot to type in FROM…