Paul White

8,425 reputation

2662

bio	website	sqlblog.com/blogs/paul_white/…
	location	New Zealand
	age	43
visits	member for	2 years, 3 months
	seen	3 mins ago
stats	profile views	1,474

Rockstar Blogger SQL ServerMVP

Blog: SQLblog.com Twitter: @SQL_Kiwi

	8,425 reputation	bio	website	sqlblog.com/blogs/paul_white/…	visits	member for	2 years, 3 months
2662 badges		location	New Zealand		seen	3 mins ago

summary answers questions tags badges favorites bounties reputation activity

319 Comments

suggestions reviews revisions comments badges posts accepts all

18h	comment	Why does selecting top 1 from composite index DESC also used to partition by month not select the top value? @MartinSmith Itzik Ben-Gan wrote a very clear explanation and workaround here
Jun 5	comment	Cross-product generates duplicate rows, how to unique/distinct the result? It would be helpful to add a brief description of why you need the result you do, together with a small set of sample data (and expected output). Given that, the question could be reopened.
May 30	comment	How can I achieve a unique constraint with two fields? @MartinSmith You seem to have an answer here that is masquerading as a comment ;c)
May 30	comment	PK as ROWGUIDCOL or use a separate rowguid column? This is essentially a link-only answer. These are discouraged because links can stop working. I couldn't see much in the link that answers the question, but summarizing the points you feel are relevant would help your answer attract up votes.
May 28	comment	query processor ran out of internal resources and could not produce a query plan -1 This is incorrect.
May 28	comment	Statistical Analysis of Data that has to be done in an order? The question is not amazingly clear, but if you need to filter rows before applying a window function, consider using an in-line table-valued function (parameterized view) instead of a view. Another option might be a user-defined aggregate. Perhaps adding a small example to the question would help make the requirement clearer?
May 28	comment	Failed to set releationship for queries As the question stands (though I have done my best to correct the wording and layout) this is not a real question. Add more detail, including the current SQL you have.
May 25	comment	What's the difference between a temp table and table variable in SQL Server? Extras for the next cumulative update: (1) temporary tables can be partitioned, table variables cannot. (2) CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection. Neither are very important, but they are differences.
May 24	comment	Is there any way to prevent the memo structure from being pruned? @JonSeigel Mail sent.
May 23	comment	Is there any way to prevent the memo structure from being pruned? @JonSeigel We could discuss it in chat or by email some time if you like.
May 23	comment	Is there any way to prevent the memo structure from being pruned? @JonSeigel My answer is a qualified 'yes' to all of those :)
May 22	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