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


Complex Computed Columns


Complex Computed Columns

Author
Message
drnetwork
drnetwork
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 82
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
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6324 Visits: 2048

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
David le Quesne
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 32

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
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715

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.



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
drnetwork
drnetwork
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 Visits: 82

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





Tim Chapman-218780
Tim Chapman-218780
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 Visits: 115
Glad you liked the article. If you get a chance, check out our new site at www.sqlservernation.com.
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