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?