SQL2005 learning notes apply operator

Time:2019-12-11

Use the apply operator to call a table valued function for each row returned by an external table expression that implements the query operation.
The table valued function is the right input and the external table expression is the left input.
By evaluating the right input, the calculation results of each row of the left input are obtained, and the generated rows are combined as the final output.
The list of columns generated by the apply operator is the set of columns in the left input, followed by the list of columns returned by the right input.
The left and right operands of the apply operator are table expressions.
The main difference between these operands is that the table valued function can be used for the right operand, and a column can be obtained from the left operand as one of the parameters of the function. The left operand can include a table valued function, but cannot take a column from the right operand as an argument.
Show me how to use the apply operator:

Copy codeThe code is as follows:
–Build a table
CREATE TABLE MyData
(
ids INT IDENTITY PRIMARY KEY,
Data NVARCHAR(1000)
)
go
–Insert test data
INSERT INTO MyData VALUES(”)
INSERT INTO MyData VALUES(‘a,b,c’)
INSERT INTO MyData VALUES(‘q’)
INSERT INTO MyData VALUES(‘i,p’)
GO
select * from MyData
go
–Query results
ids Data
1
2 a,b,c
3 q
4 i,p

Create a table to break down characters by commas, and return a row of data for each character broken down

Copy codeThe code is as follows:
create FUNCTION fun_MyData(
@data AS NVARCHAR(1000)
)
RETURNS @tem TABLE( id INT , value nvarchar(100) )
AS
BEGIN
select @data=isnull(@data,”)
if len(@data)=0
Return — character length is 0, exit
declare @id AS INT
select @id=1
declare @end AS INT
select @end = CHARINDEX(‘,’, @data)
while(@end>0)
begin
insert into @tem(id,value)
select @id,left(@data,@end-1)
select @[email protected]+1
select @data=right(@data,len(@data)[email protected])
select @end = CHARINDEX(‘,’, @data)
end
if len(@data)>0
begin
insert into @tem(id,value)
select @id,@data
end
RETURN
END

Start using the apply operator:

Copy codeThe code is as follows:
SELECT m.ids, f.*
FROM MyData m CROSS APPLY fun_MyData(data) f
go
— result
ids id value
2 1 a
2 2 b
2 3 c
3 1 q
4 1 i
4 2 p
SELECT m.ids, f.*
FROM MyData m OUTER APPLY fun_MyData(data) f
go
— result
ids id value
1 NULL NULL
2 1 a
2 2 b
2 3 c
3 1 q
4 1 i
4 2 p

We see that outer apply returns more result rows than cross apply.
This is a bit like the relationship between inner join and left outer join
In fact, there are two forms of apply: cross apply and outer apply.
Cross apply only returns the rows in the external table that generate the result set through the table valued function.
Outer apply returns both the row generating the result set and the row not generating the result set, where the value in the column generated by the table valued function is null.
The above is the solution of SQL2005. Let me demonstrate how SQL2000 can solve such a query:
The idea is: make a loop to link queries one by one.

Copy codeThe code is as follows:
–SQL2000 version
declare @ids int
select @ids =0
declare @data nvarchar(200)
select @data=”
–Define table variables to store data temporarily
declare @tem table(
ids int,
id int,
value nvarchar(100)
)
DECLARE test_cursor CURSOR FOR
SELECT ids, Data FROM MyData
OPEN test_cursor
FETCH NEXT FROM test_cursor
INTO @ids,@data
WHILE @@FETCH_STATUS = 0
begin
insert into @tem
select @ids,id,value
from dbo.fun_MyData(@data)
FETCH NEXT FROM test_cursor
INTO @ids,@data
end
CLOSE test_cursor
DEALLOCATE test_cursor
select * from @tem

The result is also obtained, but SQL2000 needs to use the loop, so the code is complex and the calculation is time-consuming.
Let’s make full use of the new weapon of SQL Server 2005: apply operator, which brings us a simple and quick way of operation