15,346 reputation
44591
bio website sqlblog.com/blogs/paul_white/…
location New Zealand
age 44
visits member for 3 years, 1 month
seen 1 hour ago

2h
comment Index is not used with same Query on two Servers
Consider deleting this question?
2h
comment Creating Clustered Index uses all my tempdb space. Why?
Yes it has to sort somewhere, but for SORT_IN_TEMPDB = OFF as stated in the question, neither the sort runs nor the mapping index use tempdb. The point about uniquifiers for non-unique indexes is valid, but doesn't really answer the question either.
11h
comment SQL Agent still attempting replication for deleted publication
It's ok to mark your own answer to your own question as "the answer".
2d
comment Do I need to stop the service to back up the database?
@ShawnMelton Not necessarily. The question is not how to backup using each product, but whether stopping the services is required/sufficient to capture a consistent state using VSS. Whether this is advisable or not is certainly relevant, but interpreting the question as too broad does not necessarily make it so, IMO.
2d
comment Location of #th iteration of a character or a word in a sql text field
@ZacG You're welcome. Is that solution suitable, or do you need another option? If so, please edit your question to provide additional detail.
Apr
13
comment Variable numbers of parameters in a SqlClr UDF or Stored Proc
@JustinDearing "Pain" is far too small a world to describe the process. Even if you succeed, once you have an XP you've written yourself running inside the sqlserver.exe process space without managed code protections, you'll ascribe every future system weirdness to a bug in your XP (and you might well be right). I implore you not to go down this path. Find another solution, or change the design.
Apr
11
comment Making a recursive CTE like query, but for many records
Uh oh. NOLOCK on every table reference :)
Apr
11
comment Deadlock when update different rows with non-clustered index
@Bood I updated my answer to include your question about viewing lock sequences. Analyzing deadlocks in detail can be time-consuming and highly-skilled work. A certain level of deadlocking is often unavoidable; the application should be coded to cope with deadlocks anyway (e.g. recheck conditions and resubmit the transaction).
Apr
11
comment Deadlock when update different rows with non-clustered index
@Bood The two references I provided at the bottom of my answer describe the common deadlock patterns well. You clearly have lots of questions about locking behaviour; if you still have questions after reading that information, please ask a new question for each, providing a clear explanation and preferable a complete reproduction script for us to use. Broad questions that require a lot of clarification comments do not fit the Q & A model well. We like discussion in the chat rooms, but not on the main site. Thanks.
Apr
11
comment Getting Visual Studio to deploy a UDF to Return NVARCHAR(MAX)
Note to close-voters: this is a SQLCLR question with a SQL Server-specific answer. Seems on-topic here.
Apr
1
comment Is this a race condition or bug in sql server using serializable TIL with updlock?
This did not repro for me on 2012 (build 3393) or 2008 R2 (build 4286).
Mar
26
comment Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?
An Sch-M lock is not required for online build of a new nonclustered index, though it is required in all other cases. A new nonclustered index requires only a table-level shared lock during the final phase, same as was needed during the preparation phase. See this White Paper for details.
Mar
22
comment Connecting to MySQL database from Analysis Services
Your answer will attract more up-votes if it does more than provide a link to an external resource (that might disappear one day). Consider summarizing the main points and links here, then referring to your post for more detail. I would make this edit for you, but I am not a MySQL user.
Mar
22
comment Sort warning tempDB spill
@mustaccio A DISTINCT can also be implemented by Hash Match or Stream Aggregate. A Stream Aggregate might need a sort on any GROUP BY attributes, but a Hash Match does not.
Mar
2
comment Reduce DB size - SQL Server, SSMS
Do you mean 2008 R2 or 2012?
Feb
27
comment Is it completely pointless to use NOT NULL after DATE types?
It must have taken longer to write this question that it would have taken to test it on SQLFiddle :)
Feb
26
comment MAX from Subquery
First answers from new users go to a review queue automatically, with limited context. I suspect the down-voters regarded the answer as a low-quality single line of code answer, which is generally discouraged. I'm just guessing though. It is quite a poor question after all.
Feb
23
comment Cassandra - Query a column with collection type
+1 And a suggestion: consider editing the substance of "Zanson's comment" into the answer here.
Feb
22
comment Poor query performance
Just to be clear: what is the graph of exactly? RANGE_HI_KEY on the x-axis presumably, but what is on the y-axis? EQ_ROWS? RANGE_ROWS? The sum of those?
Feb
14
comment SQL Server shows database in recovery
Please add an answer rather than editing the question. You should also mark your answer as "the answer".