SQL Server Data Tools (SSDT) takes the Database Development Lifecycle to the next level by offering tight integration with Visual Studio Application Lifecycle Management framework.
Using SSDT, we can reverse engineer the database schema and start version controlling. This is definitely a game changer for many shops especially to those who are looking forward for better change management around DB assets..once you started treating version control tool such as TFS as source of truth for DB assets, it's possible to enhance quality by embracing some regular non-DB coding best practices such as unit testing, static code analysis and integrate the SQL development lifecycle with Build and Release management which unlocks greater abilities around traceability.
Recently, I noticed a feature which is very useful for DB development.
Imagine, you have started using SSDT, reverse engineered your DB and the model exist in TFS as SQL Server Database Project. Obviously, you might be using the build and publish features to promote the code changes across the environments.
What If someone wanted to insert "not null" column in the middle of the table… which might sound easy..
But the key requirement is they don't want to lose the data in the table..
Refer below diagram…I want to add a column called "PassportNumber (NOT NULL)" to the Employee Table which has 290 rows already…
In general, there are several different approaches people take…
they might prefer to insert a null column first and then convert that to not null column later..
or
create a batch to extract current data out of that table into temp table, delete the data, insert the not null column and move the data back..
For sure any of these approaches consume time.. and there is a better way to accomplish the same using SSDT.
If you are using SQL Server Database project, it offers bunch of different deployment (publish) options which can be accessed via the project properties à Debug tab àAdvanced (1) .. or the right click and choose Publish (2) from the context menu..
That will bring up below dialog.. make sure to check "Generate smart defaults, with applicable"..
What it does is, it'll create not null column and inserts arbitrary not null value into the newly created column.. which is very cool first step…
Later on, to update the column with actual values, you can write t-sql based update script and run the same from post-deployment as shown below
Although it gets tricky when we want to insert a not null column with unique key constraint or foreign key constraints.. I'm sure this feature alleviates a lot of pain for DB developers and improves productivity.
