22,826 reputation
767138
bio website sqlperformance.com/author/…
location New Zealand
age 45
visits member for 3 years, 10 months
seen 8 mins ago

8h
comment How to add External data source into MySQL?
An ODBC data source to a MySQL database? Or from a MySQL instance to something else? Edit more details into the question please. This will avoid your question being put on hold for being unclear.
20h
comment Moving SSIS Packages to New Server
Wouldn't this be better as an edit to Bill's answer?
20h
comment This Extended Events session works on Dev but not on Pro. Why?
Please merge your accounts. The question is on hold because there aren't enough details to allow someone else to reproduce the issue. This leads to extended discussion and guesswork, which is not a good fit for this site. It might be a good question; it's just not on-topic here.
Jan
28
comment I have an Access 2013 split database. How can I save the data when the network is down?
Hello. Sorry your first question on this site has been placed on hold, but as the standard text above says, it's a bit light on detail. Please take the tour and read about how to ask a good question. Once the question has been edited and improved, the hold can be removed.
Jan
24
comment Optimization around static cursor use
Be careful changing cursor threshold because it will prevent allocation order scans
Jan
22
comment How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?
@predestination If you're asking about concurrency and locking, try Kalen's book (the PDF version is free).
Jan
22
comment How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2?
@predestination HOLDLOCK and SERIALIZABLE are synonyms. I use the latter because it is more descriptive. HOLDLOCK misleads people into thinking locks are just held longer.
Jan
22
comment SQL Server: grant select access to a user in a view and not in its tables
The essential point you are missing is how Ownership Chaining works. Cross-database ownership chaining is possible, but very rarely used because it is so complex and prone to introducing security holes.
Jan
19
comment On Prem SQL Server: Log shipping via Azure Blob Storage: Possible?
Have you seen this?
Jan
19
comment How should I (re) index the table while keeping the transaction log small?
By the way, DBCC DBREINDEX is deprecated. It's probably not the cause of your log growth issues, but should you still think about moving to ALTER INDEX - it is much more flexible as well.
Jan
18
comment ER diagram to tables
@MaxVernon Exam/course/homework questions are not automatically off-topic. There has been plenty of meta discussion about this e.g. here and here. This question seems on-topic to me.
Jan
16
comment Would it be better for query plans to be split by statement for re-usability?
@usr It's the reverse: the hash and cache keys are at the batch level because the unit of caching is the batch. There would be no extra parsing work at statement granularity because batches are already parsed and compiled into compiled plans (CP) with compiled statements (CStmt) as sub-structures. Etc.
Jan
14
comment Looking for a set-based solution for a rolling 30-day period
I generally use a SQLCLR procedure for this. If I get time tomorrow I might contribute an implementation as an answer, for posterity if nothing else.
Jan
14
comment Error when using views on a linked server
@RafaelPiccinelli Different statistics could easily cause the optimizer to choose a different plan option, yes. The problem could well come back in future though, so probably best to remove the SELECT, just in case.
Jan
13
comment How to use a subquery that returns multiple rows?
@RafaelPiccinelli The only thing worse than a link-only answer is a link-only comment! Please consider writing an answer based on the content of those links instead.
Jan
10
comment Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character?
In SQL Server, we have to use a Supplementary Characters (_SC) collation to handle this correctly. I have no idea if MySQL has SC collations though.
Jan
10
comment How can I make this query sargable?
@MartinSmith Try e.g. DECLARE @rv rowversion = NULL; SELECT @rv; or DECLARE @T AS table (rv rowversion); INSERT @T DEFAULT VALUES; SELECT * FROM @T AS T LEFT JOIN @T AS T2 ON T2.rv != T.rv;
Jan
10
comment How can I make this query sargable?
@MartinSmith rowversion NULL is 'semantically' varbinary(8) because it can store 0x, which is not possible in binary(8). It's not possible to store NULL in a rowversion, it is translated to 0x. Notice the documentation does not say rowversion NULL is equivalent to varbinary(8) *NULL* nor does it say the physical storage will be the same :)
Jan
10
comment Identity value jumps when restarting SQL Server
@MaxVernon That Q & A explains the cause, but that's not the question here, as I read it.
Jan
8
comment Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?
I would just add that it can be useful to name local temporary tables uniquely per procedure, because the procedure may be called from another procedure that has already created a local temporary table with the same name.