Questions tagged [cross-apply]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
37
votes
2answers
63k views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
17
votes
2answers
1k views

CROSS APPLY produces outer join

In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER (): SELECT * FROM #MyTable AS mt CROSS APPLY ( SELECT ...
8
votes
2answers
195 views

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo ...
7
votes
3answers
9k views

No advantage of using Cross Apply or CTE over inline sub-query

I came across a query like this: SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C] FROM Items i I changed it to following ;WITH cte_count AS ( SELECT COUNT(1) c, OrderId FROM ...
7
votes
1answer
874 views

Cross Apply Fails When DateTime Added

In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a ...
7
votes
1answer
12k views

CROSS APPLY on Scalar function

I have this: SELECT A ,B ,dbo.Func(C) ,dbo.Func(D) ,dbo.Func(E) ,F FROM abcdef WHERE 0 = dbo.Func(C) + dbo.Func(D) I have read that this is not good practice because the function is called million ...
7
votes
1answer
353 views

How to get cross apply to operate row by row on a view?

We have a view that is optimized for single item queries (200ms no parallelism): select * from OptimizedForSingleObjectIdView e2i where ObjectId = 3374700 It also works on small sets of ...
6
votes
3answers
1k views

How does this derived table with cross apply work?

I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is ...
5
votes
3answers
2k views

Using CROSS APPLY OPENJSON causes Azure to hang

I have a table with around 8 million rows with a schema of: CREATE TABLE [dbo].[Documents]( [Id] [uniqueidentifier] NOT NULL, [RemoteId] [int] NOT NULL, [Json] [nvarchar](max) NULL, [...
5
votes
1answer
1k views

Build a three table join with a recusive table in the middle?

I have three relevant tables: Parts, PartGroup, and MarkupGroup. Parts is simple. PartID artificial primary key Part part number PartGroupID Foreign key sample data: 1 ...
5
votes
1answer
6k views

Using CROSS APPLY with GROUP BY and TOP 1 with duplicate data

I have a table that contains Status information about Product items over time. Each row has a Modified DATETIME. I want to get the latest Status row, using the MODIFIED field, for each ProductNumber ...
4
votes
1answer
133 views

Does the apply operator use row by row processing?

I'm working with 2 of the same datasets. However, one is derived from a temp table and the other is from a derived table (cross apply operator). The derived table takes much longer to process and it ...
4
votes
1answer
572 views

How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?

Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
4
votes
1answer
1k views

While using Cross Apply, can I force “empty” records to appear?

I have a query that is working fantastically, except that I would like to force some records to appear. I am using SqlServer 2016, and this query will ultimately be used in a VIEW and used by SSRS ...
4
votes
1answer
324 views

Convert columns into rows for use with indexed/materalized view

I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. ...
3
votes
2answers
154 views

Return batches of rows based on one row

Say I have a table like this in SQL Server 2008: id | name | qty ------------------- 1 | john | 1 2 | bill | 3 3 | mary | 2 4 | jill | 5 I would like to query this ...
3
votes
1answer
928 views

Cross Apply without a function

I would like to run a query very similar to this on one of my production databases: -- Setup the table (Already there in my real scenario) create table dbo.MyTable(MyValue int, MyGroup varchar(5)) ...
3
votes
1answer
1k views

Insert a range of numbers from a call to sp_sequence_get_range

Given the following code: CREATE SEQUENCE dbo.NextTestId AS [bigint] START WITH 10 INCREMENT BY 2 NO CACHE GO DECLARE @variableNumberOfIdsNeeded INT = 7, -- This will change for each call @...
3
votes
1answer
566 views

Speed Up Cross Apply Without Index Hint

I have a very small table with 12 rows in it that can be created with the following statement: CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY, ...
3
votes
1answer
3k views

CROSS APPLY-ing a parameterised function

Given the following definitions: CREATE TABLE MyTable ( col1 int, col2 int, col3 int, col4 int ) CREATE FUNCTION fn_MyFunction ( @Param1 int, @Param2 int ) RETURNS TABLE AS ...
3
votes
1answer
243 views

Translate query using APPLY to run under compatibility level 80

I want to use the following query: SELECT SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ...
2
votes
1answer
277 views

Refactoring subquery to JOIN and CROSS APPLY, get only one row for each record in parent table

Given the following query: SELECT p.ProductName, CASE WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1) THEN 1 ELSE 0 END ...
2
votes
1answer
2k views

Performance improvement Outer Apply

I have a query which runs little slower. SELECT b.BreakdownClassificationId, k.IsinCode, k.SedolCode, ClassificationDate, NAME, InstrumentType, ...
1
vote
2answers
2k views

How to keep row ordering with DISTINCT command or how to add row number to cross apply

I have problem that after distinct, order direction of Variable column change. Here is example Data Source DECLARE @Table TABLE ( FirstName NVARCHAR(255), LastName NVARCHAR(255), ...
1
vote
2answers
98 views

TSQL help my feeble mind grasp Pivot/Unpivot on two columns

I've been trying to wrap my feeble mind around this one for some time, but I am having difficulty. I have looked at a few other examples like this SE.DBA answer, but I am having difficulty applying it ...
1
vote
1answer
810 views

Issue with SUM OVER PARTITION BY in conjunction with GROUP BY

I am getting a typical group by error that one would see when a field is not in a group by or an aggregate in the select. Column 'v.EDTAmount' is invalid in the select list because it is not ...
1
vote
1answer
98 views

Where and how to add selective totals in SSRS

I need to get some totals output in SSRS that I am having difficulty putting together. I am not sure if I should do the totals for the below query within the query, or in SSRS. The output is going to ...
1
vote
1answer
61 views

is there a way to improve this query by not re calculating functions? cross apply?

when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures. this is the picture from the ...
0
votes
1answer
2k views

Pivoting some row data into columns (SQL Server 2008 R2)

Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another. Full-Disclosure: I did ...
0
votes
1answer
55 views

Refactor SQL to left join on TVF requiring input variable

I would like to join two tables and a table-valued function. The TVF take a date as input variable. The expected output is: Date, LimitName, Exposure, ProductValueEligible, ProductValueNonEligible ...
0
votes
1answer
1k views

CROSS APPLY query with CASE and running totals [closed]

I'm trying to make this business logic make sense, but I'm not having any luck. The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after ...
0
votes
3answers
137 views

How to convert query results into groups of common results

I have a query that returns results like this: MyValue MyGroup _______________|_______________ 56 | I _______________|_______________ 12 | I ...
0
votes
0answers
358 views

Using clustered columnstore indexes in conjunction with non-clustered columnstore indexes

Table A has approximately 500 million records in it and is of the form similar to the following | ID | FirstName | LastName | MiddleName | ------------------------------------------ | 1 | John |...
0
votes
1answer
52 views

How to use reference to another table in aggregate function

I have following easy-to-explain task: we have a table, and we should calculate total count of rows grouped by columns. Column number is fixed so we write: SELECT [GroupId] = cr.[GroupId], [...