Why does adding a TOP allow the SQL Server query optimizer to find a plan that can honor a FORCESEEK hint?

sql-server

Joe ObbishJoe Obbish

12Although the query text contains an `APPLY`, the optimizer rewrites this to a regular join using the `ApplyHandler` rule early in the compilation process (during simplification). The optimizer prefers joins over applies because it has more transformations for joins[^1](For background on the difference between join and apply, see my article [Apply versus Nested Loops Join](https://www.sql.kiwi/2019/06/apply-versus-nested-loops-join.html)).

Paul White

7To supplement Paul's answer, ironically, the optimizer is capable of producing apply plan for the original query, if `OUTER APPLY` is used instead of the `CROSS APPLY`

i-one

Missed optimisations for ScalarGbAggToTop with MAX(nullable_column)

sql-server

Martin SmithMartin Smith

12You're quite right about the reason for the separate `NULL` and `NOT NULL` branches.

Paul White

Handling Erroneous Data Entry in SQL

sql-server-2012

DS ChainsDS Chains

6[`TRY_CONVERT`](https://docs.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15) is what you are after:

Jack Douglas

Confused about STRING_SPLIT

sql-server-2019

Humble noviceHumble novice

11`STRING_SPLIT` [is a table-valued function](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15):

Jack Douglas

Why is there an extra predicate with an IN clause?

sql-server-2019

ForrestForrest

9The extra predicate on `#B` is added during query simplification by the "implied predicate" mechanism.

Martin Smith

DBCC CHECKDB WITH ESTIMATEONLY, significant over estimate.

sql-server-2014

James JenkinsJames Jenkins

Why doesn't disabling row goals give me a different query plan?

sql-server

Joe ObbishJoe Obbish

14In your first example, we can see the row goal and its effects on costing using undocumented trace flags 8607 and 8612 (as I describe in my article you linked to):

Paul White

How can this MAXDOP 4 query have CPU time greater than 4X the elapsed time?

sql-server

Joe ObbishJoe Obbish

13DOP limits the number of schedulers that can be used by ***additional parallel workers***. These are threads used to execute the statement that are not provided by the originating session.

Paul White

How do you add line returns after every nth item in a string?

sql-server

JamesJames

6If you're using the *Results to Text* output option in SSMS, the following procedure might be suitable:

Paul White

6[`string_agg()`](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) is best agg

PeterVandivier

Why isn't this query eligible for a hash join?

sql-server

Joe ObbishJoe Obbish

15This is because of `HASH` join algorithm requires join equality predicate, but query optimizer eliminates it during simplification.

i-one