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 Sunday, September 12, 2010 7:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 17,854, Visits: 15,802
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
Post #984373
Posted Sunday, September 12, 2010 7:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 17,854, Visits: 15,802
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
Post #984375
Posted Sunday, September 12, 2010 8:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #984379
Posted Sunday, September 12, 2010 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 10:58 AM
Points: 5,333, Visits: 25,272
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

Before posting a performance problem please read
Post #984380
Posted Sunday, September 12, 2010 8:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
Thanks very much Ron!



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #984382
Posted Sunday, September 12, 2010 8:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
Hmm. If anyone is wondering why 'Lennie' mentioned CASE before - it's because (s)he asked the same question on another site and has become confused as to who said what where.

It seems Lennie is actually asking how to use the IIF statement in SQL Server to 'decode' a bit column. The answer given on the other site was to use CASE. No example was provided there, so Lennie got a grump on.

Lennie, instead of:

Select SupplierID, SupplierName, 
iif (Status = 1, 'Active', 'NotActive') as [Status]
from TblSupplier

Use:
SELECT  SupplierID, 
SupplierName,
[Status] =
CASE
WHEN [Status] = 1 THEN 'Active'
WHEN [Status] = 0 THEN 'NotActive'
ELSE NULL
END
FROM dbo.tblSupplier;





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #984385
Posted Sunday, September 12, 2010 8:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 17,854, Visits: 15,802
Nice work Paul. That could certainly explain some of it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #984387
Posted Sunday, September 12, 2010 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 8:09 AM
Points: 6,735, Visits: 8,495
just my 2ct:

Bit is context driven !

If your column name doesn't declare it by nature, you should document it well !

e.g. a column named "AreLightsOn" would by nature have a bit value 0 for NO (lights are not ON) and the other value (-1 or 1, depending on you sqlserver version) would be YES (lights are on)

A column named "Gender" would have to be documented which gender code would be used for male and which code for female.

Keep in mind a bit column can be NULL unless declared otherwise.

Up to 7 bit columns can be stored in a byte, but off course the first bit column will add a byte to your row length.


btw: There is no reason to shout at any forum !

In many cases if you don't get the answer you aimed for, or if the answers aren't what you would expect, chances are you malformed the question.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #984390
Posted Sunday, September 12, 2010 9:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:40 AM
Points: 845, Visits: 7,396
GilaMonster (9/12/2010)
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.


Your point about Access is why it is so important to understand that while, as Paul White says
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'.


this won't always be the case. If you're dealing with a table that someone else designed and they were primarily an Access developer before creating this database or if the database was "upsized" to SQL Server from an Access database, then 0 could easily be "true" and 1 would be "false".

This could also be the case if the specs for the database comes from a picky programmer or a business person with no tech experience.

You also have to remember that a bit field can be declared as accepting NULL values. This can complicate matters if the original designer considers NULL a default position and instead of meaning "unknown" it could mean either "true" OR "false".


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #984398
Posted Sunday, September 12, 2010 9:55 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 3:23 PM
Points: 1,453, Visits: 8,269
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 ?

These are my question that I asked in my earlier posting.


What do 0 and 1 mean? They mean whatever the database designer wanted them to represent. If the answers provided are not sufficient for you, then you should be asking the database designer, not us.





Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #984401
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse