Optimizing Scripts for Faster Incremental Deployment

  • Article

Incremental deployment is the process of updating an existing database with new changes that were made by the developer in SSDT. Below we show you a tip on how to speed up incremental deployment by storing your scripts in their canonical form.

Background

SQL Server stores scripts in canonical form to have a standardized way of presenting scripts that have the same semantics but different syntax. 

For example, if you create a table with a CHECK constraint that makes sure that the value of column c1 is BETWEEN 1 and 5, SQL Server will end up storing the CHECK constraint's condition in a
different syntax.

 CREATE TABLE [dbo].[Table1]
 (
 [Id] INT NOT NULL PRIMARY KEY,
 [c1] INT NOT NULL,
 CHECK (c1 BETWEEN 1 and 5)
 )

If you view the constraint definition in SQL Server Management Studio, you will get something as follows:

 ALTER TABLE [dbo].[Table1] WITH CHECK ADD CHECK (([c1]>=(1) AND [c1]<=(5)))

Notice how the condition's syntax changed from "c1 BETWEEN 1 and 5" to "(([c1]>=(1) AND [c1]<=(5)))". Although their syntax is different, those two expressions have the same exact meaning, and SQL Server chooses a standard format to represent both.

Current Behavior in SSDT

Due to the lack of a public API to do so, SSDT doesn't currently have the capability to convert scripts into their canonical form. So it simply compares the script body of objects using string comparison. As a result, during incremental deployment, the script for an object written by the developer in SSDT and the one stored in the database do not syntactically match, and thus the object will be treated as changed. Given that the object is changed, we will either alter it or drop it and then re-create it. This means that any other objects that depend on this object will need to be also dropped and recreated. 

How to improve performance?

After you have deployed your SSDT project for the first time, go to SQL \ Schema Compare \ New Schema Comparison. Select the database as the source and the project as the target, then click Compare. If some objects are different, this is a likely a sign that their definition in the project needs to match the canonical form in the database.

Click “Update”, and this should update your project with the syntax in canonical form. Remember to choose the project as the “target”.

Examples

Below are a few cases where customers noticed that objects got dropped and re-created on incremental deployment although the objects' definitions didn't actually change. If you discover more cases, please share and we can update this blog post with your links so that other people can benefit from it.

We hope this will help in speeding up your incremental deployments. Enjoy!