InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:06:21 GMT20http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxGreat article.Tue, 25 Dec 2012 08:27:31 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxSolomon, here's the article I wrote that similar but instead uses change tracking. [url=http://www.sqlservercentral.com/articles/Change+Tracking/74397/][/url]Tue, 09 Oct 2012 13:16:00 GMTLuke Chttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]Solomon Rutzky (10/5/2012)[/b][hr... and [i]now[/i] in SQL Server 2012 you can add a NOT NULL field with a default without locking the table ... [/quote]That is a nice little tidbit of information that I had missed in the SQL 2012 documentation. Thanks for pointing that out, that will be helpful.Mon, 08 Oct 2012 09:55:35 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxGosh... another great article, Solomon. As with anything else, planning and having the resources available for parallel table existance is important but this article covers a problem that a whole lot of people have had even on just SSC. Instead of explaining the whole gambit in the future, I'm just going to point them to this article if they have the extra disk space. Very well done.As a side bar, I agree with you about triggers in general but especially on the type of triggers needed to pull off this bit SQL prestidigitation. If they are well written and supported by correct indexing, they don't add much in the line of overhead at all.Again, my hat's off to you for this great article.Fri, 05 Oct 2012 19:52:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]derek.colley (10/5/2012)[/b][hr]An excellent article that applies a common-sense approach to doing these kinds of changes. It does rely of course on sufficient resources to allow this approach to take place - disk space springs to mind. ...Because you're rebuilding side-by-side, you potentially will need to allocate up to 730GB of 'elbow-room' for processing in the way you've described.[/quote]Hi Derek. Thanks for the reply.You are absolutely correct: you would need sufficient disk space for the operation. The specific calculations don't matter as you can tell by the current size of the table and what you are trying to do with it. So a 300 GB table would need another 300 GB at least if you are keeping most of the columns. The main issue is what do you sacrifice due to requirements. Our requirements are that there is nothing more than a "blip" in the system as most of the time we do not turn the application off. So doing these changes in the current table are (or were) not always possible. Changes in Clustered Index, PK, FKs, adding NOT NULL fields all complicate doing it in place. Yes, you [i]should[/i] be able to drop and recreate the Clustered Index using ONLINE = ON (IF you have Enterprise Edition!), and [i]now[/i] in SQL Server 2012 you can add a NOT NULL field with a default without locking the table, but I have not tested either of these approaches so know how they work in the real world rather than theory. And again, the extent of the changes might dictate this approach as a requirement and in that case the business needs to accept the sacrifice of having that disk space if their primary concern is no down-time. Or maybe you have an update trigger on this table and/or Change Tracking and/or Change Data Capture that would be adversely affected by updating the current table. However, if you just want to change a column or two then yes, you should first look to doing that in-place by just renaming the column and dropping the old one (or whatever the situation demands).Take care,Solomon...Fri, 05 Oct 2012 05:59:37 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxAn excellent article that applies a common-sense approach to doing these kinds of changes. It does rely of course on sufficient resources to allow this approach to take place - disk space springs to mind. Suppose you have a 100 million-row table containing VARCHAR data to restructure? Then some back-of-a-fag-packet calculations show that (and these calculations are REALLY approximate):Average row size: let's pick 6000 bytes, for some in-row VARCHAR data.Total row size: (6000 + 90-byte header) * 100m rows = 609GB with no row or page compression.+ indexes, let's say they occupy 20% of the table space = 609GB * 1.2 = 730.8GBBecause you're rebuilding side-by-side, you potentially will need to allocate up to 730GB of 'elbow-room' for processing in the way you've described.Side-by-side implementations are great but I feel sometimes there is an argument for doing these changes [i]in-situ[/i], especially when dealing with large data sets. You would also have to consider factors like load on the data, for example if your applications are hitting that table with various locks every, let's say 700ms out of 1000ms, then siphoning off the data in the manner you've described would have to be VERY careful that lock times don't increase beyond the app's (or database's) query timeout thresholds, and that you aren't siphoning data in a manner that encourages deadlocks.Please don't get me wrong, I'm not slating the approach you've described, it's a common-sense and logical approach to doing large schema or table data changes. I would however be wary of this approach if resource is an issue.Fri, 05 Oct 2012 03:24:38 GMTderek.colleyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]Luke C (4/29/2011)[/b][hr]I purchased this book a couple of months ago. It has several useful techniques that could go along with this topic. Refactoring Databases: Evolutionary Database Design[/quote]Thanks for the tip Luke. This book looks useful.Fri, 29 Apr 2011 10:15:49 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxI purchased this book a couple of months ago. It has several useful techniques that could go along with this topic. Refactoring Databases: Evolutionary Database DesignFri, 29 Apr 2011 10:07:05 GMTLuke Chttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxThanks Solomon for a great article and I agree this is a great topic. Here at my company we have very large databases and an application that is required to be up 24/7 so we need to make table modifications happen with minimal downtime. So the article and discussion are very useful as we work on a similar process.Fri, 29 Apr 2011 10:03:21 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]mar10br0 (4/27/2011)[/b][hr][quote][b]andrewkane17 (4/27/2011)[/b][hr]you mention having to update views, udf and procs after the table rename, why is this required?andrew[/quote]That is because the new table and its constraints, indexes, etc, although having the same name, are different objects (with different object-ids) as far as the server is concerned. So all dependent objects (with their compiled code still referencing the old object-ids) need a recompile to reference the new table. In many cases SQL-Server is smart enough to realize the dependent objects have disappeared and will try a recompile on first use, but this is not a fail-safe method (and I have seen in the past that objects get recompiled on each use after that, or not using the appropriate stats, degrading performance).Marco[/quote]Andrew and Marco: Actually, the reason to update those objects after the Table rename is because the objects themselves have changed to accommodate the "new" Table structure but that is not in place until the swap is made so we need to minimize the amount of time when calls can be made to the Procs, etc. (remember, the application is still running during the release) that would point to the incorrect structure and hence cause errors. This is also why we rename all of the objects that have no programmatic name-dependencies first (indexes, primary keys, foreign keys, constraints, Triggers) and swap the Tables at the very end (with the exception of then adding any Triggers that might exist). By doing the Table rename last in the "swap" script and then immediately updating the Procs, etc. we reduce that gap down to 1 or 2 seconds (and in some cases less than a second).But to Marco's point about the objects having different IDs but yet the same name (i.e. stale references), this is why I have seen some people issue an "sp_recompile" on the "new" table after the rename in order to mark all dependent objects for recompile. I cannot say that I have ever personally seen an issue of a stale reference but doing the sp_recompile against the table is something that I used to do until I was told that it really didn't provide any benefit and I had no evidence to support the idea of stale references*. However, if stale references are provable then all you would need to do is add the "sp_recompile" to the bottom of the swap script.Take care,Solomon...*Note: One might claim that I haven't seen stale references because I am, in fact, updating the objects after the rename anyway, possibly correcting the issue before it is noticeable. True, but I don't always update all objects. Sometimes Views and/or Procs use a subset of fields that are not changing so those objects don't need to be updated.Wed, 27 Apr 2011 22:13:30 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]Todd.Everett (4/27/2011)[/b][hr]Great article Solomon. Have you considered using a log apply tool to do the data capture? Would be interested in your feedback on such an option.[/quote]Hi Todd. Thanks and I'm glad you liked the article.Regarding a log reader (or even the new Change Data Capture feature), that was not really an option since we don't take down the application during our Releases. So the data can be changing at any moment (though we do try to minimize the areas that will be making changes so that they are considerably fewer than normal) and taking the time to sync data changes leaves us open to additional changes being made while that sync is in process. This would require an additional step of comparing the data after the Table swap and if necessary then moving records from the now "old" / DELETEME table into the "new" current table (I mentioned this in a reply above as well). And now that I think about this question again, I just realized that an additional complication with this situation of data in the DELETEME table is that if there is an IDENTITY field, then there might be a collision if new IDs were created just before the Table swap (but after the data sync) and then new rows were added to the "new" current table before you were able to move these "missed" rows over. By using Triggers it is not really possible to be in this situation. But if you have a controlled downtime where you know for certain that data changes are not coming in, then yes, any of these other methods would be fine (though possibly still more work than just going with Triggers ;-)).Take care,Solomon...Wed, 27 Apr 2011 21:41:17 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]ModestyBlaise84 (4/26/2011)[/b][hr]Every change made in the production database should be considered as a release.For example, adding a trigger could affect performances and should be planned ahead, tested, released, monitored.Where does FKs fit in? (Their name has to be unique on the database level)What happens in case of the replication, if changed table is published?How this data transfer affects tlog and backup size?What happens in case there is a log shipping set up on the database? How is space affected? How are performances affected?Depending on the table structure and the way transfer is done, indexes can end up heavily fragmented if created before table is populated.[/quote]Hello. In a sense, yes, every Production change is a "release" of sorts, but for our purposes here the actual "RELEASE" is the one that includes the application changes that are depending on the new table structure. Also, the purpose of my article was not to teach people proper software development methods. I can only hope/assume that people have development, QA, and Staging environments and do these changes as they would do any updates to Production. If people are not testing and/or are doing development in Production, well, then that requires a vastly different article.As far as your specific concerns go, I addressed most of those in the article: I gave a naming convention for FKs that maintains uniqueness; I did mention that the transaction log would increase and might need to be pre-grown; I addressed when to create the indexes on the new table.I am not sure why log shipping would be an issue. And if the table is published then it would obviously need an extra 2 steps to drop the current article and add the new article, but that doesn’t really change or invalidate the overall process that I described.Take care,Solomon...Wed, 27 Apr 2011 21:23:17 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]mar10br0 (4/26/2011)[/b][hr]Great article Solomon!I do however avoid triggers like the plague in production-code as a principle...I much more take rbarbati's approach in an earlier reply-post, for this I prepare ALL my tables to ALWAYS include a timestamp-column, so I can find updated/inserted rows since a certain time.[/quote]Hello. Thanks for the compliment and the additional input. As with my reply to rbarbati, in my experience the Triggers have not been a problem, even in a SaaS environment with 24/7 access to the Table. But again, circumstances for someone else might be different and so having another method to handle the data sync is certainly valuable and appreciated. I think you could accomplish the same goal as your method with a regular DATETIME field as opposed to TIMESTAMP / ROWVERSION, which would also be human-readable though not any more functional than the TIMESTAMP (just a thought I had as I was reading your comment).However, and I forgot to mention this in my reply to rbarbati, one advantage of the Trigger method is that the data is ALWAYS up to date without a secondary process (which takes time to run) to accomplish the sync. Since we have data changing all of the time, if we did not have a Trigger then we could run into a case where we sync the data and start the process of swapping the Tables but then a change occurs and there would have to be an additional step to see if any new rows exist in the DELETEME table and if so, then move them back into the "new" current table.Take care,Solomon...Wed, 27 Apr 2011 20:19:56 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]rbarbati (4/26/2011)[/b][hr]Good article, great topic.I agree with the use of change tracking in 2008, and would add that triggers are not always straight forward. You must consider referential integrity, cascading events , and the typically more complex object relationships in an enterprise system.[/quote]Hello and thanks for both the compliment and the input. I certainly do agree that options are good to have and do appreciate your technique. In the projects that I have done with 60 - 180 million rows per table and plenty of read/write activity all throughout the day, the Triggers did not pose a problem for performance; the UPDATE and DELETE operations were JOINed on the Clustered Index (PK in these cases) and was rather quick.Since we are only working on one table at a time here (or at least no related tables), there is no concern regarding referential integrity or object relationships. In the case that the table being restructured is a child table where both it and the original table have cascade delete FKs pointing to the same parent table, if a record is deleted from the parent it will delete in both child tables leaving the trigger with no matching rows for the DELETE operation, but no logical harm. In the case that the table being copied is a parent table and both it and the original have a cascade delete FK to the same child table, if the parent gets a DELETE it will cascade to the child table and the trigger will replicate the DELETE to the copy table which will then try to cascade the delete to the child table but there will be no matching row(s) now, but again no logical harm. If the situation is more complex then the FKs can always be added during the release, just before swapping the tables.Take care,Solomon...Wed, 27 Apr 2011 19:55:21 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxAmol Naik, Jason AKA CirqueDeSQLeil, and belgarion:Thanks! I'm glad you enjoyed the article. :-)Take care,Solomon...Wed, 27 Apr 2011 19:03:50 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]andrewkane17 (4/27/2011)[/b][hr]you mention having to update views, udf and procs after the table rename, why is this required?andrew[/quote]That is because the new table and its constraints, indexes, etc, although having the same name, are different objects (with different object-ids) as far as the server is concerned. So all dependent objects (with their compiled code still referencing the old object-ids) need a recompile to reference the new table. In many cases SQL-Server is smart enough to realize the dependent objects have disappeared and will try a recompile on first use, but this is not a fail-safe method (and I have seen in the past that objects get recompiled on each use after that, or not using the appropriate stats, degrading performance).MarcoWed, 27 Apr 2011 17:19:01 GMTmar10br0http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxyou mention having to update views, udf and procs after the table rename, why is this required?andrewWed, 27 Apr 2011 09:12:05 GMTandrewkane17http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxGreat article Solomon. We used similar techniques in our legacy DB2 MVS/390 database in the early 2000's. Except in those cases we had a log reader tool from BMC called LogMax and instead of using triggers/batch jobs for change data capture between the old and new table we just put the old table into a RO mode at implementation time, ran a LogMax against the old table and applied all the transactions to the new table, and then did the switch. I have heard there are log tools for SQL Server from both Toad and RedGate. Have you considered using a log apply tool to do the data capture? Would be interested in your feedback on such an option.Thanks,-ToddWed, 27 Apr 2011 07:33:26 GMTTodd.Everetthttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]ModestyBlaise84 (4/26/2011)[/b][hr]In my opinion, adding a trigger to the production database; creating a table in production database, dropping a table from production database, creating a job in production database - IS RELEASE.Every change made in the production database should be considered as a release.For example, adding a trigger could affect performances and should be planned ahead, tested, released, monitored. Same with the other changes which makes these steps actually: Release I, Release II and Release IIIWhere does FKs fit in? (Their name has to be unique on the database level)What happens in case of the replication, if changed table is published?How this data transfer affects tlog and backup size?What happens in case there is a log shipping set up on the database? How is space affected? How are performances affected?Depending on the table structure and the way transfer is done, indexes can end up heavily fragmented if created before table is populated.Although being useful in some cases, it seems to me that this approach has limited usage and hidden risks which should have been mentioned as well.[/quote]I agree with you except your conclusion.Of course you need to plan and test everything before you apply to a production database. That includes the steps to release itself (even when done in a single step, not doing so places a ridiculous amount of faith in the release-script(s)). That doesn't diminish the usefulness of this approach at all (especially if it can get your user's experience up thousand-fold by minimizing actual downtime).Tue, 26 Apr 2011 23:32:11 GMTmar10br0http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxIn my opinion, adding a trigger to the production database; creating a table in production database, dropping a table from production database, creating a job in production database - IS RELEASE.Every change made in the production database should be considered as a release.For example, adding a trigger could affect performances and should be planned ahead, tested, released, monitored. Same with the other changes which makes these steps actually: Release I, Release II and Release IIIWhere does FKs fit in? (Their name has to be unique on the database level)What happens in case of the replication, if changed table is published?How this data transfer affects tlog and backup size?What happens in case there is a log shipping set up on the database? How is space affected? How are performances affected?Depending on the table structure and the way transfer is done, indexes can end up heavily fragmented if created before table is populated.Although being useful in some cases, it seems to me that this approach has limited usage and hidden risks which should have been mentioned as well.Tue, 26 Apr 2011 23:02:04 GMTModestyBlaise84http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxGreat article Solomon!I've often used similar methods during migrations. Even on small datasets (achieving sub-second deployment of new releases, a great feature for websites, a similar trick can be used on MS IIS by setting up the new site and just switching the root-folder and virtual folder at the same time as switching the tables).So far I've always developed a migration "application" together with the new developments making extensive use of specific contents/structures of the underlying system to optimize the migration/synchronization of data. Your article goes a long way to formalize the method (which I like a lot, since it allows me to spend more time on the new developments and less on the migration itself).I do however avoid triggers like the plague in production-code as a principle, they can cause more problems than they're worth (I've been developing SQL since version 6.5 and have learned the hard way). I much more take rbarbati's approach in an earlier reply-post, for this I prepare ALL my tables to ALWAYS include a timestamp-column, so I can find updated/inserted rows since a certain time (kept in a special table containing just one row with one timestamp field and a binary(8) field containing the previous timestamp; starting any synchronization-job by copying the real timestamp into the previous timestamp field, this way I can find a window of timestamps to look for since the previous synchronization across all tables with 100% accuracy (timestamps are global to the database and I've so far never seen any problems/bugs). To avoid problems with table-relations changing during the migration-queries, I even ignore changes made after this timestamp-window, so the timestamp really gives a snapshot of data to look at with guaranteed referential integrity even if users are making changes during the time the pre-release migration runs (those will be taken care of in the next run).One note about indexing this timestamp field though: On big tables you can create an non-clustered unique index on the timestamp and primary key. Be careful however creating indexes on tables with high modification-rates, this index is ALWAYS hit by ALL updates/inserts and it may be wiser to have the migration-code do dirty-reads (non-blocking) and a SLOW table-scan and deal with the consequences of dirty-reads rather than penalizing production-code with the index-update). You'll have to test both ways on test-servers simulating the high load and measure the penalty-hit. Deletions can of course be found by left-joining the primary keys of old and new tables. I have used 'delete' flags on some occasions rather than physical deletes, but this brings a burden to the application to always filter out those records. But it can be a great way for very fast "deletion" of records that are highly referred to by other tables without having to update those references as well as the deletion AND have a history of 'old' records AND have a good way of finding deleted records at migration-time (the timestamp is set at 'deletion'), so it may be worth it for certain tables in certain applications).Combine this with Solomons windowed copying and you have a hell of a migration-platform with low to almost zero impact on production downtime which is of more an more importance in todays globally connected world.Tue, 26 Apr 2011 18:19:48 GMTmar10br0http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxGreat arcticle, Solomon!I used to do this with humongus mainframe databases for banks back in the 90s. And yes, you really can implement the change in seconds if you're well prepared. Real 24/7 operations.It amazes me how many 'architects' still don't get this sort of technique and 'polute' databases with silly tables/views as they try to work around the fact that even a 60 second outage is unacceptable.BTW - I've used very similar techniques to migrate huge amounts of data from an old system to a new system. Triggers are very cool. ;-)Tue, 26 Apr 2011 16:47:19 GMTbelgarionhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxHi,Good article, great topic.I agree with the use of change tracking in 2008, and would add that triggers are not always straight forward. You must consider referential integrity, cascading events , and the typically more complex object relationships in an enterprise system.For simple structures, triggers can be used wisely - but watch them on any mass updates to your source as they can quickly become points of contention.For more complex structures that may already have cascading triggers, or constraints - then things can get unruly fast and you may find your self spending more time coding than is worthy of the effort.But still, options are our friends, and you've certainly presented some above with triggers and change tracking and RedGate (also newer object level SQL replication services for that matter...)One other I use on occasion (usually from a remote host via open datasource/openrowset) but basically the same on the local host, is a simple SQL statement (simple is my best friend - simple is fast, simple is stable - so whenever possible I say go simple).If again, the table/object in question is fairly stand-alone and has good key values (basics required for a trigger copy as well usually), then another approach is to sync the copy from the source using straight TSQL.Example:Table1 has a key column named RecordID and a datetime column named EntryDateTable2 is an exact duplicate structure of table1 (assumes initial bulk load).-- declare a table variableDeclare @TABVAR Table(RecordID INT)--Declare storage and set value for the latest record in your copy tableDeclare @synctime datetimeset @synctime = Select max(entydate) from Table2-- Fetch NEW IDs for processing (records in source - not yet in copy)insert into @TABVARselect RecordID from table1 where entrydate &gt;= @synctime--Delete records from your copy table, prepping for new inbound--- Sometimes not required if source is insert only. But we'll assume some updates also occur...delete from table2 where RecordID in (select RecordID from @TABVAR)--Insert and Sync dataInsert into Table2Select * from table1 where recordID in (select RecordID from @TABVAR)DoneRun this on interval during your day and your copy is in sync.May be similar to trigger code but is not bound to your source table and hence has no impact on application code or performance unless you are executing the task.* Example may be lose, but it gives you the idea .Deletes in the source are a consideration, but I suppose in any replication the first thing is a review of structures and limitations and finding the right fit.And again, this is simple code that can also be run from a remote host without need for a link server or DSN. remote host selects just use OPENDATASOURCE/ROWSET calls...)Enjoy and thanks for sharing your insights !Tue, 26 Apr 2011 15:55:26 GMTrbarbatihttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxNice article and thanks for posting and sharing your methods.Tue, 26 Apr 2011 13:52:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxExcellent stuff and very detailed. Thanks for sharing. I have done something similar in the past but never thought of using triggers, instead compared the data between old and new table quickly using Red-Gate data compare tool. But I guess using the Change tracking feature makes it even more convenient.Thanks!Tue, 26 Apr 2011 13:30:53 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]noeld (4/26/2011)[/b][hr]The article was well explained and straight to the point.The title on the other hand is *VERY* misleading (seconds?) Apparently you don't count the time you spent setting AND processing it all ... :([/quote]Hi Noel. I am sorry if you feel it was misleading, but I did address this in my article at the beginning of the Overview section. The main intent of the "quickly" making changes was to minimize customer / system impact. The end result is that from the outside (and from the perspective of the application), the restructuring does indeed take only seconds. I also stated in the article summary that this is a way to make large-scale changes that do not require a down-time or maintenance &#119;indow.Take care,Solomon...Tue, 26 Apr 2011 12:22:34 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]jvrakesh-858370 (4/26/2011)[/b][hr]This is a very good article . My Client here would do enahancements to the application frequently and I end up adding columns to the table with over 3 million rows. Any suggestions how I would do in this scenario?[/quote]Hello. I don't really understand your question. Can you please explain in more detail? Thanks.Take care,Solomon...Tue, 26 Apr 2011 12:15:05 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]sathishsathish81 (4/26/2011)[/b][hr]If Suppose i have 1 Billion Records in the Table. what is the time complexity. Could you please explain the time complexity.[/quote]Hello. The amount of time it takes to move the data over varies based on several factors:1) How wide is each row2) How much activity/contention is there on the table3) How fast is the underlying disk subsystem4) etc?So it really takes some testing on each system to really have a decent idea.Take care,Solomon...Tue, 26 Apr 2011 12:14:08 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspx[quote][b]Luke C (4/26/2011)[/b][hr]Solomon, this is a great post and thanks for sharing! Just this last week I took a similar approach with 4 large tables that had multiple columns being changed from varchar to nvarchar to support our localization effort. Much of the techniques you mention here were used with the exception of triggers. Given that we were on SQL Server 2008 I was able to use Change Tracking in place of triggers which resulted in 0 modifications to the existing tables. I'd be interested to hear how other folks have used Change Tracking or Change Data Capture.[/quote]Hey Luke. Thanks! CDC is an interesting option that I did not think of due to us being on SQL Server 2005. But if I get the chance (someday :-)), I will revise this article with that option.Take care,Solomon...Tue, 26 Apr 2011 12:11:31 GMTSolomon Rutzkyhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxThe article was well explained and straight to the point.The title on the other hand is *VERY* misleading (seconds?) Apparently you don't count the time you spent setting AND processing it all ... :(Tue, 26 Apr 2011 11:55:44 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxThis is a very good article . My Client here would do enahancements to the application frequently and I end up adding columns to the table with over 3 million rows. Any suggestions how I would do in this scenario?Tue, 26 Apr 2011 07:36:14 GMTjvrakesh-858370http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxIf Suppose i have 1 Billion Records in the Table. what is the time complexity. Could you please explain the time complexity.Tue, 26 Apr 2011 07:21:22 GMTsathishsathish81http://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxSolomon, this is a great post and thanks for sharing! Just this last week I took a similar approach with 4 large tables that had multiple columns being changed from varchar to nvarchar to support our localization effort. Much of the techniques you mention here were used with the exception of triggers. Given that we were on SQL Server 2008 I was able to use Change Tracking in place of triggers which resulted in 0 modifications to the existing tables. I'd be interested to hear how other folks have used Change Tracking or Change Data Capture. Thanks again for sharing!Tue, 26 Apr 2011 06:43:10 GMTLuke Chttp://www.sqlservercentral.com/Forums/Topic1098390-403-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Data+Modeling/72814/">Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!</A>[/B]Mon, 25 Apr 2011 21:34:04 GMTSolomon Rutzky