How SQL Server’s Compute Scalar Will Impede You

Time to read: ~ 2 minutes

Words: 417

Postgres Blogs

We’re getting more and more Postgres instances at work. To get up to speed on Postgres, I’ve started to expand my RSS feed to include Postgres blogs.

It was one of those blogs, by David Stokes, that captured my attention; namely, “How PostgreSQL’s Aggregate FILTER Will Spoil You“

It got me thinking, “Huh, does SQL Server not have filters? And, if not, how can we work around that?”

So, cheers, David, for the idea for this post.

The Big Set up

We’re going to use the same setup that David has in his post

USE tempdb;
GO

CREATE TABLE z (a int, b int, c int);
GO

INSERT INTO z VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400);
GO

SELECT a,b,c FROM z;
GO

Stupid, but does it work

Now, SQL Server doesn’t have the filter option, but we can do some pretty weird things, like a SELECT...WHERE statement with no FROM clause.

SELECT
	a,b,c,
	[filter?] = (SELECT b WHERE b > 11)
FROM z;
GO

So, is that it? Can we just throw that into a COUNT() function and essentially have the same thing as Postgres?

SELECT
	all_rows	= COUNT(*),
	b_gt_11		= COUNT((SELECT b WHERE b > 11))
FROM z;
GO

Workaround

We can rewrite that strange little filter we have using an OUTER APPLY.

SELECT
	a,b,c,
	[filter?] = bees.bee
FROM z
OUTER APPLY (SELECT b WHERE b > 11) AS bees (bee);
GO

Now, can we throw that in a COUNT() function?

SELECT
	all_rows	= COUNT(*),
	b_gt_11		= COUNT(bees.bee)
FROM z
OUTER APPLY (SELECT b WHERE b > 11) AS bees (bee);
GO

Things I don’t know

• Postgres, yet. Still learning this one.
• Why the OUTER APPLY works, but the subquery doesn’t?
  Let’s review the plans and see if we can identify a culprit.

Strangely, the first Compute Scalar (from the left, after the SELECT) is simply a scalar operator applied to the other Compute Scalar.

It’s enough to block aggregation, though, but I’ll leave it to more specialised people than I to tell me why. I’ll just store this under “errors out” for future reference.

• Performance Implications
  This is a test on 4 rows. What would happen if this were tens of millions of rows?
  …
  I leave that to the reader.