Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex Computed Columns


Complex Computed Columns

Author
Message
Tim Chapman
Tim Chapman
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tchapman/complexcomputedcolumns.asp
David le Quesne
David le Quesne
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 32

Hi Tim,

have you tried using computed columns in Table variables returned from functions?

I found what I believe to be a bug in SQL Server 2000, if you try and return a table from a UDF which contains a computed column, it seems to mess up the UDF definition, adding an extra row at the bottom of the UDF. See the post below, no-one ever responded to it.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=256329#bm256724

I welcome your thoughts on this

David



If it ain't broke, don't fix it...
Radhi A Y
Radhi A Y
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
just a quesion, what is the different between the computed columun and the trigger. I mean updating a column with a trigger procedure.




Maria Carmen Vilbar
Maria Carmen Vilbar
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 25
It was great!. Thanks a lot.
Rob Sanguin
Rob Sanguin
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 78
It was an interesting article, but I don't see why you would store the logic for the calculation in the table definition rather than in the SQL you use to retrieve the data from that table. The latter allows you to get at data from the table without the expense of performing the calculation if that wasn't required.
Tudor Gabriel
Tudor Gabriel
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 1
this is only to add some simplification to our sql code at the cost of performance i guess.
the computed columns are calculated every time we query the table, and because they use UDF's they are not deterministic ... so we can't index the computed column ... so that the value will be automatically updated. i tried this once, but if the table is a large one (and they all tend to have alot of data) it takes alot of time
Bruce Morrison
Bruce Morrison
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

Tim mentions that once a function is used within a calculated column the function can not be altered or dropped. This is a serious problem.

I have inherited a group of databases that use a function to calculate taxes in a calculated column in multiple tables. At first glance this seems reasonable because it allow for code reuse. The problem comes when the tax calculation was discovered to be wrong. This affected 5 tables in 60 databases.

There is a solution to this problem, but again it must be handled with care. Drop all the calculated columns, alter the user defined function, and then alter all the tables to add the calculated columns back. I think you can see the number of points where an error could be introduced.

While using user defined functions within calculated columns is possible, for the purposes of maintainability I do not recommend it.


Mike C
Mike C
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1575 Visits: 1168

You can also add "complex logic" to your computed columns via CASE expressions. This might offer performance benefits over a UDF.

Just as a really simple example:

CREATE TABLE test (
i INT NOT NULL PRIMARY KEY,
j INT NULL,
k AS (CASE
WHEN j < 0 THEN i
WHEN j IS NULL THEN 0
ELSE (i * j)
END)
)
GO
INSERT INTO test (i, j)
SELECT 0, 1
UNION SELECT 1, 1
UNION SELECT 2, 3
UNION SELECT 4, NULL
UNION SELECT 10, -1
GO
SELECT *
FROM test

In the sample, k is computed based on the value of j. If j is negative then k = i, if j is NULL then k = 0, otherwise k = i * j. Really simple and not really all that useful of an example, but it's a pretty powerful concept.

You can also add an index to a computed column with a CASE expression:

CREATE INDEX IX_test ON test(k)
GO


Ric Sierra
Ric Sierra
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1453 Visits: 342

The use of UDF is great but... (always there is a BUT), when you use UDF in large tables the performance of queries would be "slow down". Remember: Computed columns via UDF can't be indexed. And use proper fields names for identify this computed fields for other users (because are read only fields!)... Example: RO_TOTAL (Read Only_Total)


noeld
noeld
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7144 Visits: 2048

>> Remember: Computed columns via UDF can't be indexed <<

That is not entirely true!

You CAN index the computed column if the UDF is DETERMINISTIC

I hope this clears the confusion of all readers of this thread

Cheers,




* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search