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.

2 Answers 2


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).

INSERT dbo.foo SELECT -1, 0;

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


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
  • I'll bow out my answer and plus one this one. I'll keep out of SQL-Server stuff for the time being... Commented Jul 19, 2012 at 21:12
  • @RolandoMySQLDBA I don't know that you had to delete your answer; TINYINT is still an option if the OP can be convinced that he/she doesn't need to store -1, but it offers little over BIT because it is unsigned, requires an additional constraint, and can take up more space if there are multiple columns. Commented Jul 19, 2012 at 21:22
  • +1: I'll have to do some quick testing in my front end (MS Access) to see if the 1 vs -1 will be an issue for me. One thing that I have relied on in the past is doing an ORDER BY on a Yes/No column in Access. In ASC order that means the TRUE items show first then the FALSE ones. There may be other considerations as well, but your point about up to 8 BIT columns fitting in a single byte is well taken.
    – mwolfe02
    Commented Jul 19, 2012 at 21:41
  • You can use a fairly simple expression for that: ORDER BY CASE BooleanColumn WHEN 1 THEN -1 ELSE 0 END ... or you could simply add DESC to your order by... Commented Jul 19, 2012 at 21:48

This will do it:

MyField smallint NOT NULL CONSTRAINT MyField_YesNo CHECK (MyField = 0 OR MyField = -1)
  • As discussed by the other answer: the reason to use SMALLINT is that TINYINT is unsigned, so you have to use a 16-bit field you you insist on using -1 for "true". Commented Jul 19, 2012 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.