How to change the column order in a SQL Server Compact Edition table schema?

Question

I'm using Visual Studio 2008 to design a SQL Server Compact Edition database (*.sdf). I had to change the schema of one of my tables adding a new column.

I want to "move" that new column from the bottom to a different position in the field list. How can I do this in the Visual Studio designer?

EDIT: I know that from a pure technical view point the order of the columns doesn't matter. I'm prototyping an application, so I have to change the schema a couple of times. If, for example I have to change the primary key, it would be "ugly" having the most important column at the end. Other developers looking at the code would be confused, I think. It's a matter of esthetics.

Answer 1

you can't just move it in the designer. You'll have to create a new column, drop the old one, generate the script and edit the script where it inserts into the temp table from the old table, making the old column value (in the select) go into the new column.

Answer 2

I agree with Pax. If, for a specific reason, you need to return fields in a specific order in your query, just alter the query putting the field in the place where you need it.

If, for whatever reason, you need at all costs to move that field, you can do it with a script like the following, which makes FIELD3 the first column in a table called TestTable:

/* Original sample table with three fields */
CREATE TABLE [dbo].[TestTable](
    [FIELD1] [nchar](10) NULL,
    [FIELD2] [nchar](10) NULL,
    [FIELD3] [nchar](10) NULL
) ON [PRIMARY]

/* The following script will make FIELD3 the first column */
CREATE TABLE dbo.Tmp_TestTable
    (
    FIELD3 nchar(10) NULL,
    FIELD1 nchar(10) NULL,
    FIELD2 nchar(10) NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.TestTable)
     EXEC('INSERT INTO dbo.Tmp_TestTable (FIELD3, FIELD1, FIELD2)
        SELECT FIELD3, FIELD1, FIELD2 FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.TestTable
GO

EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO

However, I insist that probably your problem could be solved with a different approach which doesn't require restructuring table.