Optimizing Scripts for Faster Incremental Deployment

Article
08/12/2013

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!

Comments

Anonymous
August 12, 2013
One case that we are hit with quite frequently is when using the IN syntax in a check constraint, such as: CHECK (c1 IN ('Alpha', 'Beta', 'Gamma')) This is converted by SQL Server to: CHECK ((c1 = 'Alpha') OR (c1 = 'Beta') OR (c1 = 'Gamma')) (Which is a shame, because the latter syntax is much harder to maintain.)
Anonymous
August 13, 2013
Nice! Thanks for sharing Daniel! Hopefully the tip on using schema compare to update the scripts in the project made things easier for you.
Anonymous
January 13, 2014
I had this trying to use current_timestamp on table default constraints. SQL Server converts it to GETDATE() Like the idea for getting the model synched, thanks
Anonymous
May 20, 2014
Muito bom Post.
Anonymous
March 23, 2015
Warning: This GRANT statement was mangled in my case (SSDT 12.0.50318.0): Before: GRANT SELECT,INSERT,UPDATE,DELETE ON [dbo].[MyTable] TO [MyRole] After: GRANT UPDATE ON OBJECT::[dbo].[MyTable] TO [MyRole] AS [dbo]; Notice that the SELECT, INSERT, and DELETE grants were not preserved.
Anonymous
December 01, 2017
I believe everything said was actually very logical.However, what about this? suppose you added a little content?I mean, I don't want to tell you how to run your website, but suppose you added a headline that makes people want more? I mean Optimizing Scripts for Faster Incremental Deployment | SQL Server Data Tools Team Blog is a little plain. You should glance at Yahoo's home page and watch how they create article titles to get people to click. You might add a related video or a picture or two to get readers interested about everything've got to say.In my opinion, it might make your website a little bit more interesting.

Share via