SQL Server 2008:Cannot Insert New Column in the Middle Position and Change Data Type

SQL Server 2008 : Cannot Insert new column in the middle position and change data type

In SSMS Tools -> Options -> Designers you would need to uncheck the option "Prevent Saving Changes that require table re-creation" to allow you to do this in SSMS.

This will rebuild the table and so generally isn't worth the hassle if the table is at all large and will make deployment to production trickier.

If there are columns which logically you would prefer to have next to each other to make writing queries easier you can create a View with the desired column order.

how to add a column at a specific position in a table?

Not very easy in sql server..

I think the best way is to add the column at the end of the table and create a view with desired order of columns..

another option..

put all the data in a temp table and recreate the actual table with correct column order, then insert the data back to the actual table from temp table

SQL Server 2008 R2 Add column into a specific location

You cannot. Column are always added at the end of the column list. The only way to change the order is to recreate the table from scratch.

That being said, it should never ever matter to you what is the actual physical order of the columns, nor the logical order of column definitions. If you have dependencies on column order, your code is broken. If you expect performance gains from column order, those are myths.

Add a column to specific position in MSSQL Server

You would need to drop the table and recreate it with the columns in the correct order. If you make the table changes in SSMS then it can generate a change script for you which you could then use to deploy the change to a production server.

Cannot insert a new column in a table

ALTER TABLE {TABLENAME} 
    ADD {COLUMNNAME} {TYPE} {NOT NULL} 
    CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

You have to set a default value.

ALTER TABLE Product ADD Modified_By datetime DEFAULT (GETDATE())

The default value will be set to today.

Changing the column order/adding new column for existing table in SQL Server 2008

One possibility would be to not bother about reordering the columns in the table and simply modify it by add the columns. Then, create a view which has the columns in the order you want -- assuming that the order is truly important. The view can be easily changed to reflect any ordering that you want. Since I can't imagine that the order would be important for programmatic applications, the view should suffice for those manual queries where it might be important.

Change Table Structure in SQl Server 2008

Go to: Tools - Options - Designers - Table and Database Designers and de-select "Prevent saving changes that requiere table re-creation"

Can't change table design in SQL Server 2008

The answer is on the MSDN site:

The Save (Not Permitted) dialog box warns you that saving changes is
not permitted because the changes you have made require the listed
tables to be dropped and re-created.

The following actions might require a table to be re-created:

• Adding a new column to the middle of the table
• Dropping a column
• Changing column nullability
• Changing the order of the columns
• Changing the data type of a column

EDIT 1:

Additional useful informations from here:

To change the Prevent saving changes that require the table
re-creation option, follow these steps:

1. Open SQL Server Management Studio (SSMS).
2. On the Tools menu, click Options.
3. In the navigation pane of the Options window, click Designers.
4. Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata
structure of the table. In this case, data loss may occur when you
save the table.

Risk of turning off the "Prevent saving changes that require table re-creation" option

Although turning off this option can help you avoid re-creating a
table, it can also lead to changes being lost. For example, suppose
that you enable the Change Tracking feature in SQL Server 2008 to
track changes to the table. When you perform an operation that causes
the table to be re-created, you receive the error message that is
mentioned in the "Symptoms" section. However, if you turn off this
option, the existing change tracking information is deleted when the
table is re-created. Therefore, we recommend that you do not work
around this problem by turning off the option.

---

Related Topics