computed columns or UDFs

Hi,

What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?

thanks,
Frankjust look up books online they have a better xplanation than anyone here can give you in 1-2 lines.

hth

computed column formula

Is it possible to retrieve the formula associated with a computed column using t-SQL? I can use COLUMNPROPERTY( id, column, 'IsComputed') to find the computed columns, but how do I get the formula itself?

Thanks,

Mable

sp_helptext 'TableName', 'Computed Column Name'

For eg

CREATE TABLE T(i int, j AS i *2)

INSERT INTO T(i) VALUES(10)
SELECT * FROM T

sp_helptext 'T', 'j'

|||

Thank you! That is just what I needed.

--Mable

computed column formula

Is it possible to retrieve the formula associated with a computed column using t-SQL? I can use COLUMNPROPERTY( id, column, 'IsComputed') to find the computed columns, but how do I get the formula itself?

Thanks,

Mable

sp_helptext 'TableName', 'Computed Column Name'

For eg

CREATE TABLE T(i int, j AS i *2)

INSERT INTO T(i) VALUES(10)
SELECT * FROM T

sp_helptext 'T', 'j'

|||

Thank you! That is just what I needed.

--Mable

Computed Column - Error Validating Formula

I want to create a computed column with this formula:

ISNULL(NULLIF (tot_mnc, 0) / NULLIF (repl_value, 0), 0)

It works in a straight select query, but when I put it in the formula of the table design window, I get an error "Error validating the formula for column 'test_fci'"

I don't know if it's relevant but repl_value is itself a computed column with the formula:

(repl_value_e_g + repl_value_aux)

Is it possible to use the system functions in a computed column? If not, how would I pass those values into a udf and use it for the formula?

Thanks.

You are correct: you are not allowed to create a computed column that is based on a different computed column. You can use the built-in functions, so just create your new computed column based on the calculations from the base columns.

If you were to run an ALTER statement to create your computed column based on a computed column you would potentially get an error message like:

Msg 1759, Level 16, State 0, Line xxxx

Computed column 'ccccc' in table 'tttttt' is not allowed to be used in another computed-column definition.

Kent

|||

That was it, thank you. The table designer gui hid the details of the error message and I didn't know which of several possiblities were causing the error.

Thanks again.