UDF as Computed Column

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:

ProductID

int

PK NOT NULL

ProductName

varchar(20)

NOT NULL

ParentID

int

FK (ProductID) NULL

Sample data:

ProductID

ProductName

ParentID

1

ProductA0001

NULL

2

ProductB0001

1

3

ProductC0001

2

4

ProductA0002

NULL

5

ProductA0003

NULL

6

ProductB0002

4

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:

ProductID

ProductName

ParentName

1

ProductA0001

ProductA0001

2

ProductB0001

ProductA0001

3

ProductC0001

ProductA0001

4

ProductA0002

ProductA0002

5

ProductA0003

ProductA0003

6

ProductB0002

ProductA0002

As you can see, in order to get the desired result set, we have to
write a recursive function like below:

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.

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.