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.

T-SQL Computed Columns, part 2

My previous post presented the basics of using computed columns in SQL Server. Here we have a slightly more complex example that also deals with data types and sizes.

I have a table of locations in the city - each location has a direction, street, and cross street, such as

NB Austin Blvd @ Cermak Rd
SB Austin Blvd @ Cermak Rd
EB Cermak Rd @ Austin Blvd
WB Cermak Rd @ Austin Blvd

If you examine the list, you see that all four locations are actually the same street intersection - Austin and Cermak - and in the application (MS-Access) we need the ability to sort by the intersection, displayed in a format like street @ cross_street.

The original app used an expression in the query to place the streets in alphabetical order, like this:

IIf([Street] > [Cross_Street],
[Cross_Street] & " @ " & [Street],
[Street] & " @ " & [Cross_Street])

...and the result for all four locations would be:
Austin Blvd @ Cermak Rd
which provides the right answer & works fine, but takes a lot of typing, so it was eventually converted to a VBA function.

Now that the data has been moved into SQL Server, we can avoid using IIf() or VBA functions, which use resources in the GUI, and instead take advantage of the power of T-SQL.

We can start by creating a computed column like this:

ALTER TABLE
my_table
ADD
Intersection_Name
AS
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END

...which gives the correct result; however, Street and Cross_Street are both nvarchar(255) so our calculated column comes out as nvarchar(513) - why? it is the sum of the sizes of the two columns, plus the ' @ ' expression - and MS-Access regards that as a Memo field, so we cannot sort by that.

(Street and Cross_Street were originally defined as Text(255) in MS-Access, but the data is never that long - 100 would have been a better choice; but that's a topic for another day.)

We could force the computed column to nvarchar(255) to accommodate the limitations of MS-Access, like this:

ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN Cross_Street + ' @ ' + Street
ELSE Street + ' @ ' + Cross_Street
END
AS nvarchar(255)
)

...but now we have a potential bug - we don't expect this will ever happen - but - if the concatenation is ever more than 255 characters, the CAST will fail. The solution is to shorten the street names to 126 characters, so that 126 + 3 + 126 = 255 to ensure the computed column will never fail:

ALTER TABLE
my_table
ADD
Intersection_Name
AS
CAST(
CASE
WHEN Street > Cross_Street
THEN LEFT(Cross_Street, 126) + ' @ ' + LEFT(Street, 126)
ELSE LEFT(Street, 126) + ' @ ' + LEFT(Cross_Street, 126)
END
AS nvarchar(255)
)

One final note - when you work with computed columns, it is not possible to ALTER a computed column; in order to change it you must DROP the column & then ADD the new column definition. As a result, the column to move to the last position in the table.