Problem with ALTER TABLE within a Transaction
Brian Donahue
Posts: 6,590 Bronze 1
Hello,
Sorry about the delay. I was under the impression that the first problem was because the third column needs a default value because you may have changed it from allowing NULLs to not allowing NULLs. If there is existing NULL data in the table, the NOT NULL constraint will cause the ALTER TABLE to fail.
The older version of SQL Compare used to always rebuild tables. Version 3+ has some intelligence that can determine if the table actually needs to be rebuilt or if an ALTER TABLE will suffice. For example, if you use the 'force column order to be identical' option and you need to insert a column in the middle of a table, SQL Compare will make a new table, copy all of the data from the old table, drop the old table, and rename the new one to the old table's name.
Sorry about the delay. I was under the impression that the first problem was because the third column needs a default value because you may have changed it from allowing NULLs to not allowing NULLs. If there is existing NULL data in the table, the NOT NULL constraint will cause the ALTER TABLE to fail.
The older version of SQL Compare used to always rebuild tables. Version 3+ has some intelligence that can determine if the table actually needs to be rebuilt or if an ALTER TABLE will suffice. For example, if you use the 'force column order to be identical' option and you need to insert a column in the middle of a table, SQL Compare will make a new table, copy all of the data from the old table, drop the old table, and rename the new one to the old table's name.
This discussion has been closed.
