> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Yes/No Fields in SQL Server
- URL: https://nolongerset.com/yes-no-fields-in-sql-server/
- Published: 2021-01-05T04:00:22.000Z
- Updated: 2026-05-08T13:07:02.000Z
- Description: The SQL Server equivalent of an Access Yes/No field is the "bit" data type. Or is it?
- Author: Mike Wolfe
- Tags: SQL Server, Migration, #Import 2026-05-20 02:59

"Upsizing" back-end data storage from ACE (.mdb/.accdb files) to SQL Server carries many benefits: improved concurrency, better security, more robust backup options, performance enhancements, etc. But there are some pitfalls you need to be aware of.

One example is "Yes/No" fields. Yes/No fields in Jet/ACE are stored in [1-byte BIT](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/sql-data-types) [fields](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/sql-data-types). Note that the BIT keyword has multiple [aliases](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/equivalent-ansi-sql-data-types) in Access SQL, including BOOLEAN, LOGICAL, LOGICAL1, and YESNO.

These BIT fields can hold one of two values: -1 for True and 0 for False. They cannot contain NULL values, as this classic article from Allen Browne makes clear: [Why I stopped using Yes/No fields](http://allenbrowne.com/NoYesNo.html).

> "JET is quite happy working with Nulls in Number fields. Since Access uses 0 for False, and -1 for True, **use a Number field in place of the Yes/No field**, and JET will process these queries correctly."  
> *\- Allen Browne (emphasis in original)*

### Upsizing Yes/No fields to SQL Server

Most upsizing wizards will convert a Jet/ACE BIT (aka YESNO) field to a SQL Server BIT field. That simplistic approach can lead to problems.

#### "Equivalent" is in the eye of the beholder

In this [list of equivalent data types](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/equivalent-ansi-sql-data-types), the SQL Server **BIT** data type is listed as equivalent to the Access SQL **YESNO** data type. There is one critical difference, though. [SQL Server BIT fields](https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15) return 1 for True, whereas Jet/ACE BIT fields return -1 for True. 

For this reason, you risk introducing problems in your Access application if you simply convert your Access YESNO fields to SQL Server BIT fields. For me, this was a problem when I relied on these fields for sorting purposes, such as showing all active records at the top of a lookup form.

#### Requirements for an equivalent SQL Server field

To match the behavior of a Jet/ACE YESNO field, a SQL Server field needs to meet the following criteria:

1. Only two values: 0 for False and -1 for True
2. Cannot be Null
3. Have a default value (either False or True)

#### T-SQL to create such a field

When adding a YESNO-equivalent field to a table in SQL Server, I use the following T-SQL:

```sql
ALTER TABLE dbo.Customer ADD
 IsActive smallint NOT NULL  
  CONSTRAINT df_Customer_IsActive DEFAULT -1 
  CONSTRAINT ck_Customer_IsActive_YesNo CHECK (IsActive In (0, -1))
```

I use the **smallint** type because it is the [smallest data type](https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15) in terms of storage size that supports both 0 and -1.

Since I want this to mimic the behavior of the Access YESNO type, I make it a required field via the **NOT NULL** keywords.

I then set the default to True in the third line, though you could just as easily set the default to False by switching out the **\-1** for a **0**.

Finally, I restrict the possible values to 0 or -1 via the [Check Constraint](https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-column-constraint-transact-sql?view=sql-server-ver15) in line four. This helps avoid [unexpected behavior](https://nolongerset.com/deceptively-complex-booleans/) when applying bitwise logical operations in VBA to true values other than -1.

#### A note about constraint names

Naming constraints in SQL Server is optional. I always name mine, though, because I script all of my database changes. By naming my constraints, it makes it easier (and more readable) to drop them in later scripts as my database schema evolves.

#### Caveats

Obviously, this approach requires more storage space than using the SQL Server **bit** data type. Every Yes/No field defined as a **smallint** will take up 16 bits of space, while SQL Server **bit** fields may use as little as a single bit of storage space. In most cases, the added simplicity is a good tradeoff.

Additionally, if you drag the table field onto a form in design view, Access will create a text box instead of a check box. The simple workaround is to create an unbound check box first, then assign the field as the ControlSource afterwards. The Allen Browne article I linked to earlier addresses this, too.

*Photo from [pexels.com](https://www.pexels.com/photo/antique-bills-business-cash-210600/)*