SQL Project in VS: Why column order matters?

Many legacy projects use separate SQL projects to maintain SQL database code in Visual Studio. SQL Projects provide a great way to manage your database schema as code and CI/CD integration. But subtle details can make a big impact - like how you add new columns to existing tables. Why Column Order Matters? When using SQL Projects, any schema change (no matter how small) is interpreted by the SSDT build process and turned into deployment scripts. Here is an example: There is an existing table TestTable123 CREATE TABLE [dbo].[TestTable123] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(10) NULL, [Description] NVARCHAR(100) NULL, [StatusId] INT NULL ) You add a new column into the middle of the existing table CREATE TABLE [dbo].[TestTable123] ( [Id] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(10) NULL, [Description] NVARCHAR(100) NULL, [Created] DATETIME NULL, -- new column [StatusId] INT NULL -- existing column ) When you deploy this change to your database, SSDT treats it as a breaking change and uses the following SQL code: -- create the temp table CREATE TABLE [dbo].[tmp_ms_xx_TestTable123] ( [Id] INT NOT NULL, [Created] DATETIME NULL, [Name] NVARCHAR (10) NULL, [Description] NVARCHAR (100) NULL, [StatusId] INT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) ); -- copies all data into new temp table IF EXISTS (SELECT TOP 1 1 FROM [dbo].[TestTable123]) BEGIN INSERT INTO [dbo].[tmp_ms_xx_TestTable123] ([Id], [Name], [Description], [StatusId]) SELECT [Id], [Name], [Description], [StatusId] FROM [dbo].[TestTable123] ORDER BY [Id] ASC; END -- remove the existing table DROP TABLE [dbo].[TestTable123]; -- rename the new temp table EXECUTE sp_rename N'[dbo].[tmp_ms_xx_TestTable123]', N'TestTable123'; This approach: Is slow for large tables, Requires more transaction log space, Risks of locking or downtime. Best Practice: Append Columns Whenever possible, add new columns at the end of the table. This helps SSDT generate a simple ALTER TABLE ... ADD statement, which: Is fast, Non-blocking (in most cases), Doesn’t require temp tables, Keeps deployments safer and more predictable. Just because SSDT simplifies your deployments doesn't mean you shouldn't understand how it works under the hood. Knowing why certain practices—like adding columns at the end—matter can help you avoid performance issues, deployment delays, and production downtime. SSDT is a powerful tool, but it's only as effective as the developer using it.

May 16, 2025 - 21:26

Many legacy projects use separate SQL projects to maintain SQL database code in Visual Studio. SQL Projects provide a great way to manage your database schema as code and CI/CD integration. But subtle details can make a big impact - like how you add new columns to existing tables.

Why Column Order Matters?

When using SQL Projects, any schema change (no matter how small) is interpreted by the SSDT build process and turned into deployment scripts. Here is an example:

There is an existing table TestTable123

CREATE TABLE [dbo].[TestTable123]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(10) NULL,
    [Description] NVARCHAR(100) NULL,
    [StatusId] INT NULL
)

You add a new column into the middle of the existing table

CREATE TABLE [dbo].[TestTable123]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(10) NULL,
    [Description] NVARCHAR(100) NULL,
    [Created] DATETIME NULL,             -- new column
    [StatusId] INT NULL                  -- existing column
)

When you deploy this change to your database, SSDT treats it as a breaking change and uses the following SQL code:

-- create the temp table
CREATE TABLE [dbo].[tmp_ms_xx_TestTable123] (
    [Id]          INT            NOT NULL,
    [Created]     DATETIME       NULL,
    [Name]        NVARCHAR (10)  NULL,
    [Description] NVARCHAR (100) NULL,
    [StatusId]    INT            NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

-- copies all data into new temp table
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[TestTable123])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_TestTable123] ([Id], [Name], [Description], [StatusId])
        SELECT   [Id],
                 [Name],
                 [Description],
                 [StatusId]
        FROM     [dbo].[TestTable123]
        ORDER BY [Id] ASC;
    END

-- remove the existing table
DROP TABLE [dbo].[TestTable123];

-- rename the new temp table 
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_TestTable123]', N'TestTable123';

This approach:

Is slow for large tables,
Requires more transaction log space,
Risks of locking or downtime.

Best Practice: Append Columns

Whenever possible, add new columns at the end of the table. This helps SSDT generate a simple ALTER TABLE ... ADD statement, which:

Is fast,
Non-blocking (in most cases),
Doesn’t require temp tables,
Keeps deployments safer and more predictable.

Just because SSDT simplifies your deployments doesn't mean you shouldn't understand how it works under the hood.

Knowing why certain practices—like adding columns at the end—matter can help you avoid performance issues, deployment delays, and production downtime.

SSDT is a powerful tool, but it's only as effective as the developer using it.