Questions tagged [cross-apply]
The cross-apply tag has no usage guidance.
34
questions
16
votes
2answers
842 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 ...
0
votes
1answer
49 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
...
7
votes
1answer
154 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 ...
2
votes
1answer
214 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
...
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 ...
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,
[...
4
votes
1answer
279 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
1answer
447 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,
...
0
votes
0answers
303 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 |...
3
votes
2answers
151 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 ...
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 ...
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,
...
5
votes
1answer
5k 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 ...
1
vote
1answer
693 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 ...
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 ...
1
vote
1answer
97 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
2answers
92 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 ...
4
votes
1answer
130 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 ...
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
1answer
51 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],
[...
7
votes
1answer
806 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 ...
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),
...
3
votes
1answer
240 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)
...
8
votes
2answers
187 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 ...
6
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 ...
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
@...
6
votes
1answer
11k 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 ...
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 ...
34
votes
2answers
58k 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 ...
4
votes
1answer
568 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 ...
3
votes
1answer
899 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))
...
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
...
5
votes
1answer
998 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 ...
3
votes
1answer
2k 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 ...
