THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

CASTs become CONVERTs in computed columns with implications for SSDT

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:

image

you see this:

image

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):

image

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:

image

Another one, keywords in CHECK constraints get uncapitalised* and parentheses get added in:

image

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”?

UPDATE: The SSDT team have discussed this issue on their blog at Optimizing Scripts for Faster Incremental Deployment
Published Thursday, July 19, 2012 12:03 PM by jamiet
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

PhilP said:

There is no pleasing opposite of 'capitalised'. If you can stomach the forced transition from noun to verb, 'lowercase' is about as good as you can get. As in: "that sentence needs to be lowercased!"

July 19, 2012 6:54 AM
 

Dave Ballantyne said:

In addition to your case, there is a bug with peristed computed columns always beening seen as a change.

http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/8cee9593-3edd-49fb-958d-6b824ca30cea

".....I had a hunch when I first read the post that the column was indeed a nullable persisted computed column. There is currently a known bug that our deployment stack does not appropriately account for changes to the nullability of persisted computed columns thus causing the drop/add on the columns on deployment you are experiencing."

July 19, 2012 7:40 AM
 

jbooker said:

How about 'minusculized'?  Think it'll catch on?

July 21, 2012 9:17 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement