Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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
share|improve this answer
    
I'll bow out my answer and plus one this one. I'll keep out of SQL-Server stuff for the time being... –  RolandoMySQLDBA Jul 19 '12 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. –  Aaron Bertrand Jul 19 '12 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 Jul 19 '12 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... –  Aaron Bertrand Jul 19 '12 at 21:48

This will do it:

MyField smallint NOT NULL CONSTRAINT MyField_YesNo CHECK (MyField = 0 OR MyField = -1)
share|improve this answer
    
+1 for use of CHECK constraint –  RolandoMySQLDBA Jul 19 '12 at 21:05
    
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". –  Jon of All Trades Jul 19 '12 at 22:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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