Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SQL SERVER 2000 DataType Expand / Collapse
Author
Message
Posted Friday, September 10, 2010 11:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
Hi Good Guys,
I am back and need your help. Please help me.

SQL SERVER 2000 Table.
What is the meaning of DataType BIT and also what's the meaning of value 0 and 1.

I feel so helpless about it. Please Help me


Thank you.
Cheers,
Lennie
Post #984249
Posted Saturday, September 11, 2010 3:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #984262
Posted Saturday, September 11, 2010 7:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
GilaMonster (9/11/2010)
A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.


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.
Post #984333
Posted Sunday, September 12, 2010 2:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
Lennie (9/11/2010)
GilaMonster (9/11/2010)
A bit is a numeric data type that stores just two possible numeric, 0 and 1. Those are the mathematical numbers and have no special meaning.


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 ?


Up to you.

Intrinsically in SQL 0 means just 0 and 1 means just 1. It's a bit column, not a boolean. You can assign true/false meanings to the values as you like. It's not MS-Access where the boolean column has such meanings (0 true and 1 false)

Typically, if people do assign such logical meanings to the 1 and 0, 1 is true and 0 false, but it's totally up to you.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #984350
Posted Sunday, September 12, 2010 2:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
It's not up to me. I am just a logical users.
It's up to the creator of the SQL SERVER and it's datatype defination.
I am just a logically progammer and user.
Post #984352
Posted Sunday, September 12, 2010 4:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
There's no need to shout at me, and doing so is not going to get you the response that you apparently want! Yelling at someone because you don't like or don't agree with what they're saying is rude at best.

Now, as I said before, there is NO special meaning to 0 or 1 in the bit type. There is no intrinsic definition of the two in the definition of the data type. If you want to declare a column called Colour as BIT and have 1 mean 'red' and 0 mean 'blue', that is totally up to you.

The only meanings of 0 and 1 in the BIT data type are the mathematical ones.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #984358
Posted Sunday, September 12, 2010 4:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 12:14 AM
Points: 37, Visits: 50
A helper is supposed to assist by providing sample coding instead of saying it's UP TO YOU and try not to help by providing intense information of proposed suggestion on using CASE without defining what sort of category it belongs to
Post #984359
Posted Sunday, September 12, 2010 5:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
I don't understand what you want me to give you.

When you create a BIT column in a table, you must decide what the 1 and 0 mean, if they mean anything more than the mathematical values. There's no 'sample coding' I can give you that will show you anything meaningful, it's the same as creating a status column of type INT and deciding that 1 means 'active', 2 means 'inactive', 3 means 'deleted', 4 means 'invalid', etc.

By convention (and only by convention) when a BIT is used as a True/False flag, 1 is true and 0 is false, but that is solely by convention and not enforced anywhere by SQL Server.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #984363
Posted Sunday, September 12, 2010 6:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
Lennie (9/11/2010)
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);

-- Success
SELECT *
FROM #Temp
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
SELECT *
FROM #Temp
WHERE b = 456;

-- Success
SELECT *
FROM #Temp
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.

Paul

edit: updated to reflect Ron's confirmation of behaviour in SQL 2000




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #984366
Posted Sunday, September 12, 2010 6:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 2:08 PM
Points: 405, Visits: 2,670
Paul White NZ (9/12/2010)

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.

I believe this feature was introduced in SQL Server 2005.
Nicely explained Paul and Gail

Post #984371
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse