Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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)
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.
Total article views: 10721 | Views in the last 30 days: 11
 
Related Articles
FORUM

Alter column order

Alter column order

FORUM

Columns order in the index

Columns order in the index

FORUM

user defined functions & columns

user defined functions & columns

ARTICLE

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

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

FORUM

MDX query using ORDER and UNION functions

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

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones