48

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


24

Summary SQL Server uses the correct join (inner or outer) and adds projections where necessary to honour all the semantics of the original query when performing internal translations between apply and join. The differences in the plans can all be explained by the different semantics of aggregates with and without a group by clause in SQL Server. Details ...


21

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


14

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


13

I read that this is not good practice because function is called 'zilion' times and it have bad impact on performance. While CROSS APPLY can be useful in some cases, I don't expect any difference in performance between calling the function in WHERE or CROSS APPLY in the specific case. If the table has a million rows (and columns C and D possibly a million ...


11

There are often several ways to derive a cardinality estimate, with each method giving a different (but equally valid) answer. That is simply the nature of statistics and estimations. You ask essentially why one method produces an estimate of 296.839 rows, while another gives 181.8 rows. Let's look at a simpler example of the same AdventureWorksDW2017 join ...


10

It is not possible to fully guarantee evaluating the view per row of the outer query, without using something that introduces a new T-SQL execution scope, for example a non-inline (multi-statement, BEGIN...END) table-valued function. This is pretty much the advice given in response to your previous question How to use merge hints to isolate complex queries ...


10

is this a bug in SQL Server? Yes, certainly, the 1 that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent rows. It looks like the same basic issue as looked at in detail by Paul White here. I executed your final query in dbfiddle (SQL Server 2019) and pasted the ...


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

The APPLY operator is a tabular operator in the T-SQL language. As the language itself, it is declarative and does not indicate in any way what is the physical implementation that the relational engine should be using to retrieve the data. This means that the correlation strategy between the input set and the applied function is not limited to nested loops. ...


8

It's easy with a numbers table. Since also qty cannot be more than 10, we only need a very small numbers table: CREATE TABLE numbers ( i int NOT NULL PRIMARY KEY ) ; INSERT INTO numbers (i) VALUES (1), (3), (5), (7), (9) ; We need only the odd numbers because the numbers of rows wanted in the result is half of qty (or about half). The query: SELECT ...


7

What you`re seeing is the XQuery implementation in SQL Server . Although XQuery uses its own parser and performs its own algebrarization during the query compilation stage, the results are combined and optimized together with the DML portion of the query, then combined into a single execution plan. SQL Server supports five different methods. value , exist ,...


7

The fact that you cannot obtain even an estimated execution plan for the query against the large table suggests that compilation is waiting on statistics creation (or update) for the nvarchar(max) column. When running against the smaller sample table, statistics creation completes quickly, so an execution plan is quick to create. I can reproduce your ...


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


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

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


5

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


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


5

You need better indexing, and a bit of a rewrite. Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition. SELECT grp_fk_obj_id, grp_name, grp_id FROM ( SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-...


5

You can't, as far as I'm aware. One might try things like: OPTION (TABLE HINT (sp, READUNCOMMITTED)) ...but this will fail for things that aren't real tables. Table-valued or OPENROWSET function 'sp' cannot be specified in the TABLE HINT clause. It would also fail because the desired hint is semantic-affecting. If the thing you need to access doesn't ...


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

The problem you're likely facing is around SARGability, namely using the LEFT function in your WHERE clause: LEFT(s2.RecordKey, 2) = a.Prefix With that in there, you're stuck running the function for every row and then comparing it. You can't index for that, as-is. Putting the transformation into a CTE, view, or derived table wouldn't help, nor would ...


4

I'd probably structure this query as below WITH ProductDetails AS (SELECT ProductSuperID, HasImage = MAX(CASE WHEN HasImage = 1 THEN 1 ELSE 0 END), StockBalance = Sum(StockBalance), HasPrice = CASE WHEN COUNT(*) = COUNT(Price) AND COUNT(*) = COUNT(DiscountPrice) THEN 1 ELSE 0 END FROM Product ...


4

It should be clear from the below SELECT WhatDoesThisDo.CardId FROM (VALUES (1), (2) ) WhatDoesThisDo(CardId) WhatDoesThisDo provides the table alias for the derived table defined by the VALUES clause. It then requires a comma delimited list of all column names (as there is no way of naming them inside the VALUES itself). In this case ...


3

The Cross Apply is being used in conjunction with the .nodes() XQuery call. This is basically getting every element <I></I> for the XML arrays constructed within the XMLTaggedList CTE statement. The outer .value() XQuery call then extracts that value and casts it to a VARCHAR(MAX), returning a record for each code within each group. I find it ...


3

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


3

The first query may run parallel by only one request to sql server. It fetched the all record and gives output based on filter criteria. But in case of second one it runs row by row and for each row Table2 will be scanned and appended to result. if your outer query has less record then Second one is better(OUTER APPLY). But if first query may get more data ...


3

This is a bug in the way SQL Server decides whether a rebind is required with certain plan shapes. It has been in the product since SQL Server 2005. Minimal repro This uses a trace flag to force a spool: CREATE TABLE #T (v integer NOT NULL); INSERT #T VALUES (1), (2), (3), (4); SELECT #T.v, A.* FROM #T CROSS APPLY ( SELECT #T.v UNION ALL ...


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 CROSS APPLY way of doing this would be: SELECT et.Payroll , v.Item , Tax = SUM(v.TaxValue) , Gross = SUM(v.GrossValue) FROM #EmployeeTaxes et CROSS APPLY ( VALUES ('FIT', fit, fit_Gross, 1) , ('Employee_FICA', Employee_FICA, Employee_FICA_Gross, 2) , ('Employer_FICA', Employer_FICA, Employer_FICA_Gross, 3) , ('...


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