If you query databases who support query folding, you’re probably very aware of every step you take and check if folding happens with every new step like so:
“View Native Query” shows folding query that’s send back to the server (if not greyed out)
Folding will (usually) happen as long as “View Native Query” isn’t greyed out.
So when doing an inner join on tables whithin the same database, I was a bit surprised to see this greyed out actually. As according to the literature, it should fold.
But guess what? After I expanded a column from it, the folding was back again:
Expanding column(s) brings back folding
Also I vaguely remember having seen queries fold, where it was actually greyed out. So I would always recommend to check the actual query in SQL Server Profiler before shouting at your screen.
Edit:
You should use this technique as well when you actually don’t need any of the expanded columns, but just use the inner join to filter out records from your “left” table. Thanks to Ed Hansberry for pointing this out:
Yup. I found that out after trial and error when I was doing inner joins just to limit data in my first table and deleting the joined table with no expansion. Now I expand and then delete the expanded column. Folding continues!
— Ed Hansberry (@ehansalytics) 18. April 2019
Enjoy and stay queryious 😉