WilberBeast

Often it is convenient to be able to default values in where they are not specified, or to not require items to be specified to make life easier.
For maintainability it is best to mark default values in the data itself, rather than in some code somewhere, and where better than in the source reference table.
By adding a bit column ‘IsDefault’ it is simple to mark a particular record as the default, however it is also easy to mark several items as default by mistake.
But with careful use of a filtered unique index it is possible to apply a constraint such that there can be only one default record as below.

Create Unique NonClustered Index IX_UNIQUE_IS_DEFAULT On MyReferenceTable
(
	IsDefault Asc
)
Where (IsDefault=(1))

By filtering, we don’t index the ‘false’ values so that more than one record can be marked as “not the default”, and by making it unique we ensure that we only have a single record marked as default.