Steven Rushing @RushingSQL

The latest SQL Server cumulative updates (2022 CU 10, 2019 CU23) contain an interesting bug fix, which @josh_the_coder noticed earlier today: Before this update, in some rare cases, if IDENTITY_INSERT is set to ON, the identity value for the target table is set to the highest value in the source table when joining tables, even if the highest value doesn't satisfy the join condition or the predicate. This update improves the current design and fixes this issue. Note: To apply this update, you need to set the QUERY_OPTIMIZER_HOTFIXES database scoped configuration to ON. To turn off this update, you can enable trace flag 13193. The cases turn out not to be so rare: SQL Server assigns the new identity value when IDENTITY_INSERT is ON by calling an internal function setidentity() in a Compute Scalar plan operator. It is the placement of this operator in the plan that is the problem. If it appears before a filtering condition, it might be called more times than necessary, using the wrong source values. In other words, the destination will behave as if identity values had been assigned when they ultimately weren't, due to the later filtering. In many cases, SQL Server is saved by deferred evaluation of expressions in a Compute Scalar. This is where SQL Server waits until another operator needs the result of the computation before performing it, despite the position of the Compute Scalar operator in the plan. (This optimisation was added in 2005 and only applies to row mode Compute Scalar operators.) Now, the only operator that will need the result of the setidentity() call is the Insert operator adding rows to the target table with IDENTITY_INSERT. This means any filtering of rows will occur before the insert, and no unnecessary setting of identity values occurs. Yay! This is all well and good, unless there is a blocking operator in the plan after the setidentity() Compute Scalar but before the filtering. A blocking operator has to materialize the data it receives in one way or another - in a hash table or sort memory for example - and this materialization necessarily needs the result of the setidentity() call. Oops. When this happens, setidentity() is called when it shouldn't be. The target table then behaves as if identity values have been used when they were not. The fix is to ensure the Compute Scalar appears on the 'write cursor' (left) side of the insert plan, where SQL Server has full control, and any user-coded filtering of rows is bound to have already happened. Without the fix, the Compute Scalar can appear surprisingly early on the 'read cursor' side of the plan where rows are read and processed to see which ones qualify for the update. The optimizer tends to push Compute Scalars as far to the right as possible for several reasons, including computed column matching. This will never apply to the special setidentity() Compute Scalar of course, but general rules apply generally. I wrote a toy demo that materializes the setidentity() call in the build-side hash table of a hash join: dbfiddle.uk/v7Ci0_pU Unfortunately, db<>fiddle is still at 2022 RTM so I can't show the fix with query optimizer hotfixes enabled there, but as you can imagine it ensures the Compute Scalar stays in a safe place on the left side of the plan. For more about deferred expression evaluation see my 2012 article, Compute Scalars, Expressions and Execution Plan Performance sql.kiwi/2012/09/comput…

Like the spread of attendees in the community zone at #PassDataSummit. Hope we see even more pins next year

I swear this week just started! Sad to say goodbye, but THANK YOU to our host @redgate , some absolutely fantastic presenters, and the kick ass staff of the Seattle Convention Center for allowing me to Connect, Share, and Learn #PASSDataSummit

keyboard_arrow_left Previous keyboard_arrow_right Next

SQL Superheroes in the making here at Summit ‘23! #PureStorage #DiskIsDone, #PASSDataSummit

@dgmelkin @PASSDataSummit Great session, thanks!

@LaurieLCarr You too! Look forward to chatting again

Goodbye Seattle, you were a great host! Hopefully I'll get to return next year for #PASSDataCommunitySummit . Such an amazing conference and community! #SQLFamily

Hanging out with Lego folk! @redgate #PASSDataSummit #SingWithLegoKathi #LegoSteveReturns

@Ask_Spectrum Fix your line that’s been down since November 222

@GetSpectrum Why is my cable line still not repaired after 11 months of spotty Internet service?

Bumping this new Azure Feedback item about making TF 3226 be enabled by default. It only has 8 votes so far... feedback.azure.com/d365community/… #sqlserver #sqlserver2022

100%!

Jay Robinson @downshiftdata

4 years ago

Having @KimberlyLTripp and @PaulRandal do the last session together on Friday should be a #PASSDataCommunitySummit tradition

1 1 10 0 0

@SQLChick @PASSDataSummit @SQLAllFather Amazing talk, thanks @SQLAllFather for sharing so much

#LegoSteveAtSummit Who's ready for lunch? @redgate

First conference day woot! #passdatacommunitysummit #sqlfamily