Query folding on JoinKind.Inner gotcha for Power BI and Power Query

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:

Enjoy and stay queryious 😉

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz