Blog Post

SQL Compare Force Column Order in a Table


I had a client that was concerned about SQL Compare behavior when a developer adds a column to the middle of a table. I wanted to reassure them, so I wrote this post to show how SQL Compare behaves by default.

This is part of a series of posts on SQL Compare

Making a Table Change

Let’s assume I have this table in a database:

CREATE TABLE [dbo].[Product]
[ProductID] [int] NOT NULL,
[ProductName] [varchar] (50) NULL,
[ProductDesc] [varchar] (1000) NULL,
[ProductSize] [char] (1) NULL,
[ProductWeight] [int] NULL,
[ProductColor] [varchar] (20) NULL,
[StatusID] [int] NULL

I want to add a column to this table, called ProductQtyPerUnit. However, I decide to add this before that StatusID column so all my product data is together.

Note: This shouldn’t be done. Don’t worry about order of columns. Deal with that in your INSERT/SELECT statements instead.

If I do this in the SSMS designer, I’ll right click the table and select INSERT Column.

2024-03-12 12_23_15

Then I can add the column, as appropriate to my table.

2024-03-12 12_24_54

Before I save this, I’ll create a scripts folder and compare things. As you can see, things are in synch.

2024-03-14 13_10_46

Now I’ll save the change.

SQL Compare Behavior

Now I’ll refresh my project. When I do that, I see a difference, as I should. Note that SQL Compare detects the change, and shows the new column in the middle of the table.

2024-03-14 13_12_56

I’ll click Deploy and generate the deployment script. When I do that, I see the script below. Note that SQL Compare has just added a column, not rebuilt the table.

2024-03-14 13_14_21

This is controlled by the Force Column Order option, which is off by default. This is the way we’d like to have the tool behave, as rebuilding tables is unnecessary.

I’ll close this dialog and then click Edit Project and select the options tab. I can search for Force and see the option is off.

2024-03-14 13_15_32

to show how this works, I’ll check the checkbox and then recompare. Now when I generate the deployment script, I see this. The deployment wizard opens to this warning.

2024-03-14 13_16_30

If I view this script, you can see below that this part of the script creates a new table and then renames it after data is moved and the old table dropped.

2024-03-14 13_17_13

In general, you should leave this option off all the time. The physical order of columns doesn’t matter.

If you haven’t used SQL Compare from Redgate, it’s the industry standard for SQL Server schema comparison and an amazing tool. Download an eval today and give it a try.

Original post (opens in new tab)
View comments in original post (opens in new tab)


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating