Questions tagged [cross-apply]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
54 views

Slow Cross Apply Query Needs Optimization

I have a stored procedure which takes about 4 min to complete. I added some logging and discovered that the reason it is slow is the Cross Apply queries, of which there are 5. Each one takes about 40 ...
FlyFish's user avatar
  • 111
0 votes
0 answers
793 views

How to use column names stored in another table? [duplicate]

I have a few tables in which I store some table names, column names, and some data. I need to get some column names from the content of these tables, and then use those names to create a bigger query. ...
entryton's user avatar
  • 135
3 votes
2 answers
3k views

Two OPENJSON, return each array element in separate rows

I have a table where I save in JSON format the quantity that exit and return of a product. The data saved in the two columns out_quantity and in_quantity is in array format. CREATE TABLE products_info ...
Mattia's user avatar
  • 33
6 votes
2 answers
2k 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....
Hannah Vernon's user avatar
  • 69.1k
5 votes
1 answer
320 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 ...
ck123's user avatar
  • 51
3 votes
1 answer
718 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) ...
Brent Ozar's user avatar
  • 42.5k
3 votes
1 answer
3k 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 ...
EtherPaul's user avatar
  • 133
11 votes
1 answer
768 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 ...
Павел Ковалёв's user avatar
-1 votes
1 answer
1k 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 ( ...
DharmaTurtle's user avatar
10 votes
2 answers
566 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....
Phist0ne's user avatar
  • 103
18 votes
2 answers
3k 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 ...
Paul White's user avatar
  • 80.3k
1 vote
1 answer
1k 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 ...
Roberto's user avatar
  • 119
7 votes
1 answer
2k 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 ...
crokusek's user avatar
  • 2,036
2 votes
1 answer
1k 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 ...
Marc.2377's user avatar
  • 187
1 vote
1 answer
72 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 ...
Marcello Miorelli's user avatar
6 votes
3 answers
4k 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, [...
Darren's user avatar
  • 175
4 votes
1 answer
822 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. ...
user3621633's user avatar
3 votes
1 answer
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, ...
philomathic_life's user avatar
3 votes
2 answers
283 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 ...
pvdjay's user avatar
  • 33
6 votes
3 answers
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 ...
Anthony Genovese's user avatar
3 votes
1 answer
7k views

Performance improvement Outer Apply

I have a query which runs little slower. SELECT b.BreakdownClassificationId, k.IsinCode, k.SedolCode, ClassificationDate, NAME, InstrumentType, ...
Pரதீப்'s user avatar
5 votes
1 answer
12k 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 ...
Keegan's user avatar
  • 53
1 vote
1 answer
2k 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 ...
Jeff.Clark's user avatar
4 votes
1 answer
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 ...
Jeff.Clark's user avatar
1 vote
1 answer
104 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 ...
Jeff.Clark's user avatar
1 vote
2 answers
319 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 ...
Jeff.Clark's user avatar
4 votes
1 answer
145 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 ...
Guest's user avatar
  • 41
0 votes
1 answer
3k 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 ...
Ruisf's user avatar
  • 1
0 votes
1 answer
273 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], [...
Alex Zhukovskiy's user avatar
7 votes
1 answer
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 ...
ΩmegaMan's user avatar
  • 399
1 vote
2 answers
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), ...
Muflix's user avatar
  • 1,039
3 votes
1 answer
268 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) ...
Magier's user avatar
  • 4,717
8 votes
2 answers
315 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 ...
ame's user avatar
  • 83
8 votes
3 answers
14k 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 ...
TheVillageIdiot's user avatar
3 votes
1 answer
2k 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 @...
Vaccano's user avatar
  • 2,454
8 votes
2 answers
17k 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 ...
Muflix's user avatar
  • 1,039
0 votes
1 answer
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 ...
James's user avatar
  • 3
43 votes
2 answers
111k 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 ...
Pratyush Dhanuka's user avatar
4 votes
1 answer
584 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 ...
MattyZDBA's user avatar
  • 1,913
3 votes
1 answer
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)) ...
Vaccano's user avatar
  • 2,454
0 votes
3 answers
143 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 ...
Vaccano's user avatar
  • 2,454
5 votes
1 answer
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 ...
Jeff Sacksteder's user avatar
3 votes
1 answer
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 ...
Stuart Blackler's user avatar