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