SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

UDF as Computed Column

By Dinesh Priyankara,

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:

CREATE FUNCTION getTopLevelParent(@ProductID int)
RETURNS varchar(20)
   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
      SET @ProductName = dbo.getTopLevelParent(@ParentID)
   RETURN @ProductName

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.

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.

OrderNumber int NOT NULL)
CREATE FUNCTION formatOrderNumber (@OrderNumber int)
RETURNS varchar(6)
   RETURN REPLICATE('0', 6-LEN(@OrderNumber)) + CONVERT(varchar(6), @OrderNumber)

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.

ADD FormattedOrderNumber AS dbo.formatOrderNumber(OrderNumber)
CREATE INDEX in_Orders_OrderNumber ON Orders(FormattedOrderNumber)
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.
Total article views: 10788 | Views in the last 30 days: 9
Related Articles

Alter column order

Alter column order


Columns order in the index

Columns order in the index


user defined functions & columns

user defined functions & columns


Stairway to MDX - Level 3: The Order() Function

The Order() function provides the 'hierarchized' sorts you need for reports and applications using M...


MDX query using ORDER and UNION functions

Having trouble using the ORDER function in an MDX query that uses a UNION