1

Does anyone know how the SchemaCompare in Visual Studio (using 2010 currently) determines how to handle [SQL Server 2008R2] database table updates (column data type, optionality, etc)?

The options are to:

  • Use separate ALTER TABLE statements
  • Create a new table, copy the old data into the new table, rename the old table before the new one can be renamed to assume the proper name

I'm asking because we have a situation involving a TIMESTAMP column (for optimistic locking). If SchemaCompare uses the new table approach, the TIMESTAMP column values will change & cause problems for anyone with the old TIMESTAMP values.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Should be fairly easy to find out via SQL Profiler. My money would be on the `ALTER TABLE` variant. – Tomalak Dec 18 '11 at 17:46

1 Answers1

2

I believe Schema Compare employs the same CREATE-COPY-DROP-RENAME (CCDR) strategy as VSTSDB described here: link

Should be able to confirm this by running a compare and scripting out the deploy, no?

nathan_jr
  • 9,092
  • 3
  • 40
  • 55
  • There are situations where it will generate `ALTER TABLE` statements. However, the only time I've seen that was shortly after I used `ALTER TABLE` statements on a table and immediately compared the original database with the updated database. In this case, I was merely adding new columns; SQL Server just appended them to the end of the table. Usually, it does the create, copy, drop, and rename so I was actually a bit surprised. – Zarepheth Mar 26 '14 at 17:57
  • 1
    it will use alter table if adding columns to the end of the table. – Dasith Wijes Jun 12 '15 at 03:58