Calculated columns on tables can be helpful.

For example let’s assume you have a table with Name Fields and you have a formula for FullName. You typically want that formula in a single spot.  A common way to handle that is using a view and all queries can then reference the view. Another method is using a calculated column on the table.

The downside to this that I have found in the past is that now every time I want to deploy from SSDT I always get a table rebuild, or a DROP COLUMN then READD COLUMN even though the formula did not change. Note: this is also the case for Check Constraints.

Consider these examples:

Calculated Column
[CrossMidnightFlag]        AS (CAST(CASE WHEN [ShiftStartScheduleTimeKey] < [ShiftStartScheduleTimeKey] THEN 1 ELSE 0 END as bit))
Check Constraint
CONSTRAINT [CHK_CONFIG_StaffProfiles_RoleKey_IsValid] CHECK ([RoleKey] BETWEEN 1 AND 11)

This syntax is perfectly fine. However, each time you publish the project the tooling will try to rebuild those items, because the comparison is looking at the canonical form of the syntax that got created in the database.

What does that mean?

You can see what SQL actually deployed by doing the following.

  1. Publish the database project successfully.
  2. Now do a SQL Compare using the deployed database as your source and the database project as your target.
  3. You will see the tool says there are differences outlined below

 Calculated Column
[CrossMidnightFlag]                        AS           (CAST(case when [ShiftStartScheduleTimeKey]<[ShiftStartScheduleTimeKey] then (1) else (0) end as bit))
Check Constraint
CONSTRAINT [CHK_CONFIG_StaffProfiles_RoleKey_IsValid] CHECK ([RoleKey]>=(1) AND [RoleKey]<=(11))

While the formulas yield the same result they are not textually equivalent, which is what the tooling is looking at.

To optimize the build process after you publish successfully you can do the comparison above and then update the project with the differences.

Now going forward the formulas match and the rebuild will be removed.

You can read more about this in the Optimizing Scripts for Faster Incremental Deployment article.

Do you have any tips or tricks for faster deployment? Drop a comment below.