One Friday afternoon, a junior developer was tasked with a seemingly simple change: add a new NOT NULL column to a fact table called FactTransactions . Following standard practice, she opened the SSDT project, added the column to the table definition, and hit “Publish.” SSDT helpfully generated the deployment script, showing a standard ALTER TABLE ADD command. She deployed to the development environment—no issues. Then to QA—fine.
The story became a legend in their team: “Always review the actual generated deployment script before publishing—never trust the visual diff.” And they added a mandatory step to their CI/CD pipeline: generate the script, inspect it for hidden table rebuilds, then deploy. sql server data tools
After an hour of panic, the senior DBA looked at the actual script SSDT generated for that specific environment. Because the staging table already had 50 million rows, SSDT didn’t just add the column with a default—it created a new temporary table with the new schema, inserted all 50 million rows into it (leaving the new column as NULL because the default was applied at table creation, not during the bulk insert), renamed the tables, and swapped them. The default constraint was there, but the insert operation into the temp table never invoked it. The column was NULL for every existing row, violating the NOT NULL constraint. One Friday afternoon, a junior developer was tasked
But when she deployed to a pre-production staging environment that mirrored production data, disaster struck. The deployment failed with a bizarre error: “Cannot insert NULL into column ‘NewColumn’.” But the column definition had a DEFAULT value of GETDATE() ! How could it try to insert NULL? Then to QA—fine
It’s a classic SSDT moment: brilliant for source control and repeatable builds, but occasionally too clever for its own good.
A few years ago, a mid-sized financial analytics firm had a critical reporting database. Every night, a complex ETL process ran, and every morning, executives got their dashboards. The team used SSDT for version control and deployments—modeling the entire database schema as a Visual Studio project.