Guidance please. We have a small configuration table where we need to ensure only 1 record has bit checked. In the past, we have done check constraint with function that checks count, which we are told causes too much overhead. But someone is suggesting unique index with where clause. I want to make sure I understand all of the pros and cons. Or is there a different better way to achieve this? This is for in house applications. Some people have also said to not bother putting anything in database, just make sure the application prevents it.
Edit: The column is a bit column that will not allow nulls. The table itself should be < 50 rows total. But only 1 can have this particular column marked true.