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

Smart defaults [SSDT]

I’ve just discovered a new, somewhat hidden, feature in SSDT that I didn’t know about and figured it would be worth highlighting here because I’ll bet not many others know it either; the feature is called Smart Defaults. It gets around the problem of adding a NOT NULLable column to an existing table that has got data in it – previous to SSDT you would need to define a DEFAULT constraint however it does feel rather cumbersome to create an object purely for the purpose of pushing through a deployment – that’s the situation that Smart Defaults is meant to alleviate.

The Smart Defaults option exists in the advanced section of a Publish Profile file:

image

The description of the setting is “Automatically provides a default value when updating a table that contains data with a column that does not allow null values”, in other words checking that option will cause SSDT to insert an arbitrary default value into your newly created NON NULLable column. In case you’re wondering how it does it, here’s how:

SSDT creates a DEFAULT CONSTRAINT at the same time as the column is created and then immediately removes that constraint:

ALTER TABLE [dbo].[T1]
   
ADD [C1] INT NOT NULL,
       
CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b] DEFAULT 0 FOR [C1];

ALTER TABLE [dbo].[T1] DROP CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b];

You can then update the value as appropriate in a Post-Deployment script. Pretty cool!

On the downside, you can only specify this option for the whole project, not for an individual table or even an individual column – I’m not sure that I’d want to turn this on for an entire project as it could hide problems that a failed deployment would highlight, in other words smart defaults could be seen to be “papering over the cracks”. If you think that should be improved go and vote (and leave a comment) at [SSDT] Allow us to specify Smart defaults per table or even per column.

@Jamiet

Published Thursday, May 31, 2012 12:55 PM by jamiet

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

 

Peter Schott said:

Thanks for the update on this. I saw the option and considered it really briefly, but decided against it for pretty much the reason you specified - hiding a bigger problem. (not to mention those names)

I'd rather have the app fail on deploy due to existing data in the table rather than succeed with a missing constraint. I'm not quite sure I'd want control over this on a per-column basis. If I've gone that far, I might as well just add a real default with a decent name that won't change between deployments.

May 31, 2012 7:02 AM
 

Dattatrey Sindol (Datta) said:

Good one Jamie!!

Thanks for sharing :-)

May 31, 2012 12:34 PM
 

Phil Steffek said:

Just in case anyone is trying to track this down: I put together a list of the "smart defaults" by data type:

http://www.sqldbpros.com/2013/10/ssdt-default-values-for-smart-defaults/

October 17, 2013 3:15 PM
 

Andrew Fenna (GOSQL) said:

Jamie, could this not be resolved by using:-

CREATE TABLE [dbo].[T1] (

 [Id] INT IDENTITY (1, 1) NOT NULL,

 [C1] INT NOT NULL

)

GO

ALTER TABLE [dbo].[T1]

ADD CONSTRAINT [DF_dbo_T1_C1] DEFAULT ((123)) FOR [C1] WITH VALUES;

GO

This would remove the need to update the fields afterwards in a post-deployment script!

What do you think?

Thanks

Andrew

December 23, 2013 10:24 AM
 

jamiet said:

Hi Andrew,

Would this not try and create the new field before the default constraint is created? Thus, by the time it attempts to create the constraint it would already have errored. Also, you'd have a constraint left lying around in your deployed database that you probably wouldn't want there after the deployment.

Thoughts? Have I misunderstood?

JT

December 24, 2013 1:53 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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