InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 19:18:54 GMT20http://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspx[quote][b]Ol'SureHand (2/15/2009)[/b][hr]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?[/quote]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.Mon, 16 Feb 2009 00:24:36 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspx[quote][b]ALZDBA (2/10/2009)[/b][hr] - 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 ![/quote]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?Sun, 15 Feb 2009 16:22:26 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxThank 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 youWed, 11 Feb 2009 10:12:06 GMTmail.arun2005http://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxExcellent article...Wed, 11 Feb 2009 04:53:02 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxIt's always nice if some one makes life easy for you. :cool: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 !Tue, 10 Feb 2009 14:09:09 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxReally 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[b]Kumar Ranjan[/b]Tue, 10 Feb 2009 11:32:34 GMTranjan.ucehttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxHiGreat Article dude.It really helped me and decreased my effort and time considerably. Thanks for the code.Tue, 10 Feb 2009 10:31:23 GMTcalllove2004http://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxYou are very welcome.Congratulations on writing a good article and taking criticism. Keep up the good work.Tue, 10 Feb 2009 10:19:02 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxHi,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.Tue, 10 Feb 2009 10:15:35 GMTmail.arun2005http://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxGood 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.Tue, 10 Feb 2009 09:08:47 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxI'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.Tue, 10 Feb 2009 06:45:08 GMTJim C-203340http://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxyes you are rit, but we can use it in some cases where we have columns like last inserted or last updated....Tue, 10 Feb 2009 02:46:44 GMTSriram-288748http://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxYour drop code drops all default constraintsYour 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 */Tue, 10 Feb 2009 02:36:25 GMTKoenraad Dendievelhttp://www.sqlservercentral.com/Forums/Topic653386-1480-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/65607/">Automate Default Constraints</A>[/B]Mon, 09 Feb 2009 21:41:15 GMTmail.arun2005