How can I set up a simple calculated field in SQL Server?

Question

I have a table with several account fields like this:

MAIN_ACCT
GROUP_ACCT
SUB_ACCT

I often need to combine them like this:

SELECT MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT
FROM ACCOUNT_TABLE

I'd like a calculated field that automatically does this, so I can just say:

SELECT ACCT_NUMBER FROM ACCOUNT_TABLE

What is the best way to do this?

I'm using SQL Server 2005.

Catholicon

Answer 1

ALTER TABLE ACCOUNT_TABLE 
ADD ACCT_NUMBER AS MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT PERSISTED

This will persist a calculated column and may perform better in selects than a calculation in view or UDF if you have a large number of records (once the intial creation of the column has happened which can be painfully slow and should probably happen during low usage times). It will slow down inserts and updates. Usually I find a slow insert or update is tolerated better by users than a delay in a select unless you get locking issues.

The best method for doing this will depend a great deal on your usage and what kind of performance you need. If you don't have a lot of records or if the computed column won't be called that frequently, you may not want a persisted column, but if you are frequently running reports with all the records for the year or other large sets of data, you may find the persisted calculated column works better for you. As with any task of this nature, the only way to know what works best in your situation is to test.

Juanajuanita

Great, helpful still for me quite a few years later. – Globetrotter

Answer 2

This is a great candidate for a View.

CREATE VIEW vwACCOUNT_TABLE
AS

SELECT MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT AS ACCT_NUMBER 
FROM ACCOUNT_TABLE

GO

--now select from the View
SELECT ACCT_NUMBER FROM  vwACCOUNT_TABLE

Lavoie

Ugh. Must've beaten me by seconds. heh – Narcotize

Answer 3

ALTER TABLE ACCOUNT_TABLE ADD ACCT_NUMBER AS MAIN_ACCT+'-'+GROUP_ACCT+'-'+SUB_ACCT;

the column is not persisted in the table, will be recreated on-the-cly each time you reference it. You can achieve the same result by using a view. If you use filtering predicates or ordering on the computed column and want to add an index on it see Creating Indexes on Computed Columns.

Defective

YOu can persist the column and not spend the time recomputing which you would have to do in a view. We persist ours because the slight delay when the data is changed is far better than the long delay whenever we want to query the information. You can also index the column if persisted – Juanajuanita

As long as you are aware of the persistent column when performing maintenance on the project. If you're not then this is where problems could occur. – Obryan

Answer 4

Well, you could create a view of the ACCOUNT_TABLE and query that. Or I believe you could create a user defined function which would accomplish the same thing.

Narcotize