T-SQL Computed Columns, part 3

My last two posts described how to create a computed column, and how to control the data type and size.

In the first example, the computed column would be "A" if there is an approval date, otherwise it would be "".

The general rule is that the computed column can only refer to other columns within the same table. However, it is possible to use a scalar function to perform a lookup from another table. This might have a dramatic effect on performance, depending on table size, row count etc. but it is an option according to the BOL.

So then, what if that approval date actually resides in a different table?

Assuming that my_table has a primary key PK int, and other_table has the related column ID int, we could write a lookup function, like this:

CREATE FUNCTION dbo.fnLookup
(
@PK int
)
RETURNS varchar(1)
AS
BEGIN
DECLARE @Lookup varchar(1)

SELECT @Lookup =
CASE
WHEN approval_date IS NOT NULL
THEN 'A'
ELSE ''
END
FROM other_table
WHERE ID = @PK

RETURN @Lookup
END

...and then use that function in our computed column, like this:

ALTER TABLE my_table
ADD A_display
AS
dbo.fnLookup(PK)

Again, this could have an impact on the performance - any query run against my_table will execute the function for every row - but this is one more option for computed columns.

A better solution might be to create a column in my_table to hold the A_display data, and then create a trigger on other_table that would keep A_display in sync. We'll explore that in a future post.