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

Automate Default Constraints Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 9:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:25 PM
Points: 15, Visits: 175
Comments posted to this topic are about the item Automate Default Constraints
Post #653386
Posted Tuesday, February 10, 2009 2:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:32 AM
Points: 14, Visits: 130
Your drop code drops all default constraints
Your create code creates only some constraints /*e.g. datetime where col name like '%Last%Date%' */

I think if you'd want to do something like this you'd better have same conditions in the drop code yes?
I never felt the need (yet) to standardize default constraints based on type and maybe column name.

/* Edit s/You're/Your */
Post #653486
Posted Tuesday, February 10, 2009 2:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 20, 2013 2:04 AM
Points: 222, Visits: 331
yes you are rit, but we can use it in some cases where we have columns like last inserted or last updated....

Sriram

Post #653492
Posted Tuesday, February 10, 2009 6:45 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:10 PM
Points: 466, Visits: 172
I'm not sure that I would have a use for this code. I would be too worried about properly re-buildling ALL of my default constraints. In this particular case, it seems to work great for the author since all of the columns he needs defaults on fit the format last%date. However, I'm sure most developers are in a situation similar to myself in that the columns that we use defaults for do not fit a particular naming scheme.

In my case, I run into this situation maybe once a month. I would much prefer to manually write a script to handle each column as they arise.




Regards,

Jim C

Post #653646
Posted Tuesday, February 10, 2009 9:08 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: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
Good article.

You should put a check in for columns that support NULL. They don't need defaults and in many cases should not have them. For example I'm doing a project where our handheld data units have to report up to the Enterprise back end. We do all the validation and update the local database in real time. I have a column on the transaction log table that is DateExported. If the transaction has not been reported out then the column is NULL. That means no date. This saves me from having to have a bit column called HasThisBeenExported.

You might think that NvarChar fields would be OK to always have a default of blank but that's not true either. Let's bypass talking about auditing for a second (I want to know if the user missed filling in data or if they said blank) and talk about space considerations. A column that is NULL takes up less than a column that is blank. One place where this comes up is when using the Compact Edition on handheld computers and mobiles. The other is when you have millions of rows and lots of columns.

Now all that being said this procedure is a good starting point where you could put in you business rules and design standards.


ATB

Charles Kincaid

Post #653862
Posted Tuesday, February 10, 2009 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:25 PM
Points: 15, Visits: 175
Hi,
first of all thanks a lot for your comments.

I wrote this procedure as a template. You can modify it as per your need.
It suits my requirement. Hope after little modification, you will get what you need.

Thank you.
Post #653968
Posted Tuesday, February 10, 2009 10:19 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: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
You are very welcome.

Congratulations on writing a good article and taking criticism. Keep up the good work.


ATB

Charles Kincaid

Post #653979
Posted Tuesday, February 10, 2009 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 10, 2009 11:48 AM
Points: 1, Visits: 1
Hi

Great Article dude.
It really helped me and decreased my effort and time considerably.
Thanks for the code.
Post #653990
Posted Tuesday, February 10, 2009 11:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 10, 2009 11:12 AM
Points: 1, Visits: 0
Really a nice topic. I have also faced in such a situation in my last project. i think it will help the users a lot.
Thanks a lot once again
Kumar Ranjan
Post #654056
Posted Tuesday, February 10, 2009 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:56 AM
Points: 6,723, Visits: 8,462
It's always nice if some one makes life easy for you.

As always, adding default values for columns is something that depends on the needs of your data model !
If your DA agreed on only having defaults defined for some columns and a DBA wants to add them anyways, he (the DBA!) should give that feedback to the DA and get it approved !

With your staging db, rules may defer from the actual OLTP db because you are preparing stuff for later purposes.
Then adding defaults may ease tool usage to load data into a DWH, because in many cases NULL isn't an option.
Also in this case, double check with your DWH-DA to be sure you are providing the correct adjustments.

Keep in mind, when adding a default value, it doesn't alter the actual current content of the column !


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #654224
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse