To enable adaptive join in SQL Server, the compatibility level, the database setting and the query must all agree. This post builds a demo database and probes the plan under each condition. It also corrects older advice whose statements switch the feature off.
Adaptive Threshold Rows: How an Adaptive Join Chooses
Adaptive threshold rows is the row count at which an adaptive join switches from nested loops to a hash join. This post runs two queries on WideWorldImporters, reads each threshold from the cached plan, and shows which branch ran. The first query stays below its threshold and the second goes above it.
JOIN Elimination in SQL Server: When a Table Drops Out
JOIN elimination removes a joined table from the plan when the query does not need it. This post proves it with STATISTICS IO, then tests nine variations to show when it applies and when it does not, including a foreign key that has lost its trust.
Keyword Join or Inner Join – SQL in Sixty Seconds #099
Have you ever wondered what is the difference between keyword Join or Inner Join? Are they same or different? If different, how are they different?
Creating Covering Index to Eliminate Join – SQL in Sixty Seconds #090
How to improve performance so today we are going to see a video about creating converting index to eliminate join and improve query performance.
What is Full Outer Join With Exclusion? – Interview Question of the Week #286
Question: What is Full Outer Join With Exclusion?
Answer: I got this question after reading my earlier blog post about Basic of JOINs.
Year-Over-Year Comparisons in One Query
Build year-over-year monthly comparisons with a complete calendar, LAG, date-aligned joins, safe percentages, and missing-period rules.