SQL Project in VS: Why column order matters?

[Lomanu4]

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.

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.