Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Complex Computed Columns Expand / Collapse
Author
Message
Posted Wednesday, April 26, 2006 9:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 20, 2006 8:32 AM
Points: 28, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tchapman/complexcomputedcolumns.asp
Post #275753
Posted Tuesday, May 9, 2006 12:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, 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...
Post #278492
Posted Tuesday, May 9, 2006 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 19, 2007 12:43 AM
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.
 



Post #278514
Posted Tuesday, May 9, 2006 5:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 8:11 AM
Points: 169, Visits: 25
It was great!. Thanks a lot.
Post #278525
Posted Tuesday, May 9, 2006 6:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 20, 2011 4:37 AM
Points: 114, 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.
Post #278540
Posted Tuesday, May 9, 2006 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 30, 2006 6:09 AM
Points: 37, 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
Post #278574
Posted Tuesday, May 9, 2006 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 3, 2006 7:20 AM
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.

Post #278594
Posted Tuesday, May 9, 2006 8:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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

 

Post #278605
Posted Tuesday, May 9, 2006 8:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:34 PM
Points: 1,426, Visits: 333

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)

Post #278628
Posted Tuesday, May 9, 2006 11:41 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028

>> 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
Post #278705
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse