I normally create plan guides by first constructing a query that uses the correct plan, and copying it across to the similar query that doesn't. However, that is sometimes tricky, especially if the query is not exactly the same. What is the right way of creating plan guides from scratch?
SQLKiwi has mentioned drawing up plans in SSIS, is there a way or useful tool to assist in laying out a good plan for SQL Server?
The specific instance in question is this CTE: SQLFiddle
with cte(guid,other) as ( select newid(),1 union all select newid(),2 union all select newid(),3) select a.guid, a.other, b.guid guidb, b.other otherb from cte a cross join cte b order by a.other, b.other;
Is there ANY way to make the result come up with exactly 3 distinct
guids and no more? I'm hoping to be able to better answer questions in future by including plan guides with CTE-type queries that are referenced multiple times to overcome some SQL Server CTE quirks.