8,135 reputation
2458
bio website sqlblog.com/blogs/paul_white/…
location New Zealand
age 43
visits member for 2 years, 2 months
seen 9 hours ago
stats profile views 1,380

Rockstar Blogger SQL ServerMVP

Blog: SQLblog.com Twitter: @SQL_Kiwi


16h
comment Is there any way to prevent the memo structure from being pruned?
@JonSeigel My answer is a qualified 'yes' to all of those :)
19h
comment How to get a row_number to have the behavior of dense_rank
Is there a reason you want to use CASE rather than IF or dynamic SQL?
Apr
30
comment Mysql float(13,3) not taking 10 digits
Don't use the non-standard syntax, use FLOAT or DOUBLE (alone) instead. The value 123456789 can be stored exactly in a DOUBLE but you should consider switching to a fixed-length type instead if that behaviour suits your needs better. [This question is not an exact duplicate of the one linked so I will not be voting for closure on that basis]
Apr
30
comment Key lookup partition pruning
@Amam A Key Lookup is always a seek. There is no partition elimination in your original example by the way - the query processor is just reporting that it touched 7 distinct partitions (3..9) while executing the query. None of the lookups accessed other partitions.
Apr
30
comment Can I move rows between partitions by updating the partition key?
-1 This answer is fundamentally wrong in all respects.
Apr
30
comment How to determine cause of runtime increase given two query plans with SpillToTempDb warning
A plan is not re-optimized if the estimates turn out to be wrong. Recompile can be a good option to get a plan for particular parameter values; it may also enable certain other optimizations that might be important in your case. It does add an overhead to each execution of course.
Apr
29
comment Add Contents in a Column and make them 0
Thanks @TravisGan - there's nothing special about a CTE though, one could equally well use a derived table. Also, this is just an alternative - the SUM OVER results in a segment spool which may or may not be as efficient as your original solution :)
Apr
19
comment How does SQL Server generate a query execution plan that adds up to 6,000%?
+1 And just want to emphasise that the estimated cost display problems are purely an SSMS issue - the costs used internally by the optimizer during exploration are (generally) correct and so the plan selected is 'trustworthy'.
Apr
4
comment Unexpected Table Scan with Parameterized LIKE
The crucial point is that the @Criteria variable might not contain a match specification that could possibly use the same index seek plan, so caching that plan would be unsafe.
Mar
22
comment Unable to run union in parallel on SQL Server
To answer your question about setting parallel execution at the union point, no, that is not how parallelism works in SQL Server
Mar
22
comment Database after restore process still display Restoring
This doesn't add anything to the accepted answer for this question, does it?
Mar
14
comment DELETE vs TRUNCATE
@StuartBlackler An extent is 8 pages. See msdn.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
Mar
11
comment Is there an execution difference between a JOIN condition and a WHERE condition?
possible duplicate of Is a join optimized to a where clause at runtime?
Mar
11
comment Script to Monitor MySQL Replication
Please consider adding some detail to this answer to improve it. As it stands, it is pretty much a link-only answer, see the FAQ for guidance.
Mar
11
comment Parallel Statistics Update
@SQLLearner Ha :) Actually, I didn't notice this question was so old when I answered it. Something magic somewhere bumps old unanswered questions from time to time - that must have just happened when I checked the active questions list. Anyway, all good.
Mar
10
comment Is it possible to make one column readonly?
Can you not just revoke update permissions on the column in question?
Mar
10
comment Is it possible to make one column readonly?
@PhilippM Defining the column as an expression in the view prevents it from being updated.
Mar
9
comment Write differences between varchar and nvarchar
If you use data compression (the lightweight row compression is enough) you will usually find nchar and nvarchar take no more space than char and varchar, due to Unicode compression.
Mar
8
comment Does this query make sense?
Can't you just change SELECT TOP (1) person_id to SELECT TOP (1) WITH TIES person_id...?
Mar
6
comment How to avoid deadlock while updating 2 rows in MySQL
This answer would be better if it explained the relationship between isolation levels and deadlock potential, and why you feel a change would help in the specific situation described in the question.