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.<\/p>\n
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.<\/p>\n
— 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)
\nSELECT COUNT(*) FROM Table<\/p>\n
So that example is easy enough…<\/p>\n
SELECT COUNT(column1) FROM Table<\/p>\n
This is actually NOT the same query as COUNT(*), in general. It means "count the number of non-null column1 rows". <\/p>\n
(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).<\/p>\n
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.<\/p>\n
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. <\/p>\n
Remember that these are semantically different operations.<\/p>\n
Conor<\/p>\n","protected":false},"excerpt":{"rendered":"
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, […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-395","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\n