Spinning Out Of Control
It’s sorta kinda pretty crazy when every major database platform has something implemented, and SQL Server doesn’t.
Geez, even MySQL.
But a fairly common need in databases is to find the max value from two columns.
Maybe even across two tables.
For one table, it’s fairly straight forward.
SELECT MAX(x.CombinedDate) AS greatest FROM dbo.Users AS u CROSS APPLY( VALUES( u.CreationDate ), ( u.LastAccessDate )) AS x( CombinedDate );
We’re using our old friend cross apply with a values clause to create on “virtual” column from two date columns.
As far as indexing goes, I couldn’t find any performance difference between these two. They both take about 1 second.
CREATE INDEX smoochies ON dbo.Users(CreationDate, LastAccessDate); CREATE INDEX woochies ON dbo.Users(LastAccessDate, CreationDate);
Indexing strategy will likely rely on other local factors, like any where clause filtering.
A similar pattern will work across two tables:
SELECT MAX(x.Score) FROM dbo.Posts AS p JOIN dbo.Comments AS c ON p.Id = c.PostId CROSS APPLY( VALUES( p.Score ), ( c.Score )) AS x ( Score );
Though this is the worst possible way to write the query. It runs for around 10 seconds.
The indexes I have for this query look like so:
CREATE INDEX thicc ON dbo.Posts(Id, Score); CREATE INDEX milky ON dbo.Comments(PostId, Score);
Reversing the key column order helps — the query runs in about 3 seconds, but I need to force index usage.
Of course, this is still the second worst way to write this query.
The best way I’ve found to express this query looks like so:
SELECT MAX(x.Score) FROM ( SELECT MAX(p.Score) AS Score FROM dbo.Posts AS p ) AS p CROSS JOIN ( SELECT MAX(c.Score) AS Score FROM dbo.Comments AS c ) AS c CROSS APPLY( VALUES( p.Score ), ( c.Score )) AS x( Score );
The cross join here isn’t harmful because we only produce two rows.
And it finishes before we have time to move the mouse.
Likewise, the faster pattern for a single table looks like this:
SELECT MAX(x.Dates) FROM ( SELECT MAX(u.CreationDate) CreationDate FROM dbo.Users AS u ) AS uc CROSS JOIN ( SELECT MAX(u.LastAccessDate) LastAccessDate FROM dbo.Users AS u ) AS ul CROSS APPLY (VALUES (uc.CreationDate), (ul.LastAccessDate)) AS x (Dates);
Because we’re able to index for each MAX
CREATE INDEX smoochies ON dbo.Users(CreationDate); CREATE INDEX woochies ON dbo.Users(LastAccessDate);
Of course, not every query can be written like this, or indexed for perfectly, but it’s gruel for thought if you need specific queries like this to be as fast as possible.
Thanks for reading!