Sql-server – Merits of inserting to temp table and renaming versus just adding/removing columns

best practicespage-splitssql-server-2008

If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed.

When I use RedGate SQL Compare to create conversion scripts its strategy is to create a temporary table, copy all the data into that table, drop the old table, and then rename the temporary table.

I assume this cleans up the pages as all the rows were "perfectly" sequentially inserted.

I recently had a DBA tell me that this "copy and rename" approach is inefficient, expensive and unnecessary.

What are the merits of these two approaches?

Best Answer

I recommend you read SQL Server table columns under the hood. You'll see that many column DDL operations result in having 'ghost' columns in the table, physical columns that are existing in the table but not visible to the user. A rebuild will remove all these ghost columns.

Most times this is benign, but there are some dark areas that can lead to the issues described in KB2504090:

This issue occurs because the accessor that SQL Server uses to insert data into different partitions recognizes the metadata changes incorrectly. When data is inserted into the new partition that is created after a column is dropped, the number of the maximum nullable columns in the new partition may be one fewer than the number of the maximum nullable columns in the old partition.

I for one shun all schema compare tools and comparison diff based deployment/upgrades. There simply is no one-size-fits-all correct approach with regard to changing schema. Just like Henrik mentions, I've been burned by the 500GB table 'copy', tyvm, no more diffs for me. Instead I recommend migrations, coded as SQL scripts and tested on relevant data size before being deployed. See Version Control and your Database. Rails ActiveRecord Migrations really grok this.

Related Solutions

Sql-server – How to get table columns with datatypes (and lengths) using PowerShell

The lengths are found at <Column>.Properties['Length'].Value, so you can select it like:

#Get column names
$colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' | 
    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} | 
        ForEach-Object {$_.Columns} |
            Select-Object Name, DataType, `
                @{Name='Length'; Expression = {$_.Properties['Length'].Value}}
$colNames

Sql-server – Performance Inserting and Updating Millions of rows into a table

If you are bringing data from a CSV with a large import process, I'd recommend you utilize a staging table. This will allow you to dump all of your imported data into the staging table that won't be queried by end-users/applications. You can use this staging table as a source for your INSERT and UPDATE operations.

Once the data is in the staging table you can kick off a stored procedure (or multiple stored procedures, depending on how vast your business logic is) to do the necessary INSERT and UPDATE DML statements into the actual queried table.

As for performance, you need to decide what is more important for your implementation. If you're looking to get the data in the table as quickly as possible, a very feasible route may be to disable the indexes. But realize that OLAP performance will be impacted by that. If you want to ensure the minimal amount of end-user impact then you would have to approach this a different way. When you say "the DB should be kept availabel as normal" then I would assume the latter.

Performance is double-edged in this situation. Performance for the data import will most likely take away from performance of end-user interaction with the data, and vice versa.

Best Answer

Related Solutions

Sql-server – How to get table columns with datatypes (and lengths) using PowerShell

Sql-server – Performance Inserting and Updating Millions of rows into a table

Related Question