Column definition for a SQL Server equivalent to Access BOOLEAN type

Question

The BOOLEAN datatype in Access (ie, Jet/ACE) returns a -1 for True and 0 for False; the field is always required (i.e., it cannot be set to NULL). The SQL Server BIT type returns 1 for True and 0 for False and also allows NULLs.

I'm looking for a T-SQL column definition using a combination of smallint, check constraints, and NOT NULL to replicate the functionality of the Jet/ACE BOOLEAN datatype. In other words, the column would only accept the values 0 and -1.

Answer 1

To address your concerns about BIT:

1. You can set your BIT column to NOT NULL.
2. You can use -1 when setting a BIT column to "true".
3. You can create a view that translates to -1, but +1 should be fine anyway unless your application explicitly checks for the numeric -1 (anything but zero should yield true in your client language).

CREATE TABLE dbo.foo(bar BIT NOT NULL, blat BIT NOT NULL);

INSERT dbo.foo SELECT -1, 0;

SELECT bar, blat, -CONVERT(SMALLINT, bar), -CONVERT(SMALLINT, blat) FROM dbo.foo;

Results:

bar   blat   
----  ----  ----  ----
1     0     -1    0

The nice thing about BIT over TINYINT/SMALLINT is that if you have between 1-8 BIT columns, they can fit into a single byte.

In all of these cases, you still aren't going to be able to say

WHERE NOT BooleanColumn
-- or
WHERE !BooleanColumn

You will still have to say

WHERE BooleanColumn = 0

Answer 2

This will do it:

MyField smallint NOT NULL CONSTRAINT MyField_YesNo CHECK (MyField = 0 OR MyField = -1)