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.

Solution 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.

Solution 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.

Comments

[splattne]

Thanks for the answer. How can I script a table in VS? I didn't find that option.

[splattne]

It's just my anal retentiveness. ;-) I can't look at a table with a column which is the most important at the end...

[Diego]

Ok, I hope my suggestion can be of some help then. :)

[KM.]

make sure you copy over all the other table items: index, fk, check constraints, etc...

[splattne]

I must be blind. I can't find the toolbar. Are you sure you used a SQL Server Compact database?

[splattne]

Hm, I'm looking for a way to script the objects. Seems to be not a trivial task for a SQL Server COMPACT database...

[KM.]

I'm not using CTE, and after checking around, you're out of luck for a Microsoft scripting solution, because there isn't one. People have said to use this: primeworks-mobile.com/Products/DataPortConsole.html

[splattne]

Thank you, KMike! I guess the best I can do now, is drop that table and recreate it from scratch.