Unanswered: Best Yes / No type field in Server Table
I am new to SQL Server. I recently migrated my MS Access tables on SQL Server 2005.
Almost field type I can play with. But the problem arises when Check mark field in MS Access table converted to BIT field when the table was shifted to Server. My Field name is Access was Confirm (Yes / No type). I believe this word is reserve word at Server hence I change it to TConfirm in server table.
Below was runing smoothly in Access but not with server table.
Yes\No is just another word for Boolean in Access. Boolean is another name for Bit. Bit and Yes\ No are logically the same. The only issue is that Booleans in Access can be NULL, 0 or -1. In SQL Server they can be NULL, 0 or 1. However, Access automatically interprets any non-zero value to be -1 when comparing to booleans.
"Not running smoothly" doesn't really help us - you need to specifically tell us what the problem is.
In JET (the default RDBMS for Access), the JET YESNO data type is a synonym for the JET Boolean data type. BIT is another JET synonym of the Boolean datatype. Now I was about to say that Boolean in JET is numeric. However, I'm not certain.
in JET returns all rows with non-zero (or false) values for the boolean yn column.
in JET returns all rows with non-zero values for the numeric num.
It also coerces numeric to boolean.
WHERE yn < false
returns all true, or minus one, values.
My position was that my understanding is that Booleans and numerics in Access can be treated as numerics or booleans. You can, for example, sum Access booleans. My post was limited to Access compared to SQL Server - I suppose you could say I was inaccurate in so far as what I was describing was really a result of Access not being type safe rather than truly reflective of the difference between Bit and Boolean.