Sql Server - Apply Operator Part- 1

Apply Operator  Part- 1

In SQL Server 2005 Microsoft a new operator was introduced that allows you to join a record set with a function, and apply the function to every qualifying row of the table (or view). This new operator is the APPLY operator. Technically, the operation performed is not actually a JOIN, but because it is used like a JOIN, it is frequently referred to as a joining operation. 

Before going to discuss APPLY operator. Lets first discuss about CROSS JOIN operator so that APPLY Operation feel like similar.


Cross Join Operator :- operator can be used to combine all the records in one data set to all the records in another data set. By using the CROSS JOIN operator between two sets of records you are creating what is known as a Cartesian product.

Example :-Suppose Table1,Table2 are two table, using cross join on them

Select * from Table1 CROSS JOIN Table2 

 Using CROSS JOIN , you can generate large record set. Suppose Table1 has 20 records and Table2 has 2 records ,resulting recordset will be 40 records

another example using sys objects of sql server

Select * from sys.tables CROSS JOIN sys.objectsCROSS JOIN sys.sysusers

another Example (Cross join like inner join )

  SELECT * FROM Product P CROSS JOIN SalesItem S WHERE P.ID = S.ProductID;

            SELECT * FROM Product P INNER JOIN SalesItem S ON P.ID = S.ProductID;

These two SELECT statements. The first SELECT statement uses the CROSS JOIN operator and then uses a WHERE clause to defined how to join the two tables involved in the CROSS JOIN operation. The second SELECT statement uses a normal INNER JOIN operator with an ON clause to join the two tables.

Performance Consideration

he CROSS JOIN operator can be a great solution for identifying a result set across all the possible combinations of two sets, like all the sales for all customers for each month, even when for some months some customers have no sales. When using the CROSS JOIN operator, you should try to minimize the size of the sets being cross joined if you want to optimize performance. 


So CROSS JOIN operator produces a Cartesian product between 2 or many recordset(s) and generate a enlarge resultset.But Whenever we use this CROSS JOIN Operator , the performance down for an application .we require to focus to generate less number of records in recordset. So that Code execute quickly and application will save bandwidth of the user & as well as time of user.

in this post , we go through CROSS JOIN , next post will update regards APPLY Operator

Brief:- the APPLY Operator work with the Tabular function to generate record set. Its mostly used in higher level search development and in  comprehensive reports.

Thanks
Sunit Kanyan
technocrats@sunitkanyan.in
#SqlServer #ApplyOperator #CROSSJOIN