Functions are create little blocks of code. They are fantastic for converting data from one format to another, or for looking up other values based on a lookup. However this comes at a price. That price is CPU power. Doing all these additional lookups can cause extra strain on the database server. It’s often better for the database server to simply join to the table which you are doing the lookup against. However you can guarantee the same lookup is done every time when a function is used.
There are two kinds of functions. One is a scalar function and one is a table function. A scalar function is used as a column of a select statement, or a value in an update statement. A table function is used in place of a table, and can be called directly or as a member of a JOIN. While functions are fairly simple in concept, they are in my opinion the most complex objects to create.
A scalar function accepts one or more input parameters and returns a single value of a predetermined data type. The CREATE FUNCTION command is used to create both scalar and table functions. To create a scalar function we use code along these lines.
CREATE FUNCTION dbo.FindObjectId
(
@ObjectName nvarchar(512)
)
RETURNS INT
AS
BEGIN
DECLARE @ObjectId INT
SELECT @ObjectId = ObjectId
FROM sysobjects
WHERE name = @ObjectName
END
GO
We use this function to return the object id of an object in the database (yes I’m aware that there are system functions to do this, but it’s an easy function to use as an example).
SELECT dbo.FindObjectId('sysobjects')
As we can see a single value is returned in a record set. We can also return more than one value in the record set by using the function against a table.
SELECT dbo.FindObjectId(name)
FROM sysobjects
Creating a table function using much the same syntax. In this case we will return all the object names which have an object type which matches our input parameter.
CREATE FUNCTION dbo.ShowObjects
(
@ObjectType CHAR(2)
)
RETURNS @TableNames TABLE (name nvarchar(512))
AS
BEGIN
INSERT INTO @TableNames
(name)
SELECT name
FROM sysobjects
WHERE xtype = @ObjectTypeRETURN
END
GOTo use this function we use a basic select statement.SELECT *
FROM dbo.ShowObjects('U')
We can also use it as a JOIN member.
SELECT sysobjects.name, a.name
FROM sysobjects
JOIN dbo.ShowObjects('U') a ON sysobjects.name = a.name
If you want to get very complex you can use what’s called an inline table function or single statement table function. This is basically a regular table function, but you can only put a single select command within the table function. As you can see from the code below the results will be the same, but the single line table function is a bit shorter. Because we are not declaring a table variable and loading the data into that table variable, then reading from that table variable a single statement table function should have a slightly cheaper execution plan compared to the same SELECT statement within a multi-statement table function.
CREATE FUNCTION dbo.ShowObjects
(
@ObjectType CHAR(2)
)
RETURNS TABLE
AS
BEGIN
RETURN (SELECT name
FROM sysobjects
WHERE xtype = @ObjectType)
END
GO
Denny