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.
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.
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.
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.
Sep
18
comment Cast to date is sargable but is it a good idea?
I can't help noticing that LINQ2SQL generates SQL where cast(date_column as date) = 'value' when presented with C# similar to where obj.date_column.Date == date_variable.
Sep
15
comment How to get SQL Server 2012 to use the invariant culture in format()?
@JonSeigel Posted, edited in the link.
Sep
15
comment How to get SQL Server 2012 to use the invariant culture in format()?
I wonder if this should be posted to connect.misrosoft.com now.
Sep
15
comment How to get SQL Server 2012 to use the invariant culture in format()?
Very interesting, thank you.
Aug
17
comment How to get SQL Server 2012 to use the invariant culture in format()?
@AaronBertrand Omitting the third parameter, as documented, gives you the result formatted according to your connection language (SET LANGUAGE). Invariant culture is supposed to be invariant accross machines, which makes it perfect for serialization. So it's used on the SQL Server level when it comes to serializing data in some form or another, this includes generating an XML off a query. The 'g' was just an example, I'm not actually using that. And I can emulate the invariantness by escaping the special characters in the format string ('dd\/MM\/yyyy'), but that's not pretty.
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
While the asnwer solves the problem, it does not really connect the dots nicely (and while I think I did connect them, I'm not entirely sure). So let me restate: The problem is that truncate will take schema-stability locks in addition to data locks, whereas delete will only take data locks. When later SSIS goes to validate the schema, it hits the taken schema-stability lock and waits because the connection that verifies metadata is intentionally not enlisted in the distributed transaction. With delete there is no problem because no schema locks were taken in the first place. Correct?
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
@Kin sys.dm_exec_requests doesn't show what is blocking because it's -2. ValidateExternalMetadata solves the problem though, which is a bit surprising because all my OLE DB destinations already were 'Fast Load' which are said to be immune to the problem in the article.
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
@Kin Just tried that, made no difference. As for truncating outside SSIS, I don't really know, because I don't really use distributed transactions outside SSIS; within a non-distributed transaction it works, obviously.
Aug
7
comment SSIS package blocks itself if uses TRUNCATE
@JonSeigel An interesting solution, will tuck away for future use, but will probably not apply here.
Jul
26
comment Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way
@AaronBertrand I believe you misread the code; TestTest is scalar and is called as such, WillBeCalledByCLR is TV and is also called as such (with exists), no CLR-specific magic here. As for fixes, yes, the company is slow about fixes because they are believed to not only fix, but also amend and brake. Would actually be nice if this behaviour was confirmed as nonexistent on 2012, as it was last time.
Jul
26
comment Passing NULL xml value to a certain CLR stored function crashes it when called in a certain way
@AaronBertrand I call it from a case that returns single value, but even if I didn't scalar would still be okay as it is documented to return first value of first column and ignore the rest. Edited in version.
Dec
21
comment Inline table-valued function vs inline sql
the overall shape is quite different -- please be more specific here.
Sep
6
comment CATCH block is triggered when it should not
@AlexKuznetsov Ultimately, errors that client must see are passed to the client. But there are errors that require internal work. Passing them to the client would imply relying on the client to do that work, and that breaks the incapsulation. The database is not an exclusive data storage only used by one application. Many types of clients can connect.
Sep
6
comment CATCH block is triggered when it should not
Makes sense to me. The context of a try block erroneously survives, so the error functions can return their values, and another consequence would be that an error brings execution into the handler of the erroneously survived try context.