It is currently possible to define a default value for a column as a transformation. Due to the (rather strange) choice of using an actual transformation for this, it is impossible to apply this to Simple Mode tables.
It is quite common to want a backup value for a field in case a mapping does not fill it. In staging tables you will commonly need to add a transformation with an IsEmpty condition, in MDW tables you need to turn off Simple Mode and add a transformation with a default column value. The latter adds a raw table with associated I/O.
A better solution would be to use a named default constraint (see: https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-default-values-for-columns?view=sql-server-ver16) that uses DDL to ensure a field gets a default value if it is not specified in an insert. Obviously this means dropping the constraint to change data types, but that is minimal overhead compared to the advantage of not needing explicit transformations.
Default column constraints can also be applied in bulk operations (see: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/keep-nulls-or-use-default-values-during-bulk-import-sql-server?view=sql-server-ver16) meaning that they can also apply to Custom Data. This is an improvement to the current status quo there:
Default column values make the model more explicit and allow a user to see this information from the database structure instead of needing to dive into code. A default value should be a property of a field, not a transformation. The fact that SQL Server deviates from the ANSI standard does not block the implementation here. Additionally, this allows more use of Simple Mode which should speed up performance.
