Sometimes, I need to quickly test things out in SQL Server with a table. Today, I had a question about a BIT field. I was wondering what would happen if I put the value of 2 in it in an INSERT statement. Remember, BIT columns can stored only the value o (zero) or 1 (one).

I had a feeling that it would result in a 1 value in the database without throwing an error. But, to prove my point, I did this little test:

DECLARE @t AS TABLE (b bit)
INSERT @t (b) VALUES (0)
INSERT @t (b) VALUES (1)
INSERT @t (b) VALUES (2)
SELECT * FROM @t

I didn’t have to create a new table in the system to test it. Instead, I used a TABLE variable. Using a TABLE variable is very convenient. The table goes away as soon as the script is complete and I get my results fast. Here were the results in case you’re curious:

Yep. Inserting that 2 just resulting in another 1.

So, next time you have a question about a value in the database and want to see for yourself exactly what the database will do, play around with the TABLE variable. It’s really easy to do.