[Bug]: Schema Compare then Publish creates any new tables with ANSI_NULL OFF

[pthivierge]

Description

Expected the MSSQL extension (specifically the Schema Compare feature) to preserve the database's default ANSI_NULL setting (which is ON) when generating the publish script.

But what actually happened was:

When using Schema Compare to create new tables on the target database, the generated CREATE TABLE scripts explicitly include SET ANSI_NULL OFF or simply create the tables without respecting the target database default setting, resulting in tables being created with ANSI_NULL OFF. This is incorrect behavior for modern SQL Server/Azure SQL Database development and can lead to unexpected column behavior.

Steps to Reproduce

Set up the source database: Ensure your source database or project contains a table. (Optional: Check that the default ANSI_NULL setting on the target database is ON).

Create a new table: Add a new table to the source that does not exist in the target database.

Run Schema Compare: Use the MSSQL extension's Schema Compare feature, setting the source to your project/database and the target to the destination database.

Publish Changes: Review the generated script (it should contain a CREATE TABLE statement for the new table) and click Publish.

Verify the new table: After publishing, check the properties of the newly created table on the target database (e.g., using sp_help 'NewTableName') or examine the ANSI_NULLS setting on one of its columns.

Observed unexpected output or behavior: The newly created table (and its columns) has ANSI_NULL OFF, overriding the expected modern SQL Server behavior of having ANSI_NULL ON.

Note:
Several DacFx and Azure Data Studio/VS Code issues describe the same problem. When you compare a .sqlproj project with a database using Schema Compare and then generate or apply the script, the tool incorrectly prepends each object creation with SET ANSI_NULLS OFF (even when the project’s default is ON). This causes any indexes that follow to fail with the error you reported. Microsoft engineers have confirmed that this bug occurs only when the comparison source is a .sqlproj. Using a compiled .dacpac as the source does not produce the erroneous SET statements
github.com
. As of the most recent comments (Oct 2025 and Nov 2025), the bug is still open and affects the new Database Projects extension in VS Code
github.com
.

Affected Area

• Connection dialog (SQL Server | Azure browse/Fabric browse)
• Query editor
• Query results panel
• Object Explorer
• GitHub Copilot integration
• Preview/Edit data
• Table Designer
• Schema Designer
• Schema Compare
• Local SQL Server Container provisioning
• SQL database in Fabric provisioning
• DACPAC/BACPAC export/import
• SQL Database projects
• Query Plan Visualizer
• Other (please describe below)

If you selected "Other", please describe the affected area

No response

Environment Information

VS code: 1.105.1
vs-code-mssql: 1.36.0

Confirmation

• I have searched existing issues and couldn't find a match
• I want to work on this issue