• 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. 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.8GB

    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.

    Side-by-side implementations are great but I feel sometimes there is an argument for doing these changes in-situ, 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.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.