SSDT Default Values For Smart Defaults

There's a handy feature in SSDT (SQL Server Development Tools) that will automatically insert a default value into a column when you change it from nullable to NOT NULL. I wasn't able to track down documentation specifying what the default values for the "smart defaults" were so I hopped into the SQL DB Pros corporate jet and headed to our top secret North Pole laboratory to see what I could figure out. The results are below.


Default Values For SSDT Smart Defaults


bigint 0
binary 0x00
bit 0
char  <empty string>
date 1900-01-01
datetime 1900-01-01 00:00:00.000
datetime2 1900-01-01 00:00:00.0000000
datetimeoffset 1900-01-01 00:00:00.0000000 +00:00
decimal 0
float 0
hierarchyid  <smart default not valid>
image 0x
int 0
money 0.00
nchar   <empty string>
ntext   <empty string>
numeric 0
nvarchar   <empty string>
real 0
smalldatetime 1900-01-01 00:00:00
smallint 0
smallmoney 0.00
sql_variant   <smart default not valid>
text   <empty string>
time 00:00:00.0000000
timestamp  <null value not allowed>
tinyint 0
uniqueidentifier 00000000-0000-0000-0000-000000000000
varbinary 0x
varchar   <empty string>
xml   <empty string>

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published.