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

Why? In the name of all that's holy, why?

SQL is a relational algebra. It doesn't matter where the columns are.

Edit: This was really meant to be a half-humorous answer, I'd rather you vote up a solution. This answer has a point in that it shouldn't matter since order is irrelevant to the DBMS but there may be a genuine reason for the questioner needing this.

That's why I made it community wiki so as to avoid the barrage of downvotes :-) Hopefully splattne will accept a real answer.

Answer 3

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.

Answer 4

The database denormalization, also. For example, suppose you have two tables in a database: Orders and Order Details. The Orders table contains information about a customer's order. The individual products in each order are contained in the Order Details table.good article, i really like it. I am doing a bit on research about SQL Server Compact Edition table schema and i found also macrotesting www.macrotesting.com to be very good source. Thanks for you article.....

Regards... Meganathan..

Answer 5

Script the database using SDF Viewer save the script, create a new blank database and reload the script, cut and paste the column definitions into your required order and then run the script.