I was asking what the the meaning of BIT value 1 and BIT value of 0 ? Does value 0 (zero) means YES or YES and Does value 1 (one) means NO or FALSE? These are my question that I asked in my earlier posting.
The bit data type is an integer type which can hold the values 0, 1, or NULL. See http://msdn.microsoft.com/en-us/library/aa225961(v=SQL.80).aspx
The bit data type is 'special' in a number of ways:
1. If you assign any non-zero number to it, it is implicitly converted to 1.
2. Storage is optimized: up to eight bit columns can be stored in a single byte of physical storage.
3. You can assign the string values 'true' and 'false' to a bit data type. 'True' will be stored as 1, and 'False' will be stored as 0. (2005 and later only)
By convention, a bit value of 1 is associated with a boolean 'true', and 0 with a boolean 'false'. As Gail points out, this isn't enforced by SQL Server (except by implication as noted in the string assignments above), but it is extremely common.
Most people would see a bit value of 1 as implying 'true', 'on', 'yes' or some other equally 'positive' interpretation. A bit value of 0 is seen as implying 'false', 'off', or 'no'.
Fundamentally, though, the definition of bit is that it holds integer values - which may be either 0 or 1.
Here's an example to illustrate some of the behaviours of the bit data type:
CREATE TABLE #Temp (b BIT NOT NULL);
INSERT #Temp VALUES (1);
--INSERT #Temp VALUES ('True'); -- 2005 onward
INSERT #Temp VALUES (456);
WHERE b = 1;
-- No rows
-- 456 is interpreted as an integer
-- Integer has a higher precedence than BIT
-- So the BIT column value is converted to an integer
-- to make the comparison
WHERE b = 456;
WHERE b = CONVERT(BIT, 456);
DROP TABLE #Temp;
I'm afraid I don't have 2000 installed any more, so I have only been able to test the above on SQL Server 2005 and 2008.
edit: updated to reflect Ron's confirmation of behaviour in SQL 2000