Only allow one checked row in a Column in SQL Server
No

While strictly speaking it can be achieved, it is always a bad idea. In general thinking at restricting properties of sets is a bad road to travel because set property enforcement usually require inspecting the entire set to validate the constraint. In other words enforcing your 'single row check' requires to scan every other row, on every change. This is not only slow, more importantly is the 100% guaranteed recipe for deadlocks.

It would be much better if you post your actual problem rather than ask how to implement the solution you choose.