Issue Reordering columns in a table (SQL Server 2008)

Question

When I try to change this list of columns:

Nombre Imagen Descripcion Activo IdLineaProducto 

into this (first IdLineaProducto)

 IdLineaProducto Nombre Imagen Descripcion Activo 

SQL Server Management Studio says that it is not allowed to save changes because the table needs to be deleted and recreated (in Spanish ...). I could do this in SQL Server 2005, but not in SQL Server 2008.

The same thing happens in other situations, for example, when you try to convert a non-identifier identity column. Why is this happening? How can i solve this? Maybe an incorrect problem? New feature in SQL Server?

Answer 1

I found a solution for this problem .

This is by design and can be quickly fixed in Management Studio by unchecking. To fix this in Management Studio, go to Tools → Then select “Options” on the “Designer” page and uncheck the box “Prevent change changes that require re-creating the table”.

Beware that this could be undesirable consequences , for example, losing data in other tables associated with it.

Answer 2

This is how it works ... you can only add columns to the end if you are not DROP and CREATE (usually copy data back and forth that the IDE can help), but why do you want to change the order? Even if it's not perfect, your SELECT etc. Can handle it ... ie

 SELECT IdLineaProducto, Nombre, Imagen, Descripcion, Activo FROM TheTable ... 

(it is rarely easy to use SELECT * )

---

Re IDENTITY again, this is the fundamental value for the column, although in this case you could copy the data, DROP column and re ADD column as IDENTITY (on the right side), and copy it back but you have additional complications with IDENTITY INSERT and value SEED ( therefore you are not trying to get duplicates of existing identification values).