Scalar-valued functions return a single value. Table-valued functions return table, thus it can be used as a table in a query.
Function has the same benefits as a view when it comes to data protection enforced via privileges or simplifying a query. However, a Table-Valued Function has at least few advantages:
- Parameterization, a function can receive parameters so the logic inside the function can be adjusted better than using traditional predicate pushing.
- Programmability, a view can have certain amount of logic (calculations, case-structures etc.) but it’s still quite column bound so more complex logic is hard or impossible to create.
To view all of the table-valued functions contained in the database from within the Object Explorer tree, traverse this path:
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
- Include an extra
- Not have even numbers in
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; ================================================================ If you want to return one table from multiple SELECT statements
use UNION/UNION ALL between SELECT statements.
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:
Id Issued Category -- ---------- -------- 1 2011-03-10 1