SELECT x, y, z,
WHEN COUNT (PH.header_id) OVER (PARTITION BY PL.header_id)
    NOT IN (null,0) THEN L_Count
ELSE COUNT (PH.header_id) OVER (PARTITION BY PL.header_id)
END as quantity

Is there a more efficient way of writing this?

I am very conscious of it performing the count twice on a large table.

Unfortunately I can't post a query plan as the user account I'm working on is awaiting SHOWPLAN permissions.

The NOT IN clause is a side issue. Thank you for the comment feedback about ways to improve that, but the focus of the question is the duplicated counts.


There are two issues at play here:

  • the code is redundant because the window function is typed out twice
  • the concern is that SQL Server will perform the count twice since it is referenced twice

For the first, we can address the tidiness aspect by only calculating that expression once (and I would change from NOT IN (NULL, 0) logic to just checking if the count is greater than 0 (which eliminates NULLs and 0):

  SELECT x, y, z, L_Count, 
    q = COUNT (PH.header_id) OVER (PARTITION BY PL.header_id)
  FROM ...something PH and PL...
SELECT x, y, z, quantity = CASE WHEN q > 0 THEN L_Count ELSE q END
  FROM x;

Whether that's the right or most efficient partitioning choice is a different exercise.

For the second, well, SQL Server is pretty smart about performing a calculation only once at a given scope. As mentioned above, referencing an expression twice doesn't necessarily mean it will be calculated twice, and hiding a second reference inside a CTE like I do in this answer doesn't guarantee that it will be calculated exactly once, either. To know for sure, you'd have to examine the execution plans and other details (like the expressions tab and performance metrics provided by, say, Plan Explorer), and compare.

Having table structure, sample data, and desired results (and even more of the query) would really help create a better (and validated) solution for you. I'm only suggesting the same logic you have in your question, which I can only assume is working for you right now (I'm trying to understand what your CASE logic even means - it seems to say "if there is a valid count from this window function, then use this other column, but if there isn't a valid count from this window function, use the invalid count"). I suspect something got lost in translation / simplification.

See also:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.