As all of you know, User-Defined Functions greatly helps us in

development. In this Article I will show you that how to add a function as a

computed column. Adding a scalar function to a table makes other developers

life easier and sometimes it enhance performance greatly if it is made as part

of an index. But remember, you can make an index on UDF, if it is

deterministic. See BOL for more information about determinism.

Now let’s see that how to add a function to the table.

Example 1

Let's take a table that has a circular relationship (which points to

itself.). This is one of the tables I designed to hold products information in

one of our clients’ database. Primarily, this table has more than twenty

columns but let’s take only what we need.

Here is the structure for the table:

Sample data:  

As you can see, data is organized to maintain relationship between

products. According to data:

• ProductA0001(1) is a top level product.
• ProductC0001(3) is a child of ProductB0001(2) which is a child of

  ProductA0001(1).

• ProductB0002(7) is a child of ProductA0002.

Let’s say we want to get a result set like below that shows

products with their top level parent:

As you can see, in order to get the desired result set, we have to

write a recursive function like below:

CREATE FUNCTION getTopLevelParent(@ProductID int)
RETURNS varchar(20)
AS
BEGIN
   DECLARE @ProductName varchar(20), @ParentID int
   SELECT @ParentID = ParentID FROM Products WHERE ProductID = @ProductID
   IF @ParentID IS NULL
      SELECT @ProductName = ProductName FROM Products WHERE ProductID = @ProductID
   ELSE
      SET @ProductName = dbo.getTopLevelParent(@ParentID)
   RETURN @ProductName
END

Now simply you can write a query:

SELECT *, dbo.getTopLevelParent(ProductID) FROM Products

You can encapsulate this function by adding to the table as

computed column.

ALTER TABLE Products
ADD ParentName AS dbo.getTopLevelParent(ProductID)

Now you can run query without writing the function.

SELECT * FROM Products

Because of this function is not a deterministic function, we

cannot create index on it. I will take another example and show you how add an

index on it.

Example 2

Let’s take an Order table that contains OrderID and OrderNumber

columns. I will take the OrderID as int type identity column and OrderNumber as

int column. Most Order tables maintain OrderNumber as int column but display as

string when order is presented. For an example, OrderNumber 1 as ‘000001’. In

order to get it formatted, let’s write a small function. Here is the complete code.

CREATE TABLE Orders
(OrderID int IDENTITY(1,1) PRIMARY KEY,
OrderNumber int NOT NULL)
 
CREATE FUNCTION formatOrderNumber (@OrderNumber int)
RETURNS varchar(6)
WITH SCHEMABINDING
AS
BEGIN
   RETURN REPLICATE('0', 6-LEN(@OrderNumber)) + CONVERT(varchar(6), @OrderNumber)
END

Note that I have added SCHEMABINDING option to the function. This

is one of the conditions should be satisfied to make the function as

deterministic. Now you can add the column to an index. Definitely, this will

enhance performance of data retrieval.

ALTER TABLE Orders
ADD FormattedOrderNumber AS dbo.formatOrderNumber(OrderNumber)
CREATE INDEX in_Orders_OrderNumber ON Orders(FormattedOrderNumber)
SELECT * FROM Orders
SELECT * FROM Orders WHERE FormattedOrderNumber = '000500'

Hope this broadened your mind. Let’s see more things about UDF's

with my next article. I highly appreciate all your comments about this article.