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 Wednesday, February 11, 2009 4:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:15 AM
Points: 5,471, Visits: 1,402
Excellent article...


Post #654656
Posted Wednesday, February 11, 2009 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:25 PM
Points: 15, Visits: 175
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
Post #655047
Posted Sunday, February 15, 2009 4:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 7:52 PM
Points: 371, Visits: 717
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?
Post #657422
Posted Monday, February 16, 2009 12:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:03 AM
Points: 6,748, Visits: 8,544
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

- 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 #657498
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse