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