SQLServerCentral / Discuss Content Posted by Tim Chapman / Article Discussions / Article Discussions by Author / Complex Computed Columns / Latest Posts

RE: Complex Computed Columns

Tim Chapman-218780 — Fri, 20 Mar 2009 08:32:23 GMT

Glad you liked the article. If you get a chance, check out our new site at [url=http://www.sqlservernation.com]www.sqlservernation.com[/url].

RE: Complex Computed Columns

drnetwork — Wed, 10 May 2006 06:27:00 GMT

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

RE: Complex Computed Columns

colin.Leversuch-Roberts — Wed, 10 May 2006 05:27:00 GMT

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.

RE: Complex Computed Columns

David le Quesne — Wed, 10 May 2006 00:53:00 GMT

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

RE: Complex Computed Columns

noeld — Tue, 09 May 2006 15:38:00 GMT

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,

RE: Complex Computed Columns

drnetwork — Tue, 09 May 2006 14:27:00 GMT

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.

RE: Complex Computed Columns

noeld — Tue, 09 May 2006 11:41:00 GMT

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

RE: Complex Computed Columns

Ric Sierra — Tue, 09 May 2006 08:42:00 GMT

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)

RE: Complex Computed Columns

Mike C — Tue, 09 May 2006 08:01:00 GMT

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) )GOINSERT INTO test (i, j)SELECT 0, 1UNION SELECT 1, 1UNION SELECT 2, 3UNION SELECT 4, NULLUNION SELECT 10, -1GOSELECT * 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

RE: Complex Computed Columns

Bruce Morrison — Tue, 09 May 2006 07:49:00 GMT

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.

RE: Complex Computed Columns

Tudor Gabriel — Tue, 09 May 2006 07:23:00 GMT

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

RE: Complex Computed Columns

Rob Sanguin — Tue, 09 May 2006 06:18:00 GMT

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.

RE: Complex Computed Columns

Maria Carmen Vilbar — Tue, 09 May 2006 05:24:00 GMT

It was great!. Thanks a lot.

RE: Complex Computed Columns

Radhi A Y — Tue, 09 May 2006 04:17:00 GMT

just a quesion, what is the different between the computed columun and the trigger. I mean updating a column with a trigger procedure.

RE: Complex Computed Columns

David le Quesne — Tue, 09 May 2006 00:57:00 GMT

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

Complex Computed Columns

Tim Chapman — Wed, 26 Apr 2006 21:52:00 GMT

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