Table-Valued Functions have been around since SQL Server version 2005. Basically a Table-Valued Function is a function that returns a table, thus it can be used as a table in a query.
Creating a Simple Table-Valued Function with (some kind of) Logic
First, let's create a small table to store some data:
CREATE TABLE TrackingItem (
Id int NOT NULL IDENTITY(1,1),
Issued date NOT NULL,
Category int NOT NULL
);
CREATE INDEX X_TrackingItem_Issued ON TrackingItem (Issued);
And then add few rows for test data:
INSERT INTO TrackingItem (Issued, Category) VALUES ( DATEADD( day, 0, GETDATE()), 1);
INSERT INTO TrackingItem (Issued, Category) VALUES ( DATEADD( day, 1, GETDATE()), 2);
INSERT INTO TrackingItem (Issued, Category) VALUES ( DATEADD( day, 4, GETDATE()), 1);
INSERT INTO TrackingItem (Issued, Category) VALUES ( DATEADD( day, 4, GETDATE()), 2);
Now, if we would need a result set which would:
Include all the columns from TrackingTable
Include an extra Modified (date) column
Not have even numbers in Category
The Modified-column indicates when the changes into the data have been made
Return only TrackingItem-rows having the Id greater than or equal to the parameter passed
The Table-Valued Function could look like this:
CREATE FUNCTION TrackingItemsModified(@minId int)
RETURNS @trackingItems TABLE (
Id int NOT NULL,
Issued date NOT NULL,
Category int NOT NULL,
Modified datetime NULL
)
AS
BEGIN
INSERT INTO @trackingItems (Id, Issued, Category)
SELECT ti.Id, ti.Issued, ti.Category
FROM TrackingItem ti
WHERE ti.Id >= @minId;
UPDATE @trackingItems
SET Category = Category + 1,
Modified = GETDATE()
WHERE Category%2 = 0;
RETURN;
END;
The function defines a new table called @trackingItems. This is a temporary table stored in the tempdb. The contents of this table will be return value for the function when the function exits.
First, the function inserts all the desired rows from the TrackingItem-table to the temporary table. After that, the contents of the temporary table are modified based on the specifications and then returned to the caller.
Using the Function
The next step is to use the function. If we want to select all the rows having Id equal to 2 or more, the query would look like:
SELECT * FROM TrackingItemsModified(2);
And the results:
Id Issued Category Modified
-- ---------- -------- -----------------------
2 2011-03-11 3 2011-03-10 23:46:53.523
3 2011-03-14 1 NULL
4 2011-03-14 3 2011-03-10 23:46:53.523
As the result is a table, it can be used like one. For example, if we want to query all the original tracking items that don’t exist in this subset, the query could be:
SELECT *
FROM TrackingItem ti
WHERE ti.Id NOT IN (SELECT tim.Id
FROM TrackingItemsModified(2) tim)
An the results would be:
Hide Copy Code
Id Issued Category
-- ---------- --------
1 2011-03-10 1
Since this is a procedural approach. The performance won’t be as good as it would be using a good, set-based approach. However, since functions can provide more flexibility
No comments:
Post a Comment