Hot answers tagged

10

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


10

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

In the result, the value column must have a data type, like always. SQL Server determines the type using the rules for data type precedence (more precisely, the VALUES clause is a UNION, so types are matched there). In your first example, the precedence rules give a column of type float. In the second example, it is datetime. Solve the problem by ...


9

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


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;


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

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

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

Even with your first query the output order is in no way guaranteed. Unless a specific ordering is demanded via an ORDER BY clause the database is free to hand you the results in any order it see fit. For simple queries that can use an index it may look like the order is guaranteed because the output will be in the order of which ever index is used as the ...


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


2

The results from that query will not be scoped to particular database. They will show activity across the whole instance. In general, it would be quite difficult to provide information at this granularity, because queries can access data from more than one database (Azure SQL Database aside). In non-trivial queries, accounting for costs on a per-database ...


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

Not sure I get what you are want here but it could be that you can use a sort order value in your table expression and then use that in an order by clause. SELECT DISTINCT t.FirstName, t.LastName, v.VariableName, v.Value, v.Sortorder FROM @Table as t CROSS APPLY ( VALUES ('Variable1', t.Variable1, 1), ('Variable3', t.Variable3, ...


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