April 26, 2006 at 9:52 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tchapman/complexcomputedcolumns.asp
May 9, 2006 at 12:57 am
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...
May 9, 2006 at 4:17 am
May 9, 2006 at 5:24 am
It was great!. Thanks a lot.
May 9, 2006 at 6:18 am
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.
May 9, 2006 at 7:23 am
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
May 9, 2006 at 7:49 am
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.
May 9, 2006 at 8:01 am
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
May 9, 2006 at 8:42 am
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)
May 9, 2006 at 11:41 am
>> 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
May 9, 2006 at 2:27 pm
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.
May 9, 2006 at 3:38 pm
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
May 10, 2006 at 12:53 am
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...
May 10, 2006 at 5:27 am
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/
May 10, 2006 at 6:27 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply