Reputation
822
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
9 16
Impact
~24k people reached

Apr
24
comment Forcing correct execution plan when passing a table UDT to a table function
@PaulWhite Funnily enough, the nested functions are inline table valued ones. As opposed to the outermost function, they properly use the name assignment trick to make them inline. Which only confuses me more about why the outer function is scalar. Not sure if I should post my solution as the solution, happy to accept your answer if you do (or don't, as a matter of fact). However I must admit that my personal preference is to make the query work without hints.
Apr
23
comment Forcing correct execution plan when passing a table UDT to a table function
What we did in the end, we moved the XML selection logic to another function that accepts l.invoice_row_id as an int and only calculates XML for one invoice, then we cross apply that to the @lines table. Seems to work very well in all situations.
Apr
23
comment Forcing correct execution plan when passing a table UDT to a table function
Unless there are absolutely compelling reasons to stick with the scalar function - I cannot reliably answer that because some time passed since that function was written, but I'm pretty sure it was because it does not compile with xmlnamespaces as is, and the solution that was found on stackoverflow was to convert it to scalar function to make it compile. with schemabinding was not an option because several databases are involved, and enforcing hint usage on external callers was no good either.
Apr
8
awarded  Popular Question
Mar
10
comment Forcing correct execution plan when passing a table UDT to a table function
@spaghettidba If I clear cache, pass a @lines with one row to trick it into the seek plan, then add OPTION(KEEPFIXED PLAN) and recompile, only then it works a couple of times, but goes back to scan anyway.
Mar
10
asked Forcing correct execution plan when passing a table UDT to a table function
Jan
26
awarded  Yearling
Jul
16
awarded  Notable Question
Jul
6
awarded  Notable Question
Apr
3
awarded  Taxonomist
Feb
17
comment Work queue with complex select and long processing: Ensuring concurrency
@JNK There are two problems with this kind of locking - 1) it needs to be done outside of the transaction, so it must be taken out of the stored procedure that does the job and put on the client side, which breaks incapsulation and trusts the client too much, and 2) when the client closes unexpectedly the row remains permanently locked, so there needs to be a watch mechanism to unlock them that would be clever enough to not give false positives and yet unlock promptly. Also there is no choosing on the client side, the client must be happy with the row the system thinks it should take.
Feb
16
asked Work queue with complex select and long processing: Ensuring concurrency
Dec
19
awarded  Yearling
Sep
24
awarded  Autobiographer
Aug
18
awarded  Popular Question
Jul
31
awarded  Popular Question
Jul
23
awarded  Popular Question
Jul
2
awarded  Curious
Dec
19
awarded  Yearling
Nov
29
comment How to get SQL Server 2012 to use the invariant culture in format()?
Apparently people found nchar(0) to be able to crash SQL Server.