Complex Computed Columns

Question

Post reply

Complex Computed Columns

Tim Chapman

SSC Rookie

Points: 40
More actions
April 26, 2006 at 9:52 pm

#172183

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tchapman/complexcomputedcolumns.asp

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

David le Quesne SSCertifiable Points: 5229 More actions · Answer 1

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 SSC Journeyman Points: 81 More actions · Answer 2

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 Ten Centuries Points: 1037 More actions · Answer 3

Maria Carmen Vilbar

Ten Centuries

Points: 1037

May 9, 2006 at 5:24 am

#636680

It was great!. Thanks a lot.

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 4

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 SSC-Addicted Points: 483 More actions · Answer 5

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 SSC Journeyman Points: 77 More actions · Answer 6

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 SSC-Insane Points: 23224 More actions · Answer 7

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 SSCrazy Points: 2260 More actions · Answer 8

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 SSC Guru Points: 96590 More actions · Answer 9

>> 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

drnetwork SSCommitted Points: 1815 More actions · Answer 10

Has anyone had any problems with computed columns messing up the calling of SQLMAINT.EXE for reindexing and DBCC's? This was a big problem in our environment for SQL 2000.

noeld SSC Guru Points: 96590 More actions · Answer 11

Correct. sqlmaint.exe does hardcode the connection settings and there is no way to change that. In sql 2005 MS introduced support for those cases with a switch.

The workaround is to create the dbcc reindex and the update statistics job independently from sqlmaint.exe and make sure that you specify the appropriate (required) connection settings for computed columns.

Cheers,

* Noel

David le Quesne SSCertifiable Points: 5229 More actions · Answer 12

Thanks Tim for an interesting article. I have not used computed columns with functions in, and I would be vary wary about the limitation on changing functions used by computed columns. Is there a some form of schema_binding setting you can use to turn this off?

The only major use I make of computed columns is in temporary tables when I am compiling report data. Even then, you are limited because you can't reference one computed column from another computed column, so every calculation has to be performed from scratch using the 'real' columns.

David

If it ain't broke, don't fix it...

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 13

I've been using functions in tables this way for many years, used with care and probably not in highly transactional tables the advantages are great. I have also used this functionality to simplify complex joins and selects to great effect. used with care you can also gain performance.

My view on sysmaint.exe is that it should be avaoided anyway - hey you're calling an external program out of process to run a dbcc ?

I see no problems to restrictions on changing functions used this way - in a production system you shouldn't be able to make an ad-hoc chnage anyway, so with proper testing what's the problem.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

drnetwork SSCommitted Points: 1815 More actions · Answer 14

I may be mistaken but I'm fairly certain that when you set up a database maintenance plan in SQL 2000 to reindexing, among other things, it calls SQLMAINT under the cover.

The point is that if you are writing an application for generalized use by a number of customers running in the hundreds, many of which don't have full time DBA's on staff, you want to avoid computed columns because of the problem it gives your clients in setting up and running relatively simple database maintenance plans. I've dealt with the workaround but this is a skill set that many of my customers might not have.

Thanks