The T-SQL APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY acts as a replacement for an INNER JOIN and only returns rows if there is a match on the join condition. Using CROSS APPLY in some instances will perform better than an equivalent JOIN statement. It is similar to a `CROSS JOIN LATERAL` in the SQL standard
Questions tagged [cross-apply]
358 questions
0
votes
0 answers
Including Actual Query Plan Explodes Execution Time
I'm using SQL Server 2005 and I've got a fairly complex query which is taking a second or two to execute.
I wanted to debug performance using the query plan, but when I include the actual execution plan in the output, the query goes from taking 2…
RMD
- 3,421
- 7
- 39
- 85
0
votes
1 answer
cross apply in sql with sum
I have the following query:
insert into [MyDB].[dbo].[Reports_ActivityStat] (ActivityID,TaskID,LS_HowOthersAnswered,LS_ImproveMyChances)
(
SELECT
ActivityID=tasks.ActivityID,
TaskID=tasks.ID,
CAST(
CASE
WHEN…
Oren A
- 5,870
- 6
- 43
- 64
0
votes
2 answers
Cross Apply of UDF missing Results
I am performing a SQL query similar to the following
SELECT fn.FullName, pn.LastName, pn.FirstName, pn.MI
FROM Source.dbo.tblPerson fn
cross apply dbo.ParseFullName(fn.FullName) pn
the result looks fine for the first 85 rows the 86th+ always…
TimN_FL
- 67
- 9
0
votes
2 answers
Invalid obj name in stored procedure
I have a stored procedure where I am using cross apply to get a field "tagtext" from a table and then put each tagtext together as a entry in a new field called Tags in another table. I'm not to keen on how cross apply works though, and seem to be…
xxyyxx
- 2,306
- 3
- 24
- 34
0
votes
1 answer
Create indexed view in SQL Server 2008
CREATE TABLE Storage
(
ID INT IDENTITY(1,1) PRIMARY KEY,
data XML NOT NULL
)
GO
INSERT INTO Storage(data)
VALUES('
Liverpool
Anfield …
user1320771
-1
votes
1 answer
Add new variable in where clause with cross apply
Created a new date variable in select statement, i need to add this new variable to the where clause as below;
SELECT a.bim, a.cim, b.Block,
(datename(dw, a.start_date)+ ', ' + convert (varchar(20),a.start_date,107) ) as mmm
…
user22168985
- 1
- 1
-1
votes
1 answer
Why is CROSS APPLY resulting in one less row?
I split some values (REPAY_AMOUNT and REPAY_REF) which are delimited special character and cross applied them as follows:
SELECT
ARRANGEMENT_ID,
REPAY_AMOUNT,
REPAY_REF,
CAST(RIGHT(RR.Value,8) AS DATE) 'Split Date',
…
Nauman Khan
- 7
- 3
-1
votes
1 answer
Count on CROSS APPLY with JSON Data
I have the following DB structure:
ID, Datetime, JsonData
Within JsonData there is a field called party which is "," delimited and another field called Source.
I'm trying to run a simple select where I get the data grouped by (Source,…
user1203996
- 89
- 1
- 11
-1
votes
2 answers
SQL query to unpivot and union for multiple column
I'm trying to achieve the following result...
input, table A
-----+--------+--------+-----+-------+----------+----------+---------+
sub | c_f | type | F_G | layer | dec_2020 | jan_2021 |…
Maurip00
- 39
- 4
-1
votes
1 answer
Performance Issue with cross apply while reading XML nodes for large dataset
Performance issue with XML cross apply:
DataTable has 1300 entries and the field xmldata has 250 nodes, so the query is running 1300 * 250 times to brings the output and the execution times takes a while.. about an hour to generate 325000 rows.…
RMN
- 1
-1
votes
1 answer
Using STUFF Function
I've this table TableA that have these fields: [intIdEntidad],[intIdEjercicio],[idTipoGrupoCons]. The tableA look like for idTipoGrupoCons = 16 this image
enter image description here
I'm trying to use STUFF function to show the column…
-1
votes
3 answers
Cross apply / Joining two column when left column does not appear with right column
Imagining I have the following table:
We can see that Tuna was not served in Month 1.
I would like to make it appear also in Month 1.
How can I achieve this?
Thank you !
wr_lcb_ck
- 85
- 2
- 9
-1
votes
1 answer
Getting dates for each user - even if no activity in between
I have a problem, I feel like I know this but I cannot find a solution.
I have a temp table where I generated a date range.
TempTable
Date
11/1/2017
11/2/2017
11/3/2017
11/4/2017
11/5/2017
Need to join to this table:
Date User …
Martin H
- 100
- 7
-1
votes
1 answer
How to get multiple records combined to one using row number?
How can I get multiple alert records (up to 10 per student) to appear on one record per student? I'm attempting to use row number to number the records and then place them in the output, but the code I have written does not work due to incorrect…
Bill G
- 19
- 2
-1
votes
1 answer
SQL Server : select count of one column, while checking for distinct value in another
I'm trying to get the count of a certain column (sch1.[key]), while also making sure another column has distinct values (p.[ref]). Is there an easy way to accomplish this with SQL Server?
I think the answer lies somewhere in joining the [test] and…
noot
- 103
- 1
- 11