APPLY Operator in SQL Server 2005

In SQL Server 2005, Microsoft introduced the APPLY operator. We have used that in some of our previous blog posts to demonstrate some code – you can read about those examples here and here. In this post, we will cover what this operator really does. The APPLY operator allows one to invoke a table valued function for each row returned by an outer expression query. Basically, SQL Server allows us to join table and table valued function in such a way that for each record returned from outer query, we can call table valued function to retrieve the data. This was not possible in versions prior to 2005 and was done using a derived table/view.APPLY operator can be used in two ways:

• CROSS APPLY: It is like an inner join. Returns only those rows from the outer query, for which table valued function returns some data.
• OUTER APPLY: Its behavior is similar to outer join. It returns all the rows from the outer query irrespective of data returned by table function. If table function returns data then column values pertaining to table function will be not null other wise it will contain null values.

Let us use the DMVs to illustrate this functionality. We would like to find a query text for our current batch. To find this out we need to use sys.dm_exec_requests dynamic management view in conjunction with sys.dm_exec_sql_text table function. First, we will see what is being returned by sys.dm_exec_requests. It returns lots of valuable information like command, session_id, database_id, transaction_id, sql_handle and many more but at this point we are interested only in session_id, commnad and sql_handle. Connect to appropriate database using SQL Server management studio.

SELECT r.session_id, r.command, r.sql_handle
FROM sys.dm_exec_requests r
GO

And abbreviated output is as shown below.

session_id command sql_handle
———- —————- ———–
18 TASK MANAGER NULL
19 TASK MANAGER NULL
23 TASK MANAGER NULL
52 SELECT 0×020000006435D33223D66E660138A9905F1ECD1136EEE3B2

In order to see the text for interested query, we need to invoke sys.dm_exec_sql_text table valued function which requires sql_handle as an input parameter. So here is the query in isolation to get the query text followed by an output.

SELECT text
FROM sys.dm_exec_sql_text(0×020000005097B03AC054C5C0E9C91CEE079B942A54BBDA7D);
GO

text
————————————
SELECT * FROM sys.dm_exec_requests;

We can combine both the queries and using CROSS APPLY operator, we can get the same results. Here is the query.

SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
GO

Output is as shown below.

session_id command text
———- —————- —————————————
52 SELECT SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

Since above query was the last one to execute, text column shows the text of the last query. CROSS APPLY returned only one row from outer query for which table valued function returned the data. Now let us change CROSS APPLY to OUTER APPLY and see the difference.

SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
GO

Here is the abbreviated output.

session_id command text
———- —————- —————————————
18 TASK MANAGER NULL
19 TASK MANAGER NULL
23 TASK MANAGER NULL
52 SELECT SELECT r.session_id, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

This time, the result set also includes the rows from the outer query for which table valued function returned the null value.

LOCATE function in DB2 LUW

We had discussed the CHARINDEX() function in the previous post. Similar to the INSTR (Oracle) and CHARINDEX (MS SQL Server) functions, DB2 has a LOCATE function to search for a string within another string. It returns the starting position of the search string if it finds it within the string, else it returns 0. LOCATE also takes three arguments:
LOCATE(<Search String>,<Actual String>, <Starting Position>)
<Search String> is the string to be searched.
<Actual String> is the string in which we need to look for search string.
<Starting Position> position from which search should start. If it is not given, search starts from position 1.

Let’s create a sample table and populate it with test data:

CREATE TABLE INPT_STATE
(
STATE VARCHAR(50)
)
– Tablespace Clause
;

INSERT INTO INPT_STATE VALUES(‘GEORGIA,USA’);
INSERT INTO INPT_STATE VALUES(‘TEXAS,USA’);
INSERT INTO INPT_STATE VALUES(‘CALIFORNIA,USA’);
INSERT INTO INPT_STATE VALUES(‘ALBERTA,CANADA’);
INSERT INTO INPT_STATE VALUES(‘ONTARIO,CANADA’);

Following is the SQL to find out state and country from the comma delimited string.

SELECT substr(state,1,locate(‘,’,state)-1) as State,
substr(state,locate(‘,’,state)+1, length(state)- locate(‘,’,state))) as Country
FROM inpt_state;

And here is the output.

State Country
—— ———-
GEORGIA USA
TEXAS USA
CALIFORNIA USA
ALBERTA CANADA
ONTARIO CANADA

CHARINDEX function in SQL Server

In of our previous blog post, we had discussed the INSTR function and how it can be used for finding out the string within another string in Oracle.  MS SQL Server has a similar function called CHARINDEX which can perform the same operation as the INSTR function.

It takes three arguments.

CHARINDEX(<Search String>, <Actual String>, <Starrt Position>)
<Search String> is the string or expression to be to be searched.
<Actual String> is the string or expression in which we need to look for search string.
<Starting Position> position from which search should start. If it is not given or negative or 0, search starts from starting position of <Actual String>

Let us create a table and populate it with sample data.

CREATE TABLE INPT_STATE
(
STATE VARCHAR(50)
)
– Filegroup Clause
GO

INSERT INTO INPT_STATE VALUES(‘GEORGIA,USA’);
INSERT INTO INPT_STATE VALUES(‘TEXAS,USA’);
INSERT INTO INPT_STATE VALUES(‘CALIFORNIA,USA’);
INSERT INTO INPT_STATE VALUES(‘ALBERTA,CANADA’);
INSERT INTO INPT_STATE VALUES(‘ONTARIO,CANADA’);

Following is the SQL to find city and state. You can run it from query analyzer.

SELECT substring(state,1,charindex(‘,’,state)-1) as State,
substring(state,charindex(‘,’,state)+1, len(state)- charindex(‘,’,state))) as Country
FROM inpt_state
GO

And here is the output:

State Country
—— ———-
GEORGIA USA
TEXAS USA
CALIFORNIA USA
ALBERTA CANADA
ONTARIO CANADA