Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automate Default Constraints


Automate Default Constraints

Author
Message
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6283 Visits: 1407
Excellent article...



mail.arun2005
mail.arun2005
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 176
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. Smile

Thank you
Ol'SureHand
Ol'SureHand
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 720
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?
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
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


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" :-D


Need a bit of Powershell? How about this

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search