Paul White

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

	8,135 reputation	bio	website	sqlblog.com/blogs/paul_white/…	visits	member for	2 years, 2 months
2458 badges		location	New Zealand		seen	9 hours ago

summary answers questions tags badges favorites bounties reputation activity

311 Comments

suggestions reviews revisions comments badges posts accepts all

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.