| 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 |

Blog: SQLblog.com Twitter: @SQL_Kiwi
|
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 |