APPLY - 1
-
Nice question. I have only used Apply once or twice. I'd like to see more questions about this operator.
-
Thanks Ron.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Hugo Kornelis (9/6/2012)
Narud (9/6/2012)
I think that is because CROSS APPLY is more like not use explicit joins, as in SQL ANSI '92.I'd put it differently. The only difference between CROSS APPLY and INNER JOIN is that CROSS APPLY can be followed by a subquery or by a table-valued function that references data from the other table.
Wow! Your words have been a big revelation for me. I didn't find a practical application for CROSS APPLY until today, but now I've seen the light, and this is just what I needed for a development in which I'm working now.
It's great to get the right words at the right time. Thanks Hugo!
-
Hugo Kornelis (9/6/2012)
Rewriting them with APPLY makes them valid:FROM Table1 AS t CROSS APPLY (correlated subquery) AS s ON s.Col1 = t.Col1FROM Table1 AS t CROSS APPLY dbo.MyFunction(t.SomeColumn) AS f ON f.Col1 = t.Col1Umm, Hugo? CROSS APPLY cannot have an ON clause. The code above will fail with "Incorrect syntax near the keyword 'ON'.
For the first, the correlated subquery has the ON clause. For the second, there is no ON clause because the function takes the column as a parameter.
-
sknox (9/6/2012)
Hugo Kornelis (9/6/2012)
Rewriting them with APPLY makes them valid:FROM Table1 AS t CROSS APPLY (correlated subquery) AS s ON s.Col1 = t.Col1FROM Table1 AS t CROSS APPLY dbo.MyFunction(t.SomeColumn) AS f ON f.Col1 = t.Col1Umm, Hugo? CROSS APPLY cannot have an ON clause. The code above will fail with "Incorrect syntax near the keyword 'ON'.
For the first, the correlated subquery has the ON clause. For the second, there is no ON clause because the function takes the column as a parameter.
Ouch!!!!
You are totally right. Time to edit my post.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Ron - thanks for the great question. Heck, thanks for all the great questions that you do!
Hugo - thanks for the great clarifications / explanations that you do so frequently with the QOTDs. Things just wouldn't be the same without you on these every day, providing clarifying answers / examples... and great QotD's yourself.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (9/6/2012)
Ron - thanks for the great question. Heck, thanks for all the great questions that you do!Hugo - thanks for the great clarifications / explanations that you do so frequently with the QOTDs. Things just wouldn't be the same without you on these every day, providing clarifying answers / examples... and great QotD's yourself.
+1
-
WayneS
Hugo - thanks for the great clarifications / explanations that you do so frequently with the QOTDs. Things just wouldn't be the same without you on these every day, providing clarifying answers / examples... and great QotD's yourself.
+1
-
Narud (9/6/2012)
Hugo Kornelis (9/6/2012)
Narud (9/6/2012)
I think that is because CROSS APPLY is more like not use explicit joins, as in SQL ANSI '92.I'd put it differently. The only difference between CROSS APPLY and INNER JOIN is that CROSS APPLY can be followed by a subquery or by a table-valued function that references data from the other table.
Wow! Your words have been a big revelation for me. I didn't find a practical application for CROSS APPLY until today, but now I've seen the light, and this is just what I needed for a development in which I'm working now.
It's great to get the right words at the right time. Thanks Hugo!
It's great when that happens, isn't it.
Would you mind sharing what problem you had that was solved by CROSS APPLY? I haven't had a chance to use it, either, and I'm wondering what some of the practical uses are.
-
Olga B (9/6/2012)
Would you mind sharing what problem you had that was solved by CROSS APPLY? I haven't had a chance to use it, either, and I'm wondering what some of the practical uses are.CROSS APPLY may be useful when you want to "aggregate" strings. For example, someone wants to see the list of all indexes in a database, including the key columns of those indexes.
Here is a query with CROSS APPLY in it:
select schema_name(o.schema_id) as schema_name,o.name as table_name,
i.name as index_name,
t.index_columns
from sys.indexes i
inner join sys.objects o on o.object_id = i.object_id
cross apply
( select index_columns =
( select case when ic.index_column_id = 1 then c.name else ', ' + c.name end
from sys.index_columns ic
inner join sys.columns c on c.object_id = ic.object_id and c.column_id = ic.column_id
where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.key_ordinal > 0
order by ic.index_column_id
for xml path('')
)
) t
where i.index_id > 0 and ObjectProperty(i.object_id, N'IsUserTable') = 1
order by schema_name(o.schema_id), object_name(i.object_id), i.name;
Please ask questions if something's not clear in this query 🙂
-
Olga B (9/6/2012)
It's great when that happens, isn't it.Would you mind sharing what problem you had that was solved by CROSS APPLY? I haven't had a chance to use it, either, and I'm wondering what some of the practical uses are.
With pleasure, I have to make a report of taxes payments by fiscal period, financial entity, and tax type. There is a particular case in which the payment amount is obtained with a table-valued function because it's used to create the accounting entry too. So that, instead of use a cursor to be able to pass the value fields as parameters for the function and do the update of the corresponding record one-by-one, or create a new scalar-valued function to obtain only the payment amount, I have used the good tip of Hugo Kornelis:
declare @tbl as table (IdBalance int not null, IdFinancialEntity int not null
, IdTax int not null
, IdFiscalPeriod int not null
, Amount numeric (18, 2) null)
insert @tbl (IdBalance, IdFinancialEntity, IdTax, IdFiscalPeriod)
select b.IdBalance, b.IdFinancialEntity, b.IdTax, b.IdFiscalPeriod
from TBalance b
update @tbl
set Amount = g.Amount
from @tbl a
cross apply
(select Amount
from dbo.FnGetAccountingEntry(a.IdBalance, a.IdTax, a.IdFinancialEntity)) as g
where a.IdTax = 3
-- Process for other's taxes types.
-- Data source for the report
select IdBalance, IdFinancialEntity, IdTax, IdFiscalPeriod, Amount
from @tbl
-
Wooops....amazing question Ron!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Good question!Really like it.
Thanks for sharing.:-P
-
I'm loving the last QoTDs so far.
No catch, no hidden bug or limitation.
Just normal behavior.
I agree with Hugo. APPLY is a gem that is not known by many and that can make our jobs easier.
Looking forward to part #2.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply