COUNT(star) vs COUNT(column) vs. COUNT(DISTINCT) vs. COUNT(col) OVER(…)

So COUNT isn't an operator, but you should view this post as a "what is this operator" kind of post since I talk about how these things work and what they mean.

One of the benefits of building database engines is that all (well, most of) the syntax rules end up stuck in your head, but I know that many of these things are completely non-obvious, so I think it's worth mentioning a few things about what each of these things mean since I see them misused in lots of queries, blogs, etc.

— Returns the count of rows from the table at the time that the query is run in the transactional isolation mode in which the query is run (usually read committed for SQL Server)

So that example is easy enough…

SELECT COUNT(column1) FROM Table

This is actually NOT the same query as COUNT(*), in general.  It means "count the number of non-null column1 rows". 

(Now for something cool.  If you run this on a column that is non-nullable, then SQL Server converts it into the count(*) case because it is faster to run that form since it doesn't have to examine the data in each row and can instead just count rows).

SELECT COUNT(DISTINCT column1) will count the number of UNIQUE non-null column1 values.  It does not count NULL.  I don't believe SQL Server completely removes the DISTINCT operation for non-null columns in all cases.  It can in some cases.

COUNT(col) OVER (…) is a completely different beast.  It runs a count computation using the rules you've seen above but it does not collapse the rows – it adds a new column on all the rows with the computed count. 

Remember that these are semantically different operations.


Other articles

New blog location

Here is the new blog  – please update those readers. I’m getting settled into working for Microsoft again – it’s basically like drinking from


The Trouble with Triggers

(Apologies to Star Trek). I received a question about trigger performance, especially the do’s and dont’s about how they are used. Let’s first start by


Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.