15,576 reputation
44693
bio website sqlblog.com/blogs/paul_white/…
location New Zealand
age 44
visits member for 3 years, 1 month
seen 6 hours ago

7h
comment Synchronization using triggers
@Yiping Yes, exactly the same. I used (0 - x) because unary minus can be easy to miss. $0 is just zero typed as money (the same type as the Amount column).
7h
comment Need help with SQL Server PIVOT
@jamauss row_number() over (partition by office_id order by <key>)
9h
comment Need help with SQL Server PIVOT
@jamauss Note that without a key in the base table to order by, there is no guarantee the settings values will be pivoted in any particular order per office_id, nor is there any guarantee that they will be arranged the same way on each output row.
1d
comment In SQL Server, should I force a LOOP JOIN in the following case?
@JohnnyM Hints exist for a reason. It is fine to use them where you have sound reasons for doing so. That said, I rarely use join hints because of the implied FORCE ORDER. On the odd occasion I do use a join hint, I often add OPTION (FORCE ORDER) with a comment to explain why.
1d
comment Sort warning tempDB spill
As @Kin mentioned before, we need table and existing index DDL to answer this very specific question, specifically. Please edit your question to include those details.
Apr
17
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.
Apr
15
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.
Apr
15
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 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.