February 9, 2009 at 9:41 pm
Comments posted to this topic are about the item Automate Default Constraints
February 10, 2009 at 2:36 am
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 */
February 10, 2009 at 2:46 am
yes you are rit, but we can use it in some cases where we have columns like last inserted or last updated....
Sriram
February 10, 2009 at 6:45 am
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
February 10, 2009 at 9:08 am
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
February 10, 2009 at 10:15 am
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.
February 10, 2009 at 10:19 am
You are very welcome.
Congratulations on writing a good article and taking criticism. Keep up the good work.
ATBCharles Kincaid
February 10, 2009 at 10:31 am
Hi
Great Article dude.
It really helped me and decreased my effort and time considerably.
Thanks for the code.
February 10, 2009 at 11:32 am
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
February 10, 2009 at 2:09 pm
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
Learn to play, play to learn !
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 but most of the time this is me
February 11, 2009 at 4:53 am
Excellent article...
February 11, 2009 at 10:12 am
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
February 15, 2009 at 4:22 pm
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?
February 16, 2009 at 12:24 am
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
Learn to play, play to learn !
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 but most of the time this is me
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply