What’s The Point of 1 = (SELECT 1) In SQL Server Queries?

If you like what you see here, you'll love my advanced performance tuning training:
training.erikdarling.com/?cou...
From my post here: erikdarling.com/whats-the-poi... Click here for 50% off a health check: training.erikdarling.com/darl...

[@bbksvb]

I was wondering about this. Thanks for the info!

[@ErikDarlingData]

There you go!

[@christopherstyle878]

Mezcal and brisket, got it.

[@ErikDarlingData]

It’s a fairly simple checklist

[@douglascoats7081]

When dealing with wonky nonsense in plans, Instead of WHERE 1 = (SELECT 1), I smack the monitor and then blow in the USB port.

[@ErikDarlingData]

Throw the controller against the wall

[@mortezasafaee6981]

Keep going... i like you haircut😊

[@ErikDarlingData]

Thank you 😁

[@worldfromtheair]

Slightly off topic, but I guess you also use DATA_COMPRESSION = PAGE on all your index creates too? I guess just to save space and logical reads

[@ErikDarlingData]

Just space. I don't care about logical reads: ruclips.net/video/kxwEkeBCbdE/видео.html

[@andreysamykin1143]

Thanks for an interesting video! I wonder if “when 1=1” has the same effect.

[@ErikDarlingData]

I’m not sure I follow this one

[@IcarianHeights]

Apologies for being dense, but even after watching the video and reading the blog post, though I better understand why you're using `1 = (SELECT 1)`, I still don't understand why that pattern forces the optimizer to skip assigning the query as having a `trivial plan` or `simple parameterization`.
Is it simply because it's a sub-query, where the optimizer doesn't know what may be in it at runtime so it has to in fact go down the actual road of optimizing the query further than if it were a known/constant value?

[@ErikDarlingData]

Yep, you can even simplify it in some cases to be something like SELECT (SELECT Id) FROM dbo.Whatever;