Automate Default Constraints

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Arun Mallick » Automate Default Constraints

14 posts, Page 1 of 2

1 2 »»

Automate Default Constraints

Rate Topic

Display Mode

Topic Options

Author

Message

mail.arun2005

Posted Monday, February 09, 2009 9:41 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:07 PM
Points: 15, Visits: 166

Comments posted to this topic are about the item Automate Default Constraints

Post #653386

Koenraad Dendievel

Posted Tuesday, February 10, 2009 2:36 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 5:56 AM
Points: 14, Visits: 123

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

Sriram-288748

Posted Tuesday, February 10, 2009 2:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 5:08 AM
Points: 221, Visits: 329

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

Jim C-203340

Posted Tuesday, February 10, 2009 6:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, September 28, 2012 8:38 AM
Points: 466, Visits: 165

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

Charles Kincaid

Posted Tuesday, February 10, 2009 9:08 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

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

mail.arun2005

Posted Tuesday, February 10, 2009 10:15 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:07 PM
Points: 15, Visits: 166

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

Charles Kincaid

Posted Tuesday, February 10, 2009 10:19 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

You are very welcome.

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

ATB

Charles Kincaid

Post #653979

calllove2004

Posted Tuesday, February 10, 2009 10:31 AM

Forum 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

ranjan.uce

Posted Tuesday, February 10, 2009 11:32 AM

Forum 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

ALZDBA

Posted Tuesday, February 10, 2009 2:09 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:13 PM
Points: 6,866, Visits: 8,071

It's always nice if some one makes life easy for you. Cool

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

     Jul 13

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

- 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" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #654224

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions