SQL SERVER 2000 DataType

Question

SQL SERVER 2000 DataType

Lennie

SSC Eights!

Points: 841
More actions
September 10, 2010 at 11:13 pm

#223815

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 :w00t:
Thank you.
Cheers,
Lennie

Viewing 15 posts - 1 through 15 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, MVP, M.Sc (Comp Sci)
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

Lennie SSC Eights! Points: 841 More actions · Answer 2

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. 😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

Lennie SSC Eights! Points: 841 More actions · Answer 4

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. 😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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, MVP, M.Sc (Comp Sci)
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

Lennie SSC Eights! Points: 841 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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, MVP, M.Sc (Comp Sci)
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

Paul White SSC Guru Points: 150467 More actions · Answer 8

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 9

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:-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Very nicely explained by Gail and Paul.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Lennie (9/12/2010)
A helper is supposed to assist by ...

Odd to instruct others on how you must respond in a forum, when you shout in the forums as you did with the following post:

Lennie (9/12/2010)
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. 😛

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150467 More actions · Answer 12

steve-893342 (9/12/2010)
I believe this feature was introduced in SQL Server 2005.

Thanks Steve - I couldn't remember whether it worked in SQL Server 2000 or not. If anyone has a 2000 install around to test this I'd be grateful - I have a nagging doubt that it worked in 2000 but was undocumented. I may well be wrong about that.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ron McCullough SSC Guru Points: 63877 More actions · Answer 13

Paul White - tried this in Query Analyzer - yup still have SQL 2000 on an older desktop - Ran the following 3 times .. same result each time

CREATE TABLE #Temp (b BIT NOT NULL);

INSERT #Temp VALUES (1);

INSERT #Temp VALUES ('True');

INSERT #Temp VALUES (456);

Result:

(1 row(s) affected)

Server: Msg 245, Level 16, State 1, Line 2

Syntax error converting the varchar value 'True' to a column of data type bit.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 14

Thanks very much Ron! 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi