SQL Server 2008 data types

Question

I want a column to have only two values. For example I want to make the column active can only contain the values "Y" and "N" I don`t want to use boolean data type.

I`m looking for a way similar to the Look Up Wizard of the MS Access how can this be done?

Answer 1

Use a non-nullable bit

• What if you want J and N for German? Or other languages? This is client formatting
• Ditto "true", "false"
• What about y/Y/n/N? Unicode Ys and Ns?
• You'd need a check constraint to restrict to Y or N: why when you have this anyway with bit?

Finally, SQL Server has no boolean type as such: client code will interpret bit as boolean though

Edit, after comment on question.

If you need to add more values, then I suggest a lookup table and foreign key. This means you can support new values without changing code (CHECK constraint) and/or datatypes.

Answer 2

What you're looking for are Check Constraints e.g.

ALTER TABLE dbo.Vendors ADD CONSTRAINT CK_Vendor_CreditRating
    CHECK (CreditRating >= 1 AND CreditRating <= 5)

Or for you

ALTER TABLE dbo.MyTableName ADD CONSTRAINT CK_MtTable_FieldName_YN
    CHECK (FieldName = 'Y' OR FieldName = 'N')

Answer 3

You could use a varchar(1) or nvarchar(1). Put a constraint on the column in which you state that only Y and N are possible as input to keep data integrity.

Grz, Kris.