Problem Changing Column Order in a Table (SQL Server 2008)

Question

When I try to change this columns list:

Nombre
Imagen
Descripcion
Activo
IdLineaProducto

into this (IdLineaProducto is first)

IdLineaProducto
Nombre
Imagen
Descripcion
Activo

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

The same happens in other situations, like trying to transform an int non-identity column to identity. Why happens this? How can I resolve this? Maybe some misconfigured issue? Some new feature in SQL Server?

Answer 1

That is just the way it works... you can only add columns to the end, unless you DROP it and CREATE it (normally copying the data out and back in, which the IDE might help with). But why do you want to change the order? Even if it isn't ideal, your SELECT etc can handle this... i.e.

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

(it is rarely a good idea to just use SELECT *)

---

Re the IDENTITY; again, this is fundamental to the column - although in this case you could copy the data out, DROP the column, and re-ADD the column as IDENTITY (on the right hand side), and copy it back in - but then you have extra complications with IDENTITY INSERT and the SEED value (so you don't try to get duplicates of existing identity values).

Answer 2

I found the solution to this problem.

This is by design and can be quickly fixed in Management Studio by unchecking a property. To fix this in Management Studio, go to Tools -> Options then go to the Designer Page and uncheck "Prevent saving changes that require table re-creation".

Beware that it may have undesired consequences, like losing data in other tables related to this.