Questions tagged [cross-apply]
The cross-apply tag has no usage guidance.
41
questions
6
votes
2answers
255 views
Optimize extraction of json data via OPENJSON
I'm attempting to optimize extraction of values obtained from a REST API which returns json values in an array.
Here's an minimal, complete, and verifiable example that reflects exactly what I'm doing....
4
votes
1answer
154 views
Is there a more optimal way to create this view or stored procedure? Currently using a cross apply, but it's slow
I've updated this post with some test data.
I'm creating a report for my Movies database where I'd like for the end-user to be able to select movies of a certain genre. However, some movies have ...
4
votes
1answer
426 views
Add query hints to a CROSS APPLY [duplicate]
I need to apply a query hint (like NOWAIT or NOLOCK) to the CROSS APPLY in this query. How do I do it:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *
FROM sys.objects obj WITH (NOLOCK)
...
3
votes
1answer
1k views
Select with CROSS APPLY runs slow
I am trying to optimize the query to run faster.
The query is the following:
SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
CROSS APPLY (SELECT TOP 1 grp_id as gid
...
10
votes
1answer
456 views
Apply cardinality estimation problem in SQL Server
Now, I am faced with the problem of the logic of cardinality estimation that is not quite clear for me in a seemingly rather simple situation.
I encountered this situation at my work, therefore, for ...
-1
votes
1answer
218 views
"No column name was specified for column..." when aliasing results of CROSS APPLY
I have this table:
from which I create this View:
This view's CardId allows me to JOIN against the Card table with so I can retrieve the Count from any CardId. Here's my SQL:
SELECT * FROM (
...
10
votes
2answers
400 views
Microsoft SQL Server 2014 Nested From Query in Cross-Apply
When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances.
Bug reported to Azure Feedback Forum: https://feedback.azure....
18
votes
2answers
2k 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 ...
1
vote
1answer
395 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
1k 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
779 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
67 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
3k 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
641 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
1k 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
460 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
188 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
2k 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 ...
3
votes
1answer
5k 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
10k 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
1k 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
3k 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
103 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
158 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
142 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
2k 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
112 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
1k 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
4k 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
255 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
238 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 ...
8
votes
3answers
12k 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
@...
8
votes
2answers
16k 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 ...
42
votes
2answers
89k 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
582 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
1k 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
142 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
2k 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
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 ...
