CODEWITHSUNDEEP
sql-serverschema
Darthg8r
Darthg8r

Reputation: 12675

Schema change table rebuild

I'm working on a script to keep table schemas synchronized.

Is there an exhaustive list of actions done to a table schema in MS SQL that requires the table to be dropped and recreated and the data to be reinserted?

Upvotes: 2

Views: 1960

Answers (3)

D. Lambert
D. Lambert

Reputation: 1304

I've gotten pretty spoiled by using Visual Studio Database Projects to manage this sort of thing. Once my schema is imported into a project, I can make whatever change I want, and the VSDP will figure out whether the change can be done w/o dropping objects (with an ALTER, for example), or whether it needs to create a new object and copy values over from the old one (which it does automatically).

Plan on a little work to understand how you'll fit this into your specific environment and workflow, but I've found the effort to be very worthwhile.

Upvotes: 1

nathan_jr
nathan_jr

Reputation: 9282

You may be better off standardizing on the CREATE-COPY-DROP-RENAME (CCDR) strategy and only attempting an in-place alter in the few scenarios where your DDL will not require a rebuild rather than trying to compile the exhaustive list. This is the strategy described here: link.

AFAIK, you are only permitted to add columns to an existing table (without rebuilding) if the column is:

  1. added to the end of the table AND
  2. is nullable or has a default constraint

In all other cases, MSSQL will potentially fail if it does not know what to use as a value in the rows of the newly added column or data loss is a result (truncation for example). Even defaulted columns added in the middle will force a rebuild.

To further complicate things, in some cases the success of your deploy will depend on the type of data in the table, and not simply the schema involved. For example, altering a column length to a greater value (varchar(50) --> varchar(100)) will likely succeed; however, decreasing the length is only sometimes permitted. Migrating data type changes is another tricky mess.

In short, I would always rebuild and rarely alter in place.

--

To illustrate in-row data affecting outcome:

create table dbo.Yak(s varchar(100));
insert into dbo.Yak
    values(replicate('a', 100));
go

-- attempt to alter datatype to 50 (FAIL: String or binary data would be truncated.)
alter table dbo.Yak 
    alter column s varchar(50);
go

-- shorten the data in row to avoid data loss
delete from dbo.Yak;
insert into dbo.Yak
    values(replicate('a', 50));
go

-- again, attempt to alter datatype to 50 (SUCCESS)
alter table dbo.Yak 
    alter column s varchar(50);
go

select len(s),* from dbo.Yak;
go

--cleanup
drop table dbo.Yak;
go

Upvotes: 3

devio
devio

Reputation: 37205

In Management Studio, select the table you want to change and right-click Design. Change the datatype of a column in the table design window (tested with int to money).

Instead of saving, right-click in the window and select "Generate Change Script". Copy the SQL statements from the dialog.

*) In previous versions (SQL2000), any changes would recreate the whole table (as far as I remember). It seems that renaming and adding columns have been optimized to ALTER TABLE statements.

Upvotes: 1

Related Questions