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 Tuesday, May 09, 2006 2:27 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 3:38 AM
Points: 445, 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.


Post #278774
Posted Tuesday, May 09, 2006 3:38 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027

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
Post #278789
Posted Wednesday, May 10, 2006 12:53 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

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...
Post #278834
Posted Wednesday, May 10, 2006 5:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695

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/
Post #278872
Posted Wednesday, May 10, 2006 6:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 3:38 AM
Points: 445, 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




Post #278896
Posted Friday, March 20, 2009 8:32 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, June 12, 2009 7:37 AM
Points: 125, Visits: 115
Glad you liked the article. If you get a chance, check out our new site at www.sqlservernation.com.
Post #680428
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse