Tag Info

Hot answers tagged

9

But the execution plan for both is same as shown below: The plans are different. One is an inner join, the other is an outer join. The results may be the same in your simple test, but the semantics are different. In more complex queries, the difference may cause more obviously different execution plans, and come with a performance impact. There are ...


9

SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM ( SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)', 1, 2, '') FROM dbo.MyTable AS t ) AS x GROUP BY Val;


8

This behaviour is by design, as explained in detail on this Connect bug report. The most pertinent Microsoft reply is reproduced below for convenience (and in case the link dies at some point): Posted by Microsoft on 7/7/2008 at 9:27 AM Closing the loop . . . I've discussed this question with the Dev team. And eventually we have decided not to ...


7

Can anyone tell how exactly apply works and how will it effect the performance in very large data APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically ...


5

Execution plans (actual, not estimated) need to be added to the Q for a definitive answer but... How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans? Because, by your admission, they are not identical. Most likely explanation for the different execution plans is a variance in statistics. Table rows ...


5

You can do the recursion in a CTE from the top down carrying MarkupGroupID with you. with C as ( select P.PartGroupID, P.ParentID, P.MarkupGroupID from PartGroup as P where P.ParentID is null union all select P.PartGroupID, P.ParentID, coalesce(P.MarkupGroupID, C.MarkupGroupID) from PartGroup as P inner ...


5

Edit regarding fields having different types, not just decimal. You can try to use sql_variant type. I never used it personally, but it may be a good solution for your case. To try it just replace all [decimal](38, 10) with sql_variant in the SQL script. The query itself remains exactly as it is, no explicit conversion is needed for performing the ...


4

Here is another approach: SELECT di.name, di.date, x.field, x.oldValue, x.newValue FROM @diffInput AS di LEFT JOIN dbo.myTable AS mt ON mt.version = @version AND mt.name = di.name AND mt.date = di.date CROSS APPLY ( SELECT 'fieldA', mt.fieldA, di.fieldA WHERE NOT EXISTS (SELECT mt.fieldA ...


4

CROSS APPLY takes a table valued function and 'applies' parameters from each row in the query you are applying it to. The function is evaluated once for each row and the output is implicitly joined to the source row in the record set from which the parameters were obtained. Note that this 'join' can be 1:M - with a TVF one row at source can generate ...


4

This should be good up to about 2,500 values (depending on version): ;WITH x(n) AS ( SELECT TOP (@variableNumberOfIdsNeeded) (ROW_NUMBER() OVER (ORDER BY number)-1) * CONVERT(BIGINT, @SeqIncr) + CONVERT(BIGINT, @FirstSeqNum) FROM master.dbo.spt_values ORDER BY number ) --INSERT @newIds([NewId]) SELECT n FROM x; If you need more, or ...


2

Ok, here's my two pence: SELECT header.Col1, MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1, MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2, -- ... and so on.. MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99 FROM ( --- For each Col1, enumerate all the rows and return Col2 ...


1

My dilemma is about utility and advantages of using CROSS APPLY and CTE. Are there any or its just exotic? For small datasets the optimizer is probably not bothering with extensive analysis. However, if one were to look at competing plans for large data sets (say millions of Orders or Items from your example), then CROSS APPLY, especially if Items are ...


1

Seems a simpler approach: SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM ( SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '') FROM dbo.MyTable AS t ) AS x GROUP BY Val;



Only top voted, non community-wiki answers of a minimum length are eligible