SQL Server: how to constrain a table to contain a single row?

You make sure one of the columns can only contain one value, and then make that the primary key (or apply a uniqueness constraint).

CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

I have a number of these tables in various databases, mostly for storing config. It’s a lot nicer knowing that, if the config item should be an int, you’ll only ever read an int from the DB.