46

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


20

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


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

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


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

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


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) , ('...


2

It's a little unclear what you're after, but one way to add in missing values into a GROUP BY query is to add all rows to your starting table with 0 or NULL for the aggregated columns. As @RDFozz pointed out, you want to use NULL if you are doing COUNT aggregates because otherwise the results will be inflated. However, 0 is a good choice if you only do SUM ...


2

If you're looking for MAX(Modified) field over ProductNumber, you can use ROW_NUMBER() function, and then get all rows where row number = 1. WITH selMax AS ( SELECT ID, ProductNumber, DateCreated, Modified, ROW_NUMBER() OVER (PARTITION BY ProductNumber ORDER BY Modified DESC, ...


2

An addendum to John Eisbrener's answer: As far as the ExtractedDiagnosisList(X) part, it might help some of us if there as a space in it: ExtractedDiagnosisList (X) From the SQL docs, derived tables (and rowset functions, and @variable. function_calls) allow you to not only specify a table alias, but a list of column aliases for the columns returned in ...


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