I need to see two small scenario when people should use CROSS APPLY and OUTER APPLY.
Please discuss the scenario with code and example.
–> My Answer:
CROSS APPLY acts like an INNER JOIN, and OUTER APPLY acts like a LEFT OUTER JOIN.
–> The APPLY clause (irrespective of CROSS/OUTER option) gives you flexibility to pass table’s columns as parameters to UDFs/functions while Joining while that table. It was not possible with JOINS. The function will execute for each row value passed to the UDF as parameter. But the JOIN works as a whole set.
Check the blog post on CROSS APPLY vs OUTER APPLY operators, https://sqlwithmanoj.com/2010/12/11/cross-apply-outer-apply/
–> Apart from this you can also use APPLY clause with Tables/SubQueries, like if you want to get top 5 products sold by sales persons, or get top 10 populated Cities from all States.
Check here: Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived-Tables/Sub-Queries & XML data, https://sqlwithmanoj.com/2012/01/03/using-cross-apply-outer-apply-operators-with-udfs-derived-tables-xml-data/
–> Answer from Russ Loski:
First let’s start with the use for APPLY.
You would use APPLY if you need to use a column from a table as an argument in a derived table or function. For example, this query from http://blog.sqlauthority.com/2009/08/21/sql-server-get-query-plan-along-with-query-text-and-execution-count/:
SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st --WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
I need to use columns from dm_exec_cahced_plans to pass to two functions to get rows from those functions. I have to use the APPLY keyword (rather than Join) to be able to do that.
I can do the same with a derived table:
SELECT * FROM TableA outer apply ( SELECT * from TableB where TableA.id = TableB.id ) tb2
That is a horrible example (you can do the same using standard join syntax). But there are very rare circumstances where I need to use a column in a where clause in a derived table, but I can’t use a join.
The difference between CROSS APPLY and OUTER APPLY is the difference between INNER JOIN and OUTER JOIN. CROSS APPLY will only return rows where there is a row in both the first table and the second table/function, while OUTER APPLY returns a row if there is a row in the first Table even if the second table/function returns no rows.
I have a stored procedure which steps through a list of transaction log backups and applies them to a DR database with NORECOVERY. As part of our DR test process, it is sometimes possible that the DR database gets ahead of the transaction log list. In this circumstance I get the error:
Msg 4326, Level 16, State 1, Line 8
The log in this backup set terminates at LSN 74000000023300001, which is too early to apply to the database. A more recent log backup that includes LSN 74000000025200001 can be restored.
Msg 3013, Level 16, State 1, Line 8
RESTORE LOG is terminating abnormally.
This is fine and I’m happy to ignore this log and move on to the next one. However there appears to be no way to catch the 4326 error. Wrapping the restore in a TRY … CATCH only identifies the 3013 error number and that could be caused by many issues that I don’t want to ignore. If my TRY … CATCH uses the THROW command, both the 4326 and 3013 errors are displayed so my session clearly has a handle to them both.
My questions are:
1. Can I catch the first error thrown?
2. Can I review all the errors thrown?
3. Can the output from THROW be captured in a variable so I can parse it?
While using RAISERROR it will only catch the last error thrown, it won’t catch and return all the errors.
The new THROW keyword introduced in SQL Server 2012 returns all the errors, check this link: https://sqlwithmanoj.com/2015/02/04/capture-multiple-errors-in-try-catch-by-using-throw-statement/
But I don’t think if there is any mechanism to store both the errors returned by any script as after THROW the execution ends and control is transferred to the client.
Other than using DBCC OUTBUFFER(@@spid), you will need to parse the multiple error details spread through several rows.
Running 2016 CTP3.
I would appreciate your personal comments/experience more than links.
External Tables in SQL Server 2016 are to setup the new Polybase feature with SQL Server.
With this new feature (Polybase) you can connect to Azure blog storage or Hadoop to query non-relational data from SSMS and integrate with SQL Server relational tables.
You can check some details here, where I’ve : https://sqlwithmanoj.com/2015/05/29/installing-sql-server-2016-ctp2-and-new-features-in-ssms/
Check here how to setup Polybase: https://msdn.microsoft.com/en-IN/library/mt163689.aspx
“SELECT *” or “SELECT 1” or “SELECT column_Name” while using EXISTS() – what shoud I use? – MSDN TSQL forum
Please tell me which method is good and fast
1) select a,b from t1 where exists (select 1 from t2 where t1.t1id = t2.t1id )
2) select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id )
3) select a,b from t1 where exists (select t2.t1id from t2 where t1.t1id = t2.t1id )
–> My Answer:
It does not matter what you use with SELECT inside EXISTS () statement
Because, as soon as EXIST() statement get one row it breaks/exits, and it just ignores the column’s list in SELECT clause.
You can check by this by simple example:
select a,b from t1 where exists (select 1/0 from t2 where t1.t1id = t2.t1id)
The “SELECT 1/0” ideally should give divide-by-zero error, but here inside EXISTS() it does not.
So, I use “SELECT *” happily within EXISTS() clause 🙂 like:
select a,b from t1 where exists (select * from t2 where t1.t1id = t2.t1id)
–> Answer By CELKO:
The optimizer is smart enough to figure out the exist () predicates are the same. However, this was not always true in early versions of SQL. Oracle originally liked using a constant or an actual column name, because it is it did not have a good optimizer. The original SQL–86 specs implied (but did not require) that the table be materialized. We really were not very smart in those days.
Today, for stylistic reasons and to make searching easier, we prefer exists (SELECT * FROM ..) In code. This makes it easier to locate the exist predicates with this text search, and shows that the operation is performed on an entire table rather than a single column.
SQL is much like a natural language; you can be understood if you speak with poor grammar, but people respect you more if you have good grammar and style.