Automate Default Constraints

  • mail.arun2005

    SSC Veteran

    Points: 247

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

  • Koenraad Dendievel

    SSC Veteran

    Points: 289

    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 */

  • Sriram-288748

    SSCrazy

    Points: 2874

    yes you are rit, but we can use it in some cases where we have columns like last inserted or last updated....

    Sriram

  • Jim C-203340

    SSCommitted

    Points: 1605

    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

  • Charles Kincaid

    SSChampion

    Points: 13593

    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.

    ATBCharles Kincaid

  • mail.arun2005

    SSC Veteran

    Points: 247

    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.

  • Charles Kincaid

    SSChampion

    Points: 13593

    You are very welcome.

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

    ATBCharles Kincaid

  • calllove2004

    Grasshopper

    Points: 19

    Hi

    Great Article dude.

    It really helped me and decreased my effort and time considerably.

    Thanks for the code.

  • ranjan.uce

    SSC Rookie

    Points: 25

    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

  • Johan Bijnens

    SSC Guru

    Points: 134265

    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


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

  • Anipaul

    SSC-Insane

    Points: 24681

    Excellent article...

  • mail.arun2005

    SSC Veteran

    Points: 247

    Thank u all for ur support.

    I ll keep in mind ur suggestions in my next article.

    My next article will be on "Faster extraction and loading in SSIS package"

    Encourage me for better output. 🙂

    Thank you

  • Ol'SureHand

    SSCrazy

    Points: 2744

    ALZDBA (2/10/2009)


    - abbreviated -

    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.

    --

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

    Hope this question does not need its own thread ...

    Data warehousing practitioners seem to have a constraint on EVERY single column. The above code will only preserve necessary constraints, but needs doing before the table was (re)populated, thus each constraint will be checked for each row during the extraction.

    DBA types think it is better to import the data into the table, then run an UPDATE script on each column that essentially needs to be set (e.g. Update tblRepopulated SET thisColumn = 0 where thisColumn IS NULL).

    Which practice is more efficient with large data volumes?

  • Johan Bijnens

    SSC Guru

    Points: 134265

    Ol'SureHand (2/15/2009)


    Hope this question does not need its own thread ...

    Data warehousing practitioners seem to have a constraint on EVERY single column. The above code will only preserve necessary constraints, but needs doing before the table was (re)populated, thus each constraint will be checked for each row during the extraction.

    DBA types think it is better to import the data into the table, then run an UPDATE script on each column that essentially needs to be set (e.g. Update tblRepopulated SET thisColumn = 0 where thisColumn IS NULL).

    Which practice is more efficient with large data volumes?

    As soon as possible...

    If it is e.g. an SSIS package that imports your data and that is capable on handling the null to default switch, I would suggest to do it at that time.

    If the strategy is to load the data as is into a staging area and only then start to prepare it for the DWH environment, ... that may be a valid choice to disrupt your production OLTP as less as possible.

    It all depends on the timeframe you have to pull your data into your DWH.

    Rule no 1 says: "tell your system what you know"

    In this case that would mean ... don't load data that you know must be modified to a predefined value.

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply