I discovered a peculiar little nuance in SQL Server yesterday that I think is worth sharing. Execute the following CREATE TABLE statement:
CREATE TABLE _t(c INT,
[S_Profit] AS (CAST (1 AS DECIMAL (19, 6)))
);
If you then go to Object Explorer and script that table out:
you see this:
Notice how the definition of the [S_Profit] computed column is not the same as it was what we created the table, it is now a CONVERT rather than a CAST.
OK, so that behaviour might be considered a little strange but its hard to see why it might be a problem. Well, it actually did create a problem for me yesterday as I shall now explain.
On my current project we are using Publish in SQL Server Data Tools (SSDT) [and by “SSDT” I mean the database projects, not the Visual Studio shell around the SSIS/SSAS/SSRS stuff] to deploy our database schema. That schema included computed columns using CAST and hence every time we attempted a publish SSDT would compare the table definition in the project with the existing table in the target and notice that they were different. This can be better better illustrated using SSDT’s Schema Compare tool (which does essentially the same comparison as is done at publish time):
Notice where I have underlined in red that the table definition in my source uses a CAST yet in the target it was changed to a CONVERT the last time we published. Notice also, underlined in green, that another expression has been rewritten to take out parentheses. Here’s a similar case where DEFAULT constraints also get rewritten:
Another one, keywords in CHECK constraints get uncapitalised* and parentheses get added in:
At publish time SSDT will generate a script that will attempt to make the target look like the source hence it is going to be making the same changes to these computed columns, default constraints and check constraints every single time you publish. Also note that any dependent objects will get affected too; e.g. dependent views may get dropped and recreated – if those views have indexes on them and there is a lot of data in those indexes then your publish operations are going to take a longggggg time.
The point is, your SSDT publishes might be working fine yet doing a lot more work than they actually need to so I would encourage you to change the default constraints, check constraints and computed column definitions in your source code to whatever SQL Server rewrites it to. The Schema Compare tool is your friend when trying to do this.
UPDATE: There was a little confusion from someone on Twitter as to where the problem lies here. Its is not something that SSDT is doing incorrectly, the re-writing is being done by the database engine.
@Jamiet
*Is “uncapitalised” a word? What is the opposite of “capitalised”?