Cody Patterson Cody Patterson - 29 days ago 18
SQL Question

SQL Bit field storing Non-zero character

I understand that bit fields can only store 0 or 1. I have an InfoPath form that I am inheriting and when I parse the XML from the form and store the data in the database, one of the XML nodes has a 1 or 2 and it is getting stored in a bit field. Whether the field has a 1 or 2 in it, the bit field only stores 1. My question is, bit field store a 0 as a 0, but does it also store non-zero characters as a 1? So, is the 2 also getting stored as a 1?

Answer

Yes, non-0 values are inserted as 1.

From MSDN:

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Converting to bit promotes any nonzero value to 1.

Test SQL:

Create Table Test (A bit);

Insert Test Values (-1), (0), (1), (2)

Select * From Test

Results:

A
----
1
0
1
1